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; > > > It used to work in 10.5.3.0: > > > ij version 10.5 > ij> CONNECT 'jdbc:derby://localhost/db;create=true'; > ij> CREATE TABLE a (c1 INTEGER NOT NULL, c2 INTEGER NOT NULL); > 0 rows inserted/updated/deleted > ij> PREPARE ins AS 'INSERT INTO a (c1, c2) SELECT ?, ? FROM a WHERE NOT > EXISTS (SELECT c1 FROM a WHERE c1 = ? AND c2 = ?)'; > ij> quit; > > If I fill in the first parameters of the statement (i.e. SELECT 1,1) it > does work, but since these first two need to correspond with the last > two, the advantage of using prepared statements is lost. > > I didn't see anything regarding this particular error in the release > notes, so I wonder if anyone can confirm that this is a bug? If so, I'll > file a JIRA issue. >
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 = ?)' -- Knut Anders
