Thursday, February 04, 2010

HSQL random function - calling scalar functions via JDBC

HSQL JDBC select random scalar built-in function rand fn

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?

Short answer is it's called "rand". So the SQL would be:
CALL rand()
With JDBC this is:
PreparedStatement stmt = connection.prepareStatement("CALL rand()");
... 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()}");

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 :)