2 Replies Latest reply on Oct 11, 2010 8:17 PM by garcimouche

    DB2 - FETCH FIRST ? ROWS parameterization issue

    garcimouche
    garcimouche Sep 30, 2010 10:44 AM

      Hi all,

       

      I wanted to draw your attention on a potential issue with JDBC store using DB2 db type. I try to preload a cache from my store and infinispan takes my maxEntries parameter of the eviction config to limit the number of rows fetched from the db.

       

      SELECT DATA_COLUMN, ID_COLUMN FROM MY_LUCENE_IDX_luceneIndex FETCH FIRST ? ROWS ONLY

       

      Unfortunatelly my driver is not accepting the '?' as a parameterized SQL so the prepareStatement fails with an SQLException:

       

      java.sql.SQLException: [SQL0104] Token ? was not valid. Valid tokens: ROW ROWS. Cause . . . . . :   
A syntax error was detected at token ?.  Token ? is not a valid token.  
A partial list of valid tokens is ROW ROWS.  
This list assumes that the statement is correct up to the token.  
The error may be earlier in the statement, but the syntax of the statement appears to be valid up to t
his point. Recovery  . . . :   Do one or more of the following and try the request again: -- 
Verify the SQL statement in the area of the token ?. Correct the statement.  
The error could be a missing comma or quotation mark, it could be a misspelled word, 
or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, 
correct the SQL statement because it does not end with a valid clause.
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:646)
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:617)
    at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1578)
    at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:227)
    at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:2062)
    at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1861)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:525)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

      I'm not sure it is a general problem with DB2 that's why I did not open a ticket (I attach the quick patch I've made in order to resume my testing).

      Note that I'm using a DB2/400 flavor of DB2 (running on iSeries box with jtopen version 7.1 http://jt400.sourceforge.net/).