Appendix B
SQL reference

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.

Lists in syntax notation

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> ]* 

Data types

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:

Table C.1 Data types supported by DataStore 

DataStore type

Synonyms

Attributes

Bytes of storage

Precision

Range

SHORT

SMALLINT

 

1-3

exact

-32768...32767

INT

INTEGER

 

1-5

exact

-2147483648...
2147483647

LONG

BIGINT

 

1-9

exact

-9223372036854775808...
9223372036854775807

BIGDECIMAL

DECIMAL,
NUMERIC

prec,dec

1-32

exact

-10^72...10^72

FLOAT

REAL

 

1-5

approximate

{+/-}1.4E-45...3.4E38

DOUBLE

DOUBLE PRECISION

 

1-9

approximate

{+/-}4.9E-324...1.8E308

STRING

VARCHAR,
CHAR

prec,inline

1-MAX_INT

n/a

 

BOOLEAN

BIT

 

1

n/a

 

INPUTSTREAM

BINARY

 

1-MAX_INT

n/a

 

OBJECT

 

JavaType

1-MAX_INT

n/a

 

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.

Example

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)

Literals

The following table lists the types of scalar literal values supported:

Table C.2   DataStore SQL literal values 

Type

Details

Examples

String

Strings are enclosed in single quotes. The single quote character is represented by two consecutive single quotes.

'Hello'
'don''t'

Binary number

A binary or hexadecimal seqence enclosed in single quotes and preceded by the letter B or X

B'1011001'
X'F08A'
X'f777'

Exact numeric

A number that may contain a decimal point

8
2.
15.7
.9233

Approximate numeric

A number followed by the letter E, followed with an optionally signed integer

8E0
4E3
0.3E2
6.2E-72

Date

Written in the format:
DATE 'yyyy-mm-dd'

DATE '1999-03-23'

Time

24-hour time written in the format:
TIME 'hh:mm:ss'

TIME '00:46:55'

Timestamp

Written in the format:
TIMESTAMP 'yyyy-mm-dd hh:mm:ss'

TIMESTAMP '2001-12-31 13:15:45'

There is no literal syntax for the OBJECT data type.

Keywords

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.

Table C.3   Keywords for DataStore SQL engine 

ALL

ALTER

AND

AVG

AS

ASC

BETWEEN

BINARY

BIT

BOTH

BY

CASE

CAST

CHAR

CHAR_LENGTH

CHARACTER

CHARACTER_LENGTH

COLUMN

COUNT

CREATE

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

DATE

DAY

DEC

DECIMAL

DEFAULT

DELETE

DESC

DISTINCT

DOUBLE

DROP

ELSE

END

ESCAPE

EXTRACT

FALSE

FLOAT

FOR

FROM

GROUP

HAVING

HOUR

IN

INDEX

INSERT

INTO

INT

INTEGER

IS

KEY

LEADING

NOT

LIKE

LOWER

MAX

MIN

MINUTE

MONTH

NOT

NULL

NUMERIC

ON

OR

ORDER

POSITION

PRECISION

PRIMARY

REAL

SECOND

SELECT

SET

SMALLINT

SUBSTRING

SUM

TABLE

THEN

TIME

TIMESTAMP

TIMEZONEHOUR

TIMEZONEMINUTE

TRAILING

TRIM

TRUE

UNIQUE

UNKNOWN

UPDATE

UPPER

VALUES

VARCHAR

VARYING

WHEN

WHERE

YEAR

Identifiers

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.

Example

Valid identifiers:

customer    // treated as CUSTOMER
Help_me     // treated as HELP_ME
"Hansen"    // treated as Hansen
"DATE"      // treated as DATE
" "         // treated as a single space

Invalid identifiers:

_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"

Expressions

Expression are used throughout the SQL language. They contain several infix operators, and a few prefix operators. Operator precedence from strongest to weakest is:

Syntax

<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

Example

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

Predicates

The following predicates, used in condition expressions, are supported.

BETWEEN

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

Syntax

<between expression> ::=
      <scalar expression> [NOT] BETWEEN <scalar expression> AND <scalar expression>

Example

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

IS

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.

Syntax

<is expression> ::=
      <scalar expression> IS [NOT] { NULL | TRUE | FALSE | UNKNOWN }

