Search code examples
hsqldb

hsqldb system functions how to use

Documentation of Hsqldb lists in chapter 4 "Built in Functions" several "System Functions" like for example DATABASE or SESSION_ID

I can't figure out how to use them? How to get for example the id of the current session? Am I supposed to write SESSION_ID as part of a sql query, if so what would be that complete query? Something like select SESSION_ID from ... comes to my mind, but from what?

Can these functions be called from Java Code?

Solution

  • All functions can be used in an SQL CALL statement. For example:

    CALL SESSION_ID()

    The CALL statement can be executed via JDBC executeQuery(). For example:

    Connection c = ...;
Statement s = c.createStatement();
ResultSet r = s.executeQuery("CALL SESSION_ID");
r.next();
long sessionId = r.getLong(1);

    A more flexible alternative to the CALL statement is the VALUES statement. This returns a table containing the specified values, which can be constants or results of function calls.

    ResultSet r1 = s.executeQuery("VALUES SESSION_ID()");

ResultSet r2 = s.executeQuery("VALUES SESSION_ID(), DATABASE()");