0

Database engine: HSQLDB (because, well, it allows an embedded application DB in Java)

Language: Java 8 (actually, Groovy 3.0.2, but it's basically JRE)

Schema as follows:

  CREATE TABLE A (
    START VARBINARY(16) NOT NULL,
    END VARBINARY(16) NOT NULL,
    ID BIGINT NOT NULL,
    COUNTRY VARCHAR(8),
    DESCRIPTION VARCHAR(256),
    PRIMARY KEY (START, END)
  );

START and END are IPv4 or IPv6 addresses encoded as 4 or 16 element byte arrays.

I get an error "org.hsqldb.HsqlException: incompatible data type in conversion" in this code (simplified):

stmt.addBatch(a, b, c, d, e)

When using this "upsert" to insert data which doesn't already exist in the table:

  MERGE INTO A
  USING (VALUES ?,?,?,?,?) I (START, END, ID, COUNTRY, DESCRIPTION)
  ON (A.START = I.START AND A.END = I.END)
  WHEN MATCHED THEN UPDATE
    SET A.ID = I.ID, A.COUNTRY = I.COUNTRY, A.DESCRIPTION = I.DESCRIPTION 
  WHEN NOT MATCHED THEN INSERT (START, END, ID, COUNTRY, DESCRIPTION)
    VALUES (I.START, I.END, I.ID, I.COUNTRY, I.DESCRIPTION)

Here a and b are byte[], c is a long and d and e are Strings and stmt is a BatchingPreparedStatementWrapper.

Debugging implies that the stmt variable thinks that parameters 1 and 2 of the statement (a and b) are of type VARCHAR... they should be VARBINARY(16).

In fact it seems to think all the parameters are VARCHARs. This seems odd.

I don't think there's anything obviously wrong with my SQL, as it used to work when I had the START and END fields defined as VARCHARs. It just doesn't now they're VARBINARYs.

(The reason I want VARBINARIES is to allow numeric style comparisons between VARBINARY values.)

My Googling finds nothing relevant, but perhaps someone here can offer a clue?

GMB
  • 216,147
  • 25
  • 84
  • 135
wu-lee
  • 749
  • 4
  • 17

1 Answers1

2

Does it work it you explicitly cast() the input parameter in the query?

MERGE INTO A
USING 
    (VALUES CAST(? AS VARBINARY(16)), CAST(? AS VARBINARY(16)), ?, ?, ?) 
    I (START, END, ID, COUNTRY, DESCRIPTION)
ON (A.START = I.START AND A.END = I.END)
WHEN MATCHED 
    THEN UPDATE
    SET A.ID = I.ID, A.COUNTRY = I.COUNTRY, A.DESCRIPTION = I.DESCRIPTION 
WHEN NOT MATCHED 
    THEN INSERT (START, END, ID, COUNTRY, DESCRIPTION)
    VALUES (I.START, I.END, I.ID, I.COUNTRY, I.DESCRIPTION)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Hey, that does seem to work for the upsert! I wonder why casting is required? But just investigating how much other CASTing I need to do with queries... – wu-lee Apr 22 '20 at 21:50
  • @wu-lee: presumably the driver is not mapping the parameter to the correct datatype... There might be options to fix that when binding the parameter (but I can't tell you how since I don't know a lot about jdbc). – GMB Apr 22 '20 at 21:57
  • It's better to cast the value for c as BIGINT, to avoid conversion to VARCHAR and back to BIGINT. – fredt Apr 23 '20 at 11:04