Difference between revisions of "ODBC: Supported SQL Grammar"

From Recital Documentation Wiki
Jump to: navigation, search
(statement)
 
(7 intermediate revisions by one user not shown)
Line 6: Line 6:
 
|<blank>||No qualifier is required
 
|<blank>||No qualifier is required
 
|-
 
|-
|<command> | <command>||Use one of the specified commands
+
|<command> &#124; <command>||Use one of the specified commands
 
|-
 
|-
 
|[qualifier]||Optional qualifier, may be omitted
 
|[qualifier]||Optional qualifier, may be omitted
Line 17: Line 17:
  
  
statement::= ALTER [[#alter|alter]] | CREATE [[#create|create]] | DELETE [[#delete|delete]] | DROP [[#drop|drop]] | INSERT [[#insert|insert]] | GRANT [[#grant|grant]] | RECITAL [[#recital|recital]] | REVOKE [[#revoke|revoke]] | SELECT [[#select|select]] | UPDATE [[#update|update]]
+
==statement==
 +
[[#statement|statement]]::= ALTER [[#alter|alter]] | CREATE [[#create|create]] | DELETE [[#delete|delete]] | DROP [[#drop|drop]] | INSERT [[#insert|insert]] | GRANT [[#grant|grant]] | RECITAL [[#recital|recital]] | REVOKE [[#revoke|revoke]] | SELECT [[#select|select]] | UPDATE [[#update|update]]
  
  
Line 45: Line 46:
 
[[#identifier|identifier]]
 
[[#identifier|identifier]]
 
====comparison====
 
====comparison====
([[#boolean|boolean]]) | [[#colref|colref]] IS NULL | [[#colref|colref]] IS NOT NULL | [[#expression|expression]] LIKE [[#pattern|pattern]] | [[#expression|expression]] NOT LIKE [[#pattern|pattern]] | [[#expression|expression]] IN {[valuelist]]} | [[#expression|expression]] NOT IN {[[#valuelist|valuelist]]} | [[#expression|expression]] [[#op|op]] [[#expression|expression]]
+
([[#boolean|boolean]]) | [[#colref|colref]] IS NULL | [[#colref|colref]] IS NOT NULL | [[#expression|expression]] LIKE [[#pattern|pattern]] | [[#expression|expression]] NOT LIKE [[#pattern|pattern]] | [[#expression|expression]] IN {[[#valuelist|valuelist]]} | [[#expression|expression]] NOT IN {[[#valuelist|valuelist]]} | [[#expression|expression]] [[#op|op]] [[#expression|expression]]
 
====create====
 
====create====
 
TABLE [[#tablename|tablename]] ([[#createcols|createcols]]) | 0 [UNIQUE] INDEX [[#indexname|indexname]] ON [[#tablename|tablename]] ([[#indexcols|indexcols]])
 
TABLE [[#tablename|tablename]] ([[#createcols|createcols]]) | 0 [UNIQUE] INDEX [[#indexname|indexname]] ON [[#tablename|tablename]] ([[#indexcols|indexcols]])
Line 63: Line 64:
 
TABLE [[#tablename|tablename]] | INDEX [[#indexname|indexname]]
 
TABLE [[#tablename|tablename]] | INDEX [[#indexname|indexname]]
 
====expression====
 
====expression====
[[#expression|expression]] + [[#times|times]] | [[#expression|expression]] - [times]] | [times]]
+
[[#expression|expression]] + [[#times|times]] | [[#expression|expression]] - [[#times|times]] | [[#times|times]]
 
====forupdate====
 
====forupdate====
 
<blank> | FOR UPDATE | FOR UPDATE OF [[#columnlist|columnlist]]
 
<blank> | FOR UPDATE | FOR UPDATE OF [[#columnlist|columnlist]]
 
====grant====
 
====grant====
[privilege]] ON [[#tablename|tablename]] TO [usergroups]]
+
[[#privilege|privilege]] ON [[#tablename|tablename]] TO [[#usergroups|usergroups]]
 
====groupby====
 
====groupby====
GROUP BY [groupbyterms]]
+
GROUP BY [[#groupbyterms|groupbyterms]]
 
====groupbyterms====
 
====groupbyterms====
 
[[#colref|colref]] | [[#colref|colref]], [[#groupbyterms|groupbyterms]]
 
[[#colref|colref]] | [[#colref|colref]], [[#groupbyterms|groupbyterms]]
 
====groups====
 
====groups====
[[#integer|integer]], [groups]] | [[#integer|integer]] | [[#integer|integer]] – [[#integer|integer]], [groups] | [[#integer|integer]] – [[#integer|integer]] | *
+
[[#integer|integer]], [[#groups|groups]] | [[#integer|integer]] | [[#integer|integer]] – [[#integer|integer]], [[#groups|groups]] | [[#integer|integer]] – [[#integer|integer]] | *
 
====having====
 
====having====
 
<blank> | HAVING [[#boolean|boolean]]
 
<blank> | HAVING [[#boolean|boolean]]
Line 79: Line 80:
 
an identifier (identifiers containing spaces must be enclosed in double quotes)
 
an identifier (identifiers containing spaces must be enclosed in double quotes)
 
====indexcols====
 
====indexcols====
[columnname] [ASC | DESC] | [indexcols], [indexcols]
+
[[#columnname|columnname]] [ASC | DESC] | [[#indexcols|indexcols]], [[#indexcols|indexcols]]
 
====indexname====
 
====indexname====
 
[[#identifier|identifier]]
 
[[#identifier|identifier]]
 
====insert====
 
====insert====
INTO [[#tablename|tablename]] [insertvals-]
+
INTO [[#tablename|tablename]] [[#insertvals|insertvals]]
 
====insertvals====
 
====insertvals====
([[columnlist]]) VALUES ([[#valuelist|valuelist]]) | VALUES ([[#valuelist|valuelist]])
+
([[#columnlist|columnlist]]) VALUES ([[#valuelist|valuelist]]) | VALUES ([[#valuelist|valuelist]])
 
====integer====
 
====integer====
 
a non-negative integer
 
a non-negative integer
 
====join====
 
====join====
INNER JOIN | OUTER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN | CROSS JOIN [[#tableref|tableref]] ON [[table]].[[column]] = [[table]].[[column]]
+
INNER JOIN | OUTER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN | CROSS JOIN [[#tableref|tableref]] ON [[#table|table]].[[#column|column]] = [[#table|table]].[[#column|column]]
 
====neg====
 
====neg====
[term] | + [term] | - [term]
+
[[#term|term]] | + [[#term|term]] | - [[#term|term]]
 +
 
 
====not====
 
====not====
[comparison] | NOT [comparison]
+
[[#comparison|comparison]] | NOT [[#comparison|comparison]]
 +
 
 
====op====
 
====op====
 
> | >= | < | <= | = | <>
 
> | >= | < | <= | = | <>
 
====orderby====
 
====orderby====
<blank> | ORDER BY [orderbyterms]]
+
<blank> | ORDER BY [[#orderbyterms|orderbyterms]]
 
====orderbyterm====
 
====orderbyterm====
[[#colref|colref]] [asc]] | [[#integer|integer]] [asc]]
+
[[#colref|colref]] [[#asc|asc]] | [[#integer|integer]] [[#asc|asc]]
 +
 
 
====orderbyterms====
 
====orderbyterms====
[orderbyterm]] | [orderbyterm]], [orderbyterms]]
+
[[#orderbyterm|orderbyterm]] | [[#orderbyterm|orderbyterm]], [[#orderbyterms|orderbyterms]]
 
====pattern====
 
====pattern====
[string]] | ? | USER
+
[[#string|string]] | ? | USER
 +
 
 
====privilege====
 
====privilege====
ALL | ALTER | DELETE | INSERT | READ ONLY [columnlist]] | SELECT [columnlist]] | UPDATE [columnlist]]
+
ALL | ALTER | DELETE | INSERT | READ ONLY [[#columnlist|columnlist]] | SELECT [[#columnlist|columnlist]] | UPDATE [[#columnlist|columnlist]]
 
====realnumber====
 
====realnumber====
 
a non-negative real number
 
a non-negative real number
Line 111: Line 116:
 
any recital command excluding user interface commands
 
any recital command excluding user interface commands
 
====revoke====
 
====revoke====
[privilege]] ON [[#tablename|tablename]] FROM [usergroups]]
+
[[#privilege|privilege]] ON [[#tablename|tablename]] FROM [[#usergroups|usergroups]]
 
====select====
 
====select====
[selectcols]] FROM [tablelist]] [where]] [groupby]] [having]] [orderby]] [forupdate]]
+
[[#selectcols|selectcols]] FROM [[#tablelist|tablelist]] [[#where|where]] [[#groupby|groupby]] [[#having|having]] [[#orderby|orderby]] [[#forupdate|forupdate]]
 
====selectallcols====
 
====selectallcols====
 
<blank>| ALL | DISTINCT
 
<blank>| ALL | DISTINCT
 
====selectcols====
 
====selectcols====
[selectallcols]] * | [selectallcols]] [selectlist]]
+
[[#selectallcols|selectallcols]] * | [[#selectallcols|selectallcols]] [[#selectlist|selectlist]]
 
====selectlist====
 
====selectlist====
[[#expression|expression]], [selectlist]] | [[#expression|expression]]
+
[[#expression|expression]], [[#selectlist|selectlist]] | [[#expression|expression]]
 
====set====
 
====set====
[column]] = NULL | [column]] = [simpleterm]]
+
[[#column|column]] = NULL | [[#column|column]] = [[#simpleterm|simpleterm]]
 
====setlist====
 
====setlist====
[set]] | [setlist]], [set]]
+
[[#set|set]] | [[#setlist|setlist]], [[#set|set]]
 
====simpleterm====
 
====simpleterm====
[string]] | [realnumber]] | ? | USER | [date]] | [time]] | [timestamp]
+
[[#string|string]] | [[#realnumber|realnumber]] | ? | USER | [[#date|date]] | [[#time|time]] | [[#timestamp|timestamp]]
 
====string====
 
====string====
 
a string (enclosed in single quotes)
 
a string (enclosed in single quotes)
 
====table====
 
====table====
[[#tablename|tablename]] | [aliasname]]
+
[[#tablename|tablename]] | [[#aliasname|aliasname]]
 
====tablelist====
 
====tablelist====
[[#tableref|tableref]], [tablelist-] | [[#tableref|tableref]] | [[#tableref|tableref]] [join-] | [viewname-]
+
[[#tableref|tableref]], [[#tablelist|tablelist]] | [[#tableref|tableref]] | [[#tableref|tableref]] [[#join|join]] | [[#viewname|viewname]]
 +
 
 
====tablename====
 
====tablename====
 
[[#identifier|identifier]]
 
[[#identifier|identifier]]

Latest revision as of 12:41, 26 March 2009

The grammar declarations are described with these notations:

Notation Description
<blank> No qualifier is required
<command> | <command> Use one of the specified commands
[qualifier] Optional qualifier, may be omitted
(paramval) Required parameter value
expression Numeric or string calculation


statement

statement::= ALTER alter | CREATE create | DELETE delete | DROP drop | INSERT insert | GRANT grant | RECITAL recital | REVOKE revoke | SELECT select | UPDATE update


aggterm

COUNT (*) | AVG (expression) | MAX (expression) | MIN (expression) | MIN (expression) | SUM (expression)

alias

AS aliasname

aliasname

identifier

alter

TABLE tablename ADD (createcols)

and

not | not AND and

asc

<blank> | ASC | DESC

boolean

<blank> and | and OR boolean

coldesc

<blank> | DESCRIPTION string

colref

aliasname.columnname | columnname

column

columnname

columnlist

identifier, identifier | identifier

columnname

identifier

comparison

(boolean) | colref IS NULL | colref IS NOT NULL | expression LIKE pattern | expression NOT LIKE pattern | expression IN {valuelist} | expression NOT IN {valuelist} | expression op expression

create

TABLE tablename (createcols) | 0 [UNIQUE] INDEX indexname ON tablename (indexcols)

createcol

columnname datatype coldesc | columnname datatype (integer) coldesc | columnname datatype (integer, integer) coldesc

createcols

createcol, createcols | createcol

cursorname

identifier

datatype

CHAR | VARCHAR | LONG VARCHAR | DECIMAL | NUMERIC | SMALLINT | INTEGER | REAL | FLOAT | DOUBLE PRECISION | BIT | TINYINT | SHORT | DATE | LOGICAL | MEMO | BYTE | CURRENCY | DATETIME | LONG VARBINARY | ZONED

date

a date in ODBC escape clause format (for example, {d'1996-04-04'} or --(*vendor(Microsoft), product(ODBC)d'1996-04-04'*)--

delete

FROM tablename where

drop

TABLE tablename | INDEX indexname

expression

expression + times | expression - times | times

forupdate

<blank> | FOR UPDATE | FOR UPDATE OF columnlist

grant

privilege ON tablename TO usergroups

groupby

GROUP BY groupbyterms

groupbyterms

colref | colref, groupbyterms

groups

integer, groups | integer | integerinteger, groups | integerinteger | *

having

<blank> | HAVING boolean

identifier

an identifier (identifiers containing spaces must be enclosed in double quotes)

indexcols

columnname [ASC | DESC] | indexcols, indexcols

indexname

identifier

insert

INTO tablename insertvals

insertvals

(columnlist) VALUES (valuelist) | VALUES (valuelist)

integer

a non-negative integer

join

INNER JOIN | OUTER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN | CROSS JOIN tableref ON table.column = table.column

neg

term | + term | - term

not

comparison | NOT comparison

op

> | >= | < | <= | = | <>

orderby

<blank> | ORDER BY orderbyterms

orderbyterm

colref asc | integer asc

orderbyterms

orderbyterm | orderbyterm, orderbyterms

pattern

string | ? | USER

privilege

ALL | ALTER | DELETE | INSERT | READ ONLY columnlist | SELECT columnlist | UPDATE columnlist

realnumber

a non-negative real number

recital

any recital command excluding user interface commands

revoke

privilege ON tablename FROM usergroups

select

selectcols FROM tablelist where groupby having orderby forupdate

selectallcols

<blank>| ALL | DISTINCT

selectcols

selectallcols * | selectallcols selectlist

selectlist

expression, selectlist | expression

set

column = NULL | column = simpleterm

setlist

set | setlist, set

simpleterm

string | realnumber | ? | USER | date | time | timestamp

string

a string (enclosed in single quotes)

table

tablename | aliasname

tablelist

tableref, tablelist | tableref | tableref join | viewname

tablename

identifier

tableref

tablename | tablename alias

term

(expression) | colref | simpleterm | aggterm

time

a time in ODBC escape clause format (for example,{t'10:19:48'} or --(*vendor(Microsoft), product(ODBC)t'10:19:48'*)--

times

times * neg | times / neg | neg

timestamp

a timestamp in ODBC escape clause format (for example, {ts'1996-04-04 10:19:48.529'} or --(*vendor(Microsoft), product(ODBC)ts'1996-04-04 10:19:54.529'*)

update

tablename SET setlist where

usergroup

user and group access control string in the format '[users,groups]'

usergroups

usergroup, usergroups | PUBLIC

users

integer, users | integer | integerinteger , users | integerinteger | *

valuelist

NULL, valuelist | expression, valuelist | expression | NULL

viewname

identifier

where

<blank> | WHERE boolean | WHERE CURRENT OF cursorname