Supported SQL Syntax

SELECT Statement
 

SELECT select_expression [, select_expression . . .]
[FROM table_expression
[WHERE general_expression]
[ORDER BY general_expression [ASC | DESC], . . .]
[LIMIT [row_offset,] row_count ] ]

Note: SELECT statements used with a FROM command will retrieve data from the table(s) specified in the table_expression (see below). Usage without a FROM command will return a single row, executing any specified expressions in the select_expression (see below). At least one select_expression is required.

UPDATE Statement
 

UPDATE table_expression
SET column1_name=general_expression [, column2_name=general_expression . . .]
[WHERE where_expression]
[ORDER BY order_expression [ASC | DESC], . . .]
[LIMIT [row_offset,] row_count ]

Note: UPDATE statements used with an ORDER BY command will control the order in which file operations are performed. This can be useful if the order of updating filenames may otherwise cause a name collision with pre-existing files. If the SET command includes any expressions with column names, the value of the field in the currently updating row will be used. LIMIT will constraint which files are updated from the total UPDATE rowset.

INSERT Statement
 

INSERT INTO table_name
[(column1_name, ...)]
{VALUES | VALUE} (general_expression, . . .)

INSERT INTO table_name
SET column1_name=general_expression [, column2_name=general_expression . . .]

Note: INSERT statements may use either syntax shown above. If the first syntax is used without specifying column names, the number of VALUES/VALUE expressions specified must equal the number of columns in the table. For read only fields, the value specified is ignored.

DELETE Statement
 

DELETE {table_name[.*] | *}
FROM table_expression
[WHERE general_expression]
[ORDER BY general_expression [ASC | DESC], . . .]
[LIMIT [row_offset,] row_count ]

Note: DELETE statements used with an ORDER BY command will control the order in which file operations are performed. LIMIT will constraint which files are deleted from the total DELETE rowset.

Select Expressions
 

{general_expression | [table_name.] { * | column_name} } [[AS] alias]

Note: Table and column names may be delineated using the ` character.

Table Expressions
 

{table1_name} [[AS] alias] [, {table2_name} [ [AS] alias] . . .]
[[INNER | LEFT [OUTER] | CROSS] JOIN table_name
[ON general_expression] . . .]

Note: Table and column names may be delineated using the ` character. Comma separated tables specified after the first table in a table_expression before JOIN commands will be treated as CROSS JOINed tables.

General Expression Operators and Functions
 

Literal Operands/Arguments Precedence Description
= binary 1 Assign. Recognized in UPDATE and INSERT statements
= binary 7 Equal. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true)
<> binary 7 Not equal. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true)
> binary 7 Greater than. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true)
>= binary 7 Greater than or equal. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true)
< binary 7 Less than. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true)
<= binary 7 Less than or equal. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true)
LIKE binary 7 String comparison with wildcard matching. '%' matches 0 or more characters. '_' matches 1 character. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true)
+ binary 11 Add. Parses strings to numeric equivalent.
- binary 11 Subtract. Parses strings to numeric equivalent.
* binary 12 Multiply. Parses strings to numeric equivalent.
/ binary 12 Division. Parses strings to numeric equivalent.
% binary 12 Modulo. Parses strings to numeric equivalent.
IS binary 7 Equal (NULL safe). Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true)
IS NOT binary 7 Not equal (NULL safe). Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true)
AND binary 2 Logical AND. Returns boolean value (0 = false, 1 = true)
OR binary 4 Logical OR. Returns boolean value (0 = false, 1 = true)
NOT unary 5 Logical NOT. Returns boolean value (0 = false, 1 = true)
CONCAT Variable Function String concatenation. CONCAT(string1, ....)
CONVERT 2 Function Type conversion. CONVERT(value, type)
LOCATE 2/3 Function Return starting position of substring. LOCATE(substring, full string, [start index])
SUBSTR 2/3 Function Return substring. SUBSTR(string, [start index,] num of chars)

SQL Types

CHAR, VARCHAR, LONG VARCHAR, BINARY, SMALLINT, INTEGER, FLOAT, DOUBLE, TIMESTAMP

Miscellaneous ODBC Support

"{d '1995-01-15'}" style date literals, unnamed parameters via '?' literals, single prepare/multiple execution model with parameter updating, thread-safety. Contact Synthetic Dreams regarding any further ODBC support questions.

© 2022 Synthetic Dreams LLC Back to Top

Back to Top

Follow Us

Twitter icon
Facebook icon
LinkedIn icon

Tweets from the Team