Difference between revisions of "ODBC: Supported SQL Grammar"

From Recital Documentation Wiki
Jump to: navigation, search
 
(→‎statement)
 
(14 intermediate revisions by one user not shown)
Line 1: Line 1:
−
=Supported SQL Grammar=
 
−
 
 
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>||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 19: Line 17:
  
  
−
statement::= ALTER [alter-] | CREATE [create-] | DELETE [delete-] | DROP [drop-] | INSERT [insert-] | GRANT [grant-] | RECITAL [recital-] | REVOKE [revoke-] | SELECT [select-] | 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]]
  
  
−
{| class="wikitable"
+
====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]] | [[#not|not]] AND [[#and|and]]
−
|-
+
====asc====
−
|asc||<blank> | ASC | DESC
+
<blank> | ASC | DESC
−
|-
+
====boolean====
−
|boolean||<blank> [and-] | [and-] OR [boolean-]
+
<blank> [[#and|and]] | [[#and|and]] OR [[#boolean|boolean]]
−
|-
+
====coldesc====
−
|coldesc||<blank> | DESCRIPTION [string-]
+
<blank> | DESCRIPTION [[#string|string]]
−
|-
+
====colref====
−
|colref||[aliasname-].[columnname-] | [columnname-]
+
[[#aliasname|aliasname]].[[#columnname|columnname]] | [[#columnname|columnname]]
−
|-
+
====column====
−
|column||[columnname-]
+
[[#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|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-] ([createcols-]) | 0 [UNIQUE] INDEX [indexname-] ON [tablename-] ([indexcols-])
+
TABLE [[#tablename|tablename]] ([[#createcols|createcols]]) | 0 [UNIQUE] INDEX [[#indexname|indexname]] ON [[#tablename|tablename]] ([[#indexcols|indexcols]])
−
|-
+
====createcol====
−
|createcol||[columnname-] [datatype-] [coldesc-] | [columnname-] [datatype-] ([integer-]) [coldesc-] | [columnname-] [datatype-] ([integer-], [integer-]) [coldesc-]
+
[[#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====
−
|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
−
|-
+
====date====
−
|date||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|where]]
−
|-
+
====drop====
−
|drop||TABLE [tablename-] | INDEX [indexname-]
+
TABLE [[#tablename|tablename]] | INDEX [[#indexname|indexname]]
−
|-
+
====expression====
−
|expression||[expression-] + [times-] | [expression-] - [times-] | [times-]
+
[[#expression|expression]] + [[#times|times]] | [[#expression|expression]] - [[#times|times]] | [[#times|times]]
−
|-
+
====forupdate====
−
|forupdate||<blank> | FOR UPDATE | FOR UPDATE OF [columnlist-]
+
<blank> | FOR UPDATE | FOR UPDATE OF [[#columnlist|columnlist]]
−
|-
+
====grant====
−
|grant||[privilege-] ON [tablename-] TO [usergroups-]
+
[[#privilege|privilege]] ON [[#tablename|tablename]] TO [[#usergroups|usergroups]]
−
|-
+
====groupby====
−
|groupby||GROUP BY [groupbyterms-]
+
GROUP BY [[#groupbyterms|groupbyterms]]
−
|-
+
====groupbyterms====
−
|groupbyterms||[colref-] | [colref-], [groupbyterms-]
+
[[#colref|colref]] | [[#colref|colref]], [[#groupbyterms|groupbyterms]]
−
|-
+
====groups====
−
|groups||[integer-], [groups-] | [integer-] | [integer-] – [integer-], [groups-] | [integer-]– [integer-] | *
+
[[#integer|integer]], [[#groups|groups]] | [[#integer|integer]] | [[#integer|integer]] – [[#integer|integer]], [[#groups|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)
−
|-
+
====indexcols====
−
|indexcols||[columnname-] [ASC | DESC] | [indexcols-], [indexcols-]
+
[[#columnname|columnname]] [ASC | DESC] | [[#indexcols|indexcols]], [[#indexcols|indexcols]]
−
|-
+
====indexname====
−
|indexname||[identifier-]
+
[[#identifier|identifier]]
−
|-
+
====insert====
−
|insert||INTO [tablename-] [insertvals-]
+
INTO [[#tablename|tablename]] [[#insertvals|insertvals]]
−
|-
+
====insertvals====
−
|insertvals||([columnlist-]) VALUES ([valuelist-]) | VALUES ([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-] 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||[comparison-] | NOT [comparison-]
+
====not====
−
|-
+
[[#comparison|comparison]] | NOT [[#comparison|comparison]]
−
|op||> | >= | < | <= | = | <>
+
 
−
|-
+
====op====
−
|orderby||<blank> | ORDER BY [orderbyterms-]
+
> | >= | < | <= | = | <>
−
|-
+
====orderby====
−
|orderbyterm||[colref-] [asc-] | [integer-] [asc-]
+
<blank> | ORDER BY [[#orderbyterms|orderbyterms]]
−
|-
+
====orderbyterm====
−
|orderbyterms||[orderbyterm-] | [orderbyterm-], [orderbyterms-]
+
[[#colref|colref]] [[#asc|asc]] | [[#integer|integer]] [[#asc|asc]]
−
|-
+
 
−
|pattern||[string-] | ? | USER
+
====orderbyterms====
−
|-
+
[[#orderbyterm|orderbyterm]] | [[#orderbyterm|orderbyterm]], [[#orderbyterms|orderbyterms]]
−
|privilege||ALL | ALTER | DELETE | INSERT | READ ONLY [columnlist-] | SELECT [columnlist-] | UPDATE [columnlist-]
+
====pattern====
−
|-
+
[[#string|string]] | ? | USER
−
|realnumber||a non-negative real number
+
 
−
|-
+
====privilege====
−
|recital||any recital command excluding user interface commands
+
ALL | ALTER | DELETE | INSERT | READ ONLY [[#columnlist|columnlist]] | SELECT [[#columnlist|columnlist]] | UPDATE [[#columnlist|columnlist]]
−
|-
+
====realnumber====
−
|revoke||[privilege-] ON [tablename-] FROM [usergroups-]
+
a non-negative real number
−
|-
+
====recital====
−
|select||[selectcols-] FROM [tablelist-] [where-] [groupby-] [having-] [orderby-] [forupdate-]
+
any recital command excluding user interface commands
−
|-
+
====revoke====
−
|selectallcols||<blank>| ALL | DISTINCT
+
[[#privilege|privilege]] ON [[#tablename|tablename]] FROM [[#usergroups|usergroups]]
−
|-
+
====select====
−
|selectcols||[selectallcols-] * | [selectallcols-] [selectlist-]
+
[[#selectcols|selectcols]] FROM [[#tablelist|tablelist]] [[#where|where]] [[#groupby|groupby]] [[#having|having]] [[#orderby|orderby]] [[#forupdate|forupdate]]
−
|-
+
====selectallcols====
−
|selectlist||[expression-], [selectlist-] | [expression-]
+
<blank>| ALL | DISTINCT
−
|-
+
====selectcols====
−
|set||[column-] = NULL | [column-] = [simpleterm-]
+
[[#selectallcols|selectallcols]] * | [[#selectallcols|selectallcols]] [[#selectlist|selectlist]]
−
|-
+
====selectlist====
−
|setlist||[set-] | [setlist-], [set-]
+
[[#expression|expression]], [[#selectlist|selectlist]] | [[#expression|expression]]
−
|-
+
====set====
−
|simpleterm||[string-] | [realnumber-] | ? | USER | [date-] | [time-] | [timestamp-]
+
[[#column|column]] = NULL | [[#column|column]] = [[#simpleterm|simpleterm]]
−
|-
+
====setlist====
−
|string||a string (enclosed in single quotes)
+
[[#set|set]] | [[#setlist|setlist]], [[#set|set]]
−
|-
+
====simpleterm====
−
|table||[tablename-] | [aliasname-]
+
[[#string|string]] | [[#realnumber|realnumber]] | ? | USER | [[#date|date]] | [[#time|time]] | [[#timestamp|timestamp]]
−
|-
+
====string====
−
|tablelist||[tableref-], [tablelist-] | [tableref-] | [tableref-] [join-] | [viewname-]
+
a string (enclosed in single quotes)
−
|-
+
====table====
−
|tablename||[identifier-]
+
[[#tablename|tablename]] | [[#aliasname|aliasname]]
−
|-
+
====tablelist====
−
|tableref||[tablename-] | [tablename-] [alias-]
+
[[#tableref|tableref]], [[#tablelist|tablelist]] | [[#tableref|tableref]] | [[#tableref|tableref]] [[#join|join]] | [[#viewname|viewname]]
−
|-
+
 
−
|term||([expression-]) | [colref-] | [simpleterm-] | [aggterm-]
+
====tablename====
−
|-
+
[[#identifier|identifier]]
−
|time||a time in ODBC escape clause format (for example,{t'10:19:48'} or --(*vendor(Microsoft), product(ODBC)t'10:19:48'*)--
+
====tableref====
−
|-
+
[[#tablename|tablename]] | [[#tablename|tablename]] [[#alias|alias]]
−
|times||[times-] * [neg-] | [times-] / [neg-] | [neg-]
+
====term====
−
|-
+
([[#expression|expression]]) | [[#colref|colref]] | [[#simpleterm|simpleterm]] | [[#aggterm|aggterm]]
−
|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'*)
+
====time====
−
|-
+
a time in ODBC escape clause format (for example,{t'10:19:48'} or --(*vendor(Microsoft), product(ODBC)t'10:19:48'*)--
−
|update||[tablename-] SET [setlist-] [where-]
+
====times====
−
|-
+
[[#times|times]] * [[#neg|neg]] | [[#times|times]] / [[#neg|neg]] | [[#neg|neg]]
−
|usergroup||user and group access control string in the format '[users,groups]'
+
====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'*)
−
|usergroups||[usergroup-], [usergroups-] | PUBLIC
+
====update====
−
|-
+
[[#tablename|tablename]] SET [[#setlist|setlist]] [[#where|where]]
−
|users||[integer-], [users-] | [integer-] | [integer-] – [integer-] , [users-] | [integer-] – [integer-] | *
+
====usergroup====
−
|-
+
user and group access control string in the format '[users,groups]'
−
|valuelist||NULL, [valuelist-] | [expression-], [valuelist-] | [expression-] | NULL
+
====usergroups====
−
|-
+
[[#usergroup|usergroup]], [[#usergroups|usergroups]] | PUBLIC
−
|viewname||[identifier-]
+
====users====
−
|-
+
[[#integer|integer]], [[#users|users]] | [[#integer|integer]] | [[#integer|integer]] – [[#integer|integer]] , [[#users|users]] | [[#integer|integer]] – [[#integer|integer]] | *
−
|where||<blank> | WHERE [boolean-] | WHERE CURRENT OF [cursorname-]
+
====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


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 | 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=4411"