Thread: EmbedPreparedStatement.executeUpdate(Unknown Source) SQLException

I have a nested select prepared statement used to compare two tables and delete rows with matching columns from one table. This was working previously but for some reason now fails with a SQLException.

Here is the stack trace:

Java exception: ': java.lang.StackOverflowError'.
SQLCode : 0
SQLState : XJ001
org.apache.derby.impl.jdbc.SQLExceptionFactory40.g etSQLException(Unknown Source)
org.apache.derby.impl.jdbc.Util.newEmbedSQLExcepti on(Unknown Source)
org.apache.derby.impl.jdbc.Util.javaException(Unkn own Source)
org.apache.derby.impl.jdbc.TransactionResourceImpl .wrapInSQLException(Unknown Source)
org.apache.derby.impl.jdbc.TransactionResourceImpl .handleException(Unknown Source)
org.apache.derby.impl.jdbc.EmbedConnection.handleE xception(Unknown Source)
org.apache.derby.impl.jdbc.ConnectionChild.handleE xception(Unknown Source)
org.apache.derby.impl.jdbc.EmbedStatement.executeS tatement(Unknown Source)
org.apache.derby.impl.jdbc.EmbedPreparedStatement. executeStatement(Unknown Source)
org.apache.derby.impl.jdbc.EmbedPreparedStatement. executeUpdate(Unknown Source)


And this is the query string and code from my JAVA project:

String sqlStr = "DELETE FROM ROOT.MYTABLE2 WHERE ((COLUMN1 NOT IN (SELECT COLUMN1 FROM ROOT.MYTABLE1))OR(COLUMN2 NOT IN (SELECT COLUMN2 FROM ROOT.MYTABLE1))OR(COLUMN3 NOT IN (SELECT COLUMN3 FROM ROOT.MYTABLE1))OR(COLUMN4 NOT IN (SELECT COLUMN4 FROM ROOT.MYTABLE1))OR(COLUMN5 NOT IN (SELECT COLUMN5 FROM ROOT.MYTABLE1))OR(COLUMN6 NOT IN (SELECT COLUMN6 FROM ROOT.MYTABLE1))OR(COLUMN7 NOT IN (SELECT COLUMN7 FROM ROOT.MYTABLE1))OR(COLUMN8 NOT IN (SELECT COLUMN8 FROM ROOT.MYTABLE1)))";

PreparedStatement ps = mySqlConn.prepareStatement(sqlStr);

ps.executeUpdate(); //this is the line that causes the exceptions

I am sure this statement could be written much better in another way and would welcome advice on that too but my main concern is getting it working again. Basically I need to delete rows from myTable2 where columns 1 to 8 do not match columns 1 to 8 in myTable1. Both tables have the same structure with 10 columns. Perhaps better put like this: Delete from myTable2 rows that do not exist in myTable1 but need to ignore columns 9 and 10 for the comparison.


The method in which these lines reside is preceded by a number of similar methods. They all have a finally clause to ensure that database connections are closed(after a commit). Could it be that one of these is not releasing the row locks on one of the tables? If so then how do I ensure they get released? I already have conn.commit(); and conn.close(); and set conn.setAutoCommit(true);
I am using Derby 10.9.1.0, Netbeans 7.2.1, JDK 1.7 and JRE7 on a windows server 2003.
Help me!


--- Update ---

Also here is the entry from the derby.log file:

