SQL Syntax

Hypersonic SQL currently supports the following SQL statements and syntax:

SELECT
Expression
CALL
Stored Procedures / Functions List
INSERT
UPDATE
DELETE
CREATE TABLE
DROP TABLE
Datatypes
CREATE INDEX
DROP INDEX
CREATE ALIAS
SET AUTOCOMMIT
COMMIT
ROLLBACK
CONNECT
DISCONNECT
CREATE USER
DROP USER
GRANT
REVOKE
SET PASSWORD
SET REFERENTIAL_INTEGRITY
SET WRITE_DELAY
CHECKPOINT
SCRIPT
SET IGNORECASE
SET LOGSIZE
SHUTDOWN
Comments


Alphabetical list:


CALL
CHECKPOINT
COMMIT
CONNECT
CREATE ALIAS
CREATE INDEX
CREATE TABLE
CREATE USER
DELETE
DISCONNECT
DROP INDEX
DROP TABLE
DROP USER
GRANT
INSERT
REVOKE
ROLLBACK
SCRIPT
SELECT
SET AUTOCOMMIT
SET IGNORECASE
SET LOGSIZE
SET PASSWORD
SET REFERENTIAL_INTEGRITY
SET WRITE_DELAY
SHUTDOWN
UPDATE

Expression
Comments
Datatypes
Stored Procedures / Functions List




CALL Expression

Any expression can be called like a stored procedure, including, but not only Java stored procedures or functions. This command returns a ResultSet with one column and one row (the result) just like a SELECT statement with one row and one column.

See also: Stored Procedures / Functions, Expression.




CHECKPOINT

Closes the database files, shrinks the script file and opens the database.

See also: SHUTDOWN, SET LOGSIZE.




COMMIT [WORK]

Ends a transaction and makes the changes permanent.

See also: ROLLBACK, SET AUTOCOMMIT.




CONNECT USER username PASSWORD password

Connects to the database as a different user. Use "" for an empty password.

See also: GRANT, REVOKE




CREATE ALIAS function FOR javaFunction

Creates an alias for a Java function. The function must be accessible from the JVM in that the database runs. Example:
CREATE ALIAS ABS FOR "java.lang.Math.abs"

See also: CALL, Stored Procedures / Functions




CREATE [UNIQUE] INDEX index ON table (column [, ...])

Creates an index on one or more columns in a table.
Creating an index on searched columns may improve performance.

See also: CREATE TABLE, DROP INDEX




CREATE [ MEMORY | CACHED ] TABLE name
( columnDefinition [, ...] [, constraintDefinition...])

Creates a tables in the memory (default) or on disk and only cached in memory. Identity columns are autoincrement columns. They must be integer columns and are automatically primary key columns. The last inserted value into an identity column for a connection is available using the function IDENTITY(), for example (where Id is the identity column):
INSERT INTO Test (Id, Name) VALUES (NULL,'Test'); CALL IDENTITY()

columnDefinition:
column Datatype [(anything)] [[NOT] NULL] [IDENTITY] [PRIMARY KEY]

constraintDefinition:
[ CONSTRAINT name ]
UNIQUE ( column [,column...] ) |
PRIMARY KEY ( column [,column...] ) |
FOREIGN KEY ( column [,column...] ) REFERENCES refTable ( column [,column...] )

See also: DROP TABLE




CREATE USER username PASSWORD password [ADMIN]

Creates a new user or new administrator in this database. Empty password can be made using "".
Only an administrator do this.

See also: CONNECT, GRANT, REVOKE




DELETE FROM table [ WHERE Expression ]

Removes rows in a table.

See also: Expression, INSERT, SELECT




DISCONNECT

Closes this connection. It is not required to call this command when using the JDBC interface: it is called automatically when the connection is closed. After disconnecting, it is not possible to execute other queries (also not CONNECT) with this connection.

See also: CONNECT




DROP INDEX table.index

Removes the specified index from the database.

See also: CREATE INDEX




DROP TABLE table

Removes a table, the data and indexes from the database.

See also: CREATE TABLE




DROP USER username

Removes a user from the database.
Only an administrator do this.

See also: CREATE USER




