Skip to Content
0

Schema Missing/Stripped From DataWindow During Runtime

Feb 21, 2017 at 12:27 AM

212

avatar image
Former Member

We are starting to write a new PowerBuilder 12.5 (Classic) application and are having an issue calling a DB2 Stored Procedure defined as follows:

CREATE PROCEDURE REGIONDEV.GET_COMPANYDTL()

DYNAMIC RESULT SETS 1

LANGUAGE SQL

BEGIN

DECLARE RESULT_SET_1 CURSOR WITH RETURN TO CLIENT FOR

SELECT COMPANY_NUM, COMPANY_NAME

FROM REGIONDEV.COMPANY;

OPEN RESULT_SET_1;

END

PB 12.5 is able to retrieve the result set when we retrieve it via the DataWindow Painter.

However, when we dw_1.Retrieve() it from our application, the DBError event throws the following error message:

IBM][CLI Driver][DB2] SQL0440N No authorized routine named "GET_COMPANYDTL" of type "" having compatible arguments was found. SQLSTATE=42884

Looking at the variable sqlsyntax, it contains:

1 execute GET_COMPANYDTL;0

I guess the absence of "REGIONDEV" is causing the above error. But why was it stripped? Is there a setting to leave it?

BTW, PBUseProcOwner='YES'.

More power to PB!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

9 Answers

avatar image
Former Member Feb 21, 2017 at 01:27 PM
0

I don't use DB2 but my guess would be that it is related to the userid that you were logged in when creating the datawindow verses the one the application is logging in as.

You should be able to 'Edit Source' the datawindow and add it in.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 21, 2017 at 02:07 PM
0

Roland,

When I "Edit Source" the datawindow, "REGIONDEV" is there, like so:

procedure="1 execute REGIONDEV.GET_COMPANYDTL;0 " )

For some reason, it is stripped off during runtime.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 21, 2017 at 05:19 PM
0

It could be that the error message just isn't showing it. Try using DB Trace (Add 'TRACE ' to the front of the sqlca.DBMS value).

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 21, 2017 at 07:38 PM
0

Roland, here are the contents of the trace file:


/*---------------------------------------------------*/
/* 2/21/2017 14:29 */
/*---------------------------------------------------*/
(7d1c068): LOGIN:(DBI_LOGIN) (131.113 MS)
(7d1c068): CONNECT TO TRACE ODBC:
(7d1c068): DBPARM=ConnectString='DSN=RCB_DSN_DB2COPY;UID=CARL;PWD=<******>;PBUseProcOwner='Yes'(DBI_CONNECT) (131.121 MS)
(7d1c068): (DBI_ADJUST_CONNECT) (131.121 MS)
(7d1c068): (DBI_CURRENT_OF_CURSOR) (131.123 MS)
(7d1c068): PREPARE:(DBI_PARSE_ONLY) (131.123 MS)
(7d1c068): (DBI_SPECIAL_CURSOR) (131.123 MS)
(7d1c068): (DBI_SKIP_VERIFY) (131.123 MS)
(7d1c068): (DBI_DESCRIBE_BEFORE_EXEC) (131.123 MS)
(7d1c068): (DBI_SUPPORT_INPUT_PARM_BIND) (131.129 MS)
(7d1c068): BEGIN TRANSACTION:(DBI_START_TRAN) (131.143 MS)
(7d1c068): PROCEDURE PREPARE:
(7d1c068): execute REGIONDEV.GET_COMPANYDTL;0 (DBI_PROC_PREPARE) (570.629 MS)
(7d1c068): *** ERROR -440 ***(rc -1) : SQLSTATE = 42884
[IBM][CLI Driver][DB2] SQL0440N No authorized routine named "GET_COMPANYDTL" of type "" having compatible arguments was found. SQLSTATE=42884

(7d1c068): CANCEL:(DBI_CANCEL) (570.662 MS)
(7d1c068): COMMIT:(DBI_COMMIT) (589.400 MS)
(7d1c068): DISCONNECT:(DBI_DISCONNECT) (609.068 MS)
(7d1c068): SHUTDOWN DATABASE INTERFACE:(DBI_SHUTDOWN_INTERFACE) (609.069 MS)

It appears that REGIONDEV is part of the call? Hmmm, I wonder why Retrieve() fails during runtime but succeeds during development. I am the one creating the DataWindow and I'm the one running it. Same credentials.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 21, 2017 at 10:44 PM
0

Is it possible that runtime connects to a different database than the one you are using in Dev?

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 22, 2017 at 01:09 PM
0

Neil, I'm pretty sure I am connecting to the same DB. Plus, I can successfully call it (and fetch the result set) using the standard ANSI method for calling stored procedures (code snippet):

transaction db2

db2 = CREATE Transaction

db2.DBMS = 'ODBC'

db2.DBParm = "ConnectString='DSN=RCB_DSN_DB2COPY;UID=CARL;PWD=<******>;PBUseProcOwner='Yes''"

DECLARE testProc PROCEDURE FOR REGIONDEV.GET_COMPANYDTL
USING db2;

EXECUTE testProc;

IF db2.SQLCode < 0 THEN

MessageBox('EXECUTE Failure', db2.SQLErrText, StopSign!)

ELSE

FETCH testProc INTO :ls_company_num, :ls_company_name;

END IF

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 22, 2017 at 05:41 PM
0

Just an additional info. I am using PowerBuilder 12.5.2 Build 5006 Enterprise Edition.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 23, 2017 at 02:13 PM
0

Even if I remove the schema "REGIONDEV" from the datawindow, like so:

procedure="1 execute GET_COMPANYDTL;0 " )

the trace file still shows "REGIONDEV":

/*---------------------------------------------------*/
/* 2/21/2017 14:29 */
/*---------------------------------------------------*/
(7d1c068): LOGIN:(DBI_LOGIN) (131.113 MS)
(7d1c068): CONNECT TO TRACE ODBC:
(7d1c068): DBPARM=ConnectString='DSN=RCB_DSN_DB2COPY;UID=CARL;PWD=<******>;PBUseProcOwner='Yes'(DBI_CONNECT) (131.121 MS)
(7d1c068): (DBI_ADJUST_CONNECT) (131.121 MS)
(7d1c068): (DBI_CURRENT_OF_CURSOR) (131.123 MS)
(7d1c068): PREPARE:(DBI_PARSE_ONLY) (131.123 MS)
(7d1c068): (DBI_SPECIAL_CURSOR) (131.123 MS)
(7d1c068): (DBI_SKIP_VERIFY) (131.123 MS)
(7d1c068): (DBI_DESCRIBE_BEFORE_EXEC) (131.123 MS)
(7d1c068): (DBI_SUPPORT_INPUT_PARM_BIND) (131.129 MS)
(7d1c068): BEGIN TRANSACTION:(DBI_START_TRAN) (131.143 MS)
(7d1c068): PROCEDURE PREPARE:
(7d1c068): execute REGIONDEV.GET_COMPANYDTL;0 (DBI_PROC_PREPARE) (570.629 MS)
(7d1c068): *** ERROR -440 ***(rc -1) : SQLSTATE = 42884
[IBM][CLI Driver][DB2] SQL0440N No authorized routine named "GET_COMPANYDTL" of type "" having compatible arguments was found. SQLSTATE=42884

(7d1c068): CANCEL:(DBI_CANCEL) (570.662 MS)
(7d1c068): COMMIT:(DBI_COMMIT) (589.400 MS)
(7d1c068): DISCONNECT:(DBI_DISCONNECT) (609.068 MS)
(7d1c068): SHUTDOWN DATABASE INTERFACE:(DBI_SHUTDOWN_INTERFACE) (609.069 MS)

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 23, 2017 at 07:49 PM
0

Guys, this is totally my bad.

I was using the following incorrect DBParm string during runtime:

db2.DBPARM = "ConnectString='DSN=RCB_DSN_DB2COPY;UID=CARL;PWD=<******>;PBUseProcOwner='Yes'"

After using the DBParm string that was generated in the trace file when the datawindow is retrieved during development (in the DataWindow Painter), like so:

db2.DBParm = "ConnectString='DSN=RCB_DSN_DB2COPY;UID=CARL;PWD=<******>',PBUseProcOwner='Yes'"

and now the Stored Procedure executes perfectly during runtime. There is a slight difference right before PBUseProcOwner.

Thank you Roland and Neil for your inputs. More power to PB!

Share
10 |10000 characters needed characters left characters exceeded