Skip to Content
0

retrieve top 100 and then search in all records??

Nov 18, 2016 at 09:17 AM

188

avatar image

Hi

I have window- with dw object - when i open this window i retrieve all records in the datawindow (dw_1.retriev())

when user wants to search for a specific record i put criteria he enteres in valid expression(ls_filter)

then dw_1.setfilter(ls_filter)

dw_1.filter()

my app works fine when server is on local net work

now it is required to put server on the net(cloud) so in open event i can not retrieve all rows as it take about five minutes to retrieve all rows(very very long period)

so in sql synatx of dw_1 I i added top 100(after select) that make dw to retrieve only last 100 rows

my question is if user want to search for specific record is this mean i must retrieve all rows to search and thus take long time again to load all row s

is this right??

or there are other solution?and how

Best Regards

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

René Ullrich Nov 18, 2016 at 10:10 AM
0

This is the easy way. You may look for Retrieve.AsNeeded property for datawindows for this way.

If you want to filter or aggregate data (sum, ...) in datawindow you need all data from server.

A more complex solution: You may use the filter expression to build a sql that retrieves only the rows you don't already have and you need for display.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Rene

Retrieve Rows As Needed is overridden if you have specified sorting or have used aggregate functions, such as Avg and Sum, in the DataWindow object. This is because PowerBuilder must retrieve every row before it can sort or perform aggregates.

In a multiuser situation, Retrieve Rows As Needed might lock other people out of the tables.

I had sorting in DW so i cant use rows as needed - also this might lock other people out of table

other approach my solve this issue but i do not know how to make it in PB

1-in open event of window i retrieve only top 100 of rows and then displaying messagebox "there are other rows to retrieve continue" YesNO!

2-if yes then retrieve the next 100 rows -thus i had 200 rows now on the screen (

-i do not want to retrieve top 200 i want to retrieve only second last 100 plus 100 row previously retrieved )-and repeat until no is selected or there are no other rows -so in every yes i retrieve only 100 rows displaying what previously retrieved + this 100 rows

3-to serach i enter full sql text " select ... from ... where ..."and add selection criteria (without considering the row already displyed in steps 1 &2) i.e i begin new retriev from scratch with search critiria

4-as step 1 i retrieve only 100 records that satisfy what i search displaying message if there is more rows with YesNo! buttons

How to do this and what event i may use?

Regards


0
avatar image
Former Member Nov 18, 2016 at 02:47 PM
0

Why not retrieve no rows up front and wait until the user enters the search criteria and retrieve from the database using the criteria in the where clause.

You can have the SQLPreview event on the result DataWindow modify the where clause to include the columns/values entered in the criteria DataWindow.

Share
10 |10000 characters needed characters left characters exceeded
ahmed tawfik Nov 18, 2016 at 04:08 PM
0

Hi Roland

can you explain with more detail

another Q - what if the user try to search that return most of records - thus the client must wait and wait .....

is stored procedure can be used here and how?? i do not use stored procedure befor so if it can used can you give me some ref on how to do it (i use pb 11.5 and sql server 2008)

Regards

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 18, 2016 at 05:44 PM
0

When you call Retrieve() on the results DataWindow, the SQLPreview event is called first. One of the event arguments is the SQL statement that it is going to send to the database. You can change the string and call SetSQLPreview to update it. The new SQL statement is then sent to the database. This allows you to build the WHERE clause entirely in code by looking at the criteria fields to see which ones have a value in them and which ones don't.

You could use the TOP clause (SELECT TOP 50 ...) to return a few rows and then run the full query in a background thread. When the background thread finishes, it could then update the visible DataWindow with Reset/ImportString.

Share
10 |10000 characters needed characters left characters exceeded
ahmed tawfik Nov 19, 2016 at 12:11 PM
0

Hi Roland

I did not use sqlpreview event befor - i do not know how to run full query in a background nor update

the visible DataWindow with Reset/ImportString.

so if you can send to me an example that show me this steps ,

I really appreciate all your help

Best Regards

Share
10 |10000 characters needed characters left characters exceeded