4

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.

Gagan
  • 135
  • 1
  • 1
  • 11
  • What about defining your own function: http://therealdanvega.com/blog/2010/02/17/creating-apache-derby-custom-functions-part-2 – Bryan Pendleton Mar 03 '18 at 15:09
  • @BryanPendleton I tried using Derby as per your suggestion but it doesn't support the `limit` keyword. HSQLDB was much 'closer' to what I was looking to do. Thanks! – Gagan Mar 06 '18 at 01:58

1 Answers1

3

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.

fredt
  • 24,044
  • 3
  • 40
  • 61
  • Thanks! Took me sometime to get the schema translated to HSQLDB and create the SQL function from java, but it worked! – Gagan Mar 05 '18 at 22:00