GRANT { SELECT | DELETE | INSERT | UPDATE | ALL } [,...]
ON { table | CLASS "package.class" } TO { username | PUBLIC }

Assigns privileges to a user or to all users (PUBLIC) for a table or for a class. To allow a user to call a function from a class, the right ALL must be used. Examples:
GRANT SELECT ON Test TO GUEST
GRANT ALL ON CLASS "java.lang.String" TO PUBLIC
Only an administrator do this.

See also: REVOKE, CREATE USER




INSERT INTO table [ ( column [,...] ) ]
{ VALUES(Expression [,...]) | SelectStatement }

Adds one or more new rows of data into a table.




REVOKE { SELECT | DELETE | INSERT | UPDATE | ALL } [,...]
ON { table | CLASS "package.class" } TO { username | PUBLIC }

Withdraws privileges from a user or for PUBLIC (all users) for a table or class.
Only an administrator may do this.

See also: GRANT




ROLLBACK [WORK]

Undoes changes made since the last COMMIT or ROLLBACK.

See also: COMMIT




SCRIPT ['file']

Creates an SQL script describing the database.
Only an administrator may do this.




SELECT [DISTINCT]
{ selectExpression | table.* | * } [, ... ]
[ INTO newTable ]
FROM tableList
[ WHERE Expression ]
[ GROUP BY Expression [, ...] ]
[ ORDER BY orderExpression [, ...] ]
[ { UNION [ALL] | {MINUS|EXCEPT} | INTERSECT } selectStatement ]

Retrieves information from one or more tables in the database.

tableList:
table [ { INNER | LEFT [OUTER] } JOIN table ON Expression ] [, ...]

selectExpression:
{ Expression | COUNT(*) | {COUNT | MIN | MAX | SUM | AVG} (Expression) }

orderExpression:
{ columnNr | columnAlias | selectExpression } [ ASC | DESC ]

See also: INSERT, UPDATE, DELETE




SET AUTOCOMMIT { TRUE | FALSE }

Switches on or off the connection's auto-commit mode. If switched on, then all statements will be committed as individual transactions. Otherwise, the statements are grouped into transactions that are terminated by either COMMIT or ROLLBACK. By default, new connections are in auto-commit mode.




SET IGNORECASE { TRUE | FALSE }

Disables (ignorecase = true) or enables (ignorecase = false) the case sensitivity of text comparing. By default, new databases are case sensitive. The sensitivity must be switched before creating tables. Existing tables are data is not affected. When switched on, the data type VARCHAR is set to VARCHAR_IGNORECASE. This special data type can also be used without switching ignorecase on; so it is possible to have some rows case sensitive and some not, even in the same table.
Only an administrator may do this.




SET LOGSIZE size

Sets the maximum size in MB of the .script file. Default is 200 MB. The database will be closed and opened (just like using CHECKPOINT) if the .script file gets over this limit, and so the .script file will shrink. 0 means no limit.

See also: CHECKPOINT




SET PASSWORD password

Changes the password of the currently connected user. Empty password can be set using ""




SET REFERENTIAL_INTEGRITY { TRUE | FALSE }

This commands enables / disables the referential integrity checking (foreign keys). Normally it should be switched on (this is the default) but when importing data (and the data is imported in the 'wrong' order) the checking can be switched off.
Only an administrator may do this.

See also: CREATE TABLE




SET WRITE_DELAY { TRUE | FALSE }

When the write delay is switched on, the executed commands are written to the log file (.script) at most 1 second after they are executed. This improves the performance of applications that makes a lot of inserts/updates/deletes. When switched off (this is the default) then all SQL commands are written into the log file (.script) just after they are executed.
Only an administrator may do this.


SET TABLE tableName INDEX 'index1rootPos index2rootPos ... '.

This command is only used internally to store the position of index roots in the index file. It appears only in database script files; it should not be used directly.




SHUTDOWN [ IMMEDIATELY | COMPACT ]

Closes the current database. SHUTDOWN IMMEDIATELY just closes the databasefiles (like a external poweroff); this command is used internally to test the recovery mechanism.
SHUTDOWN COMPACT recreates the database files. This operations shrinks all files to the minimum size.
Only an administrator may do this.




UPDATE table SET column = Expression [, ...]
[WHERE Expression]

Modifies data of a table in the database.

See also: SELECT, INSERT, DELETE




Datatypes: The types on the same line are equivalent.
{
INTEGER | INT | "int" | "java.lang.Integer" |
DOUBLE [PRECISION] | FLOAT | "double" | "java.lang.Double" |
VARCHAR | "java.lang.String" | VARCHAR_IGNORECASE |
CHAR | CHARACTER |
LONGVARCHAR |
DATE | "java.sql.Date" |
TIME | "java.sql.Time" |
TIMESTAMP | DATETIME | "java.sql.Timestamp" |
DECIMAL | "java.math.BigDecimal" |
NUMERIC |
BIT | "boolean" | "java.lang.Boolean" |
TINYINT | "short" | "java.lang.Short" |
SMALLINT |
BIGINT | "long" | "java.lang.Long" |
REAL | "float" | "java.lang.Float" |
BINARY | "byte[]" |
VARBINARY |
LONGVARBINARY |
OTHER | OBJECT | "java.lang.Object" }

The first column contains the data types names defined by the standard. The data types in quotes are the Java class names - if this type names are used then they must be enclosed in quotes because lower/uppercase matters.

The recommended Java mapping for the JDBC datatype FLOAT is as a Java type "double". Because of the potential confusion it is recommended that DOUBLE is used instead of FLOAT.

VARCHAR_IGNORECASE is a special case insensitive type of VARCHAR. This type is not portable.




Comments


-- SQL style line comment
// Java style line comment
/* C style line comment */

All this types of comments are ignored by the database.




Stored Procedures / Functions

Stored procedures are Java functions that are called directly from the SQL language or using an alias. Calling Java functions (directly or using the alias) requires that the Java class can be reached by the database (server). The syntax is:

"java.lang.Math.sqrt"(2.0)

This means the packacke must be provided, and the name must be written as one word, and inside " because otherwise it is converted to uppercase (and not found).

An alias can be created using the command CREATE ALIAS:

CREATE ALIAS SQRT FOR "java.lang.Math.sqrt"

When an alias is defined, then the function can be called additionally using this alias:

SQRT(2.0)




List of built in functions and stored procedures

Numerical
ABS(d) (returns the absolute value of a double value)
ACOS(d) (returns the arc cosine of an angle)
ASIN(d) (returns the arc sine of an angle)
ATAN(d) (returns the arc tangent of an angle)
ATAN2(a,b) (returns the tangent of a/b)
CEILING(d) (returns the smallest integer that is not less than d)
COS(d) (returns the cosine of an angle)
COT(d) (returns the cotangent of an angle)
DEGREES(d) (converts radians to degrees)
EXP(d) (returns e (2.718...) raised to the power of d)
FLOOR(d) (returns the largest integer that is not greater than d)
LOG(d) (returns the natural logarithm (base e))
LOG10(d) (returns the logarithm (base 10))
MOD(a,b) (returns a modulo b)
PI() (returns pi (3.1415...))
POWER(a,b) (returns a raised to the power of b)
RADIANS(d) (converts degrees to radians)
RAND() (returns a random number x bigger or equal to 0.0 and smaller than 1.0)
ROUND(a,b) (rounds a to b digits after the decimal point)
SIGN(d) (returns -1 if d is smaller than 0, 0 if d==0 and 1 if d is bigger than 0)
SIN(d) (returns the sine of an angle)
SQRT(d) (returns the square root)
TAN (returns the trigonometric tangent of an angle)
TRUNCATE(a,b) (truncates a to b digits after the decimal point)
BITAND(a,b) (return a & b)
BITOR(a,b) (returns a | b)
ROUNDMAGIC(d) (solves rounding problems such as 3.11-3.1-0.01)

