1. Daniel Vivier
  2. PowerBuilder
  3. Tuesday, 8 February 2022 22:07 PM UTC

This is in PB2019 Build 2082, using the Firebird SQL Database.

We have one user’s database from our application that is behaving extremely strangely for certain SQL retrieved through a datastore created at runtime (actually, our inherited datastore object, with a dberror event script). The datastore is created via SyntaxFromSQL and CREATE. 

For this one user’s DB, a Retrieve on that created datastore returns -15448 (not the only documented error return of -1!) and does not trigger the dberror event at all.

We can run the same SQL that is giving this Retrieve error return value with no problem with an external tool for accessing Firebird (FlameRobin), or in the Database Painter.

One other bizarre thing: although we have no known problems with JOIN clauses in Firebird or PB or our application for any other user, the problem SQL includes a JOIN, and if we replace that with old-style SQL (like SELECT ... FROM table1, table2 WHERE table1.joincolumn = table2.joincolumn AND ...) the problem goes away! 

Here's a super-simple variation of the SQL the user originally had the problem with, that still gives the same problem:

SELECT (donor.last_name) "Name"
FROM constants co
JOIN donor on co.year_number = donor.year_number
ORDER BY donor.last_name

Any bright ideas? Thanks.

Accepted Answer
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 8 February 2022 22:51 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Dan,

What kind of connection are you using? ODBC, JDBC, etc.

Even though we use Firebird with Wizsource, I don't know much about this DB.

I guess that "Name" is an alias for donor.last_name?

What happens if you do:

SELECT donor.last_name as "Name"
FROM donor 
INNER JOIN constants co ON co.year_number = donor.year_number
ORDER BY donor.last_name

regards.

EDIT: TLDR.

For anyone not going through all of the comments: It turned out to be an overflow. The amount of retrieved rows were bigger than the integer variable to receive the result.

Comment
  1. Miguel Leeuwe
  2. Tuesday, 8 February 2022 23:43 PM UTC
After doing the SyntaxFromSQL and CREATE, do a Describe("Datawindow.Syntax"), and see if there's anything weird in the created datawindow?
  1. Helpful 1
  1. Daniel Vivier
  2. Wednesday, 9 February 2022 01:54 AM UTC
Lots of interesting suggestions, Miguel, thank you.

One difference I just realized between this customer and others is that they have over 93,000 donor records. I'm not sure I have been aware of another one with over 20K.

TRACE ODBC showed it retrieving every donor name, nothing that looked like an error.

If I saved the SyntaxFromSQL to a file, it looked sensible, and then if I replaced an existing DW's syntax with that saved syntax and opened it, it retrieved all of the names!

OMG I feel so stupid. The 90K rows (more than can be held in an int) was my hint of something to look for. Here was my code for testing the Retrieve after creating the DS to make sure it worked:

int li_ret

li_ret = ids.Retrieve()

if li_ret < 0 then ERROR

All I had to do was change that int to a long and it's all fixed!
  1. Helpful
  1. Miguel Leeuwe
  2. Wednesday, 9 February 2022 02:04 AM UTC
Aha! I "thought" of an "overflow", but the numbers didn't really make sense to me. Don't feel stupid, it's a very common error. When dealing with datawindows , I always use long values. Some years ago, I read something about, internally, integer values being converted to longs by the compiler, If that's correct you don't really win anything by using smaller typed numeric values. It would even add a tiny bit of extra time due to the conversion.

I'm glad you solved it !

regards
  1. Helpful
There are no comments made yet.
Daniel Vivier Accepted Answer Pending Moderation
  1. Tuesday, 8 February 2022 23:26 PM UTC
  2. PowerBuilder
  3. # 1

Correction to my original post: changing it to not use a JOIN still gives the same error, as in:

SELECT last_name
FROM donor, constants
where constants.year_number = donor.year_number

Also, the original tests were on a colleague's computer. When I run the tests on my computer I get a different return value from the call to Retrieve(), -18180.

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.