What is it?
Retrieve As Needed is an option available in PowerBuilder that, when properly used, can reduce network traffic and speed up response times. Instead of bringing back all the rows for the datawindow on the retrieve, only as many rows as are needed to show in the datawindow at a time are retrieved. Then, if you page down through the datawindow, it will bring back the rows as needed from the server to display in the datawindow.

How does it work?
The datawindow retains an open cursor while it is retrieving the rows. This might impact server performance. You can issue a dbcancel( ) function to end the retrieval (and thereby close the cursor), and should issue this function before closing the window. Keep in mind that some databases will not allow updates against the retrieved rows while the cursor is open.

What can override the retrieve as needed option?
The option is overridden if you have sorting, filtering, or aggregation (such as sum or average) in the datawindow, since it will need to bring back all the rows to perform these functions. You can get around this if you have an order by clause in your select to do your sorting, specify a where clause instead of using a filter, and put the computed columns in the select statement rather than as computed fields with aggregate functions in the datawindow, form or report.

What happens if I print the datawindow?
The print will only print as many rows as have been brought back from the server. So if you want to print a datawindow that has retrieve as needed specified, and you want all the rows to print, you can modify the datawindow retrieve.asneeded attribute to force the remaining rows to be retrieved, so that the print will print all the rows. To force any rows to be retrieved and reset the as needed flag back, code the following:

dw_1.object.datawindow.retrieve.asneeded = 'no'
dw_1.object.datawindow.retrieve.asneeded = 'yes'
 

What will the retrieve and rowcount functions return?
The retrieve will tell you how many rows were initially brought back from the server. The rowcount will tell you the current number of rows that have been brought back. So if you do a retrieve, and then page down some, the rowcount should be higher than the return code from the retrieve. If you want to know how many rows COULD be retrieved if you retrieved them all, you can write an embedded sql select to do a count on the rows.

When will the retrievestart, retrieverow, and retrieveend events fire?
The retrievestart will fire when the retrieval begins. The retrieverow event will fire whenever a row is retrieved, whether initially, or as a result of scrolling down. The retrieveend will fire when the retrieval is finished, either by retrieving all the rows, or by issuing the dbcancel function.