You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Raymond Kroeker <ra...@raykroeker.com> on 2008/07/26 01:08:08 UTC
Caching Prepared Statements
Hi All,
I'm having a big of an issue with my cached prepared statements;
and would like a little feedback.
When my application upgrades itself to a new version; it executes
DDL scripts through my data layer which caches prepared statements.
When the user jumps 2 versions; you have the following sql:
update META set VALUE='1' where KEY='version';
alter table META alter column VALUE set data type varchar(256);
update META set value='2' where KEY='version;
And the original table definition as follows:
create table META(ID bigint generated always as identity(start with
1000),KEY varchar(64) not null,VALUE varchar(128) not null,primary
key(ID));
The issue is that when I issue an update on the cached prepared
statement I get an error of:
----------------STACK-BEGIN------------------------
Caused by: java.sql.SQLException: A PreparedStatement has been
recompiled and the parameters have changed. If you are using JDBC you
must prepare the statement again.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:203)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:380)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:345)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:1378)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1272)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1635)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:299)
----------------STACK-END-------------------------
My question is as follows; is there a generic (read jdbc) api I
can use to determine when a statement needs to be dumped from cache?
Derby: 10.2.2.0
JDK 6.0 u1
Ubuntu 8.04
--
---------------------------------------------------------
Raymond Kroeker
Re: Caching Prepared Statements
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Raymond,
I'm not aware of a JDBC method which returns the information you need. I
ran your experiment on both 10.2 and the current trunk and verified your
results. The isClosed() method (which PreparedStatement inherits from
Statement and which was introduced in Java 6) returned false throughout
the experiment. That was the only method which I thought might give you
the information you need.
Hope this helps,
-Rick
Raymond Kroeker wrote:
> Hi All,
> I'm having a big of an issue with my cached prepared statements;
> and would like a little feedback.
>
> When my application upgrades itself to a new version; it executes
> DDL scripts through my data layer which caches prepared statements.
> When the user jumps 2 versions; you have the following sql:
>
> update META set VALUE='1' where KEY='version';
> alter table META alter column VALUE set data type varchar(256);
> update META set value='2' where KEY='version;
>
> And the original table definition as follows:
> create table META(ID bigint generated always as identity(start with
> 1000),KEY varchar(64) not null,VALUE varchar(128) not null,primary
> key(ID));
>
> The issue is that when I issue an update on the cached prepared
> statement I get an error of:
> ----------------STACK-BEGIN------------------------
> Caused by: java.sql.SQLException: A PreparedStatement has been
> recompiled and the parameters have changed. If you are using JDBC you
> must prepare the statement again.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:203)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:380)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:345)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:1378)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1272)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1635)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:299)
> ----------------STACK-END-------------------------
>
> My question is as follows; is there a generic (read jdbc) api I
> can use to determine when a statement needs to be dumped from cache?
>
> Derby: 10.2.2.0
> JDK 6.0 u1
> Ubuntu 8.04
>
Re: Caching Prepared Statements
Posted by Raymond Kroeker <ra...@raykroeker.com>.
Knut,
I initially thought that might be the case as well; however there
does not appear to be a way to prevent the driver from throwing the
error to the application; which would be required.
Raymond
On Mon, Jul 28, 2008 at 14:37, Knut Anders Hatlen <Kn...@sun.com> wrote:
> Raymond Kroeker <ra...@raykroeker.com> writes:
>
>> My question is as follows; is there a generic (read jdbc) api I
>> can use to determine when a statement needs to be dumped from cache?
>
> I think you can use ConnectionPoolDataSource in conjunction with
> StatementEventListener to achieve what you want.
>
> --
> Knut Anders
>
--
---------------------------------------------------------
Raymond Kroeker
Re: Caching Prepared Statements
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Raymond Kroeker <ra...@raykroeker.com> writes:
> My question is as follows; is there a generic (read jdbc) api I
> can use to determine when a statement needs to be dumped from cache?
I think you can use ConnectionPoolDataSource in conjunction with
StatementEventListener to achieve what you want.
--
Knut Anders