MySQL function 'HEX' in H2 / Derby / HSQL

Question

I'm trying to unit test sql queries using an in-memory db. The queries make use of the MySQL in-built function 'hex' and 'unhex' (because they write binary data to the DB). Turns out neither of these 3 in-momery DB solutions support the 'hex' function, and I'm unable to test them.

My questions - is there no way to test the queries without moving hex conversion to code instead the 'hex' function directly in the query?

ps. I know in-memory DBs are not representative of the real db behavior but the queries are pretty straightforward - inserts and selects.

Answer 1

HSQLDB allows you to define your own functions. If you are not testing the actual hex or string return values, you can create mock functions HEX and UNHEX to return the input. For example:

CREATE FUNCTION UNHEX(S VARCHAR(1000)) RETURNS VARCHAR(1000) 
 BEGIN ATOMIC
   RETURN S;
 END;

If you want the functions to return the actual converted values you can extend the functions to perform the conversions. In this case, you can use the built-in functions to extract the ASCII codes, as well as any existing static Java method:

Update: support for both functions has been added to HSQLDB for version 2.4.1.