Technical information about the ODBC Driver
The following technical information is available for the driver:
Field Mappings:
The supported SQL data types map to the underlying data types as follows:
SQL_CHAR - Character
SQL_LONGVARCHAR - Memo
SQL_DECIMAL - Numeric, Float
SQL_BIT - Logical
SQL_DATE - Date
Technical Limitations:
- Only ODBC Minimum+ Level SQL is supported. See below for a complete listing of supported SQL grammar.
- Only SQL_CHAR, SQL_LONGVARCHAR, SQL_DECIMAL, SQL_BIT, and SQL_DATE are supported.
- Column and table names are not case-sensitive, string data comparisons are case sensitive.
- Transactions are not currently supported.
- Driver is for Windows 95 or Windows NT only. NT 3.51 and NT 4 are supported.
- Qualifiers and owners are not allowed on databases, tables, etc.
SQL Specification
The following is an exact description of the SQL commands supported by the Lyris List Manager ODBC Driver.
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
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 )
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 )
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 }
time ::= 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')
|