Feedback
Did this article resolve your question/issue?

   

Article

Is there a way in SQL-92 to limit the number of records in the result set that the query returns

« Go Back

Information

 
TitleIs there a way in SQL-92 to limit the number of records in the result set that the query returns
URL NameP26439
Article Number000151564
EnvironmentProduct: OpenEdge
Version: All supported versions
Product: Progress
Version: 9.x
OS: All supported platforms
Other: SQL, ODBC, JDBC
Question/Problem Description
Is there a way in SQL-92 to limit the number of records in the result set that the query returns
How to limit the number of results from an SQL query
How to restrict the number of rows returned from a SQL-92 query
How to change the Record Fetch Limit in the character SQL Explorer tool
How to limit the number of rows fetched in a result set using a JDBC client
How to limit the number of rows fetched in a result set using an ODBC client
How to change the Record Fetch Limit in the GUI SQL Explorer tool
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
The number of rows returned at one time by a query is controlled by the SQL client application, not by the SQL engine or JDBC or ODBC drivers. There are no 

An example of a SQL client application that can do this is the Progress SQL Explorer tool, that controls the number of records returned at one time with a Record Fetch Limit setting. The default setting is 101 rows.
In the character SQL Explorer tool, (available in all Progress / OpenEdge versions supporting SQL-92), enter the following from the command line, replacing "<n>" with the desired number of records to be returned:
 
@FetchLimit <n>

In the GUI SQL Explorer tool, (available in OpenEdge 10.0B or earlier), the default Record Fetch Limit may be changed using the menu option View -> Options -> Report Formatting.


To turn off the fetch limit use the following SQL statement prior to running other SQL statements which retrieve records:
@HasFetchLimit false;



Please note that the Fetch Array Size setting of a driver represents the number of rows that the driver retrieves when fetching from the server. This setting does not limit the number of results returned, nor is it the number of rows given to the user.
Workaround
Notes
References to other documentation:

Progress article(s):
000022439, "Is the JDBC method setFetchSize supported?"
000015265, "How to set Fetch Array Size with a DSN-less connection string?"
 
Java 2 Platform Std. Ed. v1.4.2 java.sql Interface ResultSet
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html#setFetchSize(int)
 
Last Modified Date1/2/2020 8:36 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.