1. Paul Wiebe
  2. PowerBuilder
  3. Thursday, 27 February 2020 14:40 PM UTC

I have a datawindow that is getting it's information via an embedded select statement to my database. I have confirmed the select statement works against the database directly, and I have confirmed that they work when I use the "Run/Preview" within the PowerBuilder IDE. But when I run the application and execute a .Retrieve() on the datawindow object (being stored in a datastore type variable) then it returns a -1 instead of the 620 rows I would expect to see.

This .Retrieve used to work just fine, but I added a left join to two different tables, which I have confirmed still allow it to work, but when I add an item to select from one of those tables then it throws this error. If I hard-code that select to simply a 0 then it also retrieved without issue, so it's not the new column that seems to be the issue.

Does anyone have any ideas as to what could be causing this?

Paul

Accepted Answer
Paul Wiebe Accepted Answer Pending Moderation
  1. Thursday, 27 February 2020 15:29 PM UTC
  2. PowerBuilder
  3. # Permalink

Hey David,

I figured out the issue. The column I had defined for this value was a long, but the database was a char(8). Despite the fact that this always returns a number, the preview could handle it, but the retrieve could not. I changed the property to a char(8) instead and then the data loaded with no issue.

Thanks for your help.

Paul

Comment
  1. David Peace (Powersoft)
  2. Thursday, 27 February 2020 15:48 PM UTC
Glad you sorted it, had to be something silly :)
  1. Helpful
  1. Chris Pollach @Appeon
  2. Thursday, 27 February 2020 19:37 PM UTC
Hi Paul;

That makes little sense as the iSQL Pane in the DW Painter and the Preview Pane in the DW Painter all use a DWO Object behind the scenes to function - and thus, perform a Retrieve() on your IDE's behalf. I wonder if the faulty DWO column definition (which was certainly an issue) was then hampered by some of your App's code around the DC/DS. Very strange. However, glad that you got it fixed!

Regards ... Chris
  1. Helpful
  1. Michael Kramer
  2. Friday, 28 February 2020 01:02 AM UTC
Configure DB connection as StaticBind=0 and you will see DW perform Prepare/Describe to catch data type changes after DW was designed.
  1. Helpful
There are no comments made yet.
David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Thursday, 27 February 2020 14:46 PM UTC
  2. PowerBuilder
  3. # 1

Hi Paul

So if I understand correctly, the DW workes fine inthe IDE and can retrieve rows. When you execude the retrieve() in the code you get -1. Can you run in debug and look at your DB transaction SQLCA and see  if there is a SQL Error occuring?

My gut tells me that your DB connection in your application is not the same as your DB connect in the IDE.

Cheers

David

Comment
There are no comments made yet.
Paul Wiebe Accepted Answer Pending Moderation
  1. Thursday, 27 February 2020 14:56 PM UTC
  2. PowerBuilder
  3. # 2

Hey David,

I took a look at the object I am using in the SetTransObject for my datawindow which is created as the SQL transaction almost directly before this datawindow retrieve is done. When I look at it's properties, the SQL Err Text is blank, and the SQL Code is 0. I did also try changing the database it's being directed to connect to in the Trans object to the same one I am connecting to in the IDE databases, but that didn't make a difference.

Is there another SQL connection that you are wanting me to check?

Paul

Comment
  1. David Peace (Powersoft)
  2. Thursday, 27 February 2020 15:09 PM UTC
Ok if there is no DB error can you check the DW dataobject value is this correct?
  1. Helpful
  1. Paul Wiebe
  2. Thursday, 27 February 2020 15:15 PM UTC
The four lines of code are:

data = CREATE datastore

data.DataObject = "d_datawindow"

data.SetTransObject(SQLCA)

rowCount = data.Retrieve()



I did confirm though, and the dataobject property is definitely pointing to my datawindow properly.
  1. Helpful
  1. Paul Wiebe
  2. Thursday, 27 February 2020 15:19 PM UTC
I was just testing something else here, and I did find that if I changed the property I am selecting to something that was already in the table then it also errors on retrieve, so no new tables added, but selecting the same property that is already in the dataset a second time. Not sure if that is helpful information or not, but wanted to provide it.
  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.