[infinispan-issues] [JBoss JIRA] (ISPN-2101) Eviction does not work with Apache Derby cache loader-- SQL error

[Manik Surtani (JIRA)]

     [ https://issues.jboss.org/browse/ISPN-2101?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Manik Surtani updated ISPN-2101:
--------------------------------

    Fix Version/s: 5.2.0.ALPHA1
                   5.2.0.FINAL

    
> Eviction does not work with Apache Derby cache loader-- SQL error
> -----------------------------------------------------------------
>
>                 Key: ISPN-2101
>                 URL: https://issues.jboss.org/browse/ISPN-2101
>             Project: Infinispan
>          Issue Type: Bug
>          Components: Core API, Eviction
>    Affects Versions: 5.1.4.FINAL
>         Environment: Infinispan 5.1.4 final, Apache Derby JDBC cache loader configured using Eviction
>            Reporter: dex chen
>            Assignee: Tristan Tarrant
>             Fix For: 5.2.0.ALPHA1, 5.2.0.FINAL
>
>
> The cache will not be created because of the following sql error:
> Running sql 'SELECT DATA_COLUMN, ID_COLUMN FROM ISPN_STRING_TABLE_keychain LIMIT ?
> ERROR org.infinispan.loaders.jdbc.DataManipulationHelper[253] - ISPN008007: SQL error while fetching all StoredEntries
> java.sql.SQLSyntaxErrorException: Syntax error: Encountered "?" at line 1, column 69.
>         at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
>         at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
>         at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> The problem is that Apache Derby does not support select LIMIT.
> See here from Derby doc:
> 5.2. Does Derby support a LIMIT command?
> ^
> Derby does not support the LIMIT syntax. However, Derby 10.4 added the ROW_NUMBER function and Derby 10.7 added the OFFSET and FETCH clauses.
> Derby also supports limiting the number of rows returned by a query through JDBC. For example, to fetch the first 5 rows of a large table:
> Statement stmt = con.createStatement();
> stmt.setMaxRows(5);
> ResultSet rs = stmt.executeQuery("SELECT * FROM myLargeTable");
> Some related tuning tips are available in this external article.
> Starting with the 10.4.1.3 release Derby also supports limiting the number of rows using the ROW_NUMBER function.
> For example, to fetch the first 5 rows of a large table:
> SELECT * FROM (
>     SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
>     FROM myLargeTable
> ) AS tmp
> WHERE rownum <= 5;
> The ROW_NUMBER function can also be used to select a limited number of rows starting with an offset, for example:
> SELECT * FROM (
>     SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
>     FROM myLargeTable
> ) AS tmp
> WHERE rownum > 200000 AND rownum <= 200005;
> For more information, refer to the ROW_NUMBER built-in function in the Derby Reference Manual (available from the Documentation page). Development notes are available on the OLAPRowNumber wiki page.
> The LIMIT keyword is not defined in the SQL standard, and is currently not supported.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira