Built In Functions
198
The format strings that can be used for TO_DATE and TO_TIMESTAMP are more restrictive than those used for
TO_CHAR, because the format string must contain the elements needed to build a full DATE or TIMESTAMP value.
For example, you cannot use the 'WW', 'W', 'HH' or 'HH12' format elements with TO_DATE or TO_TIMESTAMP
The format is internally translated to a java.text.SimpleDateFormat format string. Unsupported format
strings should not be used. With TO_CHAR, you can include a string literal inside the format string by enclosing it
in double quotes. (HyperSQL)
The supported format components are all uppercase as follows:
Table 10.1. TO_CHAR, TO_DATE and TO_TIMESTAMP format elements
BC | B.C. | AD | A.D.
Returns AD for common era and BC for before common era
RRRR
4-digit year
YYYY
4-digit year
IYYY
4-digit year, corresponding to ISO week of the year. The reported year for the last
few days of the calendar year may be the next year.
YY
2 digit year
IY
2 digit year, corresponding to ISO week of the year
MM
Month (01-12)
MON
Short three-letter name of month
MONTH
Name of month
WW
Week of year (1-53) where week 1 starts on the first day of the year and continues
to the seventh day of the year (not a calendar week).
W
Week of month (1-5) where week 1 starts on the first day of the month and ends
on the seventh (not a calendar week).
IW
Week of year (1-52 or 1-53) based on the ISO standard. Week starts on Monday.
The first week may start near the end of previous year.
DAY
Name of day.
DD
Day of month (01-31).
DDD
Day of year (1-366).
DY
Short three-letter name of day.
HH
Hour of day (00-11).
HH12
Hour of day (00-11).
HH24
Hour of day (00-23).
MI
Minute (00-59).
SS
Second (00-59).
FF
Fractional seconds.
Array Functions
Array functions are specialised functions with ARRAY parameters or return values. For the ARRAY_AGG aggregate
function, see the  Data Access and Change  chapter.
CARDINALITY
Pdf thumbnail generator - SDK Library API:C# PDF Thumbnail Create SDK: Draw thumbnail images for PDF in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Support Thumbnail Generation with Various Options for Quick PDF Navigation
www.rasteredge.com
Pdf thumbnail generator - SDK Library API:VB.NET PDF Thumbnail Create SDK: Draw thumbnail images for PDF in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
Support Thumbnail Generation with Various Options for Quick PDF Navigation
www.rasteredge.com
Built In Functions
199
CARDINALITY( <array value expr> )
Returns the element count for the given array argument. (Foundation)
MAX_CARDINALITY
MAX_CARDINALITY( <array value expr> )
Returns the maximum allowed element count for the given array argument. (Foundation)
POSITION_ARRAY
POSITION_ARRAY( <value expression>  IN <array value expr> [  FROM <int value
expr> ] )
Returns the position of the first match for the <value expression> in the array. By default the search starts from
the beginning of the array. The optional <int value expr> specifies the start position. Positions are counted
from 1. Returns zero if no match is found. (HyperSQL)
SORT_ARRAY
SORT_ARRAY( <array value expr> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] )
Returns a sorted copy of the array. By default, sort is performed in ascending order and NULL elements are sorted
first. (HyperSQL)
TRIM_ARRAY
TRIM_ARRAY( <array value expr>, <num value expr> )
Returns a new array that contains the elements of the <array value  expr> minus the number of elements
specified by the <num value expr>. Elements are discarded from the end of the array. (Foundation)
SEQUENCE_ARRAY
SEQUENCE_ARRAY( <value expr 1>, <value expr 2>, <value expr 3 )
Returns a new array that contains a sequence of values. The <value expr 1> is the lower bound of the range.
The <value expr 2> is the upper bound of the range. The <value expr 3> is the increment. The elments of
the array are within the inclusive range. The first element is <value expr 1> and each subsequent element is the
sum of the previous element and the increment. If the increment is zero, only the first element is returned. When the
increment is negative, the lower bound should be larger than the upper bound. The type of the arguments can be all
number types, or a datetime range and an interval for the third argument (HyperSQL)
In the examples below, a number sequence and a date sequence are shown. The UNNEST table expression is used
to form a table from the array.
SEQUENCE_ARRAY(0, 100, 5)
ARRAY[0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100] 
SELECT * FROM UNNEST(SEQUENCE_ARRAY(10, 12, 1))
C1 
-- 
10 
11 
12 
SDK Library API:How to C#: Generate Thumbnail for Word
VB.NET How-to, VB.NET PDF, VB.NET Word, VB.NET Excel NET Twain, VB.NET Barcode Read, VB.NET Barcode Generator, view less. How to C#: Generate Thumbnail for Word.
www.rasteredge.com
SDK Library API:How to C#: Set Image Thumbnail in C#.NET
VB.NET How-to, VB.NET PDF, VB.NET Word, VB.NET Excel NET Twain, VB.NET Barcode Read, VB.NET Barcode Generator, view less. How to C#: Set Image Thumbnail in C#.NET
www.rasteredge.com
Built In Functions
200
SELECT * FROM UNNEST(SEQUENCE_ARRAY(CURRENT_DATE, CURRENT_DATE + 6 DAY, 1 DAY)) WITH ORDINALITY
AS T(D, I) 
         I 
