Difference between revisions of "ODBC: Supported SQL Grammar"
From Recital Documentation Wiki
Yvonnemilne (Talk | contribs) |
Yvonnemilne (Talk | contribs) (→statement) |
||
| (10 intermediate revisions by one user not shown) | |||
| Line 1: | Line 1: | ||
| − | |||
| − | |||
The grammar declarations are described with these notations: | The grammar declarations are described with these notations: | ||
| Line 8: | Line 6: | ||
|<blank>||No qualifier is required | |<blank>||No qualifier is required | ||
|- | |- | ||
| − | |<command> | + | |<command> | <command>||Use one of the specified commands |
|- | |- | ||
|[qualifier]||Optional qualifier, may be omitted | |[qualifier]||Optional qualifier, may be omitted | ||
| Line 19: | Line 17: | ||
| − | statement::= ALTER [alter | + | ==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]] | ||
| + | ====aggterm==== | ||
| + | COUNT (*) | AVG ([[#expression|expression]]) | MAX ([[#expression|expression]]) | MIN ([[#expression|expression]]) | MIN ([[#expression|expression]]) | SUM ([[#expression|expression]]) | ||
| + | ====alias==== | ||
| + | AS [[#aliasname|aliasname]] | ||
| + | ====aliasname==== | ||
| + | [[#identifier|identifier]] | ||
| + | ====alter==== | ||
| + | TABLE [[#tablename|tablename]] ADD ([[#createcols|createcols]]) | ||
| + | ====and==== | ||
| + | [[#not|not]] | [[#not|not]] AND [[#and|and]] | ||
| + | ====asc==== | ||
| + | <blank> | ASC | DESC | ||
| + | ====boolean==== | ||
| + | <blank> [[#and|and]] | [[#and|and]] OR [[#boolean|boolean]] | ||
| + | ====coldesc==== | ||
| + | <blank> | DESCRIPTION [[#string|string]] | ||
| + | ====colref==== | ||
| + | [[#aliasname|aliasname]].[[#columnname|columnname]] | [[#columnname|columnname]] | ||
| + | ====column==== | ||
| + | [[#columnname|columnname]] | ||
| + | ====columnlist==== | ||
| + | [[#identifier|identifier]], [[#identifier|identifier]] | [[#identifier|identifier]] | ||
| + | ====columnname==== | ||
| + | [[#identifier|identifier]] | ||
| + | ====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|valuelist]]} | [[#expression|expression]] NOT IN {[[#valuelist|valuelist]]} | [[#expression|expression]] [[#op|op]] [[#expression|expression]] | ||
| + | ====create==== | ||
| + | TABLE [[#tablename|tablename]] ([[#createcols|createcols]]) | 0 [UNIQUE] INDEX [[#indexname|indexname]] ON [[#tablename|tablename]] ([[#indexcols|indexcols]]) | ||
| + | ====createcol==== | ||
| + | [[#columnname|columnname]] [[#datatype|datatype]] [[#coldesc|coldesc]] | [[#columnname|columnname]] [[#datatype|datatype]] ([[#integer|integer]]) [[#coldesc|coldesc]] | [[#columnname|columnname]] [[#datatype|datatype]] ([[#integer|integer]], [[#integer|integer]]) [[#coldesc|coldesc]] | ||
| + | ====createcols==== | ||
| + | [[#createcol|createcol]], [[#createcols|createcols]] | [[#createcol|createcol]] | ||
| + | ====cursorname==== | ||
| + | [[#identifier|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|tablename]] [[#where|where]] | ||
====drop==== | ====drop==== | ||
| + | TABLE [[#tablename|tablename]] | INDEX [[#indexname|indexname]] | ||
| + | ====expression==== | ||
| + | [[#expression|expression]] + [[#times|times]] | [[#expression|expression]] - [[#times|times]] | [[#times|times]] | ||
| + | ====forupdate==== | ||
| + | <blank> | FOR UPDATE | FOR UPDATE OF [[#columnlist|columnlist]] | ||
| + | ====grant==== | ||
| + | [[#privilege|privilege]] ON [[#tablename|tablename]] TO [[#usergroups|usergroups]] | ||
| + | ====groupby==== | ||
| + | GROUP BY [[#groupbyterms|groupbyterms]] | ||
| + | ====groupbyterms==== | ||
| + | [[#colref|colref]] | [[#colref|colref]], [[#groupbyterms|groupbyterms]] | ||
| + | ====groups==== | ||
| + | [[#integer|integer]], [[#groups|groups]] | [[#integer|integer]] | [[#integer|integer]] – [[#integer|integer]], [[#groups|groups]] | [[#integer|integer]] – [[#integer|integer]] | * | ||
| + | ====having==== | ||
| + | <blank> | HAVING [[#boolean|boolean]] | ||
| + | ====identifier==== | ||
| + | an identifier (identifiers containing spaces must be enclosed in double quotes) | ||
| + | ====indexcols==== | ||
| + | [[#columnname|columnname]] [ASC | DESC] | [[#indexcols|indexcols]], [[#indexcols|indexcols]] | ||
| + | ====indexname==== | ||
| + | [[#identifier|identifier]] | ||
| + | ====insert==== | ||
| + | INTO [[#tablename|tablename]] [[#insertvals|insertvals]] | ||
| + | ====insertvals==== | ||
| + | ([[#columnlist|columnlist]]) VALUES ([[#valuelist|valuelist]]) | VALUES ([[#valuelist|valuelist]]) | ||
| + | ====integer==== | ||
| + | a non-negative integer | ||
| + | ====join==== | ||
| + | INNER JOIN | OUTER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN | CROSS JOIN [[#tableref|tableref]] ON [[#table|table]].[[#column|column]] = [[#table|table]].[[#column|column]] | ||
| + | ====neg==== | ||
| + | [[#term|term]] | + [[#term|term]] | - [[#term|term]] | ||
| + | |||
| + | ====not==== | ||
| + | [[#comparison|comparison]] | NOT [[#comparison|comparison]] | ||
| + | |||
| + | ====op==== | ||
| + | > | >= | < | <= | = | <> | ||
| + | ====orderby==== | ||
| + | <blank> | ORDER BY [[#orderbyterms|orderbyterms]] | ||
| + | ====orderbyterm==== | ||
| + | [[#colref|colref]] [[#asc|asc]] | [[#integer|integer]] [[#asc|asc]] | ||
| + | |||
| + | ====orderbyterms==== | ||
| + | [[#orderbyterm|orderbyterm]] | [[#orderbyterm|orderbyterm]], [[#orderbyterms|orderbyterms]] | ||
| + | ====pattern==== | ||
| + | [[#string|string]] | ? | USER | ||
| + | |||
| + | ====privilege==== | ||
| + | ALL | ALTER | DELETE | INSERT | READ ONLY [[#columnlist|columnlist]] | SELECT [[#columnlist|columnlist]] | UPDATE [[#columnlist|columnlist]] | ||
| + | ====realnumber==== | ||
| + | a non-negative real number | ||
| + | ====recital==== | ||
| + | any recital command excluding user interface commands | ||
| + | ====revoke==== | ||
| + | [[#privilege|privilege]] ON [[#tablename|tablename]] FROM [[#usergroups|usergroups]] | ||
| + | ====select==== | ||
| + | [[#selectcols|selectcols]] FROM [[#tablelist|tablelist]] [[#where|where]] [[#groupby|groupby]] [[#having|having]] [[#orderby|orderby]] [[#forupdate|forupdate]] | ||
| + | ====selectallcols==== | ||
| + | <blank>| ALL | DISTINCT | ||
| + | ====selectcols==== | ||
| + | [[#selectallcols|selectallcols]] * | [[#selectallcols|selectallcols]] [[#selectlist|selectlist]] | ||
| + | ====selectlist==== | ||
| + | [[#expression|expression]], [[#selectlist|selectlist]] | [[#expression|expression]] | ||
| + | ====set==== | ||
| + | [[#column|column]] = NULL | [[#column|column]] = [[#simpleterm|simpleterm]] | ||
| + | ====setlist==== | ||
| + | [[#set|set]] | [[#setlist|setlist]], [[#set|set]] | ||
| + | ====simpleterm==== | ||
| + | [[#string|string]] | [[#realnumber|realnumber]] | ? | USER | [[#date|date]] | [[#time|time]] | [[#timestamp|timestamp]] | ||
| + | ====string==== | ||
| + | a string (enclosed in single quotes) | ||
| + | ====table==== | ||
| + | [[#tablename|tablename]] | [[#aliasname|aliasname]] | ||
| + | ====tablelist==== | ||
| + | [[#tableref|tableref]], [[#tablelist|tablelist]] | [[#tableref|tableref]] | [[#tableref|tableref]] [[#join|join]] | [[#viewname|viewname]] | ||
| + | |||
| + | ====tablename==== | ||
| + | [[#identifier|identifier]] | ||
| + | ====tableref==== | ||
| + | [[#tablename|tablename]] | [[#tablename|tablename]] [[#alias|alias]] | ||
| + | ====term==== | ||
| + | ([[#expression|expression]]) | [[#colref|colref]] | [[#simpleterm|simpleterm]] | [[#aggterm|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|times]] * [[#neg|neg]] | [[#times|times]] / [[#neg|neg]] | [[#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|tablename]] SET [[#setlist|setlist]] [[#where|where]] | ||
| + | ====usergroup==== | ||
| + | user and group access control string in the format '[users,groups]' | ||
| + | ====usergroups==== | ||
| + | [[#usergroup|usergroup]], [[#usergroups|usergroups]] | PUBLIC | ||
| + | ====users==== | ||
| + | [[#integer|integer]], [[#users|users]] | [[#integer|integer]] | [[#integer|integer]] – [[#integer|integer]] , [[#users|users]] | [[#integer|integer]] – [[#integer|integer]] | * | ||
| + | ====valuelist==== | ||
| + | NULL, [[#valuelist|valuelist]] | [[#expression|expression]], [[#valuelist|valuelist]] | [[#expression|expression]] | NULL | ||
| + | ====viewname==== | ||
| + | [[#identifier|identifier]] | ||
| + | ====where==== | ||
| + | <blank> | WHERE [[#boolean|boolean]] | WHERE CURRENT OF [[#cursorname|cursorname]] | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
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 |
Contents
- 1 statement
- 1.1 aggterm
- 1.2 alias
- 1.3 aliasname
- 1.4 alter
- 1.5 and
- 1.6 asc
- 1.7 boolean
- 1.8 coldesc
- 1.9 colref
- 1.10 column
- 1.11 columnlist
- 1.12 columnname
- 1.13 comparison
- 1.14 create
- 1.15 createcol
- 1.16 createcols
- 1.17 cursorname
- 1.18 datatype
- 1.19 date
- 1.20 delete
- 1.21 drop
- 1.22 expression
- 1.23 forupdate
- 1.24 grant
- 1.25 groupby
- 1.26 groupbyterms
- 1.27 groups
- 1.28 having
- 1.29 identifier
- 1.30 indexcols
- 1.31 indexname
- 1.32 insert
- 1.33 insertvals
- 1.34 integer
- 1.35 join
- 1.36 neg
- 1.37 not
- 1.38 op
- 1.39 orderby
- 1.40 orderbyterm
- 1.41 orderbyterms
- 1.42 pattern
- 1.43 privilege
- 1.44 realnumber
- 1.45 recital
- 1.46 revoke
- 1.47 select
- 1.48 selectallcols
- 1.49 selectcols
- 1.50 selectlist
- 1.51 set
- 1.52 setlist
- 1.53 simpleterm
- 1.54 string
- 1.55 table
- 1.56 tablelist
- 1.57 tablename
- 1.58 tableref
- 1.59 term
- 1.60 time
- 1.61 times
- 1.62 timestamp
- 1.63 update
- 1.64 usergroup
- 1.65 usergroups
- 1.66 users
- 1.67 valuelist
- 1.68 viewname
- 1.69 where
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
alter
TABLE tablename ADD (createcols)
and
asc
<blank> | ASC | DESC
boolean
coldesc
<blank> | DESCRIPTION string
colref
aliasname.columnname | columnname
column
columnlist
identifier, identifier | identifier
columnname
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
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
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
groups
integer, groups | integer | integer – integer, groups | integer – integer | *
having
<blank> | HAVING boolean
identifier
an identifier (identifiers containing spaces must be enclosed in double quotes)
indexcols
columnname [ASC | DESC] | indexcols, indexcols
indexname
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
not
comparison | NOT comparison
op
> | >= | < | <= | = | <>
orderby
<blank> | ORDER BY orderbyterms
orderbyterm
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
simpleterm
string | realnumber | ? | USER | date | time | timestamp
string
a string (enclosed in single quotes)
table
tablelist
tableref, tablelist | tableref | tableref join | viewname
tablename
tableref
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
usergroup
user and group access control string in the format '[users,groups]'
usergroups
usergroup, usergroups | PUBLIC
users
integer, users | integer | integer – integer , users | integer – integer | *
valuelist
NULL, valuelist | expression, valuelist | expression | NULL
viewname
where
<blank> | WHERE boolean | WHERE CURRENT OF cursorname
