Search code examples
javajunitprepared-statementhsqldbsap-ase

Sybase ASE to HSQLDB JUnit java.sql.SQLSyntaxErrorException: type not found or user lacks privilege

In a project I have inherited, there is a prepared statement defined as:

<select id="GET_FILE_BY_FILE_ID" parameterType="long" resultType="com.employer.my.File" statementType="PREPARED">
  SELECT  
    file_id fileId,
    file_name name,
    file_type type,
    CASE WHEN file_data_long is null THEN convert (image, file_data_short) ELSE file_data_long END AS fileData
    FROM FILES_TABLE
    WHERE file_id = #{id}
</select>

This SQL statements works fine at runtime, with a Sybase ASE database.

But the JUnit that executes it (as part of the build), keeps failing with

java.sql.SQLSyntaxErrorException: type not found or user lacks privilege: FILE_DATA_SHORT

I tracked this error to the CASE THEN convert statement:

    CASE WHEN file_data_long is null THEN convert (image, file_data_short) ELSE file_data_long END AS fileData

That is, the following prepared statement, does NOT produce said error:

<select id="GET_FILE_BY_FILE_ID" parameterType="long" resultType="com.employer.my.File" statementType="PREPARED">
  SELECT  
    file_id fileId,
    file_name name,
    file_type type,
    file_data_short fileData
    FROM FILES_TABLE
    WHERE file_id = #{id}
</select>

Suspecting this may have to do with convert, I came across this SO answer which suggests that to run HSQLDB with the a non-HSQLDB dialect one must first enable that syntax compatibility mode on HSQLDB.

A similar hint was found in this SO answer.

With that I have been able to find Sybase specific directive in the hsqldb.org doc:

"Use SET DATABASE SQL SYNTAX MSS TRUE or the equivalent URL property sql.syntax_mss=true to enable support for the CONVERT(<type definition>, <expression) function with switched order of arguments"

Well, I did just that, adding sql.syntax_mss=true to the project's HSDLDB properties:

HSQLDB(org.hsqldb.jdbc.JDBCDriver.class, org.hsqldb.jdbc.JDBCDataSource.class, "jdbc:hsqldb:mem:mymemdb;sql.syntax_mss=true", new TestMapperHsqlDB(), true)

But that didn't help: I am still getting that dreaded exception when running the JUnit test (only then. That query runs fine at runtime or from DBeaver).

Any idea what else I could be missing in attempting to make this work for both runtime (Sybase/ASE) and JUnit (HSDLDB)?

Solution

  • Answering my own question, for the benefit of others who may be perplexed by a similar encounter:

    After attempting the syntax/dialect modifier (see OP), I found this list of HSQLDB built it in functions, where the 'image' is not a supported type by HSQLDB, so convert has no chance of succeeding...

    That prompted me to look for a solution/workaround where the SQL itself would be slightly different based on the database on which it is running:

    <select id="GET_FILE_BY_FILE_ID" parameterType="long" resultType="com.employer.my.File" statementType="PREPARED">
  SELECT  
    file_id fileId,
    file_name name,
    file_type type,
    <if test="_databaseId == 'SYBASE'">
      CASE WHEN file_data_long is null THEN convert (image, file_data_short) ELSE file_data_long END AS fileData
    </if>  
    <if test="_databaseId == 'HSQLDB'">
        CASE WHEN FFT.file_data_long is null  THEN FFT.file_data_short  ELSE FFT.file_data_long  END AS fileData
    </if>
    FROM FILES_TABLE FFT
    WHERE file_id = #{id}
</select>

    And this -- works like a charm.