Topic
  • 4 replies
  • Latest Post - ‏2013-01-23T07:20:24Z by B.Hauser
SystemAdmin
SystemAdmin
3129 Posts

Pinned topic ?

‏2013-01-17T09:28:43Z |
i have a legacy program liba/pgma,
for accessing it i create a stored procedure in QGPL/sp_pgma.
liba is included in *libl.

THE FOLLOWING IS SP CODE:

CREATE PROCEDURE SP_PGMA (
IN I_ID CHAR(2),
OUT O_ERR_CODE CHAR(01),
OUT O_NAME CHAR(12))
LANGUAGE SQL
P1: BEGIN
DECLARE WK_PGMA_AREA CHAR(15);--;
SET O_ERR_CODE = ' ';
SET O_NAME = ' ';
SET WK_PGMA_AREA = I_ID
|| O_ERR_CODE
|| O_NAME;
CALL PGMA(WK_PGMA_AREA);
SET O_ERR_CODE = SUBSTR(WK_PGMA_AREA,3,1);
SET O_NAME = SUBSTR(WK_PGMA_AREA,4,12);
END P1

i write a embedded sql code in cobol to call sp_pgma.
but os/400 tells me she can not find out pgma in QGPL.
it seems she recognizes pgma will be in the library where SP resides.
has anyone encountered this problem?
Updated on 2013-01-23T07:20:24Z at 2013-01-23T07:20:24Z by B.Hauser
  • SystemAdmin
    SystemAdmin
    3129 Posts

    ? Stored Procedure can not locate legacy program which resides in *libl

    ‏2013-01-17T09:33:26Z  
    Sorry
    i miss the question title.
  • krmilligan
    krmilligan
    450 Posts

    Re: ? Stored Procedure can not locate legacy program which resides in *libl

    ‏2013-01-17T21:53:00Z  
    Sorry
    i miss the question title.
    Did you register your external program with a CREATE PROCEDURE statement? The SQL PATH controls the resolution of unqualified CALL statements.
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: ? Stored Procedure can not locate legacy program which resides in *libl

    ‏2013-01-23T05:48:53Z  
    Did you register your external program with a CREATE PROCEDURE statement? The SQL PATH controls the resolution of unqualified CALL statements.
    i paste the code into as/400 source physical file.

    execute runsqlstm to create the stored procedure.

    the stored procedure can work (i.e. it can search unqualified object by library list) from called by java.

    i checked the runsqlstm paramaters, which has one "naming" , whose value is "*SYS".
    i think that's why Data Studio can not access unqualified object by library list.
    i configure property of driver properties(AS/400 Toolbox for JAVA Default),
    i added naming=system,
    then created stored procedure.
    but this stored procedure can not locate my table in another library, which is in my library list.

    has any one encountered this problem?
  • B.Hauser
    B.Hauser
    255 Posts

    Re: ? Stored Procedure can not locate legacy program which resides in *libl

    ‏2013-01-23T07:20:24Z  
    i paste the code into as/400 source physical file.

    execute runsqlstm to create the stored procedure.

    the stored procedure can work (i.e. it can search unqualified object by library list) from called by java.

    i checked the runsqlstm paramaters, which has one "naming" , whose value is "*SYS".
    i think that's why Data Studio can not access unqualified object by library list.
    i configure property of driver properties(AS/400 Toolbox for JAVA Default),
    i added naming=system,
    then created stored procedure.
    but this stored procedure can not locate my table in another library, which is in my library list.

    has any one encountered this problem?
    Check whether a DEFAULT or CURRENT SCHEMA is set in your environment.
    If a default/current schema is set tables, views and indexes are searched within this schema even though System Naming is used!

    For more information about SQL and System Naming please check the following link:
    System versus SQL name: part 2

    Birgitta