cancel
Showing results for 
Search instead for 
Did you mean: 

Function Timeout? PB 12.5.1

Former Member
0 Kudos

Greetings All,

I have a function that does something simple:

Argument: sp_id (string)

Return: Integer

integer staged_rates

sp_id = trim(sp_id)

select count(*) into :staged_rates where sp_id = :sp_id;

commit;

return staged_rates

When I run the SQL in the database painter, I get 115,820 rows.

When I run the SQL in a datawindow, I get 115,820 rows.

But when I call the function from a datawindow either in a computed field as uf_get_rowcount_by_sp_id(sp_id) or in the retrieve_end script to set the values in a datawindow, I get mixed results.

If there are less than 20,000 rows, I generally get the correct count.  But I have some instances where there are 50,000 to 115,000 rows and I see '0' rows.

I tried changing the datatype of staged_rates from integer to long and now I see rows where before it was 0, but oddly, they are negative (e.g. -15252 rows).

If someone can point me in the right direction, I would certainly appreciate it.

Thanks all!!

Paul

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Have you changed the datatype of variable and also the function return type to long?

What about retrieve the count direkt as sub-select in the datawindow sql? It should be much more faster then retrieval in the retrieveend-event.

A call of an external function in a computed field is not good for performance!

Answers (3)

Answers (3)

Former Member
0 Kudos

Thanks all.

I would be interested in knowing how to do a sub-selected in a datawindow if you have a moment, René.


To all, I thought there might be a problem with using integer, but long wasn't working either.  Even if it is resource intensive, it should still work.


I ended up putting the SQL in datawindows, creating datastores and populating the datawindow in the retrieve_end event and now it works fine.


Long it is!!


Thanks all!!


Paul

Former Member
0 Kudos

Sub-Selects are a database feature.

e.g. for Oracle you can do a select like in this example

SELECT COL1,

          COL2,

          (SELECT count (*)

          FROM TAB2

          WHERE COL3 = TAB1.COL1 ) AS CNT

FROM TAB1

The Sub-Select must not return more than 1 row!

You should give it a name because otherwise powerbuilder may crash.

Former Member
0 Kudos

You can use a sub select in the WHERE clause in which case you may wish to return multiple rows.

Something like

WHERE id IN (SELECT id FROM invoice where invoice_vendor_id = 123)

ricardojasso
Participant
0 Kudos

If what you want is to show in each row the total count by sp_id then you'd be better off using the group by clause in the select statement. For example,

SELECT sp_id, COUNT(*)

FROM ...

GROUP BY sp_id

This will return the total count for each sp_id in each row.

Your solution of using an additional datastore to populate the datawindow in the retrieveend event is not bad, performance wise, because it is done only once. But then, you are performing an additional query to the database. This could easily be solved using the group by clause.

Also, calling a global function from a computed field that performs database queries is not good practice. Remember that the function gets called each time you scroll the datawindow.

Former Member
0 Kudos

A PowerBuilder integer is (from the help file): 16-bit signed integers, from -32768 to +32767

A PowerBuilder long is: 32-bit signed integers, from -2147483648 to +2147483647.

arnd_schmidt
Active Contributor
0 Kudos

This is not a Function Timeout.

Looks like a mismatch between the return type and your local stage_rates variable type.

Better use:

Argument: sp_id (string)

Return: Long

Long staged_rates

sp_id = trim(sp_id)

select count(*) into :staged_rates where sp_id = :sp_id;

commit;

return staged_rates


hth


Arnd