 | Chapter 6. Sessions and Transactions |  |
| Chapter 5. Data Access and Change |  | Chapter 7. Text Tables |
| | Chapter 6. Sessions and Transactions | |
| Chapter 5. Data Access and Change | | Chapter 7. Text Tables |
$Revision: 6692 $
Copyright 2010-2024 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.
2024-10-25
Table of Contents
All SQL statements are executed in sessions. When a connection is established to the database, a session is started. The authorization of the session is the name of the user that started the session. A session has several properties. These properties are set by default at the start according to database settings.
SQL Statements are generally transactional statements. When a transactional statement is executed, it starts a transaction if no transaction is in progress. If SQL Data (data stored in tables) is modified during a transaction, the change can be undone with a ROLLBACK statement. When a COMMIT or ROLLBACK statement is executed, the transaction is ended. Each SQL statement works atomically: it either succeeds or fails without changing any data. If a single statement fails, an error is raised but the transaction is not normally terminated. However, some failures are caused by execution of statements that are in conflict with statements executed in other concurrent sessions. Such failures result in an implicit ROLLBACK, in addition to the exception that is raised.
Schema definition and manipulation statements are also transactional according to the SQL Standard. HyperSQL performs automatic commits before and after the execution of such transactions. Therefore, schema-related statements cannot be rolled back. This is likely to change in future versions.
Some statements are not transactional. Most of these statements are used to change the properties of the session. These statements begin with the SET keyword.
If the AUTOCOMMIT property of a session is TRUE, then each transactional statement is followed by an implicit COMMIT.
The default isolation level for a session is READ COMMITTED. This
can be changed using the JDBC java.sql.Connection
object and its setTransactionIsolation(int level)
method. The session can be put in read-only mode using the
setReadOnly(boolean readOnly) method. Both
methods can be invoked only after a commit or a rollback, but not during a
transaction.
The isolation level and / or the readonly mode of a transaction can also be modified using an SQL statement. You can use the statement to change only the isolation mode, only the read-only mode, or both at the same time. This statement can be issued only before a transaction starts or after a commit or rollback.
SET TRANSACTION <transaction characteristic> [
<comma> <transaction characteristic> ]
This statement is described in detail later in this chapter.
Each session has several system attributes. A session can also have user-defined session variables.
The system attributes reflect the current mode of operation for
the session. These attributes can be accessed with function calls and
can be referenced in queries. For example, they can be returned using
the VALUES <attribute function>, ...
statement.
The named attributes such as CURRENT_USER, CURRENT_SCHEMA, etc. are SQL Standard functions. Other attributes of the session, such as auto-commit or read-only modes can be read using other built-in functions. All these functions are listed in the Built In Functions chapter.
Each session has a time zone, which is the time zone of the JVM in which the connection is made and can be different from the time zone of a server database. Different client / server sessions can therefore have different time zones and display time-zone-sensitive information differently. See the description of the SET TIME ZONE statement below for more detail.
Session variables are user-defined variables created the same way as the variables for stored procedures and functions. Currently, these variables cannot be used in general SQL statements. They can be assigned to IN, INOUT and OUT parameters of stored procedures. This allows calling stored procedures which have INOUT or OUT arguments and is useful for development and debugging. See the example in the SQL-Invoked Routines chapter, under Formal Parameters.
Example 6.1. User-defined Session Variables
DECLARE counter INTEGER DEFAULT 3; DECLARE result VARCHAR(20) DEFAULT NULL; SET counter=15; CALL myroutine(counter, result)
With necessary access privileges, sessions can access all table, including GLOBAL TEMPORARY tables, that are defined in schemas. Although GLOBAL TEMPORARY tables have a single name and definition which applies to all sessions that use them, the contents of the tables are different for each session. The contents are cleared either at the end of each transaction or when the session is closed.
Session tables are different because their definition is visible only within the session that defines a table. The definition is dropped when the session is closed. Session tables do not belong to schemas.
<temporary table declaration> ::= DECLARE LOCAL
TEMPORARY TABLE <table name> <table element list> [ ON
COMMIT { PRESERVE | DELETE } ROWS ]
The syntax for declaration is based on the SQL Standard. A session table cannot have FOREIGN KEY constraints, but it can have PRIMARY KEY, UNIQUE or CHECK constraints. A session table definition cannot be modified by adding or removing columns, indexes, etc.
It is possible to refer to a session table using its name, which takes precedence over a schema table of the same name. To distinguish a session table from schema tables, the pseudo schema name, SESSION can be used. The alternative name, MODULE is deprecated and does not work in version 2.5.1 but can be used in version 2.6 and later for backward compatibility. An example is given below:
Example 6.2. User-defined Temporary Session Tables
DECLARE LOCAL TEMPORARY TABLE buffer (id INTEGER PRIMARY KEY, textdata VARCHAR(100)) ON COMMIT PRESERVE ROWS INSERT INTO session.buffer SELECT id, firstname || ' ' || lastname FROM customers -- do some more work DROP TABLE session.buffer -- alternative schema name, MODULE can be used in version 2.7 but it is deprecated DROP TABLE module.buffer
Session tables can be created inside a transaction.
Automatic indexes are created and used on session tables when necessary
for a query or other statement. By default, session table data is held
in memory. This can be changed with the SET SESSION RESULT
MEMORY ROWS statement.
HyperSQL 2 has been fully redesigned to support different transaction isolation models. It no longer supports the old 1.8.x model with "dirty read". Although it is perfectly possible to add an implementation of the transaction manager that supports the legacy model, we thought this is no longer necessary. The new system allows you to select the transaction isolation model while the engine is running. It also allows you to choose different isolation levels for different simultaneous sessions.
HyperSQL 2 supports three concurrency control models: two-phase-locking (2PL), which is the default, multiversion concurrency control (MVCC) and a hybrid model, which is 2PL plus multiversion rows (MVLOCKS). Within each model, it supports some of the 4 standard levels of transaction isolation: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. The concurrency control model is a strategy that governs all the sessions and is set for the database, as opposed for individual sessions. The isolation level is a property of each SQL session, so different sessions can have different isolation levels. In the new implementation, all isolation levels avoid the "dirty read" phenomenon and do not read uncommitted changes made to rows by other transactions.
HyperSQL is fully multi-threaded in all transaction models. Sessions continue to work simultaneously and can fully utilise multi-core processors.
Each active session has a separate thread. When the database is run as a server, HyperSQL allocates and manages the threads. In in-process databases, sessions are accessed indirectly via JDBC connections. Each connection must be accessed via the same thread in the user application for the duration of a transaction. In in-process databases, if the user application interrupts the thread that is executing SQL statements, the interrupt is cleared by HyperSQL if it is caught. You can change this with SET DATABASE TRANSACTION ROLLBACK ON INTERRUPT TRUE to force the transaction to roll back on interrupt and keep the interrupted state of the thread.
The concurrency control model of a live database can be changed. The
SET DATABASE TRANSACTION CONTROL { LOCKS | MVLOCKS | MVCC
} can be used by a user with the DBA role.
The two-phase locking model is the default mode. It is referred to by the keyword, LOCKS. In the 2PL model, each table that is read by a transaction is locked with a shared lock (read lock), and each table that is written to is locked with an exclusive lock (write lock). If two sessions read and modify different tables then both go through simultaneously. If one session tries to lock a table that has been locked by the other, if both locks are shared locks, it will go ahead. If either of the locks is an exclusive lock, the engine will put the session in wait until the other session commits or rolls back its transaction. The engine will throw an error if the action would result in deadlock.
HyperSQL also supports explicit locking of a group of tables for the duration of the current transaction. Use of this command blocks access to the locked tables by other sessions and ensures the current session can complete the intended reads and writes on the locked tables.
If a table is read-only, it will not be locked by any transaction.
The READ UNCOMMITTED isolation level can be used in 2PL modes for read-only operations. It is the same as READ COMMITTED plus read only.
The READ COMMITTED isolation level is the default. It keeps write locks on tables until commit, but releases the read locks after each operation.
The REPEATABLE READ level is upgraded to SERIALIZABLE. These levels keep both read and write locks on tables until commit.
It is possible to perform some critical operations at the SERIALIZABLE level, while the rest of the operations are performed at the READ COMMITTED level.
Note: two phase locking refers to two periods in the life of a transaction. In the first period, locks are acquired, in the second period locks are released. No new lock is acquired after releasing a lock.
This model is referred to as MVLOCKS. It works the same way as normal 2PL as far as updates are concerned.
SNAPSHOT ISOLATION is a multiversion concurrency strategy which uses the snapshot of the whole database at the time of the start of the transaction. In this model, read-only transactions use SNAPSHOT ISOLATION. While other sessions are busy changing the database, the read-only session sees a consistent view of the database and can access all the tables even when they are locked by other sessions for updates.
There are many applications for this mode of operation. In heavily updated data sets, this mode allows uninterrupted read access to the data.
When multiple connections are used to access the database, the transaction manager controls their activities. When each transaction performs only reads or writes on a single table, there is no contention. Each transaction waits until it can obtain a lock then performs the operation and commits. Contentions occur when transactions perform reads and writes on more than one table, or perform a read, followed by a write, on the same table.
For example, when sessions are working at the SERIALIZABLE level, when multiple sessions first read from a table in order to check if a row exists, then insert a row into the same table when it doesn't exist, there will be regular contention. Transaction A reads from the table, then does Transaction B. Now if either Transaction A or B attempts to insert a row, it will have to be terminated as the other transaction holds a shared lock on the table. If instead of two operations, a single MERGE statement is used to perform the read and write, no contention occurs because both locks are obtained at the same time.
Alternatively, there is the option of obtaining the necessary locks with an explicit LOCK TABLE statement. This statement should be executed before other statements and should include the names of all the tables and the locks needed. After this statement, all the other statements in the transaction can be executed and the transaction committed. The commit will remove all the locks.
HyperSQL detects deadlocks before attempting to execute a statement. When a lock is released after the completion of the statement, the first transaction that is waiting for the lock is allowed to continue.
HyperSQL is fully multi threaded. It therefore allows different transactions to execute concurrently so long as they are not waiting to lock the same table for write.
In both LOCKS and MVLOCKS models, SQL routines (functions and procedures) and triggers obtain all the read and write locks at the beginning of the routine execution. SQL statements contained in the routine or trigger are all executed without deadlock as all the locks have already been obtained. At the end of execution of the routine or trigger, read locks are released if the session isolation level is READ COMMITTED.
In the MVCC model, there are no shared, read locks. Exclusive locks are used on individual rows, but their use is different. Transactions can read and modify the same table simultaneously, generally without waiting for other transactions. The SQL Standard isolation levels are used by the user's application, but these isolation levels are translated to the MVCC isolation levels READ CONSISTENCY or SNAPSHOT ISOLATION.
When transactions are running at READ COMMITTED level, no conflict will normally occur. If a transaction that runs at this level wants to modify a row that has been modified by another uncommitted transaction, then the engine puts the transaction in wait, until the other transaction has committed. The transaction then continues automatically. This isolation level is called READ CONSISTENCY.
Deadlock is completely avoided by the engine. The database
setting, SET DATABASE TRANSACTION ROLLBACK ON CONFLICT, determines what
happens in case of deadlock. In theory, conflict (deadlock) is possible
if each transaction is waiting for a different row modified by the other
transaction. In this case, one of the transactions is immediately
terminated by rolling back all the previous statements in the
transaction in order to allow the other transaction to continue. If the
setting has been changed to FALSE with the <set database
transaction rollback on conflict statement>, the session
that avoided executing the deadlock-causing statement returns an error,
but without rolling back the previous statements in the current
transaction. This session should perform an alternative statement to
continue and commit or roll back the transaction. Once the session has
committed or rolled back, the other session can continue. This allows
maximum flexibility and compatibility with other database engines which
do not roll back the transaction upon deadlock.
When transactions are running in REPEATABLE READ or SERIALIZABLE isolation levels, conflict is more likely to happen. There is no difference in operation between these two isolation levels. This isolation level is called SNAPSHOT ISOLATION.
In this mode, when the duration of two transactions overlaps, if
one of the transactions has modified a row and the second transaction
wants to modify the same row, the action of the second transaction will
fail. This happens even if the first transaction has already committed.
The engine will invalidate the second transaction and roll back all its
changes. If the setting is changed to false with the <set
database transaction rollback on conflict statement>, then
the second transaction will just return an error without rolling back.
The application must perform an alternative statement to continue or
roll back the transaction.
In the MVCC model, READ UNCOMMITTED is promoted to READ COMMITTED, as the new architecture is based on multi-version rows for uncommitted data and more than one version may exist for some rows.
With MVCC, when a transaction only reads data, then it will go ahead and complete regardless of what other transactions may do. This does not depend on the transaction being read-only or the isolation modes.
The SQL Standard defines the isolation levels as modes of operation that avoid the three unwanted phenomena, "dirty read", "fuzzy read" and "phantom row" during a transaction. The "dirty read" phenomenon occurs when a session can read changes to a row made by another uncommitted session. The "fuzzy read" phenomenon occurs when a session reads a row and the row is modified by another session which commits, then the first session reads the row again. The "phantom row" phenomenon occurs when a session performs an operation that affects several rows, for example, counts the rows or modifies them using a search condition, then another session adds one or more rows that fulfil the same search condition and commits, then the first session performs an operation that relies on the results of its last operation. According to the Standard, the SERIALIZABLE isolation level avoids all three phenomena and also ensures that all the changes performed during a transaction can be considered as a series of uninterrupted changes to the database without any other transaction changing the database at all for the duration of these actions. The changes made by other transactions are considered to occur before the SERIALIZABLE transaction starts, or after it ends. The READ COMMITTED level avoids "dirty read" only, while the REPEATABLE READ level avoids "dirty read" and "fuzzy read", but not "phantom row".
The Standard allows the engine to return a higher isolation level than requested by the application. HyperSQL promotes a READ UNCOMMITTED request to READ COMMITTED and promotes a REPEATABLE READ request to SERIALIZABLE.
The MVCC model is not covered directly by the Standard. Research has established that the READ CONSISTENCY level fulfils the requirements of (and is stronger than) the READ COMMITTED level. The SNAPSHOT ISOLATION level is stronger than the READ CONSISTENCY level. It avoids the three anomalies defined by the Standard, and is therefore stronger than the REPEATABLE READ level as defined by the Standard. When operating with the MVCC model, HyperSQL treats a REPEATABLE READ or SERIALIZABLE setting for a transaction as SNAPSHOT ISOLATION.
All modes can be used with as many simultaneous connections as required. The default 2PL model is fine for applications with a single connection, or applications that do not access the same tables heavily for writes. With multiple simultaneous connections, MVCC can be used for most applications. Both READ CONSISTENCY and SNAPSHOT ISOLATION levels are stronger than the corresponding READ COMMITTED level in the 2PL mode. Some applications require SERIALIZABLE transactions for at least some of their operations. For these applications, one of the 2PL modes can be used. It is possible to switch the concurrency model while the database is operational. Therefore, the model can be changed for the duration of some special operations, such as synchronization with another data source or performing bulk changes to table contents.
All concurrency models are very fast in operation. When data change operations are mainly on the same tables, the MVCC model may be faster, especially with multi-core processors.
There are a few SQL statements that must access a consistent state of the database during their executions. These statements, which include CHECKPOINT and BACKUP, put an exclusive lock on all the tables of the database when they start.
Some schema manipulation statements put an exclusive lock on one or more tables. For example, changing the columns of a table locks the table exclusively.
In the MVCC model, all statements that need an exclusive lock on one or more tables, put an exclusive lock on the database catalog until they complete.
The effect of these exclusive locks is similar to the execution of data manipulation statements with write locks. The session that is about to execute the schema change statement waits until no other session is holding a lock on any of the objects. At this point it starts its operation and locks the objects to prevents any other session from accessing the locked objects. As soon as the operation is complete, the locks are all removed.
It was mentioned that there is no limit on the number of sessions that can access the tables and all sessions work simultaneously in multi-threaded execution. However, there are internal resources that are shared. Simultaneous access to these resources can reduce the overall efficiency of the system. MEMORY and TEXT tables do not share resources and do not block multi-threaded access. With CACHED tables, each row change operation blocks the file and its cache momentarily until the operation is finished. This is done separately for each row, therefore a multi-row INSERT, UPDATE, or DELETE statement will allow other sessions to access the file during its execution. With CACHED tables, SELECT operations do not block each other, but selecting from different tables and different parts of a large table causes the row cache to be updated frequently and will reduce overall performance.
The new access pattern is the opposite of the access pattern of version 1.8.x. In the old version, even when 20 sessions are actively reading and writing, only a single session at a time performs an SQL statement completely, before the next session is allowed access. In the new version, while a session is performing a SELECT statement and reading rows of a CACHED table to build a result set, another session may perform an UPDATE statement that reads and writes rows of the same table. The two operations are performed without any conflict, but the row cache is updated more frequently than when one operation is performed after the other operation has finished.
As HyperSQL is multithreaded, you can view the current sessions
and their state from any admin session. The
INFORMATION_SCHEMA.SYSTEM_SESSIONS table contains the
list of open sessions, their unique ids and the statement currently
executed or waiting to be executed by each session. For each session, it
displays the list of sessions that are waiting for it to commit, or the
session that this session is waiting for.
ALTER SESSION
alter session statement
<alter session statement> ::= ALTER SESSION
<numeric literal> { CLOSE | RELEASE | END
STATEMENT}
The <alter session statement> is used by
an administrator to close another session or to rollback the transaction
in another session. This statement is different from the other statements
discussed in this chapter as it is not used for changing the settings of
the current session. When END STATEMENT is used, the current statement
that is waiting to run or is being executed is aborted. When RELEASE is
used, the current transaction is terminated with a rollback. The session
remains open. CLOSE may be used after RELEASE has completed.
The session ID is used as a <numeric
literal> in this statement. The administrator can use the
INFORMATION_SCHEMA.SYSTEM_SESSIONS table to find the
session IDs of other sessions.
<alter current session statement> ::= ALTER
SESSION RESET { ALL | RESULT SETS | TABLE DATA }
The <alter current session statement> is
used to clear and reset different states of the current session. When ALL
is specified, the current transaction is rolled back, the session settings
such as time zone, current schema etc. are restored to their original
state at the time the session was opened and all open result sets are
closed and temporary tables cleared. When RESULT SETS is specified, all
currently open result sets are closed and the resources are released. When
TABLE DATA is specified, the data in all temporary tables is
cleared.
SET AUTOCOMMIT
set autocommit command
<set autocommit statement> ::= SET AUTOCOMMIT {
TRUE | FALSE }
When an SQL session is started by creating a JDBC connection, it
is in AUTOCOMMIT mode. In this mode, after each SQL statement a COMMIT is
performed automatically. This statement changes the mode. It is equivalent
to using the setAutoCommit( boolean autoCommit)
method of the JDBC Connection object.
START TRANSACTION
start transaction statement
<start transaction statement> ::= START
TRANSACTION [ <transaction characteristics> ]
Start an SQL transaction and set its characteristics. All transactional SQL statements start a transaction automatically, therefore using this statement is not necessary. If the statement is called in the middle of a transaction, an exception is thrown.
SET TRANSACTION
set next transaction characteristics
<set transaction statement> ::= SET [ LOCAL ]
TRANSACTION <transaction characteristics>
Set the characteristics of the next transaction in the current session. This statement has an effect only on the next transactions and has no effect on the future transactions after the next.
transaction characteristics
transaction characteristics
<transaction characteristics> ::= [
<transaction mode> [ { <comma> <transaction mode> }... ]
]
<transaction mode> ::= <isolation level> |
<transaction access mode> | <diagnostics
size>
<transaction access mode> ::= READ ONLY | READ
WRITE
<isolation level> ::= ISOLATION LEVEL <level of
isolation>
<level of isolation> ::= READ UNCOMMITTED | READ
COMMITTED | REPEATABLE READ | SERIALIZABLE
<diagnostics size> ::= DIAGNOSTICS SIZE <number
of conditions>
<number of conditions> ::= <simple value
specification>
Specify transaction characteristics.
Example 6.3. Setting Transaction Characteristics
SET TRANSACTION READ ONLY SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED
SET CONSTRAINTS
set constraints mode statement
<set constraints mode statement> ::= SET
CONSTRAINTS <constraint name list> { DEFERRED | IMMEDIATE
}
<constraint name list> ::= ALL | <constraint
name> [ { <comma> <constraint name> }...
]
If the statement is issued during a transaction, it applies to the rest of the current transaction. If the statement is issued when a transaction is not active then it applies only to the next transaction in the current session. HyperSQL does not yet support this feature.
LOCK TABLE
lock table statement
<lock table statement> ::= LOCK TABLE <table
name> { READ | WRITE} [, <table name> { READ | WRITE}
...]}
In some circumstances, where multiple simultaneous transactions are in progress, it may be necessary to ensure a transaction consisting of several statements is completed, without being terminated due to possible deadlock. When this statement is executed, it waits until it can obtain all the listed locks, then returns. If obtaining the locks would result in a deadlock an error is raised. The SQL statements following this statement use the locks already obtained (and obtain new locks if necessary) and can proceed without waiting. All the locks are released when a COMMIT or ROLLBACK statement is issued.
When the isolation level of a session is READ COMMITTED, read locks are released immediately after the execution of the statement, therefore you should use only WRITE locks in this mode. Alternatively, you can switch to the SERIALIZABLE isolation mode before locking the tables for the specific transaction that needs to finish consistently and without a deadlock. It is best to execute this statement at the beginning of the transaction with the complete list of required read and write locks.
Currently, this command does not have any effect when the database transaction control model is MVCC.
SAVEPOINT
savepoint statement
<savepoint statement> ::= SAVEPOINT <savepoint
specifier>
<savepoint specifier> ::= <savepoint
name>
Establish a savepoint. This command is used during an SQL transaction. It establishes a milestone for the current transaction. The SAVEPOINT can be used at a later point in the transaction to rollback the transaction to the milestone.
RELEASE SAVEPOINT
release savepoint statement
<release savepoint statement> ::= RELEASE
SAVEPOINT <savepoint specifier>
Destroy a savepoint. This command is rarely used as it is not very useful. It removes a SAVEPOINT that has already been defined.
COMMIT
commit statement
<commit statement> ::= COMMIT [ WORK ] [ AND [ NO
] CHAIN ]
Terminate the current SQL-transaction with commit. This make all the changes to the database permanent.
ROLLBACK
rollback statement
<rollback statement> ::= ROLLBACK [ WORK ] [ AND [
NO ] CHAIN ]
Rollback the current SQL transaction and terminate it. The statement rolls back all the actions performed during the transaction. If NO CHAIN is specified, a new SQL transaction is started just after the rollback. The new transaction inherits the properties of the old transaction.
ROLLBACK TO SAVEPOINT
rollback statement
<rollback statement> ::= ROLLBACK [ WORK ] TO
SAVEPOINT <savepoint specifier>
Rollback part of the current SQL transaction and continue the
transaction. The statement rolls back all the actions performed after the
specified SAVEPOINT was created. The same effect can be achieved with the
rollback( Savepoint savepoint ) method of the
JDBC Connection object.
Example 6.5. Rollback
-- perform some inserts, deletes, etc. SAVEPOINT A -- perform some inserts, deletes, selects etc. ROLLBACK WORK TO SAVEPOINT A -- all the work after the declaration of SAVEPOINT A is rolled back
DISCONNECT
disconnect statement
<disconnect statement> ::=
DISCONNECT
Terminate the current SQL session. Closing a JDBC connection has the same effect as this command.
SET SESSION CHARACTERISTICS
set session characteristics statement
<set session characteristics statement> ::= SET
SESSION CHARACTERISTICS AS <session characteristic
list>
<session characteristic list> ::= <session
characteristic> [ { <comma> <session characteristic> }...
]
<session characteristic> ::= <session
transaction characteristics>
<session transaction characteristics> ::=
TRANSACTION <transaction mode> [ { <comma> <transaction
mode> }... ]
Set one or more characteristics for the current SQL-session. This
command is used to set the transaction mode for the session. This endures
for all transactions until the session is closed or the next use of this
command. The current read-only mode can be accessed with the
ISREADONLY() function.
Example 6.6. Setting Session Characteristics
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED
SET SESSION AUTHORIZATION
set session user identifier statement
<set session user identifier statement> ::= SET
SESSION AUTHORIZATION <value specification>
Set the SQL-session user identifier. This statement changes the
current user. The user that executes this command must have the
CHANGE_AUTHORIZATION role, or the DBA role. After this statement is
executed, all SQL statements are executed with the privileges of the new
user. The current authorisation can be accessed with the
CURRENT_USER and SESSION_USER
functions.
Example 6.7. Setting Session Authorization
SET SESSION AUTHORIZATION 'FELIX' SET SESSION AUTHORIZATION SESSION_USER
SET ROLE
set role statement
<set role statement> ::= SET ROLE <role
specification>
<role specification> ::= <value
specification> | NONE
Set the SQL-session role name and the current role name for the
current SQL-session context. The user that executes this command must have
the specified role. If NONE is specified, then the previous CURRENT ROLE
is eliminated. The effect of this lasts for the lifetime of the session.
The current role can be accessed with the CURRENT_ROLE
function.
SET TIME ZONE
set local time zone statement
<set local time zone statement> ::= SET TIME ZONE
<set time zone value>
<set time zone value> ::= <interval value
expression> | <string value expression> |
LOCAL
Set the current default time zone displacement for the current SQL-session. When the session starts, the time zone displacement is set to the time zone of the client. This command changes the time zone displacement. The effect of this lasts for the lifetime of the session. If LOCAL is specified, the time zone displacement reverts to the local time zone of the session that was in force prior to the use of the command.
From version 2.7.0, zone strings indicating geographical regions can be used. These zone often support daylight saving time.
This command works fine with in-process databases. When the sessions is for a connection to a server, this command should not generally be used as it only affects the server part of the session. With client / server connections, the only way to specify a session time zone that is different from the local time zone is by setting the client JVM time zone prior to connecting to the database.
Example 6.8. Setting Session Time Zone
SET TIME ZONE LOCAL SET TIME ZONE INTERVAL '+6:00' HOUR TO MINUTE SET TIME ZONE '-6:00' SET TIME ZONE 'America/Chicago'
SET CATALOG
set catalog statement
<set catalog statement> ::= SET <catalog name
characteristic>
<catalog name characteristic> ::= CATALOG
<value specification>
Set the default schema name for unqualified names used in SQL
statements that are prepared or executed directly in the current sessions.
As there is only one catalog in the database, only the name of this
catalog can be used. The current catalog can be accessed with the
CURRENT_CATALOG function.
SET SCHEMA
set schema statement
<set schema statement> ::= SET <schema name
characteristic>
<schema name characteristic> ::= SCHEMA <value
specification> | <schema name>
Set the default schema name for unqualified names used in SQL
statements that are prepared or executed directly in the current sessions.
The effect of this lasts for the lifetime of the session. The SQL Standard
form requires the schema name as a single-quoted string. HyperSQL also
allows the use of the identifier for the schema. The current schema can be
accessed with the CURRENT_SCHEMA function.
SET PATH
set path statement
<set path statement> ::= SET <SQL-path
characteristic>
<SQL-path characteristic> ::= PATH <value
specification>
Set the SQL-path used to determine the subject routine of routine invocations with unqualified routine names used in SQL statements that are prepared or executed directly in the current sessions. The effect of this lasts for the lifetime of the session.
SET MAXROWS
set max rows statement
<set max rows statement> ::= SET MAXROWS
<unsigned integer literal>
The normal operation of the session has no limit on the number of rows returned from a SELECT statement. This command set the maximum number of rows of the result returned by executing queries.
This statement has a similar effect to the
setMaxRows(int max) method of the JDBC
Statement interface, but it affects the results
returned from the next statement execution only. After the execution of
the next statement, the MAXROWS limit is removed.
Only zero or positive values can be used with this command. The
value overrides any value specified with setMaxRows(int
max) method of a JDBC statement. The statement SET
MAXROWS 0 means no limit.
It is possible to limit the number of rows returned from SELECT
statements with the FETCH <n> ROWS ONLY, or its
alternative, LIMIT <n>. Therefore, this command
is not recommended for general use. The only legitimate use of this
command is for checking and testing queries that may return very large
numbers of rows.
SET SESSION RESULT MEMORY ROWS
set session result memory rows statement
<set session result memory rows statement> ::= SET
SESSION RESULT MEMORY ROWS <unsigned integer
literal>
By default, the session uses memory to build result sets, subquery results, and temporary tables. This command sets the maximum number of rows of the result (and temporary tables) that should be kept in memory. If the row count of the result or temporary table exceeds the setting, the result is stored on disk. The default is 0, meaning all result sets are held in memory.
This statement applies to the current session only. The general database setting is:
SET DATABASE DEFAULT RESULT MEMORY ROWS <unsigned
integer literal>
SET IGNORECASE
set ignore case statement
<set ignore case statement> ::= SET IGNORECASE {
TRUE | FALSE }
This is a legacy method for creating case-insensitive columns. Still supported but not recommended for use.
Sets the type used for new VARCHAR table columns. By default,
character columns in new databases are case-sensitive. If SET
IGNORECASE TRUE is used, all VARCHAR columns in new tables are
set to use a collation that converts strings to uppercase for comparison.
In the latest versions of HyperSQL you can specify the collations for the
database and for each column and have some columns case-sensitive and some
not, even in the same table. The collation's strength is used to force
case-insensitive comparison. Collations are discussed in the Schemas and Database Objects chapter.
This statement must be switched before creating tables. Existing tables and their data are not affected.
$Revision: 6787 $
| | |
|
| Chapter 5. Data Access and Change | | Chapter 7. Text Tables |