Intermittent PreparedStatement Error

[Inns, Jeff]

Derby version: 10.1.3.1 as of 08/09/2006 Platform: WinTel, RHEL on Intel, FC5 on Intel   Our application runs Derby in a JBoss JVM in embedded mode and also serves remote GUI Clients in Server Mode (1 JVM, embedded and server mode simultaneously).   Occasionally we get the following exception:   2006-09-14 12:45:03,127 INFO  [STDOUT] Caused by: SQL Exception: Invalid character string format for type BIGINT. 2006-09-14 12:45:03,127 INFO  [STDOUT]    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:195) 2006-09-14 12:45:03,127 INFO  [STDOUT]    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:381) 2006-09-14 12:45:03,127 INFO  [STDOUT]    at org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(EmbedResultSet.java:3811) 2006-09-14 12:45:03,127 INFO  [STDOUT]    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setString(EmbedPreparedStatement.java:478) 2006-09-14 12:45:03,127 INFO  [STDOUT]    at com.extol.util.jdbc.CachePreparedStatement.setString(CachePreparedStatement.java:469)   By occasionally, I mean, we might get the error once in 50,000 database hits (not consistently).  Once we get the error, the only thing that fixes it is shutting the JVM down and restarting it.   I haven’t been able to write a simple test case that reproduces the problem, but I can reproduce it some what consistently inside our application under certain conditions.   The snippet of code that produces the above error is:   PreparedStatement stmt = connection.prepareStatement(select "A", "B" from "SCHEMA"."TABLE_A" where "C" = ? and "D" = ? ORDER BY "E"); stmt.setString(1, “some string”); stmt.setInt(2, an int);   I’ve taken the liberty to instrument the Derby code with some debugging information and here is what is produced:   Exception thrown in : org.apache.derby.impl.jdbc.EmbedPreparedStatement30 EmbedPreparedStatement.SQLText         = select "A", "B" from "SCHEMA"."TABLE_A" where "C" = ? and "D" = ? ORDER BY "E" preparedStatement.getSource            = update "SCHEMA".”TABLE_B” set A = ? where B = ? Activation.PreparedStatement.getSource = update "SCHEMA".”TABLE_B” set A = ? where B = ?   DataTypeDescriptor's from preparedStatement : 2 DTD string: BIGINT DTD string: CHAR(18)   I’ve removed the actual table and column names.  In the error that I get in our app, there are not any similarities between the column and table names – the above convention is just for illustrative purposes.   A couple of interesting things about this problem:   1.    The problem seems to be exacerbated when there is a lot of simultaneous DB access through the embedded and network interfaces.  2.    The exception is always thrown at the same point in our code (see snippet). That exact snippet of code runs in both JVM’s. 3.    The SQL in the “preparedStatement.getSource” and “Activation.PreparedStatement.getSource” objects vary between instances of the exception. So, for example, the first time we get the exception, the SQL returned by the “getSource” methods may look as it does above.  The second time we get the exception, the SQL may look like this: “select * from “SCHEMA”.”TABLE_X” where “P” = ?”   We have found a work around.  If we set the system property “derby.language.statementCacheSize” to a value of “1”, we never get the exception.  We have tried setting the property to “1000”, and it helps in that we don’t get the error as frequently, but it still happens eventually.   A couple of interesting things about our application:   1. We don’t use Hibernate, CMP or JDO. 2. We have an in house framework for caching SQL connections – at a maximum we could have 16 open connections to the database per JVM.  Practically, we don’t usually see more than 10 or 15 (1 remote JVM and 1 embedded JVM). Theoretically we could have 32. 3. All access to Derby in the Server JVM (JBoss) is done via the embedded interface. 4. All access to Derby in the GUI JVM is done via the network interface. 5. There are about 500 records in the table in question the first time the error occurs.   I can provide SQL for the table definitions if that is necessary.   Could this problem stem from unclosed statements or result sets?   Regards, Jeff Inns