---------- - 
2010-08-01 1 
2010-08-02 2 
2010-08-03 3 
2010-08-04 4 
2010-08-05 5 
2010-08-06 6 
2010-08-07 7
General Functions
General functions can take different types of arguments. Some General Functions accept a variable number of
arguments.
Also see the  Data Access and Change  chapter for SQL expressions that are similar to functions, for example CAST
and NULLIF.
CASEWHEN
CASEWHEN( <boolean value expr>, <value expr 2>, <value expr 3> )
If the <boolean value expr> is true, returns <value expr 2> otherwise returns <value expr 3>.
Use a CASE WHEN expression instead for more extensive capabilities and options.
CASE WHEN is documented in the  Data Access and Change  chapter. (HyperSQL)
COALESCE
COALESCE( <value expr 1>, <value expr 2> [, ...] )
Returns <value expr 1> if it is not null, otherwise returns <value expr 2> if not null and so on. The type
of both arguments must be comparable. (Foundation)
CONVERT
CONVERT ( <value expr> , <data type> )
<data type> ::= { SQL_BIGINT | SQL_BINARY | SQL_BIT |SQL_BLOB | SQL_BOOLEAN
| SQL_CHAR | SQL_CLOB | SQL_DATE | SQL_DECIMAL | SQL_DATALINK |SQL_DOUBLE |
SQL_FLOAT | SQL_INTEGER | SQL_LONGVARBINARY | SQL_LONGNVARCHAR | SQL_LONGVARCHAR
| SQL_NCHAR | SQL_NCLOB | SQL_NUMERIC | SQL_NVARCHAR | SQL_REAL | SQL_ROWID
|  SQL_SQLXML  |  SQL_SMALLINT  |  SQL_TIME  |  SQL_TIMESTAMP  |  SQL_TINYINT  |
SQL_VARBINARY | SQL_VARCHAR} [ ( <precision, length or scale parameters> ) ]
The CONVERT function is a JDBC escape function, equivalent to the SQL standard CAST expression. It converts
the <value expr> into the given <data type> and returns the value. The <data type> options are synthetic
names made by prefixing type names with SQL_. Some of the <data type> options represent valid SQL types,
but some are based on non-standard type names, namely { SQL_LONGNVARCHAR | SQL_LONGVARBINARY |
SQL_LONGVARCHAR | SQL_TINYINT }. None of the synthetic names can be used in any other context than
the CONVERT function.
The definition of CONVERT in the JDBC Standard does not allow the precision, scale or length to be specified. This
is required by the SQL standard for BINARY, BIT, BLOB, CHAR, CLOB, VARBINARY and VARCHAR types and
is often needed for DECIMAL and NUMERIC. Defaults are used for precision.
SDK Library API:How to C#: Generate Thumbnail for PowerPoint
VB.NET How-to, VB.NET PDF, VB.NET Word, VB.NET NET Twain, VB.NET Barcode Read, VB.NET Barcode Generator, view less. How to C#: Generate Thumbnail for PowerPoint.
www.rasteredge.com
SDK Library API:VB.NET Image: Program for Creating Thumbnail from Documents and
language. It empowers VB developers to create thumbnail from multiple document and image formats, such as PDF, TIFF, GIF, BMP, etc. It
www.rasteredge.com
Built In Functions
201
HyperSQL also allows the use of real type names (without the SQL_ prefix). In this usage, HyperSQL allows the use
of precision, scale or length for the type definition when they are valid for the type definition.
When MS SQL Server compatibility mode is on, the parameters of CONVERT are switched and only the real type
names with required precision, scale or length are allowed. (JDBC)
DECODE
DECODE( <value expr main>, <value expr match 1>, <value expr result 1> [...,]
[, <value expr default>] )
DECODE takes at least 3 arguments. The <value expr main> is compared with <value expr match 1>
and if it matches, <value expr result 1> is returned. If there are additional pairs of <value expr match
n> and <value expr result n>, comparison is repeated until a match is found the result is returned. If no
match is found, the <value expr default> is returned if it is specified, otherwise NULL is returned. The type
of the return value is a combination of the types of the <value expr result ... > arguments. (HyperSQL)
GREATEST
GREATEST( <value expr 1>, [<value expr ...>, ...] )
The GREATEST function takes one or more arguments. It compares the arguments with each other and returns the
greatest argument. The return type is the combined type of the arguments. Arguments can be of any type, so long as
they are comparable. (HyperSQL)
IFNULL
ISNULL
IFNULL | ISNULL ( <value expr 1>, <value expr 2> )
Returns <value expr 1> if it is not null, otherwise returns <value expr 2>. The type of the return value is
the type of <value expr 1>. Almost equivalent to SQL Standard COALESCE(<value expr 1>, <value
expr 2>) function, but without type modification. (JDBC)
LEAST
LEAST( <value expr 1>, [<value expr ...>, ...] )
The LEAST function takes one or more arguments. It compares the arguments with each other and returns the smallest
argument. The return type is the combined type of the arguments. Arguments can be of any type, so long as they are
comparable. (HyperSQL)
LOAD_FILE
LOAD_FILE ( <char value expr 1> [, <char value expr 2>] )
Returns a BLOB or CLOB containing the URL or file path specified in the first argument. If used with a single
argument, the function returns a BLOB. If used with two arguments, the function returns a CLOB and the second
argument is the character encoding of the file.
The file path is interpreted the same way as a TEXT TABLE source file location. The hsqldb.allow_full_path
system property must be set true in order to access files outside the directory structure of the database files.
(HyperSQL)
NULLIF
SDK Library API:How to C#: Generate Thumbnail for Raster
VB.NET How-to, VB.NET PDF, VB.NET Word, VB.NET Excel NET Twain, VB.NET Barcode Read, VB.NET Barcode Generator, view less. How to C#: Generate Thumbnail for Raster
www.rasteredge.com
SDK Library API:Create Thumbnail in Web Image Viewer | Online Tutorials
PDF Generator. PDF Reader. Twain Scanning. DICOM Reading. Here are some functions for image and documents thumbnail creating in HTML Document Image Viewers:
www.rasteredge.com
Built In Functions
202
NULLIF( <value expr 1>, <value expr 2> )
Returns <value expr  1> if it is not equal to <value expr 2>, otherwise returns null. The type of both
arguments must be the same. This function is a shorthand for a specific CASE expression. (Foundation)
NVL
NVL( <value expr 1>, <value expr 2> )
Returns <value expr 1> if it is not null, otherwise returns <value expr 2>. The type of the return value is
the type of <value expr 1>. For example, if <value expr 1> is an INTEGER column and <value expr
2> is a DOUBLE constant, the return type is cast into INTEGER. This function is similar to IFNULL. (HyperSQL)
NVL2
NVL2( <value expr 1>, <value expr 2>, <value expr 3> )
If <value expr 1> is not null, returns <value expr 2>, otherwise returns <value expr 3>. The type of
the return value is the type of <value expr 2> unless it is null. (HyperSQL)
UUID
UUID ( [ { <char value expr> | <binary value expr> ] } )
With no parameter, this function returns a new UUID value as a 16 byte binary value. With a UUID hexadecimal string
argument, it returns the 16 byte binary value of the UUID. With a 16 byte binary argument, it returns the formatted
UUID character representation. (HyperSQL)
System Functions
CRYPT_KEY
CRYPT_KEY( <value expr 1>, <value expr 2> )
Returns a binary string representation of a cryptography key for the given cipher and cryptography provider. The
cipher specification is specified by <value expr 1> and the provider by <value expr 2>. To use the default
provider, specify null for <value expr 2>. (HyperSQL)
DIAGNOSTICS
DIAGNOSTICS ( ROW_COUNT )
This is a convenience function for use instead of the GET DIAGNOSTICS ... statement. The argument specifies
the name of the diagnostics variable. Currently the only supported variable is the ROW_COUNT variable. The function
returns the row count returned by the last executed statement. The return value is 0 after most statements. Calling this
function immediately after executing an INSERT, UPDATE, DELETE or MERGE statement returns the row count
for the last statement, as it is returned by the JDBC statement. (HyperSQL)
IDENTITY
IDENTITY ()
Returns the last IDENTITY value inserted into a row by the current session. The statement, CALL IDENTITY() can be
made after an INSERT statement that inserts a row into a table with an IDENTITY column. The CALL IDENTITY()
statement returns the last IDENTITY value that was inserted into a table by the current session. Each session manages
this function call separately and is not affected by inserts in other sessions. The statement can be executed as a direct
statement or a prepared statement. (HyperSQL)
SDK Library API:Create Thumbnail Winforms | Online Tutorials
PDF Generator. PDF Reader. Twain Scanning. It is easy to integrate robust thumbnail creating & viewing capabilities into your Windows Forms applications.
www.rasteredge.com
SDK Library API:How to C#: Overview of Using XImage.Raster
Empower to navigate image(s) content quickly via thumbnail. Able to support text extract with OCR. You may edit the tiff document easily. Create Thumbnail.
www.rasteredge.com
Built In Functions
203
DATABASE
DATABASE ()
Returns the file name (without directory information) of the database. (JDBC)
DATABASE_NAME
DATABASE_NAME ()
Returns the database name. This name is a 16 character, uppercase string. It is generated as a string based on the
timestamp of the creation of the database, for example HSQLDB32438AEAFB. The name can be redefined by an
admin user but the new name must be all uppercase and 16 characters long. This name is used in log messages with
external logging frameworks. (HyperSQL)
DATABASE_VERSION
DATABASE_VERSION ()
Returns the full version string for the database engine. For example, 2.0.1. (JDBC)
USER
USER ()
Equivalent to the SQL function CURRENT_USER. (JDBC)
CURRENT_USER
CURRENT_USER
CURRENT_ROLE
CURRENT_ROLE
SESSION_USER
SESSION_USER
SYSTEM_USER
SYSTEM_USER
CURRENT_SCHEMA
CURRENT_SCHEMA
CURRENT_CATALOG
CURRENT_CATALOG
These functions return the named current session attribute. They are all SQL Standard functions.
The  CURRENT_USER  is  the  user  that  connected  to  the  database,  or  a  user  subsequently  set  by  the  SET
AUTHORIZATION statement.
SESSION_USER is the same as CURRENT_USER
Built In Functions
204
SYSTEM_USER is the user that connected to the database. It is not changed with any command until the session
is closed.
CURRENT_SCHEMA is default schema of the user, or a schema subsequently set by the SET SCHEMA command.
CURRENT_CATALOG is always the same within a given HyperSQL database and indicates the name of the catalog.
IS_AUTOCOMMIT
IS_AUTOCOMMIT()
Returns TRUE if the session is in autocommit mode. (HyperSQL)
IS_READONLY_SESSION
IS_READONLY_SESSION()
Returns TRUE if the session is in read only mode. (HyperSQL)
IS_READONLY_DATABASE
IS_READONLY_DATABASE()
Returns TRUE if the database is a read only database. (HyperSQL)
IS_READONLY_DATABASE_FILES
IS_READONLY_DATABASE_FILES()
Returns TRUE if the database is a read-only files database. In this kind of database, it is possible to modify the data,
but the changes are not persisted to the database files. (HyperSQL)
ISOLATION_LEVEL
ISOLATION_LEVEL()
Returns  the  current  transaction  isolation  level  for  the  session.  Returns  either  READ  COMMITTED  or
SERIALIZABLE as a string. (HyperSQL)
SESSION_ID
SESSION_ID()
Returns the id of the session as a BIGINT value. Each session id is unique during the operational lifetime of the
database. Id's are restarted after a shutdown and restart. (HyperSQL)
SESSION_ISOLATION_LEVEL
SESSION_ISOLATION_LEVEL()
Returns  the default  transaction isolation level  for the current session. Returns either READ COMMITTED or
SERIALIZABLE as a string. (HyperSQL)
DATABASE_ISOLATION_LEVEL
DATABASE_ISOLATION_LEVEL()
Returns  the  default  transaction  isolation  level  for  the  database.  Returns  either  READ  COMMITTED  or
SERIALIZABLE as a string. (HyperSQL)
Built In Functions
205
TRANSACTION_SIZE
TRANSACTION_SIZE()
Returns the row change count for the current transaction. Each row change represents a row INSERT or a row DELETE
operation. There will be a pair of row change operations for each row that is updated.
TRANSACTION_ID
TRANSACTION_ID()
Returns the current transaction ID for the session as a BIGINT value. The database maintains a global incremental
id which is allocated to new transactions and new actions (statement executions) in different sessions. This value is
unique to the current transaction. (HyperSQL)
ACTION_ID
ACTION_ID()
Returns the current action ID for the session as a BIGINT value. The database maintains a global incremental id which
is allocated to new transactions and new actions (statement executions) in different sessions. This value is unique to
the current action. (HyperSQL)
TRANSACTION_CONTROL
TRANSACTION_CONTROL()
Returns  the  current  transaction  model  for  the database.  Returns LOCKS,  MVLOCKS  or  MVCC  as  a string.
(HyperSQL)
LOB_ID
LOB_ID( <column reference> )
Returns internal ID of a lob as a BIGINT value. Lob ID's are unique and never reused. The <column reference> is the
name of the column (or variable, or argument) which is a CLOB or BLOB. Returns null if the value is null. (HyperSQL)
ROWNUM
ROWNUM()
ROW_NUMBER
ROW_NUMBER() OVER()
Returns the current row number (from 1) being processed in a select statement. This has the same semantics as the
ROWNUM pseudo-column in Oracle syntax mode, but can be used in any syntax mode. The function is used in a
SELECT or DELETE statement. The ROWNUM of a row is incremented as the rows are added to the result set. It is
therefore possible to use a condition such as WHERE ROWNUM() < 10, but not ROWNUM() > 10 or ROWNUM
= 10. The ROW_NUMBER() OVER() alternative performs the same function and is included for compatibility with
other database engines.(HyperSQL)
206
Chapter 11. System Management
Fred Toussi, The HSQL Development Group
$Revision: 5477 $
Copyright 2002-2015 Fred Toussi. Permission is granted to distribute this document without any alteration
under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group
to distribute this document with or without alterations under the terms of the HSQLDB license.
2015-06-29 22:26:45-0400
Mode of Operation and Tables
HyperSQL has many modes of operation and features that allow it to be used in very different scenarios. Levels of
memory usage, speed and accessibility by different applications are influenced by how HyperSQL is deployed.
Mode of Operation
The decision to run HyperSQL as a separate server process or as an in-process database should be based on the
following:
• When HyperSQL is run as a server on a separate machine, it is isolated from hardware failures and crashes on the
hosts running the application.
• When HyperSQL is run as a server on the same machine, it is isolated from application crashes and memory leaks.
• Server connections are slower than in-process connections due to the overhead of streaming the data for each JDBC
call.
• You can reduce client/server traffic using SQL Stored procedures to reduce the number of JDBC execute calls.
• During development, it is better to use a Server with server.silent=false, which displays the statements sent to the
server on the console window.
• To improve speed of execution for statements that are executed repeatedly, reuse a parameterized PreparedStatement
for the lifetime of the connections.
Database Types
There are three types of database, mem:, file: and res:. The mem: type is stored all in memory and not persisted
to file. The file: type is persisted to file. The res: type is also based on files, but the files are loaded from
the classpath, similar to resource and class files. Changes to the data in file: databases are persisted, unless the
database is readonly, or files_readonly (using optional property settings). Changes to res: databases are
not persisted.
Readonly Databases
file: catalog can be made readonly permanently, or it can be opened as readonly. To make the database readonly, the
property, value pair, readonly=true can be added to the .properties file of the database. The SHUTDOWN
command must be used to close the database before making this change.
It is also possible to open a normal database as readonly. For this, the property can be included in the URL of the
first connection to the database.
System Management
207
With readonly databases, it is still possible to insert and delete rows in TEMP tables.
RES and Files Readonly Databases
There is another option which allows MEMORY tables to  be writeable, but without persisting the changes at
SHUTDOWN. This option is activated with the property, value pair, files_readonly=true, which can be added
to the .properties file of the database, or included in the URL of the first connection to the database.
res: catalog, is a set of database files on the classpath (inside a jar or alongside class files). The database is
opened with a URL in the form of jdbc:hsqldb:res:<database  path>. These databases are always
files_readonly and have the same restrictions as files_readonly file: catalogs.
CACHED tables and LOBS in these catalogs are readonly. It is not possible to create new LOBs in these catalogs,
but you can use existing LOBs in new rows.
These options are useful for running application tests which operate on a predefined dataset.
Tables
TEXT tables are designed for special applications where the data has to be in an interchangeable format, such as CSV
(comma separated values). TEXT tables should not be used for routine storage of data that changes a lot.
MEMORY tables and CACHED tables are generally used for data storage. The difference between the two is as
follows:
• The data for all MEMORY tables is read from the *.script file when the database is started and stored in memory.
In contrast the data for cached tables is not read into memory until the table is accessed. Furthermore, only part of
the data for each CACHED table is held in memory, allowing tables with more data than can be held in memory.
• When the database is shutdown in the normal way, all the data for MEMORY tables is written out to the disk. In
comparison, the data in CACHED tables that has changed is written out during operation and at shutdown.
• The size and capacity of the data cache for all the CACHED tables is configurable. This makes it possible to allow
all the data in CACHED tables to be cached in memory. In this case, speed of access is good, but slightly slower
than MEMORY tables.
• For normal applications it is recommended that MEMORY tables are used for small amounts of data, leaving
CACHED tables for large data sets. For special applications in which speed is paramount and a large amount of free
memory is available, MEMORY tables can be used for large tables as well.
• You can change the type of the table with the SET  TABLE  <table  name>  TYPE  {  CACHED  |
MEMORY }statement.
Large Objects
HyperSQL 2.0 supports dedicated storage and access to BLOB and CLOB objects. These objects can have huge sizes.
BLOB or CLOB is specified as the type of a column of the table. Afterwards, rows can be inserted into the table using
a PreparedStatement for efficient transfer of large LOB data to the database. In mem: catalogs, CLOB and BLOB data
is stored in memory. In file: catalogs, this data is stored in a single separate file which has the extension *.lobs. The
size of this file can grow to huge, terabyte figures. By default, a minimum 32 KB is allocated to each LOB. You can
reduced this if your LOBs are generally smaller.
LOB data should be stored in the database using a JDBC PreparedStatement object. The streaming methods send the
LOB to the database in one operation as a binary or character stream. Inside the database, the disk space is allocated
Documents you may be interested
Documents you may be interested