Thursday, February 18, 2010

Configure endorsed libraries in Tomcat 6

Tomcat 6 endorsed java.endorsed.dirs Endorsed Standards Override Mechanism XML libraries xerces

If your webapp needs its own XML libraries (xerces in particular) how do you get Tomcat 6 to use this and not the JAXP APIs packaged into the JSE? This used to be as simple as dropping them into ${CATALINA_BASE}/common/endorsed but there's only a ${CATALINA_BASE}/lib folder ...

Thankfully found the solution in this blog (and comments).

Simply, create a ${CATALINA_BASE}/endorsed folder and drop the jar files in there. Tomcat will be setup to use this if it exists.

No explicit mention of this in Tomcat 6 Class Loader notes

It does note the -Djava.endorsed.dirs system property is set but you need to check setclasspath.[bat|sh] for when it's set and what it's set to by default - ie ${CATALINA_BASE}/endorsed.

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