[DERBY-4541] precompile statement with result offset and fetch first clauses

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 10.5.3.0
• Fix Version/s: 10.6.1.0
• Component/s: SQL
• Labels: precompile statement

Environment

------------------ Java Information ------------------
Java Version: 1.6.0_17
Java Vendor: Sun Microsystems Inc.
Java home: C:\Archivos de programa\Java\jdk1.6.0_17\jre
Java classpath: D:\Programming\apache-tomcat-6.0.18\lib\servlet-api.jar;D:\Programming\apache-tomcat-6.0.18\lib\jasper.jar;D:\Programming\apache-tomcat-6.0.18\lib\jsp-api.jar;D:\Programming\apache-tomcat-6.0.18\lib\el-api.jar;D:\Programming\apache-tomcat-6.0.18\lib\annotations-api.jar;D:\Programming\workspace_eclipse\prototype_24\work;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\classes;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\activation.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\commons-fileupload-1.2.1.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\freemarker-2.3.8.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\jakarta-oro-2.0.8.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\mime-util.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\postgresql-8.3-603.jdbc3.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\standard.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\derby.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\log4j-1.2.15.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\commons-io-1.4.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\jim-lib.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\commons-lang-2.4.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\json-lib-2.3-jdk15.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\jstl.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\web4j.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\derbytools.jar;D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\derbyrun.jar
OS name: Windows XP
OS architecture: x86
OS version: 5.1
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
--------- Derby Information --------
JRE - JDBC: Java SE 6 - JDBC 4.0
[D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\derby.jar] 10.5.3.0 - (802917)
[D:\Programming\workspace_eclipse\prototype_24\WEB-INF\lib\derbytools.jar] 10.5.3.0 - (802917)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------

• Assignee: Unassigned
• Reporter: burk mayer
• Created: 08/Feb/10 15:24
• Updated: 08/Feb/10 15:36
• Resolved: 08/Feb/10 15:35

Description

hi,

i'm getting an error while precompiling a prepared statement with offset and fetch first clauses.

the documentation (http://db.apache.org/derby/docs/dev/ref/rrefsqljoffsetfetch.html#rrefsqljoffsetfetch), provides following example:

SELECT * FROM T ORDER BY I OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

the prepared statement looks like:

Connection con = datasource.getConnection();
PreparedStatement p = con.prepareStatement("SELECT * FROM T ORDER BY I OFFSET ? ROWS FETCH NEXT ? ROWS ONLY");

but an SQLException is thrown: Syntax error: Encountered "?" at line 1, column 60.

i'm using following version of derby:
Bundle-Name: Apache Derby 10.5
Bundle-Version: 10.5.3000000.802917

the connection is created from a container-managed connection-pool datasource:

<Resource
name="jdbc/prototype"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
url="jdbc:derby:memory:prototype;create=true"
username=""
password=""
maxActive="10"
maxIdle="5"
maxWait="10000"
/>

thank you for your support,
burk

Issue Links

• duplicates: DERBY-4208 Parameters ? with OFFSET and/or FETCH