This is a description of the SQL dialect supported by the query engine for the DataStore JDBC driver. The methods in the JDBC API that take an SQL statement are:
int Statement.executeUpdate(String query) ResultSet Statement.executeQuery(String query) PreparedStatement Connection.prepare(String query)
Each query string must contain exactly one SQL statement. Use executeQuery for SELECT statements, and updateQuery for all other queries. If there are parameter markers in the query, a PreparedStatement must be used to pass the actual parameters.
A number of syntax element names ending with the word "list"; for example:
<select item list>
are not defined. These are to be read as a comma-separated list with at least one element; in this case:
<select item list> ::= <select item> [ , <select item> ]*
The following data types are supported in the DataStore:
In SQL these types can be specified using those names or by using synonym type names, which are more portable to other SQL engines. The possible types are given by the following table:
For some types additional attributes may be applied. For strings a precision and an inline length may be specified. The precision is the maximum number of bytes allocated to such a string, and the inline length is the maximum number of bytes a string can take before the string is treated as a large object (which yields slower performance) similar to the BINARY types. The default inline value is 64.
A string with a maximum size of 30 bytes, and any string over 10 bytes is stored in a separate stream for large objects:
CHAR(30,10)
A string with a maximum size of 30 bytes, never inlined (precision is less than default inline value of 64):
CHAR(30)
A string with no length limit, use default inline size:
CHAR
A BigDecimal with 2 decimals and space for at least 5 significant digits:
DECIMAL(5,2)
A BigDecimal with 0 decimals and space for at least 4 significant digits:
NUMERIC(4)
A BigDecimal with 0 decimals and space for at least 72 significant digits:
NUMERIC
Any Java object that is serializable:
OBJECT
Only Java strings using Java serialization:
OBJECT(java.lang.String)
The following table lists the types of scalar literal values supported:
There is no literal syntax for the OBJECT data type.
This list contains all identifiers reserved for keywords in this version of the DataStore SQL engine. All keywords are case-insensitive; for example, select
, SELECT
, and SeLeCT
are all considered to be the keyword SELECT.
Note that not all SQL-92 keywords are treated as a keyword by the DataStore SQL engine. For maximum portability do not use identifiers that are treated as keywords in any SQL dialect.
Unquoted SQL identifiers are case-insensitive and treated as uppercase. An identifier may be enclosed in double quotes, which then is treated as case-sensitive. An unquoted identifier must follow these rules:
Quoted identifiers may contain any character string including spaces, symbols, keywords, etc.
customer // treated as CUSTOMER Help_me // treated as HELP_ME "Hansen" // treated as Hansen "DATE" // treated as DATE " " // treated as a single space
_order // must start with a character date // date is a keyword borland.com // dots are not allowed
This list denotes the very same identifier, they are all treated as "ORDER":
order Order oRDeR "ORDER"
Expression are used throughout the SQL language. They contain several infix operators, and a few prefix operators. Operator precedence from strongest to weakest is:
<expression> ::= <scalar expression> | <conditional expression> <conditional expression> ::= <expression> OR <expression> | <expression> AND <expression> | NOT <expression> | <scalar expression> <compare operator> <scalar expression> | <scalar expression> [NOT] BETWEEN <scalar expression> | <scalar expression> [NOT] LIKE <scalar expression> [ ESCAPE <scalar expression> ] | <scalar expression> [NOT] IS { NULL | TRUE | FALSE | UNKNOWN } <compare operator> ::= = | <> | < | > | <= | >= <scalar expression> ::= <scalar expression> {+ | - | * | / | <concat> } <scalar expression> | {+ | -} <scalar expression> | ( <expression> ) | <column reference> | <literal> | <aggregator function> | <function> | <parameter marker> <concat> ::= | | <function> ::= <substring function> | <position function> | <trim function> | <extract function> | <lower function> | <upper function> | <char length function> | <current date function> <current date function> ::= CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP
Select the calculated value of amount times price from the orders table, for a to-be-provided customer for orders in January:
SELECT Amount * Price FROM Orders WHERE CustId = ? AND EXTRACT(MONTH FROM Ordered) = 1
The following predicates, used in condition expressions, are supported.
The BETWEEN predicate defines an inclusive range of values. The result of:
expr BETWEEN leftExpr AND rightExpr
is equivalent to the expression:
leftExpr <= expr AND expr <= rightExpr
<between expression> ::= <scalar expression> [NOT] BETWEEN <scalar expression> AND <scalar expression>
Select all the orders where a customer has orders between 3 and 7 items of the same kind:
SELECT * from Orders WHERE Amount BETWEEN 3 AND 7
The IS predicate is defined to test expressions. Any expression may evaluate to the value NULL, but conditional expressions may evaluate to one of the three the values: TRUE, FALSE, UNKNOWN. UNKNOWN is equivalent with NULL for conditional expressions. Note that for a SELECT query with a WHERE clause, only rows that evaluate to TRUE will be included. If the expression evaluates to FALSE or UNKNOWN it will not be included. The output of the IS predicate can have two results: TRUE or FALSE.
<is expression> ::= <scalar expression> IS [NOT] { NULL | TRUE | FALSE | UNKNOWN }
TRUE IS TRUE // evaluates to TRUE FALSE IS NULL // evaluates to FALSE
The LIKE predicate provides SQL with simple string pattern matching. The search item, pattern, and escape character (if given) must all evaluate to strings. The pattern may include the special "wildcard" characters _ and % where:
The escape character, if given, allows the two special wildcard characters to be included in the search pattern. The pattern match is case-sensitive. Use the LOWER or UPPER functions on the search item for a case-insensitive match.
<like expression> ::= <search item> [NOT] LIKE <pattern> [ ESCAPE <escape char> ] <search item> ::= <scalar expression> <pattern> ::= <scalar expression> <escape char> ::= <scalar expression>
Item LIKE '%shoe%'
will evaluate to true if Item contains the string "shoe" anywhere inside it.
Item LIKE 'S__'
will evaluate to true if Item is exactly 3 characters long, starting with the letter "S".
Item Like '%*%' ESCAPE '*'
will evaluate to true if Item ends with the percent character. The * is defined to escape the two special characters; if it precedes one, the special character is treated as a normal character in the pattern.
Note that functions that act on strings will work for strings of any length. Large strings are stored as blobs, so you may want to define large text fields as CHAR to enable searches.
The SQL CHAR_LENGTH and CHARACTER_LENGTH functions will yield the length of a given string.
<char length function> ::= CHAR_LENGTH ( <scalar expression> ) CHARACTER_LENGTH ( <scalar expression> )
These SQL functions will yield the current date and/or time. If the functions are placed more than once in a statement, they will all yield the same result when the statement is executed.
SELECT * from Returns where ReturnDate <= CURRENT_DATE
The SQL EXTRACT function is able to extract parts of date and time values. The expression may be a DATE, TIME or TIMESTAMP value.
<extract function> ::= EXTRACT ( <extract field> FROM <scalar expression> ) <extract field> ::= YEAR | MONTH | DAY | HOUR | MINUTE | SECOND
EXTRACT(MONTH FROM DATE '1999-05-17') // yields 5 EXTRACT(HOUR FROM TIME '18:00:00') // yields 18 EXTRACT(HOUR FROM DATE '1999-05-17') // yields an exception
The SQL LOWER and UPPER functions will simply yield the given string, converted to all-lowercase or all-uppercase.
<lower function> ::= LOWER ( <scalar expression> ) <upper function> ::= UPPER ( <scalar expression> )
The SQL POSITION function returns the position of a string within another string. If any of the arguments evaluates to NULL, the result is NULL.
<position function> ::= POSITION ( <string> IN <another> )
POSITION('BCD' IN 'ABCDEFG') // yields 2 POSITION('' IN 'ABCDEFG') // yields 1 POSITION('TAG' IN 'ABCDEFG') // yields 0
The SQL SUBSTRING function extracts a substring from a given string. If any of the operands are NULL the result is NULL. The start position indicates the first character position where the substring is taken, where 1 indicates the first character. If the FOR part is present it indicates the length of the resulting string.
<substring function> ::= SUBSTRING ( <string expression> FROM <start pos> [ FOR <length> ] )
SUBSTRING('ABCDEFG' FROM 2 FOR 3) // yields 'BCD' SUBSTRING('ABCDEFG' FROM 4) // yields 'DEFG' SUBSTRING('ABCDEFG' FROM 10) // yields '' SUBSTRING('ABCDEFG' FROM -6 FOR 3) // yields 'ABC' SUBSTRING('ABCEDFG' FROM 2 FOR -1) // raises an exception
The SQL TRIM function is able to remove leading and/or trailing padding characters from a given string. The <padding> must be a string of length 1, which is the character that is removed from the string.
<trim function> ::= TRIM ( [<trim spec>] [<padding>] [FROM] <scalar expression> ) <trim spec> ::= LEADING | TRAILING | BOTH <padding> ::= <scalar expression>
TRIM(' Hello world ') // yields 'Hello world' TRIM(LEADING '0' FROM '00000789.75') // yields '789.75'
The DataStore JDBC driver supports a subset of the ANSI/ISO SQL-92 standard. In general, it provides:
<SQL statement> ::= <data definition statement> | <data manipulation statement> <data definition statement> ::= <create table statement> | <alter table statement> | <drop table statement> | <create index statement> | <drop index statement> <data manipulation statement> ::= <select statement> | <delete statement> | <insert statement> | <update statement>
This statement will create a table in the DataStore. A column name and data type must be defined for each column.
Optionally, a default value may be specified for each column, along with uniqueness constraints.
<create table statement> ::= CREATE TABLE <table name> ( <table element list> ) <table element> ::= <column name> <data type> [ DEFAULT <default value> ] [ NOT NULL ] [ PRIMARY KEY ] [ UNIQUE ] <default value> ::= <literal> | <current date function> <table name> ::= <SQL identifier> <column name> ::= <SQL identifier>
CREATE TABLE Orders ( CustId INTEGER PRIMARY KEY, Item VARCHAR(30), Amount INT, OrderDate DATE DEFAULT CURRENT_DATE)
This statement adds and removes columns in a table in the DataStore.
<alter table statement> ::= ALTER TABLE <table name> <column change list> <column change> ::= <add column element> | <drop column element> <add column element> ::= ADD [COLUMN] <table element> <drop column element> ::= DROP [COLUMN] <column name>
The optional COLUMN keyword is included for SQL compatibility; it has no effect.
ALTER TABLE Orders Add ShipDate DATE, DROP Amount
This statement will create an index for a table in the DataStore. Each column may be specified to be ordered in ascending or descending order. Default is ascending order.
<create index statement> ::= CREATE [UNIQUE] [CASEINSENSITIVE] INDEX <index name> ON <table name> ( <index element list> ) <index name> ::= <SQL Identifier> <index element> ::= <column name> [DESC | ASC]
This will generate a non-unique, case-sensitive, ascending index on the column "ITEM" of the table "ORDERS":
CREATE INDEX OrderIndex ON Orders (Item ASC)
SELECT statements are used to retrieve data from one or more tables. The optional keyword DISTINCT eliminates duplicate rows from the result of a SELECT statement. The keyword ALL, which is the default, will get all rows including duplicates. The data may optionally be grouped or sorted.
<select statement> ::= SELECT [ ALL | DISTINCT ] * | < item list> FROM <table reference list> [ WHERE <expression> ] [ GROUP BY <column reference list> ] [ HAVING <expression> ] [ ORDER BY <order item list> ] < item> ::= <expression> [ [AS] <column name> ] <table reference> ::= <table name> [ [AS] <correlation name> ] <column reference> ::= [ <table qualifier> . ] <column name> <table qualifier> ::= <table name> | <correlation name> <correlation name> ::= <SQL identifier>
SELECT * FROM Orders WHERE Item = 'Shorts'
Aggregate functions can be used to calculate summary values from the data in a table. The WHERE clause (if present) will limit the number of rows included in the summary. If no GROUP BY clause is present, a summary for the whole table is calculated. If a GROUP BY clause is present, a summary will be computed for each unique set of values for the columns listed in the GROUP BY. Then if the HAVING clause is present, it will filter out complete groups given the conditional expression in the HAVING clause.
Summary queries have additional rules for where columns can appear in expressions:
<select statement> ::= SELECT [ ALL | DISTINCT ] * | < item list> FROM <table reference list> [ WHERE <expression> ] [ GROUP BY <column reference list> ] [ HAVING <expression> ] [ ORDER BY <order item list> ] <aggregator function> ::= <aggregator name> ( <scalar expression> ) | COUNT ( * ) <aggregator name> ::= AVG | SUM | MIN | MAX | COUNT
SELECT SUM(Amount * Price) FROM Orders
yields a single row with the total value of all orders
SELECT COUNT(Amount) FROM Orders WHERE CustId = 123
yields a single row with the number of orders where Amount is non-NULL for the customer 123
SELECT CustId, SUM(Amount * Price), COUNT(Amount) WHERE CustId < 200 GROUP BY CustId
yields a set of rows, with the sum of the value of all orders grouped by customers for the customers with an ID number less than 200.
SELECT CustId, SUM(Amount * Price), COUNT(Amount) GROUP BY CustId HAVING SUM(Amount * Price) > 500000
yields a set of big customers with the value of all their orders
SELECT CustId, COUNT(23 + SUM(Amount)) GROUP BY CustId
Illegal: nested aggregators present
SELECT CustId, SUM(Amount* Price) GROUP BY Amount
Illegal: the column CustId is referenced in the select item list, but is not present in the GROUP BY reference list.
The output of a SELECT statement is usually a set of unordered rows of data. The ORDER BY clause can be used to sort the data before it is retrieved. Each ordering factor can be:
Specify DESC to order in descending order. If neither ASC or DESC is specified ASC (ascending) is assumed.
<select statement> ::= SELECT [ ALL | DISTINCT ] * | < item list> FROM <table reference list> [ WHERE <expression> ] [ GROUP BY <column reference list> ] [ HAVING <expression> ] [ ORDER BY <order item list> ] <order item> ::= <order part> [ ASC | DESC ] <order part> ::= <integer literal> | <column name> | <expression>
SELECT Item FROM Orders ORDER BY 1 DESC
orders by the first column, the Item column.
SELECT CustId, Amount*Price+500.00 AS CALC FROM Orders ORDER BY CALC
orders by the calculated column CALC
SELECT CustId, Amount FROM Orders ORDER BY Amount*Price
orders by the expression given
SELECT CustId FROM Orders ORDER BY 2
Illegal: ordinal outside the range
An INSERT statement will insert rows into a table in the DataStore. A list of columns with associated values are listed in the INSERT statement. Columns that are not listed in the statement will be set to their default values. The values given are either a list of expressions or a SELECT expression. A SELECT expression has the same syntax as a SELECT statement, except that an ORDER BY clause is not accepted. The resulting set of rows from the SELECT statement will be inserted in the target table.
<insert statement> ::= INSERT INTO <table name> ( <column name list> ) [ <table expression> | DEFAULT VALUES ] <table expression> ::= VALUES ( <scalar expression list> ) | <select statement> <select statement> ::= SELECT [ ALL | DISTINCT ] * | < item list> FROM <table reference list> [ WHERE <expression> ] [ GROUP BY <column reference list> ] [ HAVING <expression> ]
This statement should be used in connection with a PreparedStatement in JDBC. It will insert one row each time it is executed. The columns not mentioned will be set to their default value. If a column doesn't have a default value it will be set to NULL.
INSERT INTO Orders (CustId, Item) VALUES (?,?)
This statement will find all the orders from the customer with CustId = 123, and insert the Item of these orders into the table ResTable.
INSERT INTO ResTable SELECT Item from Orders WHERE CustId = 123
An UPDATE statement is used to modify existing data. The columns that are changed by the statement are listed explicitly. All the rows where the WHERE clause evaluates to TRUE are changed. If no WHERE clause is given all rows in the table are changed.
<update statement> ::= UPDATE <table name> SET <update assignment list> [ WHERE <expression> ] <update assignment> ::= <column reference> = <update expression> <update expression> ::= <scalar expression> | DEFAULT | NULL
All orders from the customer 123 are changed to orders from the customer 500:
UPDATE Orders SET CustId = 500 WHERE CustId = 123
Increase the amount of all orders in the table:
UPDATE Orders SET Amount = Amount + 1
Reprice all underwater stock options to 2.25:
UPDATE Options SET Price = 2.25 WHERE Price > 2.25
A DELETE statement will delete rows from a table in the DataStore. If no WHERE clause is given, all the rows will be deleted. Otherwise only the rows that match the WHERE expression will be deleted.
<delete statement> ::= DELETE FROM <table name> [ WHERE <expression> ]
DELETE FROM Orders WHERE Item = 'Shorts'
This statement will delete an index from a table in the DataStore.
<drop index statement> ::= DROP INDEX <index name> ON <table name>
This will delete the index "ORDERINDEX" on the table "ORDERS":
DROP INDEX OrderIndex ON Orders
This statement will delete a table and its indexes from the DataStore.
<drop table statement> ::= DROP TABLE <table name>
DROP TABLE Orders
pubsweb@borland.com
Copyright © 1999, Inprise Corporation. All rights reserved.