dex chen created ISPN-2101:
------------------------------
Summary: 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: Manik Surtani
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