RE: SQL and retrieving the next xx rows -- MIDRANGE-L

Nope. I handle paging on my own.

Bradley V. Stone
BVSTools - www.bvstools.com
eRPG SDK - www.erpgsdk.com

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Alan Campin
Sent: Tuesday, July 08, 2008 11:43 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL and retrieving the next xx rows

<snip>
So we'd want to say:

select.... where ordernum >= searchorder

but only have it select the next 100 orders (mainly because if we
didn't,
the search would take forever if there were a bazillion orders in the
database).
</snip>

Do you mean the following? This function loads a single page of a
subfile for the number of records per page. Note the for. I think that
is what you are looking for. Hopefully the forum won't mangle the code.

* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
* LoadSubfile
* Load subfile.
* Input - None
* Out - None
* Returns - None.
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
p LoadSubfile...
p b
d pi
/Free

Exec Sql Declare C1 Scroll Cursor For
Select TDFILENAME,
TDLIBNAME,
TDACTIVE,
TDSRVPGM,
TDSRVLIB,
TDSRVPROC
From TGCTRLD
Where TDFILENAME >= :O_CURPOSTO
Order By TDFILENAME,
TDLIBNAME;

Exec Sql Close C1;

Exec Sql Open C1;
If SqlState <> cSqlStateOk;
ERRH_Throw('CPF9898' :
cCPFMessageFileName :
'Open of Cursor C1 failed');
EndIf;

ClearSubfile();
O_CURPOSTO = LoadOnePage();

Return;

/End-Free
p e
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
* LoadOnePage
* Load one page of subfile records.
* Input - None
* Out - None
* Returns - Key of first record.
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
p LoadOnePage...
p b
d pi Like(StdNam)

d RecordsRead...
d s Like(StdInt)
d NumberToLoad...
d s Like(StdInt)
d x...
d s Like(StdInt)
d SaveFirstKey...
d s Like(StdNam)
/Free

g_RecordsLoadedOnPage = 0;

NumberToLoad = cRecordsPerPage; // SQL cannot use constant.
Exec Sql Fetch Next
From C1
For :NumberToLoad Rows
Into :g_dsRecordIn;
Select;
When SqlState = cSqlStateNoRow;
RecordsRead = 0;
When SqlState <> cSqlStateOk;
ERRH_Throw('CPF9898' :
cCPFMessageFileName :
'Fetch from cursor C1 failed! Sql State = ' +
SqlState);
Other;
RecordsRead = SQLERRD(3);
EndSl;

SaveFirstKey = g_dsRecordIn(1).FileName;

For x = 1 To RecordsRead;
WriteSubfileRecord(x);
EndFor;

// If not EOF, check if more records.
If RecordsRead <> cRecordsPerPage;
DisplaySubfileEnd = cTrue;
Else;
Exec Sql Fetch Next
From C1
For 1 Rows
Into :g_dsRecordIn;
Select;
When SqlState = cSqlStateNoRow;
DisplaySubfileEnd = cTrue;
When SqlState <> cSqlStateOk;
ERRH_Throw('CPF9898'
:
cCPFMessageFileName
:
'Fetch from cursor C1 failed! Sql State = ' +
SqlState);
Other;
DisplaySubfileEnd = cFalse;
Exec Sql Fetch Prior
From C1
For 1 Rows
Into :g_dsRecordIn;
EndSl;
EndIf;

// If count of records loaded on page is zero and records in
// subfile is zero, then no records found to load.
If g_RecordsLoadedOnPage = 0;
If g_RecordsLoadedInSubfile = 0;
MSGF_SendProgramMessage('UXV0026' :
cXVMessageFileName :
' ' :
cMainProcedureName :
g_dsSystem.ModuleName:
g_ProgramName );
Else;
// No records found to load.
MSGF_SendProgramMessage('UXV0027' :
cXVMessageFileName :
' ' :
cMainProcedureName :
g_dsSystem.ModuleName:
g_ProgramName );
EndIf;
EndIf;

// Set position of sub-file to be last record loaded.
H_SFPOS = g_SubfileRecordNumber;

Return SaveFirstKey;

/End-Free
p e

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.