JDataStore is a feature of JBuilder Professional and Enterprise, and the Inprise Application Server.
This is a description of the SQL dialect supported by the query engine for the JDataStore 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.
<select item list>
aren't 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> ]*
In SQL you can be specify these types by using those names or by using synonym type names, which are more portable to other SQL engines. The possible types are listed in the following table:
You can specify additional attributes for some types. For strings, you can specify a precision and an inline length. 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.
JDataStore supports JDBC Escape sequences for specifying date and time literals, the escape character for a LIKE clause, and OUTER JOINs, and for inserting the results of string or date and time functions into your SQL statement. JDBC Escapes are always enclosed in braces {}. They are used to extend the functionality of SQL.
Date and time literals:
{ T <hh-mm-ss> }Specifies a time, which must be entered in the format indicated; hours, followed by minutes, followed by seconds.
{ D <mm-dd-yy> }Specifies a date, which must be entered in the format indicated; month, followed by day, followed by year.
{ TS <mm-dd-yy : hh-mm-ss> }Specifies a timestamp, which must be entered in the format indicated; month, day, year, colon, hour, minute, second. OUTER JOINs:
{ OJ <join table expression> }An outer join is performed on the specified table expression.
Escape character for LIKE:
{ ESCAPE <char> }The specified character becomes the escape character in the preceding LIKE clause.
Functions are written in the format:
{FN <escape function expression> }
FN
indicates that the function following
it should be performed.
String functions:
CONCAT(string1, string2) // concatenates two strings LCASE(string) // returns the string in lowercase LENGTH(string) // returns the length of the string LOCATE(string1, string2) // locates string1 in string2 LTRIM(string) // trims leading spaces from string RTRIM(string) // trims trailing spaces from string SUBSTRING(string, start, length) // returns a substring of length from the specified string, starting at start UCASE(string) // returns the string in uppercaseDate and time functions:
CURDATE() // returns the current date CURTIME() // returns the current time DAYOFMONTH(date) // extracts the day of the month from the specified date HOUR(time) // extracts the hour from the specified time MINUTE(time) // extracts the minute from the specified time NOW() // returns the current timestamp SECOND(time) // extracts the second from the specified time YEAR(date) // extracts the year from the specified date
INSERT INTO tablename VALUES({D '10-2-3'}, {T '2:55:11'}) SELECT {T '10:24'} FROM tablename SELECT {D '2000-02-01'} FROM tablename SELECT {TS '2000-02-01 10:24:32'} FROM tablenamejoin:
SELECT * FROM {OJ a LEFT JOIN b USING(id)}
specify escape char for LIKE:
SELECT * FROM a WHERE name LIKE '%*%' {ESCAPE '*'}
string functions:
SELECT {FN LCASE('Hello')} FROM tablename SELECT {FN UCASE('Hello')} FROM tablename SELECT {FN LOCATE('xx', '1xx2')} FROM tablename SELECT {FN LTRIM('Hello')} FROM tablename SELECT {FN RTRIM('Hello')} FROM tablename SELECT {FN SUBSTRING('Hello', 3, 2)} FROM tablename SELECT {FN CONCAT('Hello ', 'there.')} FROM tablename
time and date functions:
SELECT {FN NOW()} FROM tablename SELECT {FN CURDATE() } FROM tablename SELECT {FN CURTIME() } FROM tablename SELECT {FN DAYOFMONTH(datecol) } FROM tablename SELECT {FN YEAR(datacol)} FROM tablename SELECT {FN MONTH(datecol)} FROM tablename SELECT {FN HOUR(timecol) } FROM tablename SELECT {FN MINUTE(timecol) } FROM tablename SELECT {FN SECOND(timecol) } FROM tablename
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 JDataStore SQL engine. For maximum portability, don't use identifiers that are treated as keywords in any SQL dialect.
Unquoted SQL identifiers are case-insensitive and treated as uppercase. An identifier can be enclosed in double quotes, which then is treated as case-sensitive. An unquoted identifier must follow these rules:
java.lang.Character
class.
Quoted identifiers can 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> ::= <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
expr
BETWEENleftExpr
ANDrightExpr
is equivalent to the expression:
leftExpr
<=expr
ANDexpr
<=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
<is expression> ::= <scalar expression> IS [NOT] { NULL | TRUE | FALSE | UNKNOWN }
TRUE IS TRUE // evaluates to TRUE FALSE IS NULL // evaluates to FALSE
n
characters where n
>= 0
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%'
evaluates to true if Item contains the string "shoe" anywhere inside it.
Item LIKE 'S__'
evaluates to true if Item is exactly 3 characters long, starting with the letter "S".
Item Like '%*%' ESCAPE '*'
evaluates to true if Item ends with the percent character. The * is defined to escape the two special characters. If it precedes a special character, it's treated as a normal character in the pattern.
The IN
clause indicates a list of values to be matched. Any one of the values in the list will be considered a match for the SELECT
statement containing the IN
clause.
Syntax
<in expression> ::= IN ( <scalar expression commalist> )
Example
SELECT * FROM zodiac WHERE name IN ('leo', 'aquarius') // Returns all records where the name column matches // either "leo" or "aquarius".
<char length function> ::= CHAR_LENGTH ( <scalar expression> ) CHARACTER_LENGTH ( <scalar expression> )
SELECT * from Returns where ReturnDate <= CURRENT_DATE
<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
<lower function> ::= LOWER ( <scalar expression> ) <upper function> ::= UPPER ( <scalar expression> )
<position function> ::= POSITION ( <string> IN <another> )
POSITION('BCD' IN 'ABCDEFG') // yields 2 POSITION('' IN 'ABCDEFG') // yields 1 POSITION('TAG' IN 'ABCDEFG') // yields 0
<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
<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'
CAST
function permits casting one data type to another data type.
Syntax
<cast function> ::= CAST ( <column name> AS <data type> )
Example
SELECT * FROM employee WHERE CAST ( salary AS long ) = 86293 // yields row where salary = 86,292.94
Data manipulation and selection with INSERT, UPDATE, DELETE, and SELECT; but no cursors.
Support for JOINs.
<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>
Optionally, you can specify a default value for each column, along with uniqueness constraints.
You can also optionally specify a primary key. JDataStore supports the use of one or more columns as a primary key.
<create table statement> ::= CREATE TABLE <table name> ( <table element list> ) <table element> ::= <column name> <data type> [ DEFAULT <default value> ] [ NOT NULL ] [ PRIMARY KEY ] [ UNIQUE ] | PRIMARY KEY( <column name list> ) | UNIQUE( <column name list> ) <default value> ::= <literal> | <current date function> <table name> ::= <SQL identifier> <column name> ::= <SQL identifier> <data type> ::= BOOLEAN | CHARACTER [ VARYING ] | CHAR [ VARYING ] | SHORT | LONG | VARCHAR | BIGDECIMAL | INTEGER | INT | STRING | FLOAT | INPUTSTREAM | DOUBLE [ PRECISION ] | OBJECT | SMALLINT | DECIMAL | DEC | DATE | TIME | TIMESTAMP
CREATE TABLE Orders ( CustId INTEGER PRIMARY KEY, Item VARCHAR(30), Amount INT, OrderDate DATE DEFAULT CURRENT_DATE)
Example of creating a table using two columns for the primary key constraint:
create table t1 (c1 INT, c2 STRING, c3 STRING, primary key (c1, c2))
<alter table statement> ::= ALTER TABLE <table name> <column change list> <column change> ::= <add column element> | <drop column element> | <alter column element> <add column element> ::= ADD [COLUMN] <table element> <drop column element> ::= DROP [COLUMN] <column name> <alter column element> ::= ALTER [COLUMN] <column name> SET <default-definition> | DROP DEFAULT
The optional COLUMN keyword is included for SQL compatibility. It has no effect.
ALTER TABLE Orders Add ShipDate DATE, DROP Amount
<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 generates a non-unique, case-sensitive, ascending index on the column "ITEM" of the table "ORDERS":
CREATE INDEX OrderIndex ON Orders (Item ASC)
<select statement> ::= SELECT [ ALL | DISTINCT ] * | < item list> FROM <table expression list> [ WHERE <expression> ] [ GROUP BY <column reference list> ] [ HAVING <expression> ] [ ORDER BY <order item list> ] < item> ::= <expression> [ [AS] <column name> ] <table expression> ::= <table name> [ [AS] <correlation name> ] | <join table expression> <column reference> ::= [ <table qualifier> . ] <column name> <table qualifier> ::= <table name> | <correlation name> <correlation name> ::= <SQL identifier>
SELECT * FROM Orders WHERE Item = 'Shorts'
JOIN table expressions are used to SELECT a result set that combines results from two tables. JOIN can be modified with other keywords depending on the desired result.
CROSS JOIN produces the same result set as "select a.*, b.* from a,b".
ON allows a conditional expression for row selection to be specified. Only rows for which the condition evaluates to true will be selected.
USING allows the tables to be joined using columns which match in both tables. The columns specified in the column list will only be shown in the result set once, followed by the remaining columns from table a, then the remaining columns from table b.
INNER JOIN has the same result as CROSS JOIN if no ON or USING clause is specified. If an ON or USING clause is added, it means that all rows which match the ON or USING clause in both tables will be selected. If no JOIN type is specified, INNER is assumed.
LEFT JOIN, RIGHT JOIN, and FULL JOIN are all OUTER JOINs. Every OUTER JOIN must have one of the keywords NATURAL, ON, or USING.
LEFT JOIN means form "a inner join b" and then append rows of a that didn't contribute, filling in the spaces corresponding to columns in b with nulls.
RIGHT JOIN means form "a inner join b" and then append rows of b that didn't contribute, filling in the columns corresponding to columns in a with nulls.
FULL JOIN means form "a inner join b", but append rows of a or b that didn't contribute, filling in the new columns corresponding with nulls.
NATURAL JOIN is the same as "a JOIN b USING (all of the columns common to both a and b)". When NATURAL is specified, neither ON nor USING can be used, and when ON or USING is specified, NATURAL cannot be used.
Syntax
<join-table-expression> ::= <table-reference> CROSS JOIN <table reference> | <table-reference> [ NATURAL ] [ join-type ] JOIN <table-reference> [ ON <conditional-expression>] [ USING (column-column-list) ] join-type ::= INNER LEFT [ OUTER ] RIGHT [ OUTER ] FULL [ OUTER ]Examples
SELECT * FROM Tinvoice FULL OUTER JOIN Titem USING ("InvoiceNumber") SELECT * FROM Tinvoice LEFT JOIN Titem ON Tinvoice."InvoiceNumber" = Titem."InvoiceNumber" SELECT * FROM Tinvoice NATURAL RIGHT OUTER JOIN Titem SELECT * FROM Tinvoice INNER JOIN Titem USING ("InvoiceNumber") SELECT * FROM Tinvoice JOIN Titem ON Tinvoice."InvoiceNumber" = Titem."InvoiceNumber"
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 it is not present in the GROUP BY reference list.
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
<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 inserts one row each time it is executed. The columns not mentioned are set to their default values. If a column doesn't have a default value, it's set to NULL.
INSERT INTO Orders (CustId, Item) VALUES (?,?)
This statement finds all the orders from the customer with CustId = 123 and inserts the Item of these orders into the table ResTable.
INSERT INTO ResTable SELECT Item from Orders WHERE CustId = 123
<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 disposable underwater cameras to 7.25:
UPDATE UWCameras SET Price = 7.25 WHERE Price > 7.25
<delete statement> ::= DELETE FROM <table name> [ WHERE <expression> ]
DELETE FROM Orders WHERE Item = 'Shorts'
<drop index statement> ::= DROP INDEX <index name> ON <table name>
This deletes the index "ORDERINDEX" on the table "ORDERS":
DROP INDEX OrderIndex ON Orders
<drop table statement> ::= DROP TABLE <table name>
DROP TABLE Orders
jpgpubs@inprise.com
Copyright © 2000, Inprise Corporation. All rights reserved.