• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Stored Procedure during Runtime returns -1 instead of rows

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

Resolved Stored Procedure during Runtime returns -1 instead of rows

  1. How-to
0
Votes
Undo
  1. Frank Zettanucci
    Frank Zettanucci
  2. PowerBuilder
  3. Thursday, 9 June 2022 20:05 PM UTC

I created a sql server stored procedure, works fine in both the SQL Query tools and in a datawindow i created which uses Stored Procedure as the definition source

In the PB IDE Painter I can retrieve the datawindow results, it executes SP just fine using PREVIEW when in the IDE, seems legit.

When I execute the runtime build of the app the datastore I have created with the datawindow keeps returning -1

When I debug the runtime the arguments are all correct, and yet its not pulling any data.

 

Connection details:

DBMS = SNC SQL Native Client(OLE DB)

DBParm = appname='APPNAME',Identity='SCOPE_IDENTITY()',database='DB_NAME',CommitOnDisconnect='No',StaticBind='0',ncharbind='1',DisableBind='0',OJSyntax='ANSI',PBTrimCharColumns='YES',DelimitIdentifier='No'

 

Long LL_RC
datastore LDS_Data
LDS_Data = create datastore
LDS_Data.DataObject = 'd_sp_1x3p'
LDS_Data.SetTransObject(GUO_SQLSRV)

LL_RC = LDS_Data.Retrieve(AL_ID, AS_Type, AL_Acct_ID)

But LL_RC always returns -1, There should be 450+ rows ...

I can connect and do every other crud imaginable, but for some reason SP wont retrieve,

I do not see any SQLErrText strings or error codes being thrown.

Any thoughts on what else I should be looking at?

F.

 

 

PowerBuilder 2022 Beta installation problem
Schema coded in Data window select
Accepted Answer
Joseph Vendra
Joseph Vendra Accepted Answer Pending Moderation
  1. Friday, 10 June 2022 07:49 AM UTC
  2. PowerBuilder
  3. # Permalink

If you dont have a custom datastore user class object with a dberror event scripted you could create one and look at the sqlerrtext there.

edit: In case its not clear, then you change your script to use the new uo_ds object.

Long LL_RC
datastore LDS_Data
LDS_Data = create datastore

becomes ...

Long LL_RC
uo_ds LDS_Data
LDS_Data = create uo_ds

For example:

Create a new PB OBJECT > STANDARD CLASS > inherit from DATASTORE
Save as uo_ds
Add dberror event script to log or trap the sqlerrtext and sqldbcode values.
i.e. using global variables
GS_SQLERRTEXT = sqlerrtext   // When UO_DS.DBError Fires ; it will set the Error Text here
GL_SQLDBCODE = sqldbcode   // When UO_DS.DBError Fires ; it will set the Error DBCode here

Then debug your application and look a the dberror event to see what could be going on.

You probably have an invalid column data type mismatch from what you think the db is providing to what your actually getting.

SQLERRTEXT in the custom datastore object's dberror event would show up as: Select Error: Column lists do not match.

This can happen if your manually defining the result set your expecting and are not 100% bang on.

If this is the case then the Fix is easy, recreate your datawindow, define the list of columns to better match data types you are providing from the stored procedures final select statement and it should fix the problem.

Joseph,

 

Comment
Frank Zettanucci
  1. Frank Zettanucci
  2. Friday, 10 June 2022 13:40 PM UTC
This was the ticket, the standard datastore doesnt seem to show the sqlerrtext or sqlerrcode when using SQLNCLI11 provider?

Indeed the dba had a column that returns 1 or 0 set as char(1) and I was trying to define it as a number because which of us is smarter?



The only question i really have now is that I have this uo_ds datastore for debugging the problem should i go back to the standard datastore or leave the uo_ds in there?



thanks for all the help folks

  1. Helpful
Roland Smith
  1. Roland Smith
  2. Friday, 10 June 2022 14:13 PM UTC
Standard datastore objects don't update sqlca with error info. You should use a custom one with code in the dberror event to capture the error.

I would have code in the dberror event that saves all the arguments into instance variables. Then they are all available to the script that has the retrieve function call. If the return code is -1, then display the error information from the instance variables.
  1. Helpful 1
There are no comments made yet.
Responses (5)
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Thursday, 9 June 2022 20:12 PM UTC
  2. PowerBuilder
  3. # 1

Try adding the datawindow to the .pbr file when compiling the app.

It would look like this:

libname.pbl(d_sp_1x3p)

 

The DataWindow isn't directly referenced by a DW control so the EXE build may not have included it.

Comment
There are no comments made yet.
Frank Zettanucci
Frank Zettanucci Accepted Answer Pending Moderation
  1. Friday, 10 June 2022 00:32 AM UTC
  2. PowerBuilder
  3. # 2

The datawindow is in the pbl

 

 

Comment
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Friday, 10 June 2022 01:36 AM UTC
  2. PowerBuilder
  3. # 3

Suggestions:

Check the return code from the SetTransObject function call.

Since you can retrieve successfully from the DW Painter in the IDE, verify the connection properties in the IDE's DB Profile matches those used by the application. Or set up a test DB Profile that exactly matches what is used by the compiled exe and see if retrieval fails/works.

You're not using the "TrustedConnection" (i.e., Windows Authentication) DBParm setting... See if the schema/permissions of the login/user when running the compiled exe has permission to execute this stored procedure.

HTH, John

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 10 June 2022 13:37 PM UTC
  2. PowerBuilder
  3. # 4

Hi Frank;

  Try .... SQLCA.DBMS = "TRACE SNC "

Then see what the SQL trace details uncover as to why this fails.  HTH

Regards ... Chris

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.