On 29-05-10 13:28, Knut Anders Hatlen wrote:
On 05/28/10 09:26 PM, Stephan van Loendersloot wrote:
Hello everyone,
We use some queries (via prepared statements) to implement INSERT IF NOT
EXISTS functionality.
However, 10.6.1.0 aborts with a NullPointerException and kills the
connection.
The query below is not the actual query we use... it's a simplified
version for debugging purposes.
(Stack trace follows at the end of this message)
Steps to reproduce:
ij version 10.6
ij> CONNECT 'jdbc:derby://localhost/db;create=true';
CREATE TABLE a (c1 INTEGER NOT NULL, c2 INTEGER NOT NULL);
PREPARE ins AS 'INSERT INTO a (c1, c2) SELECT ?, ? FROM a WHERE NOT
EXISTS (SELECT c1 FROM a WHERE c1 = ? AND c2 = ?)';
ij> 0 rows inserted/updated/deleted
ij> ERROR XJ001: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001,
SQLERRMC: java.lang.NullPointerExceptionXJ001.U
ij> quit;
<SNIP>
Hi Stephan,
I think this is DERBY-4671 (Embedded driver does not work with
jbossCache), and a fix for it has been checked in and back-ported to the
10.6 branch.
One workaround is to wrap the parameters in a CAST operator (the NPE is
thrown because the type of the parameters isn't known, and the CAST
operator adds the missing type information):
ij> PREPARE ins AS 'INSERT INTO a (c1, c2) SELECT CAST(? AS INT), CAST(? AS
INT)
FROM a WHERE NOT EXISTS (SELECT c1 FROM a WHERE c1 = ? AND c2 = ?)'
Hi Knut Anders,
Thanks for the investigation. I had read about DERBY-4671 and checked
out the latest trunk, since I thought it might be related to my problem.
Something must have gone wrong with my build. Did it again today... and
the problem is indeed fixed.
In the meantime, the CASTing solution you suggested works flawlessly.
Thanks,
Stephan.
