[Hsqldb-user] data type cast needed for parameter or null literal

[Kim Hansen]

When I do this:
mergeStmt = connection.prepareStatement("" //
+ "MERGE INTO data USING (VALUES(?, ?, ?, ?, ?, ?,
?, ?))" //
+ " AS vals (vid, vkey_, vtype_, vno_, vdouble_,
vstring_, vboolean_, vtimestamp_)" //
+ " ON (id, key_) = (vid, vkey_)" //
+ " WHEN MATCHED THEN UPDATE SET type_=vtype_,
no_=vno_, double_=vdouble_," //
+ " string_=vstring_, boolean_=vboolean_,
timestamp_=vtimestamp_" //
+ " WHEN NOT MATCHED THEN INSERT" //
+ " VALUES (vid, vkey_, vtype_, vno_, vdouble_,
vstring_, vboolean_, vtimestamp_)");

I get this:
[junit] Caused by: java.sql.SQLException: data type cast needed
for parameter or null literal
[junit] at org.hsqldb.jdbc.Util.sqlException(Util.java:208)
[junit] at
org.hsqldb.jdbc.JDBCPreparedStatement.<init>(JDBCPreparedStatement.java:3882)
[junit] at
org.hsqldb.jdbc.JDBCConnection.prepareStatement(JDBCConnection.java:637)
[junit] at org.stowbase.aperitivo.db.hsql.HsqlMap.<init>(HsqlMap.java:49)

Am I doing something wrong or is it a bug in HSQLDB? I am using
2.0.0-rc8 compiled for Java 1.5 based on a svn snakshop (Rev 3443).

My goal is to do Update/Insert faster than delete followed by an
insert (I have profiled my application and 80% of its time is spend on
doing that), what is the fastest way to do that in hsqldb (I usually
have 5-10 values to "upsert", and most of the time it will be an
insert)?

Regards,

[Fred Toussi]

The MERGE statement is designed for your usage. The problem is during
compilation, the engine does not know the types of the fields in the
VALUES(?, ?, ..) expression. It can detect the types in an INSERT or
UPDATE statement, but not in a MERGE statement.

As the error indicates, you need to cast the ? parameters. For example

mergeStmt = connection.prepareStatement(""
+ "MERGE INTO data USING (VALUES(CAST(? AS INT), CAST(? AS
VARCHAR(20), ....)"

The MERGE statement will help performance a bit, but not a lot. Because
internally it will do something like a select + optional delete +
insert.

If you intend to insert multiple rows, then you can write the MERGE
statement to handle several rows at a time.

Fred

Post by Kim Hansen
mergeStmt = connection.prepareStatement("" //
+ "MERGE INTO data USING (VALUES(?, ?, ?, ?, ?, ?,
?, ?))" //
+ " AS vals (vid, vkey_, vtype_, vno_, vdouble_,
vstring_, vboolean_, vtimestamp_)" //
+ " ON (id, key_) = (vid, vkey_)" //
+ " WHEN MATCHED THEN UPDATE SET type_=vtype_,
no_=vno_, double_=vdouble_," //
+ " string_=vstring_, boolean_=vboolean_,
timestamp_=vtimestamp_" //
+ " WHEN NOT MATCHED THEN INSERT" //
+ " VALUES (vid, vkey_, vtype_, vno_, vdouble_,
vstring_, vboolean_, vtimestamp_)");
[junit] Caused by: java.sql.SQLException: data type cast needed
for parameter or null literal
[junit] at org.hsqldb.jdbc.Util.sqlException(Util.java:208)
[junit] at
org.hsqldb.jdbc.JDBCPreparedStatement.<init>(JDBCPreparedStatement.java:3882)
[junit] at
org.hsqldb.jdbc.JDBCConnection.prepareStatement(JDBCConnection.java:637)
[junit] at
org.stowbase.aperitivo.db.hsql.HsqlMap.<init>(HsqlMap.java:49)
Am I doing something wrong or is it a bug in HSQLDB? I am using
2.0.0-rc8 compiled for Java 1.5 based on a svn snakshop (Rev 3443).
My goal is to do Update/Insert faster than delete followed by an
insert (I have profiled my application and 80% of its time is spend on
doing that), what is the fastest way to do that in hsqldb (I usually
have 5-10 values to "upsert", and most of the time it will be an
insert)?
Regards,
--
Kim Hansen
Vadgårdsvej 3, 2.tv
2860 Søborg
Fastnet: 3956 2437 -- Mobil: 3091 2437
------------------------------------------------------------------------------
The Planet: dedicated and managed hosting, cloud storage, colocation
Stay online with enterprise data centers and the best network in the
business
Choose flexible plans and management services without long-term contracts
Personal 24x7 support from experience hosting pros just a phone call
away.
http://p.sf.net/sfu/theplanet-com
_______________________________________________
Hsqldb-user mailing list
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Kim Hansen]

Post by Fred Toussi
The MERGE statement is designed for your usage. The problem is during
compilation, the engine does not know the types of the fields in the
VALUES(?, ?, ..) expression. It can detect the types in an INSERT or
UPDATE statement, but not in a MERGE statement.
As the error indicates, you need to cast the ? parameters. For example
            mergeStmt = connection.prepareStatement(""
            + "MERGE INTO data USING (VALUES(CAST(? AS INT), CAST(? AS
            VARCHAR(20), ....)"

I expected something like that, but I couldn't find it in the
documentation because I was looking for "<table reference>" mentioned
in the MERGE INTO documentation.

I guess I can make it work now.

Post by Fred Toussi
The MERGE statement will help performance a bit, but not a lot. Because
internally it will do something like a select + optional delete +
insert.
If you intend to insert multiple rows, then you can write the MERGE
statement to handle several rows at a time.

I'll try a little and see how much it helps.

Thanks,

[Kim Hansen]

Post by Fred Toussi
The MERGE statement will help performance a bit, but not a lot. Because
internally it will do something like a select + optional delete +
insert.

Actually the MERGE statement was 10% slower than a DELETE+INSERT.

I didn't try the version where I added many rows at the same time as I
didn't use that trick with DELETE+INSERT and I wanted a fair
comparison.

[fredt]

When used with a server instance, MERGE will be a lot faster than executing
two statements.

Fred
----- Original Message -----
From: "Kim Hansen" <***@i9.dk>
To: "HSQLdb user discussions" <hsqldb-***@lists.sourceforge.net>
Sent: 02 February 2010 22:26
Subject: Re: [Hsqldb-user] data type cast needed for parameter or
nullliteral

Post by Fred Toussi
The MERGE statement will help performance a bit, but not a lot. Because
internally it will do something like a select + optional delete +
insert.

Actually the MERGE statement was 10% slower than a DELETE+INSERT.

I didn't try the version where I added many rows at the same time as I
didn't use that trick with DELETE+INSERT and I wanted a fair
comparison.

[Kim Hansen]

Post by fredt
When used with a server instance, MERGE will be a lot faster than executing
two statements.

Ok, I am using the database embedded, that is why I don't get the speedup.