String
ASCII(s) (returns the ASCII code of the leftmost character of s)
CHAR(c) (returns a character that has the ASCII code c)
CONCAT(str1,str2) (returns str1 + str2 )
DIFFERENCE(s1,s2) (returns the difference between the sound of s1 and s2)
INSERT(s,start,len,s2) (returns a string where len number of characters beginning at start has been replaced by s2)
LCASE(s) (converts s to lower case)
LEFT(s,count) (returns the leftmost count of characters of s)
LENGTH(s) (returns the number of characters in s)
LOCATE(search,s,[start]) (returns the first index (1=left, 0=not found) where search is found in s, starting at start)
LTRIM(s) (removes all leading blanks in s)
REPEAT(s,count) (returns s repeated count times)
REPLACE(s,replace,s2) (replaces all occurrences of replace in s with s2)
RIGHT(s,count) (returns the rightmost count of characters of s)
RTRIM(s) (removes all trailing blanks)
SOUNDEX(s) (returns a four character code representing the sound of s)
SPACE(count) (returns a string consisting of count spaces)
SUBSTRING(s,start[,len]) (returns the substring starting at start (1=left) with length len)
UCASE(s) (converts s to upper case)
LOWER(s) (converts s to lower case)
UPPER(s) (converts s to upper case)

Date / Time
CURDATE() (returns the current date)
CURTIME() (returns the current time)
DAYNAME(date) (returns the name of the day)
DAYOFMONTH(date) (returns the day of the month (1-31))
DAYOFWEEK(date) (returns the day of the week (1 means Sunday))
DAYOFYEAR(date) (returns the day of the year (1-366))
HOUR(time) (return the hour (0-23))
MINUTE(time) (returns the minute (0-59))
MONTH(date) (returns the month (1-12))
MONTHNAME(date) (returns the name of the month)
NOW() (returns the current date and time as a timestamp)
QUARTER(date) (returns the quarter (1-4))
SECOND(time) (returns the second (0-59))
WEEK(date) (returns the week of this year (1-53)
YEAR(date) (returns the year)

System / Connection
DATABASE() (returns the name of the database of this connection)
USER() (returns the user name of this connection)
IDENTITY() (returns the last identity values that was inserted by this connection)

System
IFNULL(exp,value) (if exp is null, value is returned else exp)
CASEWHEN(exp,v2,v2) (if exp is true, v1 is returned, else v2)
CONVERT(term,type) (converts exp to another data type)
CAST(term AS type) (converts exp to another data type)

See also: CREATE ALIAS, CALL




Expression:
[NOT] condition [ { OR | AND } condition ]

condition:
{ value [ || value ]
| value { = | < | <= | > | >= | <> | != | IS [NOT] } value
| EXISTS(selectStatement)
| value BETWEEN value AND value
| value [NOT] IN ( {value [, ...] | selectStatement } )
| value [NOT] LIKE value [ESCAPE] value }

value:
[ + | - ] { term [ { + | - | * | / } term ]
| ( condition )
| function ( [parameter] [,...] )
| selectStatement giving one value

term:
{ 'string' | number | floatingpoint
| [table.]column | TRUE | FALSE | NULL }

string:
Starts and ends with a single '. In a string started with ' use '' to create a '.
LIKE uses '%' to match any (including 0) number of characters, and '_' to match exactly one character. To search for '%' itself, '\%' must be used, for '_' use '\_'; or any other escaping character may be set using the ESCAPE clause.

name:
A name starts with a letter and is followed by any number of letters or digits. Lowercase is changed to uppercase except for strings and quoted identifiers. Names are not case sensitive. Quoted identifiers can be used as names (for example for tables or columns). Quoted identifiers starts and ends with ". In a quoted identifier use "" to create a ". With quoted identifiers it is possible to create mixed case table and column names. Example: CREATE TABLE "Address" ("Nr" INTEGER,"Name" VARCHAR); SELECT * FROM "Address". Quoted identifiers are not strings.

values:
A 'date' value starts and ends with ', the format is yyyy-mm-dd (see java.sql.Date).
A 'time' value starts and ends with ', the format is hh:mm:ss (see java.sql.Time).
Binary data starts and ends with ', the format is hexadecimal. '0004ff' for example is 3 bytes, first 0, second 4 and last 255 (0xff).

Any number of commands may be combined. ';' may be used to separate each command but is not necessary.

[ A ] means A is optional
{ B | C } means B or C must be used.
( and ) are the characters '(' and ')'.
UPPERCASE are keywords