Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (7)

Resolved Datawindow Retrieve returns -1 sometimes

  1. Issue
0
Votes
Undo
  1. Aron Cox
    Aron Cox
  2. PowerBuilder
  3. Thursday, 22 December 2022 12:03 PM UTC

PowerBuilder 2017

SQL Server 2019

 

We have a few customers who every now and then get an error message from our code, and when they run the same thing again it works just fine.

The code is looping rounnd a lot and checking to see if some matching data can be found in the database. This happens in each loop iteration:

lds_ds.DataObject = 'd_get_common_transfer_bank_details'
lds_ds.SetTransObject(SQLCA)
ll_count = lds_ds.retrieve( ll_client_ref, as_data[ 3 ], ll_property_ref, as_data[ 4 ])

Every now and then ll_count is retuned as -1 which from the help seems to indicate the SQL failed, or the datastore dataobject was invalid. However the same bit of code has just worked multiple times before except with different arguments, and when I look at the arguements for the failed code they look reasonable.

So my question is is this true: -1 is ONLY returned if there is some error, otherwise 0 would indicates no data found and a positive number, some data found?

Any ideas why a -1 would suddenly appear? Is this a known issue in PB 2017,  and would making the user upgrading to PB 2019 R3 (our current version) be of any help. They are reluctant to upgrade at the moment for various reasons.

Thank you!

  1. DataWindow
  2. Error
  3. PowerBuilder
Where is the Sales Demo PBT?
Using SAVEAS() method datawindow to PDF, but size ...
Responses (7)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 5 January 2023 17:59 PM UTC
  2. PowerBuilder
  3. # 1

Try using something more recent as the "old odbc driver". I think that's probably the buggy part of the story.

Comment
Aron Cox
  1. Aron Cox
  2. Thursday, 5 January 2023 18:03 PM UTC
Well it's been in use for many, many years now. We are slowly moving to a newer driver, but it'll take a while to get everyone on it unfortunately.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 5 January 2023 18:11 PM UTC
Hi Aron,

I get that, but it might be useful to test if a user who suffers the problem no longer suffers the problem when using a newer driver.

regards
  1. Helpful
There are no comments made yet.
Aron Cox
Aron Cox Accepted Answer Pending Moderation
  1. Thursday, 5 January 2023 07:57 AM UTC
  2. PowerBuilder
  3. # 2

Finally managed to get hold of the customer database, and it seems we do log SQL errors to a table. The error was actually this:

Error number: 999  Select Error: An invalid handle

We are using SQL Server with the very old ODBC driver.

I've seen these before and it's usually when a user is running a long running query that has some yields in it and so they are able to click on something and start another query on the same transaction (typically SQLCA) before the first one has finished.

So I'm going to change the code in my loop to use a new transaction in the hope that helps, but if anyone has any ideas please let me know!

Thanks.

Comment
There are no comments made yet.
Martin Mueller
Martin Mueller Accepted Answer Pending Moderation
  1. Tuesday, 27 December 2022 05:51 AM UTC
  2. PowerBuilder
  3. # 3

Hi Aron,

try to create the datastore once a time or clean your memory when closing

lds_ds.reset()

SetNull(lds_ds)

GarbageCollect()

 

This may help

 

Best regards

Martin

Comment
Aron Cox
  1. Aron Cox
  2. Thursday, 29 December 2022 15:20 PM UTC
Thanks Martin. I have done just that. I create the datastore once, but if I happen to get a -1 I destroy it and recreate it, and loop 10 times with a 1 second pause between each loop. That may solve it, but I wish I understood why it was required in the first place!
  1. Helpful
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 27 December 2022 00:43 AM UTC
  2. PowerBuilder
  3. # 4

1) Have you tried cleaning up a bit prior to hitting the three lines in your loop?

      ll_rc                    = lds_ds.Reset ()
      lds_ds.DataObject = 'd_get_common_transfer_bank_details'
      ll_rc                    = lds_ds.SetTransObject(SQLCA)
      ll_count               = lds_ds.retrieve( ll_client_ref, as_data[ 3 ], ll_property_ref, as_data[ 4 ])


2) Do you validate the parameters at all? How about the values specified in the array?
 - In other words, are all parameters valid at all times?


3) Final thought:  if the dataObject is always the same, why is it not simply set once, outside of any loops?




Comment
Aron Cox
  1. Aron Cox
  2. Thursday, 29 December 2022 15:24 PM UTC
Good Idea, I didn't think of doing a reset, but I'll add that to all the other things I am doing to modify this code. As far as I can tell all the parmeters look fine, I logged everything to file in an attempt to track down what was going on, and the values were all perfectly reasonable.



The dataobject switches between four different dataobjects depending on the parameters that need to be retrieved. So there is a small case statement in each loop to set the dataobject (I didn't write this code, been around for many, many years)
  1. Helpful
There are no comments made yet.
David Peace (Powersoft)
David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Thursday, 22 December 2022 14:03 PM UTC
  2. PowerBuilder
  3. # 5

Hi Aron

I trust you are keeping well.

You coudl try geting and DB error from SQLErrText, that would concusivly proof SQL error of not. Also if it's occuring on one customer at about the same point int he cod each time, is it data related? Is there something odd about the DW query that is affected by the underlying data?

Food for thought. Merry Christmas

David

Comment
Aron Cox
  1. Aron Cox
  2. Thursday, 22 December 2022 14:18 PM UTC
Thanks David. Merry Christmas to you too :) It's in the same bit of code as it loops, but not always the same loop, if you see what i mean, so I don't believe it's data related, as if we run the same code again after it has rolled back, it'll work this time. It's a weird one. It just randomly happens a small percentage of the time.



We do have ancestor code in the datastore that's being used to display any SQL Errors that come up, and nothing is displayed, so it's doesn't appear to be a SQL Error. It's literally, and calmly, returning -1, as far as I can tell. Makes me think the .DataObject hasn't worked for some reason.
  1. Helpful
David Peace (Powersoft)
  1. David Peace (Powersoft)
  2. Thursday, 22 December 2022 17:33 PM UTC
I do see what you mean. Here si another random thought, have you got garbagecollect() in the loop? And a yeild() every so often after nn rows?

Something miht be corrupting the DW memory if the dataobject is getting messed up. You could extract the syntax of the DW when you get -1 to check that?

  1. Helpful 2
Aron Cox
  1. Aron Cox
  2. Thursday, 29 December 2022 15:25 PM UTC
I have updated the code, and am logging absolutely everything now, including the syntax. It took 6 months to get the current log file from the customer, so I'll see you all in another 6 months :-)
  1. Helpful
There are no comments made yet.
Aron Cox
Aron Cox Accepted Answer Pending Moderation
  1. Thursday, 22 December 2022 13:16 PM UTC
  2. PowerBuilder
  3. # 6

The release is in PowerBuilder 2017 R2 Build 1769

Comment
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Thursday, 22 December 2022 12:58 PM UTC
  2. PowerBuilder
  3. # 7

Hi.

I would check network and database for causing this behavior.

By the way it may be useful to provide the exact version of PB 2017 you are using.

Andreas.

Comment
Aron Cox
  1. Aron Cox
  2. Thursday, 22 December 2022 13:15 PM UTC
Thanks, but it seems to only and always happen in a similar place in the code which makes me think it's not network or database that's at issue here. Though of course i could easily be worng :)
  1. Helpful
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.