Datastore Retrieve Returns -1

[former_member213365]

It's been a while since I did a lot of serious PB coding and I'm back to making newbie mistakes.  This is classic PB 12.5 with the client running on Windows Vista against an Oracle database.

When I try to retrieve my datastore the return value is -1. 

ltr_trans = Create transaction

ltr_trans.DBMS = SQLCA.DBMS

ltr_trans.LogPass = SQLCA.LogPass

ltr_trans.ServerName = SQLCA.ServerName

ltr_trans.LogId = SQLCA.LogId

ltr_trans.AutoCommit = SQLCA.AutoCommit

ltr_trans.DBParm = SQLCA.DBParm

Connect Using ltr_trans;

If ltr_trans.SQLCode = -1 THEN

  MessageBox("Database Connect Error", ltr_trans.SQLErrText)

  Return

End If

// Set up the PLAYER_HISTORY table

lds_last_change = CREATE datastore

lds_last_change.dataobject = 'dw_player_history_by_player'

If (lds_last_change.SetTransObject(ltr_trans) = -1) Then

  MessageBox('SetTransObject Failure', lds_last_change.dataobject + ' Failed')

  Return

End If

...

....

....

// Retrieve the player into the datastore

li_PlayerCount = lds_last_change.Retrieve(ls_PlayerAbbr)

// Check for a retrieve error

If li_PlayerCount = -1 Then

  MessageBox('Datawindow Retrieve Failure', 'Error: ' + ltr_trans.SQLErrText )

  Exit

End If

1. The SQLCA connection is working.  There are DataWindows in the the same Window that are retrieving data OK.  So the connection information is good.

2. There are no errors in the ltr_trans object.

3. There is no error when calling SetTransObject().

4. The Dataflow retrieves fine in the design mode.  It retrieves a single row using the same argument the script is using.

5. I've gone through the debugger and everything looks like I expect it to.

It has to be something simple that I'm not remembering.

Jim Egan

Senior Consultant

ProKarma, Inc.

[Former Member · Accepted solution]

Hi Jim;

1) => I've gone through the debugger and everything looks like I expect it to.

So are you talking about running the code from the PB IDE (as from your statement above, the code seems to be working OK) or from a compiled version?

2) Did you code the DS's ERROR, DBERROR and ITEMERROR events to capture DML issues when the Retrieve () fails on Oracle problems?

PS: The Retrieve () method returns a LONG. So you might want to change the variable li_PlayerCount to ll_PlayerCount.  

Regards ... Chris

[former_member213365 · Accepted solution]

Chris,

So far this is testing strictly within the IDE.  So there are no issues with the datastore object being unavailable.

Regarding your point #2, if there is a syntax error with the SELECT in the datastore wouldn't I see some sort of database error returned in the transaction object?

To troubleshoot this I'm going create an actual Datawindow object and put it on the Window. This is code that was cloned from another script that was working OK, so I'm rather confused as to why it isn't working.  I figured there was something very basic that I had missed.

[Former Member · Accepted solution]

As far as I know you can't see datawindow errors in the transaction object (only errors with embedded sql). You have to code the dberror event for error checking.

[Former Member · Accepted solution]

There's been a Jim Egan sighting!  Hey buddy - long time, no see!

Since you're creating your instance as the base "datastore", there's no code to trap the error in the dberror event.  You should create an ancestor class from "datastore" (call it n_ds or something).

You can drop some code in the dberror event of that class, then instantiate the n_ds class instead of "datastore" in your code.

-Paul Horan-

[former_member213365 · Accepted solution]

Hey Paul, yes I'm still alive and kicking.  I'm doing a little recreational programming in the off hours.

I see what you're saying.  It's all starting to come back....OK, not really. I probably have an object like that sitting around here somewhere.  Or maybe I'll swipe it from the PFC or something like that.  But I do see what you mean.

[Former Member · Accepted solution]

Don't even bother trying to "swipe" the datastore ancestor class from PFC.  That's a rabbit-hole you don't want to fall down.   With PFC, you're either taking the whole thing or you're not using it at all.

I remember trying to extract certain classes from PFC for use in a 3-tier PB client (PB -> EAS -> MSSQL), and found that there was one class that used an in-line SQL query - forcing you into a 2-tier client/server architecture whether you wanted it or not...   

-P-

[former_member213365 · Accepted solution]

It was a bit of a spaghetti code situation, wasn't it?

[former_member213365 · Accepted solution]

It was indeed something simple.  My SQLCA setup didn't have the database object delimit identifier turned off while my connection within the IDE (where I tested the Datawindow) did.  So my datastore object was trying to reference the table as "tablename" which in Oracle is a big no-no.  Once I added DelimitIdentifier='No' to the SQLCA (which is then copied to my local transaction object) everything worked like it was supposed to.

I got to the bottom of the problem by grabbing the actual SQL submitted to the database in the sqlpreview event.  There was much slapping of the forehead once I saw the syntax.

Thanks for the help gentlemen.

[Former Member]

Is all of the code above in one event? I notice the transaction object is declared locally and you said that the connection is okay as there are other datawindows that retrieve data. So, where do you really connect and where do you retrieve, which events?

Also, try changing this error checking to

If ltr_trans.SQLCode <> 0 THEN

  MessageBox("Database Connect Error", ltr_trans.SQLErrText)

  Return

End If

[Former Member]

Hi,

others 2 checks to do:

1) the retrieval argument defined in the datawindow  and the parameter ls_PlayerAbbr are the same type?

2) may be that another datawindow with the same name but with different retrieval argument exists in your librarylist?

Alberto

[Former Member]

Hi Jim,

It looks like you are having a security issue on the DB side. Try replacing l_trans with SLQCA in the following statement: If (lds_last_change.SetTransObject(ltr_trans) = -1) Then

If it solves your problem, you probably need to enable some roles after you connect the second transaction object.

HTH,

Manuel Marte

[Former Member]

hi Jim

maybe the oracle user used by your app has not the necessary grant for the tables in the query.

HTH

Alberto

[former_member213365]

No, the Oracle user is the schema owner.  Rights to objects shouldn't be an issue.

[Former Member]

You probably need to create a .pbr file with a reference to the DataWindow object as part of building the .exe.

A DataWindow object that does not have a hard reference may not be included in the executable. The reference to your DataWindow object is only in script so the compiler doesn't know that it is needed.

[Former Member]

Or create a PCode-EXE with PBDs. Than you don't need datawindow objects in a .PBR.

But I think, that SetTransObject would fail if the datawindow object is absent.

If you have the problem also if you run your application from IDE:

- Are all retrieval arguments specified?

- Create a class (e.g. n_ds) inherited from datastore and put some code to check the error in dberror event. And use this class in your code.

- Try it with a datawindow control. So you can see some error messages or dialoges the powerbuilder may show.