HSQL random function - calling scalar functions via JDBC

Keywords:
HSQL JDBC select random scalar built-in function rand fn

Problem:
I want to demstrate database interaction but the data doesn't really matter in this case. HSQL is ideal because you can set a connection to in-memory database .. but there's no data. Random would do, is there a random function in HSQL? Where is the function reference? Is it called rand(), random() or other?

Solution:
Short answer is it's called "rand". So the SQL would be:

CALL rand()

With JDBC this is:

PreparedStatement stmt = connection.prepareStatement("CALL rand()");
stmt.execute();

... and I can't find a complete function reference for HSQL.


Digging a bit further there is JDBC convention for calling scalar/built-in function in a vendor independent way - See JDBC 2.0: Escape Syntax and Scalar Functions. This involves the use of the "fn" keyword and braces - so the syntax is:

{fn <function()>}

Whether the JDBC driver recognises the syntax and maps it correctly to the underlying function ("rand" is called "random" in postgres for example) is up to the vendor. I can confirm that HSQL seems to handle most numeric, string and date functions (as mentioned above, can't find doco for this. The derby JDBC function reference is a reasonably good overview). So the vendor agnostic random call becomes:

PreparedStatement stmt = connection.prepareStatement("CALL {fn rand()}");

Notes:
What I could find was documentation of HSQL "Java Stored Procedures". This effectively lets you call any public static java method (from a class in the classpath) right there in the SQL statement. So, yet another (non-JDBC standard) way of making the random call would be:

CALL "java.lang.Math.random"()

... or doing more work in the SQL:

CALL "java.lang.Math.floor"("java.lang.Math.random"() * 100)

Pretty cool, I must think of a use for this :)