ODBC: Supported SQL Grammar

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