While this manual does not attempt to describe SQL syntax, the questions surrounding the following special ODBC syntax arise frequently enough to bear mentioning here. Consult your ODBC reference for detailed information on these topics.
Reserved ODBC SQL Words
While using the IDLdbDatabase::ExecuteSQL method in Dataminer, do not use the following reserved words in the SQL command string: DOUBLE, FLOAT, and TEMP. These words are reserved in ODBC SQL and result in syntax errors if you attempt to use them in your SQL code.
Date, Time, and Timestamp Data
Because there are a wide variety of date and time formats in use by different databases, ODBC uses a special clause in the SQL statement to identify dates and times. The syntax is:
|
Syntax |
Format |
|
{d 'value'} |
yyyy-mm-dd |
|
{t 'value'} |
hh:mm:ss |
|
{ts 'value'} |
yyyy-mm-dd hh:mm:ss
|
For example, to use a date-and-time timestamp, the SQL statement might look something like:
select time from events where time > { ts '1997-01-16 08:50:43' }
Scalar Functions
Scalar functions (string length, absolute value, or date, for example) require a special clause. To call a scalar function when selecting a result set, use syntax like:
{fn scalar-function}
where scalar-function is the name of the scalar function you are calling. For example, calling the UCASE function on a field might look something like this:
SELECT { fn UCASE(NAME) } FROM employee
Converting Data
ODBC provides a scalar function that requests that the data source convert data from one SQL data type to another. The syntax is:
{ fn CONVERT(value_expression, data_type) }
where value_expression is the name of a column from a table, a literal value, or the result of another scalar function, and data_type is one of ODBC’s defined data types.
LIKE Predicate Escape Characters
When using an SQL LIKE predicate, the percent character (%) and the underscore character (_) have special meanings. You can include these characters as literals in a LIKE predicate by using an escape clause, which has the following syntax:
{ escape 'escape-character' }
where escape-character is a character used in front of the special character to force evaluation with its literal value.
For example, since the percent character matches zero or more of any character when used in a LIKE predicate, the string '%AAA%' would match any number any character, followed by three “A”s, followed by any number of any character. Using an escape clause in the LIKE predicate allows you to use the literal “%” in the string. For example:
select name where name like '\%AAA%' { escape '\' }
selects names that include the percent character, followed by three “A”s, followed by any number of any character. The backslash ( \ ) is used to “escape” the percent character.
Outer Joins
ODBC supports the ANSI SQL-92 left outer join syntax. The syntax is:
{ oj outer-join }
where outer-join is:
table-reference LEFT OUTER JOIN
{ table-reference | outer-join } ON search-condition
Consult your ODBC documentation for further details on outer joins.
Procedure Calls
An application can call a procedure in place of an SQL statement. The syntax for a procedure call is:
{ [?=] call procedure-name[([parameter],[parameter],...)] }
where procedure-name specifies the name of a procedure (stored on the data source) and parameters are parameters of the procedure.
Consult your ODBC documentation for further details on procedure calls.