SELECT statement using FETCH or OFFSET options causes syntax error.

Can the value used with the OFFSET or FETCH options in a SELECT statement be parameters as opposed to literals? Error 10713 Syntax error when using sql PREPARE statement on OFFSET ? ROWS and FETCH NEXT ? ROWS ONLY.

Jun 21, 2016
Knowledge

Title

URL Name

SELECT-statement-using-FETCH-or-OFFSET-options-causes-syntax-error

Article Number

000186040

Environment

Product: OpenEdge Version: 11.x OS: All supported platforms Other: JDBC Driver

Question/Problem Description

Error 10713 Syntax error when using sql PREPARE statement on OFFSET ? ROWS FETCH NEXT ? ROWS ONLY.

The following error is seen when attempting to compile SQL code where parameters (?) are used as values for OFFSET or FETCH options in SELECT statements:

PreparedStatementCallback; uncategorized SQLException for SQL [select * from xmshdr0 sh offset ? rows fetch next ? rows only]; SQL state [HY000]; error code [-210056]; [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about "? rows fetch next ? rows only" (10713); nested exception is java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about "? rows fetch next ? rows only" (10713).

Steps to Reproduce

Clarifying Information

Only happens when parameters are used as values for OFFSET or FETCH options.
For example, SELECT * FROM <table> OFFSET ? ROWS FETCH NEXT ? ROWS ONLY.

Does not happen when literals are used as values for Offset or Fetch options.
For example, SELECT * FROM <table> OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY.

Error Message

PreparedStatementCallback; uncategorized SQLException for SQL [select * from xmshdr0 sh offset ? rows fetch next ? rows only]; SQL state [HY000]; error code [-210056]; [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about "? rows fetch next ? rows only" (10713); nested exception is java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about "? rows fetch next ? rows only" (10713).

Defect Number

Enhancement Number

Cause

This is expected behavior.

Parameters are not supported as the values for the OFFSET or FETCH options to SELECT statements. Only literals are allowed as the values for OFFSET or FETCH options.

Resolution

It was determined that the Progress Product is functioning as designed.

An enhancement may be requested through the Progress Community via an Ideas submission. Feedback is valuable and Idea submissions are monitored by Progress' Product Management team. Enhancement requests are reviewed during the planning phase of each new product release. Once an idea has been submitted as an enhancement, the Progress Software Community will have the opportunity to comment on and vote for the Idea.

For detailed information on how to submit an Idea please visit our knowledgebase at: How to submit an idea for a Progress product enhancement.

Workaround

Notes

Keyword Phrase

Last Modified Date

7/5/2016 7:29 PM

SELECT statement using FETCH or OFFSET options causes syntax error.

Files(0)

Was this article helpful?

Was this article helpful?

SELECT statement using FETCH or OFFSET options causes syntax error.

Information

Related Articles