1. Glenn Barber
  2. PowerBuilder
  3. Monday, 21 June 2021 20:14 PM UTC

In PB 2017 we have an SQL statement that can potentially retrieve too many rows to be manageable.  

We would like to limit the retrieve to a specified number of rows and use the SQL START AT syntax to say where to start based on the order by

The datawindow SQL  

SELECT TOP 1000 START AT 1000 
propasset.propassetid,..

works to give us a Thousand rows starting at row 1001 in the collation sequence

we can even pass a retrieval argument :topno to specify the number of rows

SELECT TOP :topno START AT 1000 
propasset.propassetid,..

 

However if we attempt to use a retrieval argument for the START AT value

SELECT TOP :topno START AT :startatno

we get the following message from the Datawindow SQL Checker (SQL Anywhere)

SQLSTATE = 42000

[Sybase][ODBC Driver][SQL Anywhere]Statements' Size Limit is Invalid

Does anyone know how we can use a retrieval argument to handle the START AT value?

 

Accepted Answer
John Fauss Accepted Answer Pending Moderation
  1. Monday, 21 June 2021 20:41 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi, Glenn -

Since the syntax is valid...

Can you dynamically alter the SELECT statement in the DataWindow/DataStore (via dot-notation or describe/modify) to include the desired syntax immediately following the "SELECT" keyword or replace it if it already is present?

Best regards,
John

Comment
  1. Glenn Barber
  2. Tuesday, 22 June 2021 17:30 PM UTC
Thanks Chris



It does appear that the SQLPreview is the easiest route, although I note that the Argument does work for the top value - just not for the start at.
  1. Helpful
  1. Chris Pollach @Appeon
  2. Tuesday, 22 June 2021 17:53 PM UTC
Hi Glenn;

FYI: I just tested varying the "START AT" value and it's working great for me in PB 2019R3 / PB 2021 with the SA v17 DBMS.

Regards ... Chris
  1. Helpful
  1. Glenn Barber
  2. Tuesday, 22 June 2021 20:10 PM UTC
Thanks Chris -

Both of these are major upgrades - retesting and recertifying before deploying on dozens of customers - and we've been dragging our feet somewhat, but need to do it... Thanks for the incentive.
  1. Helpful
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Tuesday, 22 June 2021 23:06 PM UTC
  2. PowerBuilder
  3. # 1

Thanks for your help.. unitl we do the upgrade to PB2019 abd SA71 - we can do this and its quite simple

(one thing we discovered is that the START AT 1 needs a space after even though followed by a (CR) - otherwise in SQLPREVIEW  it looks like START AT 1columnname, ...

in the DataWindow SQL

SELECT TOP :topno START AT 1 
propasset.propassetid,
propasset.propassetdesc,
...

FROM propasset
WHERE
...

ORDER BY ...;

in the SQL Preview event

----------------------------------------------------------

int li_rc

string ls_sql
string ls_expr

ls_sql = sqlsyntax

IF il_startrow <> 1 THEN

ls_expr = "START AT " + String(il_startrow)

ls_sql = inv_string.of_GlobalReplace(ls_sql, "START AT 1", ls_expr)

li_rc = this.SetSQLPreview(ls_sql)

END IF

 

Comment
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 22 June 2021 18:47 PM UTC
  2. PowerBuilder
  3. # 2

OK, you want the users to approved the next BLOCK of rows to be retrieved?

In the RETRIEVESTART event, set:   il_rows_retrieved = 0
                                                    il_rows_per_block = 1000

In the RETRIEVEROW event, set:  

il_rows_retrieved =il_rows_retrieved + 1
IF (il_rows_retrieved >= il_rows_per_block ) THEN
   il_rows_retrieved = 0
   ll_rc = MessageBox ("Get Next Set Of Rows?", "Do you want to retrieve the next block of " + &
                               string (il_rows_per_block ) + " rows?", YesNo!, Question!)

   IF (ll_rc = 1) THEN
       // Continue retrieving data
       ll_return = 0
   ELSE
       // Stop the retrieval
       ll_return = 1
   END IF

END IF

RETURN  ll_return

Comment
  1. Glenn Barber
  2. Tuesday, 22 June 2021 18:54 PM UTC
As the number of rows is so large - we and to make sure that we are only retrieving the next block or rows - not adding the second block to the first... Also there may be edit changes and other activities on the first block of rows - saves issued - etc. So these blocks of rows are independent of each other.



Also Ive been cautious about using code in retrieverow because of performance considerations when rowcount is high. What's' your experience with that?
  1. Helpful
  1. Olan Knight
  2. Thursday, 24 June 2021 15:26 PM UTC
Correct: code in the RetrieveRow event does slow down the code, but only minimally if there's minimal interaction with the GUI. Example, you could update a progress meter once every 500 rows.
  1. Helpful
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 22 June 2021 17:37 PM UTC
  2. PowerBuilder
  3. # 3

My solution was to implement a Retrieval Threshold service.

1. Define a Preference "max number of rows allowed" and set it to your value.
   In my case, I use 50,000 rows.

2. In the SQLPREVIEW EVENT:
       Wrap a SELECT COUNT(*) INTO ll_rowcount FROM ( <your sql> );

       IF (ll_rowcount > Max_Rows_Allowed) THEN
           Set a forced END RETRIEVAL in the RetrieveEnd event when the max rowcount is reached
           OR
           Disallow the Retrieval and popup an error msg asking the user to change the parameters

       END IF


Later -

Olan

  

Comment
  1. Glenn Barber
  2. Tuesday, 22 June 2021 17:46 PM UTC
The TOP statement in SQL works for the initial row limitation - the problem is letting the user select a Next 1000 records so as to page through a very large set in order so each successive query pics off whether the other left off, and in this case the order by is primarily alphabetical plus some additional order by elements with duplicate names.
  1. Helpful
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 22 June 2021 05:37 AM UTC
  2. PowerBuilder
  3. # 4

Hi Glenn,

Cause bind variables are not supported to specify START AT value you may use a workaround like this:

select *
from (
SELECT *,
row_number() over (order by emp_id asc) as rnum
from employee) as emp
where rnum between :startno and :startno + :topno

HTH,

René

 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.