Supported SQL Syntax
SELECT Statement
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
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
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
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
Note: Table and column names may be delineated using the ` character.
Table Expressions
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.
