tags:

views:

46

answers:

3
+1  Q: 

Derby INSERT SELECT null pointer exception

I'm writing a Java application that's working with Apache Derby via JDBC. I'm having problems with the code in the following snippet:

byte md5[] = md5sum(file);

PreparedStatement s = con.prepareStatement("INSERT INTO input_files (job_ID, hash) SELECT job_id, ? FROM job WHERE job_name = ?");
s.setBytes(1, md5);
s.setString(2, jobName);
s.executeUpdate();
s.close();

This inserts into the following tables:

CREATE TABLE input_files
(
    hash char(16) for bit data,
    job_id integer REFERENCES job,
    PRIMARY KEY(job_id, hash)
);

CREATE TABLE job
(
    job_id integer PRIMARY KEY GENERATED ALWAYS as IDENTITY,
    job_name character varying(50) UNIQUE NOT NULL,
    #other fields
);

The idea is that there is an internal integer job_id which is used internally by the database, but we want to reference it as much as possible with the human-readable String job_name

This consistently throws the following exception.

java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(UnknownSource)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
    at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at [line 3 of snippet above]

As you can see, this is thrown from the line where the prepared statement is created - it doesn't get as far as executing it. What am I doing wrong? The exact same prepared statement works fine with PostgreSQL.

[edit] Derby.log:

----------------------------------------------------------------
2010-08-18 08:47:08.779 GMT:
 Booting Derby version The Apache Software Foundation - Apache Derby - 10.6.1.0 - (938214): instance a816c00e-012a-8461-611c-0000046700d0 
on database directory /path/to/myDatabase   with class loader sun.misc.Launcher$AppClassLoader@6d6f0472

Database Class Loader started - derby.database.classpath=''
2010-08-18 08:47:12.067 GMT Thread[SwingWorker-pool-1-thread-1,5,main] (XID = 316), (SESSIONID = 1), (DATABASE = myDatabase), (DRDAID = null), Cleanup action starting
2010-08-18 08:47:12.067 GMT Thread[SwingWorker-pool-1-thread-1,5,main] (XID = 316), (SESSIONID = 1), (DATABASE = myDatabase), (DRDAID = null), Failed Statement is: INSERT INTO input_files (job_id, hash) SELECT job_id, ? FROM job WHERE job_name = ?
java.lang.NullPointerException
    at org.apache.derby.impl.sql.compile.BitTypeCompiler.storable(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(Unknown Source)
    at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
    at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at [line 3 of snippet]
Cleanup action completed

2010-08-18 08:47:12.084 GMT:
Shutting down instance a816c00e-012a-8461-611c-0000046700d0 with class loader sun.misc.Launcher$AppClassLoader@6d6f0472 
----------------------------------------------------------------

and unrolled exception:

java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
    at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at [line 3 of snippet above]
Caused by: java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 26 more
Caused by: java.lang.NullPointerException
    at org.apache.derby.impl.sql.compile.BitTypeCompiler.storable(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(Unknown Source)
    at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 19 more
SQLState: XJ001
Error code: 0
Message: Java exception: ': java.lang.NullPointerException'.
Cause: java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
    at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at [line 3 of snippet above]
Caused by: java.lang.NullPointerException
    at org.apache.derby.impl.sql.compile.BitTypeCompiler.storable(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(Unknown Source)
    at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 19 more
Cause:  java.lang.NullPointerException
    at org.apache.derby.impl.sql.compile.BitTypeCompiler.storable(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(Unknown Source)
    at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
    at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at [line 3 of snippet above]
+2  A: 

A null pointer exception in this context - especially if the same JDBC code works with another DBMS - looks like you've hit a bug in the Derby JDBC driver. Even if you'd made a mistake and passed erroneous data or a null pointer, the driver should be detecting that and reporting an error, not crashing.

Jonathan Leffler  2010-08-17 14:51:24
I agree, this looks like a bug in Derby. You can probably get more information (that is, a more complete stack trace), by unwinding the exception chain: http://wiki.apache.org/db-derby/UnwindExceptionChainAlso, have a look in derby.log for more information.
Bryan Pendleton  2010-08-17 17:14:44
Bug filed: https://issues.apache.org/jira/browse/DERBY-4780
Scott  2010-08-18 09:41:10
For the record, apparently this was a known bug, and has been fixed in the Derby source. The work around is to add a cast around the parameter - i.e. `INSERT INTO input_files (job_ID, hash) SELECT job_id, CAST(? as CHAR(16) FOR BIT DATA) FROM job WHERE job_name = ?`
Scott  2010-08-18 11:53:09
A:  
 PreparedStatement s = con.prepareStatement("INSERT INTO input_files (job_ID, hash) SELECT job_id, ? FROM job WHERE job_name = ?");

"job_ID" vs "job_id" match the case and see if that corrects the problem.

Knife-Action-Jesus  2010-08-17 15:18:48
No joy. I thought SQL was case-insensitive, anyway.
Scott  2010-08-17 15:32:56
@Scott: SQL is case-insensitive unless you are using delimited identifiers (double quotes in the standard, square brackets in MS SQL Server, back ticks in MySQL, etc).
Jonathan Leffler  2010-08-18 00:08:25
+1  A: 

I suspect you won't be able to jam the literal hash code directly into the select column list, because Derby doesn't understand that you want to put a literal value there, rather than a column name.

A reasonable workaround might be to re-code your application to perform the INSERT statement from inside a loop which is reading the values from the SELECT. Something along the lines of:

updateStatement = prepareStatement("insert into input_files (job_id, hash) values (?,?)")
ResultSet rs = executeQuery("select job_id from job where job_name = ?")
while (rs.next())
   updateStatement.setString(1, rs.getString(1))
   updateStatement.setBytes(2, md5sum)
   updateStatement.executeUpdate()
Bryan Pendleton  2010-08-17 17:20:43
As a workaround, I think this is what we'll end up doing. We actually do similar queries in a few places, so if we cache the value of job_id we may actually end up improving our database performance.
Scott  2010-08-18 09:43:33

related questions

Backup SQL Schema Only?
Sql to query a dbs scheme
Timer-based event triggers
Sql query, count and group by
Paging SQL Server 2005 Results
SQL 2005 For XML Explicit - Need help formatting
How do I use T-SQL Group By
What all do I need to escape when sending a (My)SQL query?
Split String in SQL
Datatable vs Dataset
ASP.NET MVC "CRUD" Database Sample
Convert HashBytes to VarChar
Best Book for a new Database Developer
What is the best way to avoid SQL injection attacks?
What language do you use for Postgresql triggers and stored procedures?
What is the best way to handle multiple permission types?
How do I index a database field
Swap unique indexed column values in database.
cx_Oracle - what is the best way to iterate over a result set?
cx_Oracle - How do I access Oracle from Python?
Is there a version control system for database structure changes?
How to export data from SQL Server 2005 to MySQL
ASP.NET Site Maps
Decoding T-SQL CAST in C#/VB.net
Flat File Databases in PHP