Difference between revisions of "ODBC: Supported SQL Grammar"

From Recital Documentation Wiki
Jump to: navigation, search
Line 19: Line 19:
  
  
−
statement::= ALTER [alter-] | CREATE [create-] | DELETE [delete-] | DROP [[#drop|drop-]] | INSERT [insert-] | GRANT [grant-] | RECITAL [recital-] | REVOKE [revoke-] | SELECT [select-] | 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====
 
====drop====
  
−
{| class="wikitable"
+
====aggterm====
−
|aggterm||COUNT (*) | AVG ([expression-]) | MAX ([expression-]) | MIN ([expression-]) | MIN ([expression-]) | SUM ([expression-])
+
COUNT (*) | AVG ([expression]]) | MAX ([expression]]) | MIN ([expression]]) | MIN ([expression]]) | SUM ([expression]])
−
|-
+
 
−
|alias||AS [aliasname-]
+
====alias====
−
|-
+
AS [aliasname]]
−
|aliasname||[identifier-]
+
 
−
|-
+
====aliasname====
−
|alter||TABLE [tablename-] ADD ([createcols-])
+
[identifier]]
−
|-
+
====alter====
−
|and||[not-] | [not-] AND [and-]
+
TABLE [tablename]] ADD ([createcols]])
−
|-
+
====and====
−
|asc||<blank> | ASC | DESC
+
[not]] | [not]] AND [and]]
−
|-
+
====asc====
−
|boolean||<blank> [and-] | [and-] OR [boolean-]
+
<blank> | ASC | DESC
−
|-
+
====boolean====
−
|coldesc||<blank> | DESCRIPTION [string-]
+
<blank> [and]] | [and]] OR [boolean]]
−
|-
+
====coldesc====
−
|colref||[aliasname-].[columnname-] | [columnname-]
+
<blank> | DESCRIPTION [string]]
−
|-
+
====colref====
−
|column||[columnname-]
+
[aliasname]].[columnname]] | [columnname]]
−
|-
+
====column====
−
|columnlist||[identifier-], [identifier-] | [identifier-]
+
[columnname-]
−
|-
+
====columnlist====
−
|columnname||[identifier-]
+
[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-]
+
[identifier]]
−
|-
+
====comparison====
−
|create||TABLE [tablename-] ([createcols-]) | 0 [UNIQUE] INDEX [indexname-] ON [tablename-] ([indexcols-])
+
([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====
−
|createcol||[columnname-] [datatype-] [coldesc-] | [columnname-] [datatype-] ([integer-]) [coldesc-] | [columnname-] [datatype-] ([integer-], [integer-]) [coldesc-]
+
TABLE [tablename]] ([createcols]]) | 0 [UNIQUE] INDEX [indexname]] ON [tablename]] ([indexcols]])
−
|-
+
====createcol====
−
|createcols||[createcol-], [createcols-] | [createcol-]
+
[columnname]] [datatype]] [coldesc]] | [columnname]] [datatype]] ([integer]]) [coldesc]] | [columnname]] [datatype]] ([integer]], [integer]]) [coldesc]]
−
|-
+
====createcols====
−
|cursorname||[identifier-]
+
[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
+
[identifier-]
−
|-
+
====datatype====
−
|date||a date in ODBC escape clause format (for example, {d'1996-04-04'} or --(*vendor(Microsoft), product(ODBC)d'1996-04-04'*)--
+
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====
−
|delete||FROM [tablename-] [where-]
+
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-]
+
FROM [tablename]] [where]]
−
|-
+
====drop====
−
|expression||[expression-] + [times-] | [expression-] - [times-] | [times-]
+
TABLE [tablename]] | INDEX [indexname]]
−
|-
+
====expression====
−
|forupdate||<blank> | FOR UPDATE | FOR UPDATE OF [columnlist-]
+
[expression]] + [times]] | [expression]] - [times]] | [times]]
−
|-
+
====forupdate====
−
|grant||[privilege-] ON [tablename-] TO [usergroups-]
+
<blank> | FOR UPDATE | FOR UPDATE OF [columnlist]]
−
|-
+
====grant====
−
|groupby||GROUP BY [groupbyterms-]
+
[privilege]] ON [tablename]] TO [usergroups]]
−
|-
+
====groupby====
−
|groupbyterms||[colref-] | [colref-], [groupbyterms-]
+
GROUP BY [groupbyterms]]
−
|-
+
====groupbyterms====
−
|groups||[integer-], [groups-] | [integer-] | [integer-] – [integer-], [groups-] | [integer-]– [integer-] | *
+
[colref]] | [colref]], [groupbyterms]]
−
|-
+
====groups====
−
|having||<blank> | HAVING [boolean-]
+
[integer]], [groups]] | [integer]] | [integer] – [integer], [groups] | [integer]] – [integer]] | *
−
|-
+
====having====
−
|identifier||an identifier (identifiers containing spaces must be enclosed in double quotes)
+
<blank> | HAVING [boolean]
−
|-
+
====identifier====
−
|indexcols||[columnname-] [ASC | DESC] | [indexcols-], [indexcols-]
+
an identifier (identifiers containing spaces must be enclosed in double quotes)
−
|-
+
====indexcols====
−
|indexname||[identifier-]
+
[columnname] [ASC | DESC] | [indexcols], [indexcols]
−
|-
+
====indexname====
−
|insert||INTO [tablename-] [insertvals-]
+
[identifier]
−
|-
+
====insert====
−
|insertvals||([columnlist-]) VALUES ([valuelist-]) | VALUES ([valuelist-])
+
INTO [tablename-] [insertvals-]
−
|-
+
====insertvals====
−
|integer||a non-negative integer
+
([[columnlist]]) VALUES ([[valuelist]]) | VALUES ([[valuelist]])
−
|-
+
====integer====
−
|join||INNER JOIN | OUTER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN | CROSS JOIN [tableref-] ON [table-].[column-] = [table-].[column-]
+
a non-negative integer
−
|-
+
====join====
−
|neg||[term-] | + [term-] | - [term-]
+
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-]
+
|not||[comparison] | NOT [comparison]
 