Example

TRUE IS TRUE     // evaluates to TRUE
FALSE IS NULL    // evaluates to FALSE

LIKE

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.

Syntax

<like expression> ::=
      <search item> [NOT] LIKE <pattern> [ ESCAPE <escape char> ]

<search item>   ::= <scalar expression>
<pattern>            ::= <scalar expression>
<escape char>  ::= <scalar expression>

Example

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.

Functions

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.

CHAR_LENGTH and CHARACTER_LENGTH

The SQL CHAR_LENGTH and CHARACTER_LENGTH functions will yield the length of a given string.

Syntax

<char length function> ::=
      CHAR_LENGTH ( <scalar expression> )
      CHARACTER_LENGTH ( <scalar expression> )

CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP

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.

Example

SELECT * from Returns where ReturnDate <= CURRENT_DATE

EXTRACT

The SQL EXTRACT function is able to extract parts of date and time values. The expression may be a DATE, TIME or TIMESTAMP value.

Syntax

<extract function> ::=
      EXTRACT ( <extract field> FROM <scalar expression> )

<extract field> ::=
      YEAR
    | MONTH
    | DAY
    | HOUR
    | MINUTE
    | SECOND

Example

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

LOWER and UPPER

The SQL LOWER and UPPER functions will simply yield the given string, converted to all-lowercase or all-uppercase.

Syntax

<lower function> ::=
      LOWER ( <scalar expression> )

<upper function> ::=
      UPPER ( <scalar expression> )

POSITION

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.

Syntax

<position function> ::=
      POSITION ( <string> IN <another> )

Example

POSITION('BCD' IN 'ABCDEFG')     // yields 2
POSITION('' IN 'ABCDEFG')        // yields 1
POSITION('TAG' IN 'ABCDEFG')     // yields 0

SUBSTRING

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.

Syntax

<substring function> ::=
      SUBSTRING ( <string expression> FROM <start pos> [ FOR <length> ] )

Example

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

TRIM

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.

Syntax

<trim function> ::=
      TRIM ( [<trim spec>] [<padding>] [FROM] <scalar expression> )

<trim spec> ::=
      LEADING
    | TRAILING
    | BOTH

<padding> ::= <scalar expression>

Example

TRIM('  Hello world   ')              // yields 'Hello world'
TRIM(LEADING '0' FROM '00000789.75')  // yields '789.75'

Statements

The DataStore JDBC driver supports a subset of the ANSI/ISO SQL-92 standard. In general, it provides:

Syntax

<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>

CREATE TABLE

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.

Syntax

<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>

Example

CREATE TABLE Orders ( CustId INTEGER PRIMARY KEY, Item VARCHAR(30), 
        Amount INT, OrderDate DATE DEFAULT CURRENT_DATE)

ALTER TABLE

This statement adds and removes columns in a table in the DataStore.

Syntax

<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.

Example

ALTER TABLE Orders Add ShipDate DATE, DROP Amount

CREATE INDEX

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.

Syntax

<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]

Example

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

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.

Syntax

<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>

Example

SELECT * FROM Orders WHERE Item = 'Shorts'

GROUP BY and HAVING

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:

Syntax

<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

Example

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.

ORDER BY

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.

Syntax

<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>

Example

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

INSERT

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.

Syntax

<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> ]

Example

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

UPDATE

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.

Syntax

<update statement> ::=
      UPDATE <table name> SET <update assignment list>
    [ WHERE <expression> ]

<update assignment> ::=
      <column reference> = <update expression>

<update expression> ::=
      <scalar expression>
    | DEFAULT
    | NULL

Example

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

DELETE

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.

Syntax

<delete statement> ::=
      DELETE FROM <table name>
    [ WHERE <expression> ]

Example

DELETE FROM Orders WHERE Item = 'Shorts'

DROP INDEX

This statement will delete an index from a table in the DataStore.

Syntax

<drop index statement> ::=
      DROP INDEX <index name> ON <table name>

Example

This will delete the index "ORDERINDEX" on the table "ORDERS":

DROP INDEX OrderIndex ON Orders

DROP TABLE

This statement will delete a table and its indexes from the DataStore.

Syntax

<drop table statement> ::=
      DROP TABLE <table name>

Example

DROP TABLE Orders