Dr. DeeBee® ODBC Driver Kit

Supported SQL

The Dr. DeeBee® ODBC Driver Kit supports the following SQL grammar (SQL only supported by the Network, Gold, and Silver Editions is in boldface, SQL only supported by the Network and Gold Editions is in underlined boldface):

statement ::= CREATE create | DROP drop | SELECT select orderby | INSERT insert | DELETE delete | UPDATE update | passthroughSQL
 
passthroughSQL ::= any statement supported by the backend
 
create ::= TABLE tablename ( createcols ) | INDEX indexname ON tablename ( indexcolumns )
 
indexcolumns ::= indexcolumn | indexcolumn , indexcolumns
 
indexcolumn ::= columnname asc
 
createcols ::= createcol , createcols | createcol
 
createcol ::= columnname datatype | columnname datatype ( integer ) | columnname datatype ( integer , integer )
 
drop ::= TABLE tablename | INDEX indexname
 
select ::= selectcols FROM tablelist where groupby having
 
delete ::= FROM tablename where
 
insert ::= INTO tablename insertvals
 
update ::= tablename SET setlist where
 
setlist ::= set | setlist , set
 
set ::= columnname = NULL | columnname = expression
 
insertvals ::= ( columnlist ) VALUES ( valuelist ) | VALUES ( valuelist ) | ( columnlist ) VALUES ( SELECT select ) | VALUES ( SELECT select )
 
columnlist ::= columnname , columnlist | columnname
 
valuelist ::= NULL , valuelist | expression , valuelist | expression | NULL  
 
selectcols ::= selectallcols * | selectallcols selectlist
 
selectallcols ::= | ALL | DISTINCT
 
selectlist ::= selectlistitem , selectlist |
selectlistitem
 
selectlistitem ::= expression | expression aliasname |
expression AS aliasname | aliasname.*
 
where ::= | WHERE boolean
 
having ::= | HAVING boolean
 
boolean ::= and | and OR boolean
 
and ::= not | not AND and
 
not ::= comparison | NOT 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 |
EXISTS ( SELECT select ) | expression op selectop ( SELECT select ) |
expression IN ( SELECT select ) | expression NOT IN ( SELECT select ) |
expression BETWEEN expression AND expression) |
expression NOT BETWEEN expression AND expression)
 
selectop ::= | ALL | ANY
 
op ::= > | >= | < | <= | = | <>
 
pattern ::= string | ? | USER
 
expression ::= expression + times | expression - times |
times
 
times ::= times * neg | times / neg | neg
 
neg ::= term | + term | - term
 
term ::= ( expression ) | colref | simpleterm | aggterm |
scalar
 
scalar ::= scalarescape | scalarshorthand
 
scalarescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) FN fn )*--
 
scalarshorthand ::= { FN fn }
 
fn ::= functionname ( valuelist ) | functionname ( )
 
aggterm ::= COUNT ( * ) | AVG ( expression ) | MAX ( expression ) | MIN ( expression ) | SUM ( expression ) | COUNT ( expression )
 
simpleterm ::= string | realnumber | ? | USER | date | time | timestamp
 
groupby ::= | GROUP BY groupbyterms
 
groupbyterms ::= colref | colref , groupbyterms
 
orderby ::= | ORDER BY orderbyterms
 
orderbyterms ::= orderbyterm | orderbyterm , orderbyterms
 
orderbyterm ::= colref asc | integer asc
 
asc ::= | ASC | DESC
 
colref ::= aliasname . columnname | columnname
 
tablelist ::= tablelistitem , tablelist | tablelistitem
 
tablelistitem ::= tableref | outerjoin
 
outerjoin ::= ojescape | ojshorthand
 
ojescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) OJ oj )*--
 
ojshorthand ::= { OJ oj }
 
oj ::= tableref LEFT OUTER JOIN tableref ON boolean | tableref LEFT OUTER JOIN oj ON boolean
 
tableref ::= tablename | tablename aliasname
 
indexname ::= identifier
 
functionname ::= identifier
 
tablename ::= identifier
 
datatype ::= identifier
 
columnname ::= identifier
 
aliasname ::= identifier
 
identifier ::= an identifier (identifiers containing spaces must be enclosed in double quotes)
 
string ::= a string (enclosed in single quotes)
 
realnumber ::= a non-negative real number (including E notation)
 
integer ::= a non-negative integer
 
date ::= dateescape | dateshorthand
 
dateescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) d dateval )*--
 
dateshorthand ::= { d dateval }
 
dateval ::= a date in yyyy-mm-dd format in single quotes (for example, '1996-02-05')
 
time ::= timeescape | timeshorthand
 
timeescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) t timeval )*--
 
timeshorthand ::= { t timeval }
 
timeval ::= a time in hh:mm:ss format in single quotes (for example, '10:19:48')
 
timestamp ::= timestampescape | timestampshorthand
 
timestampescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) ts timestampval )*--
 
timestampshorthand ::= { ts timestampval }
 
timestampval ::= a timestamp in yyyy-mm-dd hh:mm:ss[.ffffff] format in single quotes (for example, '1996-02-05 10:19:48.529')