|-
 
|-
 
|op||> | >= | < | <= | = | <>
 
|op||> | >= | < | <= | = | <>
 
|-
 
|-
−
|orderby||<blank> | ORDER BY [orderbyterms-]
+
|orderby||<blank> | ORDER BY [orderbyterms]]
 
|-
 
|-
−
|orderbyterm||[colref-] [asc-] | [integer-] [asc-]
+
|orderbyterm||[colref]] [asc]] | [integer]] [asc]]
 
|-
 
|-
−
|orderbyterms||[orderbyterm-] | [orderbyterm-], [orderbyterms-]
+
|orderbyterms||[orderbyterm]] | [orderbyterm]], [orderbyterms]]
 
|-
 
|-
−
|pattern||[string-] | ? | USER
+
|pattern||[string]] | ? | USER
 
|-
 
|-
−
|privilege||ALL | ALTER | DELETE | INSERT | READ ONLY [columnlist-] | SELECT [columnlist-] | UPDATE [columnlist-]
+
|privilege||ALL | ALTER | DELETE | INSERT | READ ONLY [columnlist]] | SELECT [columnlist]] | UPDATE [columnlist]]
 
|-
 
|-
 
|realnumber||a non-negative real number
 
|realnumber||a non-negative real number
Line 115: Line 117:
 
|recital||any recital command excluding user interface commands
 
|recital||any recital command excluding user interface commands
 
|-
 
|-
−
|revoke||[privilege-] ON [tablename-] FROM [usergroups-]
+
|revoke||[privilege]] ON [tablename]] FROM [usergroups]]
 
|-
 
|-
−
|select||[selectcols-] FROM [tablelist-] [where-] [groupby-] [having-] [orderby-] [forupdate-]
+
|select||[selectcols]] FROM [tablelist]] [where]] [groupby]] [having]] [orderby]] [forupdate]]
 
|-
 
|-
 
|selectallcols||<blank>| ALL | DISTINCT
 
|selectallcols||<blank>| ALL | DISTINCT
 
|-
 
|-
−
|selectcols||[selectallcols-] * | [selectallcols-] [selectlist-]
+
|selectcols||[selectallcols]] * | [selectallcols]] [selectlist]]
 
|-
 
|-
−
|selectlist||[expression-], [selectlist-] | [expression-]
+
|selectlist||[expression-]], [selectlist]] | [expression]]
 
|-
 
|-
−
|set||[column-] = NULL | [column-] = [simpleterm-]
+
|set||[column]] = NULL | [column]] = [simpleterm]]
 
|-
 
|-
−
|setlist||[set-] | [setlist-], [set-]
+
|setlist||[set]] | [setlist]], [set]]
 
|-
 
|-
−
|simpleterm||[string-] | [realnumber-] | ? | USER | [date-] | [time-] | [timestamp-]
+
|simpleterm||[string]] | [realnumber]] | ? | USER | [date]] | [time]] | [timestamp]
 
|-
 
|-
 
|string||a string (enclosed in single quotes)
 
|string||a string (enclosed in single quotes)
 
|-
 
|-
−
|table||[tablename-] | [aliasname-]
+
|table||[tablename]] | [aliasname]]
 
|-
 
|-
 
|tablelist||[tableref-], [tablelist-] | [tableref-] | [tableref-] [join-] | [viewname-]
 
|tablelist||[tableref-], [tablelist-] | [tableref-] | [tableref-] [join-] | [viewname-]

Revision as of 11:09, 26 March 2009

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


drop

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