Difference between revisions of "ODBC: Supported SQL Grammar"

From Recital Documentation Wiki
Jump to: navigation, search
Line 1: Line 1:
−
=Supported SQL Grammar=
 
−
 
 
The grammar declarations are described with these notations:
 
The grammar declarations are described with these notations:
  
Line 21: Line 19:
 
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::= 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]]
  
−
 
−
====drop====
 
  
 
====aggterm====
 
====aggterm====
−
COUNT (*) | AVG ([expression]]) | MAX ([expression]]) | MIN ([expression]]) | MIN ([expression]]) | SUM ([expression]])
+
COUNT (*) | AVG ([[#expression|expression]]) | MAX ([[#expression|expression]]) | MIN ([[#expression|expression]]) | MIN ([[#expression|expression]]) | SUM ([[#expression|expression]])
  
 
====alias====
 
====alias====
−
AS [aliasname]]
+
AS [[#aliasname|aliasname]]
  
 
====aliasname====
 
====aliasname====
−
[identifier]]
+
[[#identifier|identifier]]
 +
 
 
====alter====
 
====alter====
−
TABLE [tablename]] ADD ([createcols]])
+
TABLE [[#tablename|tablename]] ADD ([[#createcols|createcols]])
 +
 
 
====and====
 
====and====
 
[not]] | [not]] AND [and]]
 
[not]] | [not]] AND [and]]
Line 39: Line 37:
 
<blank> | ASC | DESC
 
<blank> | ASC | DESC
 
====boolean====
 
====boolean====
−
<blank> [and]] | [and]] OR [boolean]]
+
<blank> [and]] | [and]] OR [[#boolean|boolean]]
 
====coldesc====
 
====coldesc====
 
<blank> | DESCRIPTION [string]]
 
<blank> | DESCRIPTION [string]]
 
====colref====
 
====colref====
−
[aliasname]].[columnname]] | [columnname]]
+
[[#aliasname|aliasname]].[columnname]] | [columnname]]
 
====column====
 
====column====
 
[columnname-]
 
[columnname-]
 
====columnlist====
 
====columnlist====
−
[identifier]], [identifier]] | [identifier]]
+
[[#identifier|identifier]], [[#identifier|identifier]] | [[#identifier|identifier]]
 
====columnname====
 
====columnname====
−
[identifier]]
+
[[#identifier|identifier]]
 
====comparison====
 
====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]]
+
([[#boolean|boolean]]) | [colref]] IS NULL | [colref]] IS NOT NULL | [[#expression|expression]] LIKE [pattern]] | [[#expression|expression]] NOT LIKE [pattern]] | [[#expression|expression]] IN {[valuelist]]} | [[#expression|expression]] NOT IN {[valuelist]]} | [[#expression|expression]] [op]] [[#expression|expression]]
 
====create====
 
====create====
−
TABLE [tablename]] ([createcols]]) | 0 [UNIQUE] INDEX [indexname]] ON [tablename]] ([indexcols]])
+
TABLE [[#tablename|tablename]] ([[#createcols|createcols]]) | 0 [UNIQUE] INDEX [indexname]] ON [[#tablename|tablename]] ([indexcols]])
 
====createcol====
 
====createcol====
−
[columnname]] [datatype]] [coldesc]] | [columnname]] [datatype]] ([integer]]) [coldesc]] | [columnname]] [datatype]] ([integer]], [integer]]) [coldesc]]
+
[columnname]] [datatype]] [coldesc]] | [columnname]] [datatype]] ([[#integer|integer]]) [coldesc]] | [columnname]] [datatype]] ([[#integer|integer]], [[#integer|integer]]) [coldesc]]
 
====createcols====
 
====createcols====
−
[createcol]], [createcols]] | [createcol]]
+
[[#createcol|createcol]], [[#createcols|createcols]] | [[#createcol|createcol]]
 
====cursorname====
 
====cursorname====
−
[identifier-]
+
[[#identifier|identifier]]
 
====datatype====
 
====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
 
CHAR | VARCHAR | LONG VARCHAR | DECIMAL | NUMERIC | SMALLINT | INTEGER | REAL | FLOAT | DOUBLE PRECISION | BIT | TINYINT | SHORT | DATE | LOGICAL | MEMO | BYTE | CURRENCY | DATETIME | LONG VARBINARY | ZONED
Line 65: Line 63:
 
a date in ODBC escape clause format (for example, {d'1996-04-04'} or --(*vendor(Microsoft), product(ODBC)d'1996-04-04'*)--
 
a date in ODBC escape clause format (for example, {d'1996-04-04'} or --(*vendor(Microsoft), product(ODBC)d'1996-04-04'*)--
 
====delete====
 
====delete====
−
FROM [tablename]] [where]]
+
FROM [[#tablename|tablename]] [where]]
 
====drop====
 
====drop====
−
TABLE [tablename]] | INDEX [indexname]]
+
TABLE [[#tablename|tablename]] | INDEX [indexname]]
 
====expression====
 
====expression====
−
[expression]] + [times]] | [expression]] - [times]] | [times]]
+
[[#expression|expression]] + [times]] | [[#expression|expression]] - [times]] | [times]]
 
====forupdate====
 
====forupdate====
 
<blank> | FOR UPDATE | FOR UPDATE OF [columnlist]]
 
<blank> | FOR UPDATE | FOR UPDATE OF [columnlist]]
 
====grant====
 
====grant====
−
[privilege]] ON [tablename]] TO [usergroups]]
+
[privilege]] ON [[#tablename|tablename]] TO [usergroups]]
 
====groupby====
 
====groupby====
 
GROUP BY [groupbyterms]]
 
GROUP BY [groupbyterms]]
Line 79: Line 77:
 
