-
1. Re: PreparedStatement & cache
johnnycannuk Jan 25, 2002 8:20 AM (in response to jmoliere)This is a bug in the 2.4.4 implementation of the XAClientDatasource.java. Calling prepareStatement always returns a forward_only resultset, no matter what the parameters passed in. This is the fix:
I submitted a "patch" consisting of a 1 minute cut-paste-edit compile to the XAClientConnection.java in the jbosspool part of the source.(see http://sourceforge.net/tracker/index.php?func=detail&aid=506673&group_id=22866&atid=376687)
The change is this for those who need to duplicate it/fix their install -
Fix for #506549 bug scrollable rs prob
Here is the patch that fixes this:
The old call to con.prepareStatement(sql,int,int)
was implemented like this:
public PreparedStatement prepareStatement(String sql,
int resultSetType, int resultSetConcurrency) throws
SQLException {
return this.prepareStatement(sql);
}
it should be like so:
public PreparedStatement prepareStatement(String sql,
int resultSetType, int resultSetConcurrency) throws
SQLException {
if(con == null) throw new SQLException(CLOSED);
try {
return con.prepareStatement(sql,
resultSetType, resultSetConcurrency);
} catch(SQLException e) {
setError(e);
throw e;
}
}
I have tested this out (although not with the
testsuite) and it works well - seems like a simple
oversight considering the implementations of
prepareStatment() and prepareCall() around it were
done correctly.
Try it and let me know if it fixes your problem.
Mike -
2. Re: PreparedStatement & cache
johnnycannuk Jan 25, 2002 8:26 AM (in response to jmoliere)BTW, You will need to download the 2.4.4 source, make the change in XAClientDataSource.java and rebuild. You can then drop the new jbosspool.jar and jbosspool_jdbc.rar into any jboss-2.4.4/lib/ext and /deploy/lib/ directory, respectively, and fix the problem. Eg:
jbosspool.jar -> <jboss-install>/lib/ext
jbosspool_jdbc.rar -> <jboss-install>/deploy/lib
This works for 2.4.4 but not for 2.4.3. You would need to repoeat this process with the 2.4.3 sources.
Also, WHY THE HELL ISN"T PREVIEW WORKING? It took me 1/2 an hour to write this post because my stuff kept disappearing..but I digress
Mike -
3. Re: PreparedStatement & cache
jmoliere Jan 25, 2002 9:10 AM (in response to jmoliere)Yes I watched this class source, you 're right!!
thanks for your answer, I will test when my download will be over (I have a very poor transfer rate today on sourceforge)
cheers
Jerome -
4. Re: PreparedStatement & cache
jmoliere Jan 28, 2002 5:53 AM (in response to jmoliere)Hi mike (& all others)
I patched jboss, recompiled, deployed OK!!!
Now my prepared statements are quite good but each parameter bound to my query is not seen by Oracle driver (not all varaiables bound exception ). I've seen a post dealing with this problem but it gave me no clues !!!
I must say that my query is very simple (something like:
select field1 from my_table where field2=?
field2 is a varchar2 (32) field , I try to make a pstmt.setString(1,my_new_value)...
Any clue welcome
Cheers
Jerome -
5. Re: PreparedStatement & cache
johnnycannuk Jan 28, 2002 7:55 AM (in response to jmoliere)Post your PreparedStatement query string and your setters...I've run into this problem with Oracle 8.1.7 when I have an extra '?' and don't assign a value to it in the setters.
Mike -
6. Re: PreparedStatement & cache
jmoliere Jan 28, 2002 8:08 AM (in response to jmoliere)> Post your PreparedStatement query string and your
> setters...I've run into this problem with Oracle
> 8.1.7 when I have an extra '?' and don't assign a
> value to it in the setters.
>
String qry = "SELECT OBJECT_PROPERTY FROM LEXICON_OBJECT_TBL WHERE OBJECT_ID =?";
PreparedStatement stmt = dataSourceConnection.prepareStatement( qry,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setString(1,aLexiconId);
ResultSet rset = stmt.executeQuery(qry);
This is quite a hello statement but it's very usefull for me!!! I can't see what's wrong with this query (it works well if my connection is fetched directly from the thin Oracle driver).
Jerome -
7. Re: PreparedStatement & cache
johnnycannuk Jan 29, 2002 7:49 PM (in response to jmoliere)What does the stack trace look like? Post the Error mesages...
We'll keep trying.
I use a very similar query and it works great....
Mike -
8. Re: PreparedStatement & cache
jmoliere Jan 30, 2002 5:36 AM (in response to jmoliere)Hi all (mike!!),
these are my traces still the first call from my code:
the problem must be something like : how to get the statements from the Jboss spool / what is the main difference with a direct call to the Oracle driver:
java.sql.SQLException: ORA-01008: not all variables bound
[12:22:05,536,Default] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
[12:22:05,537,Default]
[12:22:05,537,Default] at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
[12:22:05,538,Default]
[12:22:05,538,Default] at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
[12:22:05,539,Default]
[12:22:05,539,Default] at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
[12:22:05,540,Default]
[12:22:05,540,Default] at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643)
[12:22:05,541,Default]
[12:22:05,541,Default] at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1819)
[12:22:05,542,Default]
[12:22:05,542,Default] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2015)
[12:22:05,543,Default]
[12:22:05,543,Default] at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:571)
[12:22:05,544,Default]
[12:22:05,544,Default] at com.viveo.lmt.server.web.servlets.shared.MergedLexicon.init(MergedLexicon.java:62)
String qry = "SELECT OBJECT_PROPERTY FROM LEXICON_OBJECT_TBL WHERE OBJECT_ID =?";
PreparedStatement stmt = dataSourceConnection.prepareStatement( qry,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
System.err.println("Lexicon id size ( PreparedStatement)= " + aLexiconId.length());
stmt.setString(1,aLexiconId);
ResultSet rset = stmt.executeQuery(qry);//this is the line appearing in the bottom of the stack trace....
Cheers
Jerome -
9. Re: PreparedStatement & cache
johnnycannuk Jan 30, 2002 8:26 AM (in response to jmoliere)Strange...What is the value of aLexiconId?
try commenting out
stmt.setString(1,aLexiconId);
and hardcoding the value into the string qry and running it. (still as a prepared statement and all). See if you get a result back. You may also whant to restart jboss. Your code here should work, but I have found with jboss that if you hot deploy a new bean without undeploying or re-starting, your implementation changes aren't seen by the system because the old ones are still in memory, even if the old jar has been replaced.
Any way if the above works, change it back to the way you have it now and restart and see if it works.
Mike -
10. Re: PreparedStatement & cache
jmoliere Jan 30, 2002 9:08 AM (in response to jmoliere)> Strange...What is the value of aLexiconId?
>
this is one instance of the PK of the reflected table,
so this is a 32 chars string (I'm using an UUid Session Bean for generation of the PKs...)
this is the value used for a query:
b329d153c0a802680076cbf724ef83cc
as you can see there 's nothing wrong (I hope !!), just letters & digital chars, no special chars as (,' and so on..
> try commenting out
>
> stmt.setString(1,aLexiconId);
>
> and hardcoding the value into the string qry and
> running it. (still as a prepared statement and all).
yes I 've already attempted it, it works fine... (I've just changed my code to go back to this state & it still works).
But there 's no sense using prepared statements with fixed sql queries....
> See if you get a result back. You may also whant to
> restart jboss. Your code here should work, but I have
> found with jboss that if you hot deploy a new bean
> without undeploying or re-starting, your
> implementation changes aren't seen by the system
> because the old ones are still in memory, even if the
> old jar has been replaced.
>
I 've already attempted to start down then up several times...
> Any way if the above works, change it back to the way
> you have it now and restart and see if it works.
Thanks
Jerome -
11. Re: PreparedStatement & cache
johnnycannuk Jan 30, 2002 11:40 AM (in response to jmoliere)Sorry buddy, I'm stumped...I've never run into this the way you describe...as I stated, I have always had an extra '?' in the query to cause this, making it easy to fix...
Good luck
Mike