Prepared Statements

[Danny Gallagher <da...@generatedsystems.com>]

I have a prepared statement, that takes one parameter.

DELETE FROM TRANSAUDIT WHERE TATRACKINGID = ?

The code that executes this query is part of the database cleandown
functionality of my application.

For the data set that I am working with, the code runs properly,
deleting many records, then all of
the sudden the execution of one of these deletes blows up.

Here is what I see in the log with derby.language.logStatementText=true
I have also run it with derby.language.logQueryPlan=true, but that
information didn't seem to help me.

The log info is below, but here is a summary:
- I see the initial Begin compile and end compile for the delete
statement (I omitted it from the capture below)
- I see Executing prepared statement many, many times, as it should be,
one for each tatrackingid
- Then I get the Executing prepared statement twice, back to back for
one of
the tarackingids, my code is not executing the delete twice.
- Then an immediate begin compile and end compile of the prepared
statement again, like the compiled version that was being used
disappeared or something?
- Then the java.lang.NullPointerException with the stacktrace below

The data for that particular tatrackingid looks fine, basically like the
data for the ones that were deleted.  If I manually delete that
particular record, it still blows up, just on a different record.  

It seems like it is based on the number of times that the prepared
statement is used once it is compiled or something.  My original data
set and test was attempting to remove maybe 40,000 records from this
table, if I cut the test
down to removing 10,000 or so it works fine.  Even deleting the records
with
tatrackingids that blew up in the 40,000 record test.

I wondered if anyone had run into this sort of thing before, I didn't
see anything that jumped out at me in the mail lists or by looking at
the source code from the stack trace.  Any help would be greatly
appreciated.

Thanks



Executing prepared statement: DELETE FROM TRANSAUDIT WHERE TATRACKINGID
= ? :End prepared statement with 1 parameters begin parameter #1:
JIMG2-000100-000571 :end parameter 
DELETE FROM TRANSAUDIT WHERE TATRACKINGID = ? ******* Delete ResultSet
using row locking:

Executing prepared statement: DELETE FROM TRANSAUDIT WHERE TATRACKINGID
= ? :End prepared statement with 1 parameters begin parameter #1:
JIMG2-000100-000572 :end parameter 
DELETE FROM TRANSAUDIT WHERE TATRACKINGID = ? ******* Delete ResultSet
using row locking:

Executing prepared statement: DELETE FROM TRANSAUDIT WHERE TATRACKINGID
= ? :End prepared statement with 1 parameters begin parameter #1:
JIMG2-000100-000573 :end parameter 
Executing prepared statement: DELETE FROM TRANSAUDIT WHERE TATRACKINGID
= ? :End prepared statement with 1 parameters begin parameter #1:
JIMG2-000100-000573 :end parameter 
Begin compiling prepared statement: DELETE FROM TRANSAUDIT WHERE
TATRACKINGID = ? :End prepared statement
End compiling prepared statement: DELETE FROM TRANSAUDIT WHERE
TATRACKINGID = ? :End prepared statement
Cleanup action starting
Failed Statement is: DELETE FROM TRANSAUDIT WHERE TATRACKINGID = ? with
1 parameters begin parameter #1: JIMG2-000100-000573 :end parameter 
java.lang.NullPointerException
	at
org.apache.derby.impl.sql.execute.NoRowsResultSetImpl.finish(Unknown
Source)
	at
org.apache.derby.impl.sql.execute.DeleteResultSet.finish(Unknown Source)
	at
org.apache.derby.impl.sql.execute.BaseActivation.close(Unknown Source)
	at
org.apache.derby.impl.sql.GenericActivationHolder.execute(Unknown
Source)
	at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
Source)
	at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
Source)
	at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unkno
wn Source)
	at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown
Source)
	at
org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:396)
	at
com.gainergroup.common.database.PLBase.updateWithParams(PLBase.java:137)
	at
com.gainergroup.cte.database.PLTransAudit.delete(PLTransAudit.java:121)
	at
com.gainergroup.cte.bo.BOPayment.cleandownPayment(BOPayment.java:620)
	at
com.gainergroup.cte.gui.CleandownDialog$CleandownWiresThread.run(Cleando
wnDialog.java:172)
Cleanup action completed
Cleanup action starting
Failed Statement is: null with 1 parameters begin parameter #1:
JIMG2-000100-000573 :end parameter 
java.lang.NullPointerException
	at
org.apache.derby.impl.sql.execute.NoRowsResultSetImpl.finish(Unknown
Source)
	at
org.apache.derby.impl.sql.execute.DeleteResultSet.finish(Unknown Source)
	at
org.apache.derby.impl.sql.execute.BaseActivation.close(Unknown Source)
	at
org.apache.derby.impl.sql.GenericActivationHolder.close(Unknown Source)
	at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.closeActions(Unknown
Source)
	at org.apache.derby.impl.jdbc.EmbedStatement.close(Unknown
Source)
	at org.apache.commons.dbutils.DbUtils.close(DbUtils.java:103)
	at
org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:402)
	at
com.gainergroup.common.database.PLBase.updateWithParams(PLBase.java:137)
	at
com.gainergroup.cte.database.PLTransAudit.delete(PLTransAudit.java:121)
	at
com.gainergroup.cte.bo.BOPayment.cleandownPayment(BOPayment.java:620)
	at
com.gainergroup.cte.gui.CleandownDialog$CleandownWiresThread.run(Cleando
wnDialog.java:172)
Rolling back
Cleanup action completed








 
Danny Gallagher
Generated Systems Technologies
6525 The Corners Parkway Suite 215
Norcross, Georgia 30092
danny.gallagher@generatedsystems.com
(770) 248-0343