[colref]] | [colref]], [groupbyterms]]
 
[colref]] | [colref]], [groupbyterms]]
 
====groups====
 
====groups====
−
[integer]], [groups]] | [integer]] | [integer] – [integer], [groups] | [integer]] – [integer]] | *
+
[[#integer|integer]], [groups]] | [[#integer|integer]] | [[#integer|integer]] – [[#integer|integer]], [groups] | [[#integer|integer]] – [[#integer|integer]] | *
 
====having====
 
====having====
−
<blank> | HAVING [boolean]
+
<blank> | HAVING [[#boolean|boolean]]
 
====identifier====
 
====identifier====
 
an identifier (identifiers containing spaces must be enclosed in double quotes)
 
an identifier (identifiers containing spaces must be enclosed in double quotes)
Line 87: Line 85:
 
[columnname] [ASC | DESC] | [indexcols], [indexcols]
 
[columnname] [ASC | DESC] | [indexcols], [indexcols]
 
====indexname====
 
====indexname====
−
[identifier]
+
[[#identifier|identifier]]
 
====insert====
 
====insert====
−
INTO [tablename-] [insertvals-]
+
INTO [[#tablename|tablename]] [insertvals-]
 
====insertvals====
 
====insertvals====
−
([[columnlist]]) VALUES ([[valuelist]]) | VALUES ([[valuelist]])
+
([[columnlist]]) VALUES ([[#valuelist|valuelist]]) | VALUES ([[#valuelist|valuelist]])
 
====integer====
 
====integer====
 
a non-negative integer
 
a non-negative integer
Line 98: Line 96:
 
====neg====
 
====neg====
 
[term] | + [term] | - [term]
 
[term] | + [term] | - [term]
−
|-
+
====not====
−
|not||[comparison] | NOT [comparison]
+
[comparison] | NOT [comparison]
−
|-
+
====op====
−
|op||> | >= | < | <= | = | <>
+
> | >= | < | <= | = | <>
−
|-
+
====orderby====
−
|orderby||<blank> | ORDER BY [orderbyterms]]
+
<blank> | ORDER BY [orderbyterms]]
−
|-
+
====orderbyterm====
−
|orderbyterm||[colref]] [asc]] | [integer]] [asc]]
+
[colref]] [asc]] | [[#integer|integer]] [asc]]
−
|-
+
====orderbyterms====
−
|orderbyterms||[orderbyterm]] | [orderbyterm]], [orderbyterms]]
+
[orderbyterm]] | [orderbyterm]], [orderbyterms]]
−
|-
+
====pattern====
−
|pattern||[string]] | ? | USER
+
[string]] | ? | USER
−
|-
+
====privilege====
−
|privilege||ALL | ALTER | DELETE | INSERT | READ ONLY [columnlist]] | SELECT [columnlist]] | UPDATE [columnlist]]
+
ALL | ALTER | DELETE | INSERT | READ ONLY [columnlist]] | SELECT [columnlist]] | UPDATE [columnlist]]
−
|-
+
====realnumber====
−
|realnumber||a non-negative real number
+
a non-negative real number
−
|-
+
====recital====
−
|recital||any recital command excluding user interface commands
+
any recital command excluding user interface commands
−
|-
+
====revoke====
−
|revoke||[privilege]] ON [tablename]] FROM [usergroups]]
+
[privilege]] ON [[#tablename|tablename]] FROM [usergroups]]
−
|-
+
====select====
−
|select||[selectcols]] FROM [tablelist]] [where]] [groupby]] [having]] [orderby]] [forupdate]]
+
[selectcols]] FROM [tablelist]] [where]] [groupby]] [having]] [orderby]] [forupdate]]
−
|-
+
====selectallcols====
−
|selectallcols||<blank>| ALL | DISTINCT
+
<blank>| ALL | DISTINCT
−
|-
+
====selectcols====
−
|selectcols||[selectallcols]] * | [selectallcols]] [selectlist]]
+
[selectallcols]] * | [selectallcols]] [selectlist]]
−
|-
+
====selectlist====
−
|selectlist||[expression-]], [selectlist]] | [expression]]
+
[[#expression|expression]], [selectlist]] | [[#expression|expression]]
−
|-
+
====set====
−
|set||[column]] = NULL | [column]] = [simpleterm]]
+
[column]] = NULL | [column]] = [simpleterm]]
−
|-
+
====setlist====
−
|setlist||[set]] | [setlist]], [set]]
+
[set]] | [setlist]], [set]]
−
|-
+
====simpleterm====
−
|simpleterm||[string]] | [realnumber]] | ? | USER | [date]] | [time]] | [timestamp]
+
[string]] | [realnumber]] | ? | USER | [date]] | [time]] | [timestamp]
−
|-
+
====string====
−
|string||a string (enclosed in single quotes)
+
a string (enclosed in single quotes)
−
|-
+
====table====
−
|table||[tablename]] | [aliasname]]
+
[[#tablename|tablename]] | [aliasname]]
−
|-
+
====tablelist====
−
|tablelist||[tableref-], [tablelist-] | [tableref-] | [tableref-] [join-] | [viewname-]
+
[tableref-], [tablelist-] | [tableref-] | [tableref-] [join-] | [viewname-]
−
|-
+
====tablename====
−
|tablename||[identifier-]
+
[[#identifier|identifier]]
−
|-
+
====tableref====
−
|tableref||[tablename-] | [tablename-] [alias-]
+
[[#tablename|tablename]] | [[#tablename|tablename]] [alias-]
−
|-
+
====term====
−
|term||([expression-]) | [colref-] | [simpleterm-] | [aggterm-]
+
([[#expression|expression]]) | [colref-] | [simpleterm-] | [aggterm-]
−
|-
+
====time====
−
|time||a time in ODBC escape clause format (for example,{t'10:19:48'} or --(*vendor(Microsoft), product(ODBC)t'10:19:48'*)--
+
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-] | [times-] / [neg-] | [neg-]
+
[times-] * [neg-] | [times-] / [neg-] | [neg-]
−
|-
+
====timestamp====
−
|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'*)
+
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====
−
|update||[tablename-] SET [setlist-] [where-]
+
[[#tablename|tablename]] SET [setlist-] [where-]
−
|-
+
====usergroup====
−
|usergroup||user and group access control string in the format '[users,groups]'
+
user and group access control string in the format '[users,groups]'
−
|-
+
====usergroups====
−
|usergroups||[usergroup-], [usergroups-] | PUBLIC
+
[usergroup-], [usergroups-] | PUBLIC
−
|-
+
====users====
−
|users||[integer-], [users-] | [integer-] | [integer-] – [integer-] , [users-] | [integer-] – [integer-] | *
+
[[#integer|integer]], [users-] | [[#integer|integer]] | [[#integer|integer]] – [[#integer|integer]] , [users-] | [[#integer|integer]] – [[#integer|integer]] | *
−
|-
+
====valuelist====
−
|valuelist||NULL, [valuelist-] | [expression-], [valuelist-] | [expression-] | NULL
+
NULL, [[#valuelist|valuelist]] | [[#expression|expression]], [[#valuelist|valuelist]] | [[#expression|expression]] | NULL
−
|-
+
====viewname====
−
|viewname||[identifier-]
+
[[#identifier|identifier]]
−
|-
+
====where====
−
|where||<blank> | WHERE [boolean-] | WHERE CURRENT OF [cursorname-]
+
<blank> | WHERE [[#boolean|boolean]] | WHERE CURRENT OF [[#cursorname|cursorname]]
−
|-
+
 
−
|}
+
  
  

Revision as of 11:28, 26 March 2009

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

Retrieved from "https://www.recitalsoftware.com/wiki/index.php?title=ODBC:_Supported_SQL_Grammar&oldid=4402"