Wed Jan 16 15:18:40 GMT 2013 Thread[pool-1-thread-1,5,main] (XID = 4058482), (SESSIONID = 9), (DATABASE = myDatabase), (DRDAID = null), Cleanup action starting
Wed Jan 16 15:18:40 GMT 2013 Thread[pool-1-thread-1,5,main] (XID = 4058482), (SESSIONID = 9), (DATABASE = myDatabase), (DRDAID = null), Failed Statement is: DELETE FROM ROOT.MYTABLE2 WHERE ((COLUMN1 NOT IN (SELECT COLUMN1 FROM ROOT.MYTABLE1))OR(COLUMN2 NOT IN (SELECT COLUMN2 FROM ROOT.MYTABLE1))OR(COLUMN3 NOT IN (SELECT COLUMN3 FROM ROOT.MYTABLE1))OR(COLUMN4 NOT IN (SELECT COLUMN4 FROM ROOT.MYTABLE1))OR(COLUMN5 NOT IN (SELECT COLUMN5 FROM ROOT.MYTABLE1))OR(COLUMN6 NOT IN (SELECT COLUMN6 FROM ROOT.MYTABLE1))OR(COLUMN7 NOT IN (SELECT COLUMN7 FROM ROOT.MYTABLE1))OR(COLUMN8 NOT IN (SELECT COLUMN8 FROM ROOT.MYTABLE1)))
java.lang.StackOverflowError
at org.apache.derby.impl.sql.execute.RealResultSetSta tisticsFactory.getNoRowsResultSetStatistics(Unknow n Source)
at org.apache.derby.impl.sql.execute.RealResultSetSta tisticsFactory.getResultSetStatistics(Unknown Source)
at org.apache.derby.impl.sql.execute.RealResultSetSta tisticsFactory.getRunTimeStatistics(Unknown Source)
at org.apache.derby.impl.sql.execute.NoRowsResultSetI mpl.close(Unknown Source)
at org.apache.derby.impl.sql.execute.DeleteResultSet. cleanUp(Unknown Source)
at org.apache.derby.impl.sql.execute.DeleteResultSet. open(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement .executeStmt(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement .execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeS tatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement. executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement. executeUpdate(Unknown Source)
at com.CcmIanaJapp.Sql.Sql.deleteTable2NotInTable1(Sq l.java:1188)

This could well be returning no results and so have nothing to delete. Could this be causing the problem? If so how do I protect against this situation?
Thanks

Originally Posted by copeg

The exception is coming from Derby, so this is most likely your query - I don't know what specifically about the query is causing it, but can say the query you are using could probably be constructed better. For instance, using one or more JOIN's, intersect, or except.

Thanks for your advice I will explore constructing the query using JOIN's.
However, if I run the current query directly on the database through netbeans it works without any errors. It only fails when run as a prepared statement with ps.executeUpdate. So it must be something to do with connection/locks/authentication...............?

--- Update ---

I managed to work around it by replacing the original query:

DELETE FROM MYTABLE2 WHERE ((COLUMN1 NOT IN (SELECT COLUMN1 FROM MYTABLE1))OR(COLUMN2 NOT IN (SELECT COLUMN2 FROM MYTABLE1))OR(COLUMN3 NOT IN (SELECT COLUMN3 FROM MYTABLE1))OR(COLUMN4 NOT IN (SELECT COLUMN4 FROM MYTABLE1))OR(COLUMN5 NOT IN (SELECT COLUMN5 FROM MYTABLE1))OR(COLUMN6 NOT IN (SELECT COLUMN6 FROM MYTABLE1))OR(COLUMN7 NOT IN (SELECT COLUMN7 FROM MYTABLE1))OR(COLUMN8 NOT IN (SELECT COLUMN8 FROM MYTABLE1)))

with

DELETE FROM MYTABLE2 WHERE NOT EXISTS (SELECT * FROM MYTABLE1 WHERE MYTABLE2.COLUMN1=MYTABLE1.COLUMN1 AND MYTABLE2.COLUMN2=MYTABLE1.COLUMN2 AND MYTABLE2.COLUMN3=MYTABLE1.COLUMN3 AND MYTABLE2.COLUMN4=MYTABLE1.COLUMN4 AND MYTABLE2.COLUMN5=MYTABLE1.COLUMN5 AND MYTABLE2.COLUMN6=MYTABLE1.COLUMN6 AND MYTABLE2.COLUMN7=MYTABLE2.COLUMN7 AND MYTABLE2.COLUMN8=MYTABLE1.COLUMN8)

This works as preparedStatement. No idea why the original one stopped working even though it had been run hundreds of times successfully previously and could still be run directly on the database. Still exploring ways to improve efficiency here as this takes a long time if there are many rows in the tables. But at least it works................