<!-- start generic documentation -->
A connection (session) with a specific database. Within the context
of a Connection, SQL statements are executed and results
are returned.
A Connection's database is able to provide information describing
its tables, its supported SQL grammar, its stored procedures, the
capabilities of this connection, and so on. This information is
obtained with the
getMetaData
method.
Note: By default the Connection automatically commits
changes after executing each statement. If auto commit has been
disabled, an explicit commit must be done or database changes will
not be saved.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
To get a
Connection
to an HSQLDB database, the
following code may be used (updated to reflect the most recent
recommendations):
When using HSQLDB, the database connection
<url> must start with
'jdbc:hsqldb:'
Since 1.7.2, connection properties (<key-value-pairs>) may be appended
to the database connection
<url>, using the form:
'<url>[;key=value]*'
Also since 1.7.2, the allowable forms of the HSQLDB database connection
<url> have been extended. However, all legacy forms continue
to work, with unchanged semantics. The extensions are as described in the
following material.
Network Server Database Connections:
The 1.7.2
Server
database connection
<url> has
changed to take one of the two following forms:
- 'jdbc:hsqldb:hsql://host[:port][/<alias>][<key-value-pairs>]'
- 'jdbc:hsqldb:hsqls://host[:port][/<alias>][<key-value-pairs>]'
(with TLS).
The 1.7.2
WebServer
database connection
<url>
also changes to take one of two following forms:
- 'jdbc:hsqldb:http://host[:port][/<alias>][<key-value-pairs>]'
- 'jdbc:hsqldb:https://host[:port][/<alias>][<key-value-pairs>]'
(with TLS).
In both network server database connection
<url> forms, the
optional
<alias> component is used to identify one of possibly
several database instances available at the indicated host and port. If the
<alias> component is omitted, then a connection is made to the
network server's default database instance.
For more information on server configuration regarding mounting multiple
databases and assigning them
<alias> values, please read the
Java API documentation for
Server
and related
chapters in the general documentation, especially the Advanced Users
Guide.
Transient, In-Process Database Connections:
The 1.7.2 100% in-memory (transient, in-process) database connection
<url> takes one of the two following forms:
- 'jdbc:hsqldb:.[<key-value-pairs>]'
(the legacy form, extended)
- 'jdbc:hsqldb:mem:<alias>[<key-value-pairs>]'
(the new form)
With the 1.7.2 transient, in-process database connection
<url>,
the
<alias> component is the key used to look up a transient,
in-process database instance amongst the collection of all such instances
already in existence within the current class loading context in the
current JVM. If no such instance exists, one
may be automatically
created and mapped to the
<alias>, as governed by the
'ifexists=true|false' connection property.
Persistent, In-Process Database Connections:
The 1.7.2 standalone (persistent, in-process) database connection
<url> takes one of the three following forms:
- 'jdbc:hsqldb:<path>[<key-value-pairs>]'
(the legacy form, extended)
- 'jdbc:hsqldb:file:<path>[<key-value-pairs>]'
(same semantics as the legacy form)
- 'jdbc:hsqldb:res:<path>[<key-value-pairs>]'
(new form with 'files_in_jar' semantics)
For the persistent, in-process database connection
<url>,
the
<path> component is the path prefix common to all of
the files that compose the database.
As of 1.7.2, although other files may be involved (such as transient working
files and/or TEXT table CSV data source files), the essential set that may,
at any particular point in time, compose an HSQLDB database are:
- <path>.properties
- <path>.script
- <path>.log
- <path>.data
- <path>.backup
- <path>.lck
For example:
'jdbc:hsqldb:file:test' connects to a database
composed of some subset of the files listed above, where the expansion
of
<path> is
'test' prefixed with the path of the
working directory fixed at the time the JVM is started.
Under
Windows TM ,
'jdbc:hsqldb:file:c:\databases\test' connects to a database located
on drive
'C:' in the directory
'databases', composed
of some subset of the files:
C:\
+--databases\
+--test.properties
+--test.script
+--test.log
+--test.data
+--test.backup
+--test.lck
Under most variations of UNIX,
'jdbc:hsqldb:file:/databases/test'
connects to a database located in the directory
'databases' directly
under root, once again composed of some subset of the files:
/
+--databases/
+--test.properties
+--test.script
+--test.log
+--test.data
+--test.backup
+--test.lck
Some Guidelines:
- Both relative and absolute database file paths are supported.
- Relative database file paths can be specified in a platform independent
manner as: '[dir1/dir2/.../dirn/]<file-name-prefix>'.
- Specification of absolute file paths is operating-system specific.
Please read your OS file system documentation.
- Specification of network mounts may be operating-system specific.
Please read your OS file system documentation.
- Special care may be needed w.r.t. file path specifications
containing whitespace, mixed-case, special characters and/or
reserved file names.
Please read your OS file system documentation.
Note: Versions of HSQLDB previous to 1.7.0 did not support creating
directories along the file path specified in the persistent, in-process mode
database connection
<url> form, in the case that they did
not already exist. Starting with HSQLDB 1.7.0, directories
will
be created if they do not already exist., but only if HSQLDB is built under
a version of the compiler greater than JDK 1.1.x.
res: Connections
The new
'jdbc:hsqldb:res:<path>' database connection
<url> has different semantics than the
'jdbc:hsqldb:file:<path>' form. The semantics are similar to
those of a
'files_readonly' database, but with some additional
points to consider.
Specifically, the
'<path>' component of a
res: type
database connection
<url> is used to obtain resource URL
objects and thereby read the database files as resources on the class path.
Moreover, the URL objects
must point only to resources contained
in one or more jars on the class path (must be jar protocol).
This restriction is enforced to avoid the unfortunate situation in which,
because
res: database instances do not create a <path>.lck file
(they are strictly files-read-only) and because the
<path>
components of
res: and
file: database URIs are not checked
for file system equivalence, it is possible for the same database files to
be accessed concurrently by both
file: and
res: database
instances. That is, without this restriction, it is possible that
<path>.data and <path>.properties file content may be written
by a
file: database instance without the knowlege or cooperation
of a
res: database instance open on the same files, potentially
resulting in unexpected database errors, inconsistent operation
and/or data corruption.
In short, a
res: type database connection
<url> is
designed specifically to connect to a
'files_in_jar' mode database
instance, which in turn is designed specifically to operate under
Java WebStartTM and
Java AppletTMconfigurations,
where co-locating the database files in the jars that make up the
WebStart application or Applet avoids the need for special security
configuration or code signing.
Note: Since it is difficult and often nearly impossible to determine
or control at runtime from where all classes are being loaded or which class
loader is doing the loading under
'files_in_jar' semantics, the
<path> component of the res: database connection
<url> is always taken to be relative to the default package.
That is, if the
<path> component does not start with '/', then
'/' is prepended when obtaining the resource URLs used to read the database
files.
For more information about HSQLDB file structure, various database modes
and other attributes such as those controlled through the HSQLDB properties
files, please read the general documentation, especially the Advanced Users
Guide.
JRE 1.1.x Notes:
In general, JDBC 2 support requires Java 1.2 and above, and JDBC3 requires
Java 1.4 and above. In HSQLDB, support for methods introduced in different
versions of JDBC depends on the JDK version used for compiling and building
HSQLDB.
Since 1.7.0, it is possible to build the product so that
all JDBC 2 methods can be called while executing under the version 1.1.x
Java Runtime EnvironmentTM.
However, in addition to this technique requiring explicit casts to the
org.hsqldb.jdbcXXX classes, some of the method calls also require
int
values that are defined only in the JDBC 2 or greater
version of
ResultSet
interface. For this reason, when the
product is compiled under JDK 1.1.x, these values are defined
in
jdbcResultSet
.
In a JRE 1.1.x environment, calling JDBC 2 methods that take or return the
JDBC2-only
ResultSet
values can be achieved by referring
to them in parameter specifications and return value comparisons,
respectively, as follows:
jdbcResultSet.FETCH_FORWARD
jdbcResultSet.TYPE_FORWARD_ONLY
jdbcResultSet.TYPE_SCROLL_INSENSITIVE
jdbcResultSet.CONCUR_READ_ONLY
// etc.
However, please note that code written to use HSQLDB JDBC 2 features under
JDK 1.1.x will not be compatible for use with other JDBC 2 drivers. Please
also note that this feature is offered solely as a convenience to developers
who must work under JDK 1.1.x due to operating constraints, yet wish to
use some of the more advanced features available under the JDBC 2
specification.
(fredt@users)
(boucherb@users)
<!-- end release-specific documentation -->
clearWarnings
public void clearWarnings()
throws SQLException
<!-- start generic documentation -->
Clears all warnings reported for this
Connection
object. After a call to this method, the method
getWarnings
returns null until
a new warning is reported for this Connection.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Before HSQLDB 1.7.2,
SQLWarning
was not
supported, and calls to this method are simply ignored.
Starting with HSQLDB 1.7.2, the standard behaviour is implemented.
<!-- end release-specific documentation -->
close
public void close()
throws SQLException
<!-- start generic documentation -->
Releases this
Connection
object's database and JDBC resources immediately instead of
waiting for them to be automatically released.
Calling the method
close
on a
Connection
object that is already closed is a no-op.
Note: A
Connection
object is automatically
closed when it is garbage collected. Certain fatal errors also
close a
Connection
object.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
In 1.7.2,
INTERNAL
Connection
objects are not closable from JDBC client code.
<!-- end release-specific documentation -->
commit
public void commit()
throws SQLException
<!-- start generic documentation -->
Makes all changes made since the
previous commit/rollback permanent and releases any database
locks currently held by the Connection. This method should be
used only when auto-commit mode has been disabled.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with HSQLDB 1.7.2, savepoints are supported both
in SQL and via the JDBC interface.
Using SQL, savepoints may be set, released and used in rollback
as follows:
SAVEPOINT <savepoint-name>
RELEASE SAVEPOINT <savepoint-name>
ROLLBACK TO SAVEPOINT <savepoint-name>
<!-- end release-specific documentation -->
createStatement
public Statement createStatement()
throws SQLException
<!-- start generic documentation -->
Creates a
Statement
object for sending SQL statements to the database. SQL
statements without parameters are normally executed using
Statement
objects. If the same SQL statement is
executed many times, it may be more efficient to use a
PreparedStatement
object.
Result sets created using the returned
Statement
object will by default be type
TYPE_FORWARD_ONLY
and have a concurrency level of
CONCUR_READ_ONLY
.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with HSQLDB 1.7.2, support for precompilation at the engine level
has been implemented, so it is now much more efficient and performant
to use a
PreparedStatement
object if the same SQL statement
is executed many times.
Up to 1.6.1, HSQLDB supported
TYPE_FORWARD_ONLY
-
CONCUR_READ_ONLY
results only, so
ResultSet
objects created using the returned
Statement
object would
always be type
TYPE_FORWARD_ONLY
with
CONCUR_READ_ONLY
concurrency.
Starting with 1.7.0, HSQLDB also supports
TYPE_SCROLL_INSENSITIVE
results.
Notes:
Up to 1.6.1, calling this method returned
null
if the
connection was already closed. This was possibly counter-intuitive
to the expectation that an exception would be thrown for
closed connections. Starting with 1.7.0. the behaviour is to throw a
SQLException
if the connection is closed.
<!-- end release-specific documentation -->
- a new default Statement object
createStatement
public Statement createStatement(int type,
int concurrency)
throws SQLException
<!-- start generic documentation -->
Creates a
Statement
object that will generate
ResultSet
objects with the given type and
concurrency. This method is the same as the
createStatement
method above, but it allows the
default result set type and result set concurrency type to be
overridden.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Up to HSQLDB 1.6.1, support was provided only for type
TYPE_FORWARD_ONLY
and concurrency
CONCUR_READ_ONLY
.
Starting with HSQLDB 1.7.0, support is now provided for types
TYPE_FORWARD_ONLY
,
and
TYPE_SCROLL_INSENSITIVE
,
with concurrency
CONCUR_READ_ONLY
.
Starting with HSQLDB 1.7.2, the behaviour regarding the type and
concurrency values has changed to more closely conform to the
specification. That is, if an unsupported combination is requested,
a SQLWarning is issued on this Connection and the closest supported
combination is used instead.
Notes:
Up to 1.6.1, calling this method returned
null
if the
connection was already closed and a supported combination of type and
concurrency was specified. This was possibly counter-intuitive
to the expectation that an exception would be thrown for
closed connections. Starting with 1.7.0. the behaviour is to throw a
SQLException
if the connection is closed.
<!-- end release-specific documentation -->
type
- a result set type; one of
ResultSet.TYPE_FORWARD_ONLY
,
ResultSet.TYPE_SCROLL_INSENSITIVE
, or
ResultSet.TYPE_SCROLL_SENSITIVE
(not
supported)concurrency
- a concurrency type; one of
ResultSet.CONCUR_READ_ONLY
or ResultSet.CONCUR_UPDATABLE
(not supported)
- a new
Statement
object that will, within
the release-specific documented limitations of support,
generate ResultSet
objects with the given
type and concurrency
- JDK 1.2 (JDK 1.1.x developers: read the new overview
for jdbcConnection)
createStatement
public Statement createStatement(int resultSetType,
int resultSetConcurrency,
int resultSetHoldability)
throws SQLException
<!-- start generic documentation -->
Creates a
Statement
object that will generate
ResultSet
objects with
the given type, concurrency, and holdability. This method is
the same as the
createStatement
method above, but
it allows the default result set type, concurrency, and
holdability to be overridden.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with HSQLDB 1.7.2, this feature is supported.
Starting with HSQLDB 1.7.2, the behaviour regarding the type,
concurrency and holdability values has changed to more closely conform
to the specification. That is, if an unsupported combination is requested,
a SQLWarning is issued on this Connection and the closest supported
combination is used instead.
<!-- end release-specific documentation -->
resultSetType
- one of the following ResultSet
constants: ResultSet.TYPE_FORWARD_ONLY
,
ResultSet.TYPE_SCROLL_INSENSITIVE
,
or ResultSet.TYPE_SCROLL_SENSITIVE
resultSetConcurrency
- one of the following
ResultSet
constants: ResultSet.CONCUR_READ_ONLY
or
ResultSet.CONCUR_UPDATABLE
resultSetHoldability
- one of the following
code>ResultSet
constants: ResultSet.HOLD_CURSORS_OVER_COMMIT
or ResultSet.CLOSE_CURSORS_AT_COMMIT
- a new
Statement
object that will generate
ResultSet
objects with the given type,
concurrency, and holdability
getAutoCommit
public boolean getAutoCommit()
throws SQLException
Gets the current auto-commit state.
- the current state of auto-commit mode
getCatalog
public String getCatalog()
throws SQLException
<!-- start generic documentation -->
Returns the Connection's current catalog name.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
HSQLDB does not yet support catalogs and always returns null.
<!-- end release-specific documentation -->
- the current catalog name or null
For HSQLDB, this is always null.
getHoldability
public int getHoldability()
throws SQLException
<!-- start generic documentation -->
Retrieves the current
holdability of
ResultSet
objects created using
this
Connection
object.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with HSQLDB 1.7.2, this feature is supported.
Calling this method always returns HOLD_CURSORS_OVER_COMMIT.
<!-- end release-specific documentation -->
- the holdability, one of
ResultSet.HOLD_CURSORS_OVER_COMMIT
or ResultSet.CLOSE_CURSORS_AT_COMMIT
getMetaData
public DatabaseMetaData getMetaData()
throws SQLException
<!-- start generic documentation -->
Gets the metadata regarding this connection's database.
A Connection's database is able to provide information describing
its tables, its supported SQL grammar, its stored procedures,
the capabilities of this connection, and so on. This information
is made available through a
DatabaseMetaData
object.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
JDBC
DatabaseMetaData
methods returning
ResultSet
were not implemented fully before 1.7.2.
Some of these methods always returned empty result sets.
Other methods did not accurately
reflect all of the MetaData for the category.
Also, some method ignored the filters provided as
parameters, returning an unfiltered result each time.
Also, the majority of methods returning
ResultSet
threw an
SQLException
when accessed by a non-admin
user.
Starting with HSQLDB 1.7.2, essentially full database metadata
is supported.
For discussion in greater detail, please follow the link to the
overview for jdbcDatabaseMetaData, below.
<!-- end release-specific documentation -->
- a DatabaseMetaData object for this Connection
getTransactionIsolation
public int getTransactionIsolation()
throws SQLException
<!-- start generic documentation -->
Retrieves this
Connection
object's current transaction isolation level.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
HSQLDB always returns
Connection.TRANSACTION_READ_UNCOMMITED
.
<!-- end release-specific documentation -->
- the current transaction isolation level, which will be
one of the following constants:
Connection.TRANSACTION_READ_UNCOMMITTED
, Connection.TRANSACTION_READ_COMMITTED
,
Connection.TRANSACTION_REPEATABLE_READ
,
Connection.TRANSACTION_SERIALIZABLE
, or
Connection.TRANSACTION_NONE
Up to and including 1.7.1, TRANSACTION_READ_UNCOMMITTED is
always returned
getTypeMap
public Map getTypeMap()
throws SQLException
<!-- start generic documentation -->
Gets the type map object associated with this connection. Unless
the application has added an entry to the type map, the map
returned will be empty.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
HSQLDB 1.7.2 does not support this feature. Calling this
method always throws a
SQLException
, stating that the
function is not supported.
<!-- end release-specific documentation -->
- the
java.util.Map
object associated with
this Connection
object
- JDK 1.2 (JDK 1.1.x developers: read the new overview
for jdbcConnection)
getWarnings
public SQLWarning getWarnings()
throws SQLException
<!-- start generic documentation -->
Retrieves the first warning reported by calls on this
Connection
object. If there is more than one
warning, subsequent warnings will be chained to the first
one and can be retrieved by calling the method
SQLWarning.getNextWarning
on the warning
that was retrieved previously.
This method may not be called on a closed connection; doing so
will cause an
SQLException
to be thrown.
Note: Subsequent warnings will be chained to this
SQLWarning.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with 1.7.2, HSQLDB produces warnings whenever a createStatement(),
prepareStatement() or prepareCall() invocation requests an unsupported
but defined combination of result set type, concurrency and holdability,
such that another set is substituted.
<!-- end release-specific documentation -->
- the first
SQLWarning
object or null
if there are none
isClosed
public boolean isClosed()
Tests to see if a Connection is closed.
- true if the connection is closed; false if it's still
open
isReadOnly
public boolean isReadOnly()
throws SQLException
Tests to see if the connection is in read-only mode.
- true if connection is read-only and false otherwise
nativeSQL
public String nativeSQL(String sql)
throws SQLException
<!-- start generic documentation -->
Converts the given SQL statement
into the system's native SQL grammar. A driver may convert the
JDBC SQL grammar into its system's native SQL grammar prior to
sending it. This method returns the native form of the
statement that the driver would have sent.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Up to and including 1.7.2, HSQLDB converts the JDBC SQL
grammar into the system's native SQL grammar prior to sending
it, if escape processing is set true; this method returns the
native form of the statement that the driver would send in place
of client-specified JDBC SQL grammar.
Before 1.7.2, escape processing was incomplete and
also broken in terms of support for nested escapes.
Starting with 1.7.2, escape processing is complete and handles nesting
to arbitrary depth, but enforces a very strict interpretation of the
syntax and does not detect or process SQL comments.
In essence, the HSQLDB engine directly handles the prescribed syntax
and date / time formats specified internal to the JDBC escapes.
It also directly offers the XOpen / ODBC extended scalar
functions specified available internal to the {fn ...} JDBC escape.
As such, the driver simply removes the curly braces and JDBC escape
codes in the simplest and fastest fashion possible, by replacing them
with whitespace.
But to avoid a great deal of complexity, certain forms of input
whitespace are currently not recognised. For instance,
the driver handles "{?= call ...}" but not "{ ?= call ...} or
"{? = call ...}"
Also, comments embedded in SQL are currently not detected or
processed and thus may have unexpected effects on the output
of this method, for instance causing otherwise valid SQL to become
invalid. It is especially important to be aware of this because escape
processing is set true by default for Statement objects and is always
set true when producing a PreparedStatement from prepareStatement()
or CallableStatement from prepareCall(). Currently, it is simply
recommended to avoid submitting SQL having comments containing JDBC
escape sequence patterns and/or single or double quotation marks,
as this will avoid any potential problems.
It is intended to implement a less strict handling of whitespace and
proper processing of SQL comments at some point in the near future,
perhaps before the final 1.7.2 release.
In any event, 1.7.2 now correctly processes the following JDBC escape
forms to arbitrary nesting depth, but only if the exact whitespace
layout described below is used:
- {call ...}
- {?= call ...}
- {fn ...}
- {oj ...}
- {d ...}
- {t ...}
- {ts ...}
<!-- end release-specific documentation -->
sql
- a SQL statement that may contain one or more '?'
parameter placeholders
- the native form of this statement
prepareCall
public CallableStatement prepareCall(String sql)
throws SQLException
<!-- start generic documentation -->
Creates a
CallableStatement
object for calling database stored procedures. The
CallableStatement
object provides methods for setting up
its IN and OUT parameters, and methods for executing the call to a
stored procedure.
Note: This method is optimized for handling stored
procedure call statements. Some drivers may send the call
statement to the database when the method
prepareCall
is done; others may wait until the
CallableStatement
object is executed. This has no direct effect on users;
however, it does affect which method throws certain
SQLExceptions.
Result sets created using the returned
CallableStatement
object will by default be type
TYPE_FORWARD_ONLY
and have a concurrency level of
CONCUR_READ_ONLY
.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with 1.7.2, the support for and behaviour of
CallableStatement has changed. Please read the introductory section
of the documentation for org.hsqldb.jdbc.jdbcCallableStatement.
<!-- end release-specific documentation -->
sql
- a String object that is the SQL statement to be
sent to the database; may contain one or more ?
parameters.
Note: Typically the SQL statement is a JDBC
function call escape string.
- a new default
CallableStatement
object
containing the pre-compiled SQL statement
prepareCall
public CallableStatement prepareCall(String sql,
int resultSetType,
int resultSetConcurrency)
throws SQLException
<!-- start generic documentation -->
Creates a
CallableStatement
object that will generate
ResultSet
objects with
the given type and concurrency. This method is the same as the
prepareCall
method above, but it allows the
default result set type and result set concurrency type to be
overridden.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with HSQLDB 1.7.2, the behaviour regarding the type,
concurrency and holdability values has changed to more closely
conform to the specification. That is, if an unsupported
combination is requrested, a SQLWarning is issued on this Connection
and the closest supported combination is used instead.
Also starting with 1.7.2, the support for and behaviour of
CallableStatement has changed. Please read the introdutory section
of the documentation for org.hsqldb.jdbc.jdbcCallableStatement.
<!-- end release-specific documentation -->
sql
- a String object that is the SQL statement to be
sent to the database; may contain one or more ? parametersresultSetType
- a result set type; one of
ResultSet.TYPE_FORWARD_ONLY
,
ResultSet.TYPE_SCROLL_INSENSITIVE
, (not
supported) or ResultSet.TYPE_SCROLL_SENSITIVE
(not supported)resultSetConcurrency
- a concurrency type; one of
ResultSet.CONCUR_READ_ONLY
or ResultSet.CONCUR_UPDATABLE
(not supported)
- a new CallableStatement object containing the
pre-compiled SQL statement
- JDK 1.2 (JDK 1.1.x developers: read the new overview
for jdbcConnection)
prepareCall
public CallableStatement prepareCall(String sql,
int resultSetType,
int resultSetConcurrency,
int resultSetHoldability)
throws SQLException
<!-- start generic documentation -->
Creates a
CallableStatement
object that will generate
ResultSet
objects with
the given type and concurrency. This method is the same as the
prepareCall
method above, but it allows the
default result set type, result set concurrency type and
holdability to be overridden.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with HSQLDB 1.7.2, this feature is supported.
Starting with HSQLDB 1.7.2, the behaviour regarding the type,
concurrency and holdability values has changed to more closely
conform to the specification. That is, if an unsupported
combination is requrested, a SQLWarning is issued on this Connection
and the closest supported combination is used instead.
Also starting with 1.7.2, the support for and behaviour of
CallableStatment has changed. Please read the introdutory section
of the documentation for org.hsqldb.jdbc.jdbcCallableStatement.
<!-- end release-specific documentation -->
sql
- a String
object that is the SQL
statement to be sent to the database; may contain on or
more ? parametersresultSetType
- one of the following ResultSet
constants: ResultSet.TYPE_FORWARD_ONLY
,
ResultSet.TYPE_SCROLL_INSENSITIVE
, or
ResultSet.TYPE_SCROLL_SENSITIVE
resultSetConcurrency
- one of the following
ResultSet
constants: ResultSet.CONCUR_READ_ONLY
or
ResultSet.CONCUR_UPDATABLE
resultSetHoldability
- one of the following
ResultSet
constants: ResultSet.HOLD_CURSORS_OVER_COMMIT
or ResultSet.CLOSE_CURSORS_AT_COMMIT
- a new
CallableStatement
object,
containing the pre-compiled SQL statement, that will
generate ResultSet
objects with the given
type, concurrency, and holdability
prepareStatement
public PreparedStatement prepareStatement(String sql)
throws SQLException
<!-- start generic documentation -->
Creates a
PreparedStatement
object for sending parameterized SQL statements to the
database.
A SQL statement with or without IN parameters can be
pre-compiled and stored in a
PreparedStatement
object. This object can then be used to efficiently execute
this statement multiple times.
Note: This method is optimized for handling parametric
SQL statements that benefit from precompilation. If the driver
supports precompilation, the method
prepareStatement
will send the statement to the database for precompilation.
Some drivers may not support precompilation. In this case, the
statement may not be sent to the database until the
PreparedStatement
object is executed. This has no
direct effect on users; however, it does affect which methods
throw certain
SQLException
objects.
Result sets created using the returned
PreparedStatement
object will by default be type
TYPE_FORWARD_ONLY
and have a concurrency level of
CONCUR_READ_ONLY
.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with HSQLDB 1.7.2, support for precompilation at the engine level
has been implemented, so it is now much more efficient and performant
to use a
PreparedStatement
object if the same SQL statement
is executed many times.
Starting with 1.7.2, the support for and behaviour of
PreparedStatment has changed. Please read the introductory section
of the documentation for org.hsqldb.jdbc.jdbcPreparedStatement.
<!-- end release-specific documentation -->
sql
- an SQL statement that may contain one or more '?'
IN parameter placeholders
- a new default
PreparedStatement
object
containing the pre-compiled SQL statement
prepareStatement
public PreparedStatement prepareStatement(String sql,
String[] columnNames)
throws SQLException
<!-- start generic documentation -->
Creates a default
PreparedStatement
object capable of returning the auto-generated keys designated
by the given array. This array contains the names of the
columns in the target table that contain the auto-generated
keys that should be returned. This array is ignored if the SQL
statement is not an
INSERT
statement.
An SQL statement with or without IN parameters can be
pre-compiled and stored in a
PreparedStatement
object. This object can then be used to efficiently execute
this statement multiple times.
Note: This method is optimized for handling parametric
SQL statements that benefit from precompilation. If the driver
supports precompilation, the method
prepareStatement
will send the statement to the database for precompilation.
Some drivers may not support precompilation. In this case, the
statement may not be sent to the database until the
PreparedStatement
object is executed. This has no direct effect on users;
however, it does affect which methods throw certain
SQLExceptions.
Result sets created using the returned
PreparedStatement
object will by default be type
TYPE_FORWARD_ONLY
and have a concurrency level of
CONCUR_READ_ONLY
.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
HSQLDB 1.7.2 does not support this feature.
Calling this method always throws a
SQLException
,
stating that the function is not supported.
<!-- end release-specific documentation -->
sql
- an SQL statement that may contain one or more '?'
IN parameter placeholderscolumnNames
- an array of column names indicating the
columns that should be returned from the inserted row or
rows
- a new
PreparedStatement
object,
containing the pre-compiled statement, that is capable of
returning the auto-generated keys designated by the given
array of column names
prepareStatement
public PreparedStatement prepareStatement(String sql,
int autoGeneratedKeys)
throws SQLException
<!-- start generic documentation -->
Creates a default
PreparedStatement
object that has the capability to retrieve auto-generated
keys. The given constant tells the driver whether it should
make auto-generated keys available for retrieval. This
parameter is ignored if the SQL statement is not an
INSERT
statement.
Note: This method is optimized for handling parametric
SQL statements that benefit from precompilation. If the driver
supports precompilation, the method
prepareStatement
will send the statement to the database for precompilation.
Some drivers may not support precompilation. In this case, the
statement may not be sent to the database until the
PreparedStatement
object is executed. This has no direct effect on users;
however, it does affect which methods throw certain
SQLExceptions.
Result sets created using the returned
PreparedStatement
object will by default be type
TYPE_FORWARD_ONLY
and have a concurrency level of
CONCUR_READ_ONLY
.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
HSQLDB 1.7.2 does not support this feature.
Calling this method always throws a
SQLException
,
stating that the function is not supported.
<!-- end release-specific documentation -->
sql
- an SQL statement that may contain one or more '?'
IN parameter placeholdersautoGeneratedKeys
- a flag indicating that auto-generated
keys should be returned, one of
code>Statement.RETURN_GENERATED_KEYS
or Statement.NO_GENERATED_KEYS
.
- a new
PreparedStatement
object,
containing the pre-compiled SQL statement, that will have
the capability of returning auto-generated keys
prepareStatement
public PreparedStatement prepareStatement(String sql,
int type,
int concurrency)
throws SQLException
<!-- start generic documentation -->
Creates a
PreparedStatement
object that will
generate
ResultSet
objects with the given type
and concurrency. This method is the same as the
prepareStatement
method above, but it allows the
default result set type and result set concurrency type to be
overridden.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with HSQLDB 1.7.2, the behaviour regarding the type and
concurrency values has changed to more closely conform to the
specification. That is, if an unsupported combination is requested,
a SQLWarning is issued on this Connection and the closest supported
combination is used instead.
Also starting with 1.7.2, the support for and behaviour of
PreparedStatment has changed. Please read the introductory section
of the documentation for org.hsqldb.jdbc.jdbcPreparedStatement.
<!-- end release-specific documentation -->
sql
- a String object that is the SQL statement to be
sent to the database; may contain one or more ? IN
parameterstype
- a result set type; one of
ResultSet.TYPE_FORWARD_ONLY
,
ResultSet.TYPE_SCROLL_INSENSITIVE
, or
ResultSet.TYPE_SCROLL_SENSITIVE
(not
supported)concurrency
- a concurrency type; one of
ResultSet.CONCUR_READ_ONLY
or ResultSet.CONCUR_UPDATABLE
(not supported)
- a new PreparedStatement object containing the
pre-compiled SQL statement that will produce
ResultSet
objects with the given type and concurrency
- JDK 1.2 (JDK 1.1.x developers: read the new overview
for jdbcConnection)
prepareStatement
public PreparedStatement prepareStatement(String sql,
int resultSetType,
int resultSetConcurrency,
int resultSetHoldability)
throws SQLException
<!-- start generic documentation -->
Creates a
PreparedStatement
object that will generate
ResultSet
objects with
the given type, concurrency, and holdability.
This method is the same as the
prepareStatement
method above, but it allows the default result set type,
concurrency, and holdability to be overridden.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with HSQLDB 1.7.2, this feature is supported.
Starting with HSQLDB 1.7.2, the behaviour regarding the type,
concurrency and holdability values has changed to more closely
conform to the specification. That is, if an unsupported
combination is requested, a SQLWarning is issued on this Connection
and the closest supported combination is used instead.
Also starting with 1.7.2, the support for and behaviour of
PreparedStatment has changed. Please read the introductory section
of the documentation for org.hsqldb.jdbc.jdbcPreparedStatement.
<!-- end release-specific documentation -->
sql
- a String
object that is the SQL
statement to be sent to the database; may contain one or
more ? IN parametersresultSetType
- one of the following ResultSet
constants: ResultSet.TYPE_FORWARD_ONLY
,
ResultSet.TYPE_SCROLL_INSENSITIVE
,
or ResultSet.TYPE_SCROLL_SENSITIVE
resultSetConcurrency
- one of the following
ResultSet
constants: ResultSet.CONCUR_READ_ONLY
or
ResultSet.CONCUR_UPDATABLE
resultSetHoldability
- one of the following
ResultSet
constants: ResultSet.HOLD_CURSORS_OVER_COMMIT
or ResultSet.CLOSE_CURSORS_AT_COMMIT
- a new
PreparedStatement
object,
containing the pre-compiled SQL statement, that will
generate ResultSet
objects with the given
type, concurrency, and holdability
prepareStatement
public PreparedStatement prepareStatement(String sql,
int[] columnIndexes)
throws SQLException
<!-- start generic documentation -->
Creates a default
PreparedStatement
object capable of returning the auto-generated keys designated
by the given array. This array contains the indexes of the
columns in the target table that contain the auto-generated
keys that should be made available. This array is ignored if
the SQL statement is not an
INSERT
statement.
An SQL statement with or without IN parameters can be
pre-compiled and stored in a
PreparedStatement
object. This object can then be used to efficiently execute
this statement multiple times.
Note: This method is optimized for handling parametric
SQL statements that benefit from precompilation. If the driver
supports precompilation, the method
prepareStatement
will send the statement to the database for precompilation.
Some drivers may not support precompilation. In this case, the
statement may not be sent to the database until the
PreparedStatement
object is executed. This has no direct effect on users;
however, it does affect which methods throw certain
SQLExceptions.
Result sets created using the returned
PreparedStatement
object will by default be type
TYPE_FORWARD_ONLY
and have a concurrency level of
CONCUR_READ_ONLY
.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
HSQLDB 1.7.2 does not support this feature.
Calling this method always throws a
SQLException
,
stating that the function is not supported.
<!-- end release-specific documentation -->
sql
- an SQL statement that may contain one or more '?'
IN parameter placeholderscolumnIndexes
- an array of column indexes indicating the
columns that should be returned from the inserted row or
rows
- a new
PreparedStatement
object,
containing the pre-compiled statement, that is capable of
returning the auto-generated keys designated by the given
array of column indexes
releaseSavepoint
public void releaseSavepoint(Savepoint savepoint)
throws SQLException
<!-- start generic documentation -->
Removes the given
Savepoint
object from the current transaction. Any reference to the
savepoint after it have been removed will cause an
SQLException
to be thrown.
<!-- end generic documentation -->
savepoint
- the Savepoint
object to be removed
reset
public void reset()
throws SQLException
Resets this connection so it can be used again. Used when connections are
returned to a connection pool.
rollback
public void rollback()
throws SQLException
<!-- start generic documentation -->
Drops all changes made since the
previous commit/rollback and releases any database locks
currently held by this Connection. This method should be used
only when auto- commit has been disabled.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with HSQLDB 1.7.2, savepoints are fully supported both
in SQL and via the JDBC interface.
Using SQL, savepoints may be set, released and used in rollback
as follows:
SAVEPOINT <savepoint-name>
RELEASE SAVEPOINT <savepoint-name>
ROLLBACK TO SAVEPOINT <savepoint-name>
<!-- end release-specific documentation -->
rollback
public void rollback(Savepoint savepoint)
throws SQLException
<!-- start generic documentation -->
Undoes all changes made after
the given
Savepoint
object was set.
This method should be used only when auto-commit has been
disabled.
<!-- end generic documentation -->
savepoint
- the Savepoint
object to roll back to
setAutoCommit
public void setAutoCommit(boolean autoCommit)
throws SQLException
<!-- start generic documentation -->
Sets this connection's auto-commit mode to the given state.
If a connection is in auto-commit mode, then all its SQL
statements will be executed and committed as individual transactions.
Otherwise, its SQL statements are grouped into transactions that
are terminated by a call to either the method
commit
or
the method
rollback
. By default, new connections are
in auto-commit mode.
The commit occurs when the statement completes or the next
execute occurs, whichever comes first. In the case of
statements returning a
ResultSet
object, the
statement completes when the last row of the
ResultSet
object has been retrieved or the
ResultSet
object
has been closed. In advanced cases, a single statement may
return multiple results as well as output parameter values. In
these cases, the commit occurs when all results and output
parameter values have been retrieved.
NOTE: If this method is called during a transaction,
the transaction is committed.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Up to and including HSQLDB 1.7.2,
- All rows of a result set are retrieved internally
before the first row can actually be fetched.
Therefore, a statement can be considered complete as soon as
any XXXStatement.executeXXX method returns. - Multiple result sets and output parameters are not yet
supported.
(boucherb@users)
<!-- end release-specific
documentation -->
autoCommit
- true
to enable auto-commit
mode; false
to disable it
setCatalog
public void setCatalog(String catalog)
throws SQLException
<!-- start generic documentation -->
Sets a catalog name in order to
select a subspace of this Connection's database in which to
work.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
HSQLDB does not yet support catalogs and simply ignores this
request.
<!-- end release-specific documentation -->
catalog
- the name of a catalog (subspace in this
Connection object's database) in which to work (Ignored)
setHoldability
public void setHoldability(int holdability)
throws SQLException
<!-- start generic documentation -->
Changes the holdability of
ResultSet
objects created using this
Connection
object to the given holdability.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Starting with HSQLDB 1.7.2, this feature is supported.
As of 1.7.2, only HOLD_CURSORS_OVER_COMMIT is supported; supplying
any other value will throw an exception.
<!-- end release-specific documentation -->
holdability
- a ResultSet
holdability
constant; one of ResultSet.HOLD_CURSORS_OVER_COMMIT
or ResultSet.CLOSE_CURSORS_AT_COMMIT
setReadOnly
public void setReadOnly(boolean readonly)
throws SQLException
<!-- start generic documentation -->
Puts this connection in read-only mode as a hint to enable
database optimizations.
Note: This method should not be called while in the
middle of a transaction.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
Up to and including 1.7.2, HSQLDB will commit the current
transaction automatically when this method is called.
Additionally, HSQLDB provides a way to put a whole database in
read-only mode. This is done by manually adding the line
'readonly=true' to the database's .properties file while the
database is offline. Upon restart, all connections will be
readonly, since the entire database will be readonly. To take
a database out of readonly mode, simply take the database
offline and remove the line 'readonly=true' from the
database's .properties file. Upon restart, the database will
be in regular (read-write) mode.
When a database is put in readonly mode, its files are opened
in readonly mode, making it possible to create CD-based
readonly databases. To create a CD-based readonly database
that has CACHED tables and whose .data file is suspected of
being highly fragmented, it is recommended that the database
first be SHUTDOWN COMPACTed before copying the database
files to CD. This will reduce the space required and may
improve access times against the .data file which holds the
CACHED table data.
Starting with 1.7.2, an alternate approach to opimizing the
.data file before creating a CD-based readonly database is to issue
the CHECKPOINT DEFRAG command followed by SHUTDOWN to take the
database offline in preparation to burn the database files to CD.
<!-- end release-specific documentation -->
readonly
- The new readOnly value
setSavepoint
public Savepoint setSavepoint()
throws SQLException
<!-- start generic documentation -->
Creates an unnamed savepoint in
the current transaction and returns the new
Savepoint
object that represents it.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
HSQLDB 1.7.2 does not support this feature.
Calling this method always throws a
SQLException
,
stating that the function is not supported.
Use setSavepoint(String name) instead
<!-- end release-specific documentation -->
setSavepoint
public Savepoint setSavepoint(String name)
throws SQLException
<!-- start generic documentation -->
Creates a savepoint with the
given name in the current transaction and returns the new
Savepoint
object that represents it.
<!-- end generic documentation -->
name
- a String
containing the name of the savepoint
setTransactionIsolation
public void setTransactionIsolation(int level)
throws SQLException
<!-- start generic documentation -->
Attempts to change the transaction isolation level for this
Connection
object to the one given. The constants
defined in the interface
Connection
are the
possible transaction isolation levels.
Note: If this method is called during a transaction,
the result is implementation-defined.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
<!-- end release-specific documentation -->
level
- one of the following Connection
constants: Connection.TRANSACTION_READ_UNCOMMITTED
, Connection.TRANSACTION_READ_COMMITTED
,
Connection.TRANSACTION_REPEATABLE_READ
, or
Connection.TRANSACTION_SERIALIZABLE
. (Note
that Connection.TRANSACTION_NONE
cannot be
used because it specifies that transactions are not
supported.)
setTypeMap
public void setTypeMap(Map map)
throws SQLException
<!-- start generic documentation -->
Installs the given
TypeMap
object as the type map for this
Connection
object. The type map will be used for the custom mapping of
SQL structured types and distinct types.
<!-- end generic documentation -->
<!-- start release-specific documentation -->
HSQLDB-Specific Information:
HSQLDB 1.7.2 does not support this feature. Calling this
method always throws a
SQLException
, stating that
the function is not supported.
<!-- end release-specific documentation -->
map
- the java.util.Map
object to install as
the replacement for this Connection
object's
default type map
- JDK 1.2 (JDK 1.1.x developers: read the new overview
for jdbcConnection)