Re: SQL Cursor Positioning with Web Application

["Craig Pelkie" <craig@xxxxxxxxxx>]

Look in the IBM i SQL Reference manual for the "DECLARE CURSOR" statement, 
especially the SENSITIVE and INSENSITIVE options.

There is a lot of support built into SQL for this very scenario, letting you 
decide whether or not you want to view newly added rows (newly added since 
you ran the SELECT statement). Rather than spend any time trying to invent 
another wheel, you can use the native SQL support.

Craig Pelkie


----- Original Message ----- 
From: "Bradley V. Stone" <bvstone@xxxxxxxxxxxx>
To: <WEB400@xxxxxxxxxxxx>
Sent: Thursday, July 30, 2009 8:30 AM
Subject: [WEB400] SQL Cursor Positioning with Web Application


>   For years I've written Web Applications using dynamic SQL to build 
> result
>   sets.
>
>   For example, if a user wants to search a transaction history, I give 
> them
>   a bunch of fields that they can use to select records, and also options 
> to
>   sort by any of the columns.  Pretty simple when using dynamic SQL.
>
>   For paging I store the RRN of the result set so when they click 
> "previous"
>   or "next" I use relative positioning with my SQL.  Works great.
>
>   Now in this particular case where we're querying a transaction file it
>   gets updated very often.  Lets say a few records every minute.  So lets
>   say they view page one, click next so they're on page 2.  Then next so
>   they're on page 3.   During this time, 10 new records were added.  So 
> they
>   go back which should be page 2, but it's really (for lack of a better
>   term) page 2.5 since more records have been added.  :)  So it loses the
>   positioning.
>
>   So, without using persistance, temp files, etc..  anyone have any ideas
>   here?  Or is this a case where "that's just how it will work".
>
>   Thanks!
>   --
>
>   Bradley V. Stone
>   BVSTools, Inc.
>   [1]www.BVSTools.com
>   [2]www.eRPGSDK.com
>
> References
>
>   Visible links
>   1. http://www.bvstools.com/
>   2. http://www.erpgsdk.com/
> --
> This is the Web Enabling the AS400 / iSeries (WEB400) mailing list
> To post a message email: WEB400@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/web400
> or email: WEB400-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/web400.