ODBC: Supported SQL Grammar

The grammar declarations are described with these notations:

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

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 | 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

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 | integer – integer , [users-] | integer – integer | *

valuelist

NULL, valuelist | expression, valuelist | expression | NULL

viewname

identifier

where

<blank> | WHERE boolean | WHERE CURRENT OF cursorname