Skip to Content
avatar image
Former Member

Schema Missing/Stripped From DataWindow During Runtime

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

9 Answers

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

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 21, 2017 at 02:07 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 21, 2017 at 05:19 PM

    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).

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 21, 2017 at 07:38 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 21, 2017 at 10:44 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 22, 2017 at 01:09 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 22, 2017 at 05:41 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 23, 2017 at 02:13 PM

    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)

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 23, 2017 at 07:49 PM

    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!

    Add comment
    10|10000 characters needed characters exceeded