CALL stored_proc from CLI problem... 
Author Message
 CALL stored_proc from CLI problem...

Hi all

I have a stored procedure that utilizes IDENTITY_VAL_LOCAL() function for
multiple table inserts...
If i execute CALL from CLP or Command center it works flawlessly.
It also works if I call it from the client machine that has the database
cataloged....

However on the client machine I have a web server running that uses PHP to
produce dynamic web pages by querying database....
Trying to execute a CALL from a PHP script , however, failed with message :

Warning: SQL error: [IBM][CLI Driver][DB2/6000] SQL10013N The specified
library "/home/db2inst1/sqllib/function/SP_INS_MALL" could not be loaded. ,
SQL state S1000 in SQLExecDirect in /usr/local/apache/htdocs/DB2test.php on
line 24

OK so i figure out DB CLI that i binded to PHP is using CLI function
SQLExecDirect
So i looked at CLI Guide and Reference if CLI supports this Function
. In appendix I states that it does with a footnote :

3. An X indicates that you can execute this statement using either
SQLExecDirect()or SQLPrepare()
and SQLExecute(). If there is an equivalent DB2 CLI function, the function
name is listed.

Next I tried writing a small C app utilizing DB CLI that will call my proc
... App compiled fine
but I get the SAME error running the app on server or on the client side

/*code i used....*/
   /* select all malls */
   printf(">>> Calling sp_ins_mall stored proc... \n");
   if (SQLExecDirect(hstmt,ins_mall,SQL_NTS) != SQL_SUCCESS)
    {
       SQLCHAR      SQLState[6];
       SQLINTEGER   NativeErrorPtr;
       SQLCHAR      MessageText[1024];

       printf(">>> ERROR while calling stored procedure %s\n", ins_malls)

SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,SQLState,&NativeErrorPtr,MessageText,1
024,NULL);
       printf("%s",MessageText);
       SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
       return(SQL_ERROR);
    }

ERROR PRODUCED BY SQLGetDiagRec() is :
[IBM][CLI Driver][DB2/6000] SQL10013N  The specified library
"/home/db2inst1/sqllib/function/sp_ins_mall" could not be loaded.

This is the same error as before .....
1. Why is CALL working from CLP or command Center and not from CLI as stated
in Manuals?
2. HOW CAN I FIX THIS.....

Please help....

Thanks



Tue, 01 Jul 2003 01:29:12 GMT
 CALL stored_proc from CLI problem...

Do a

    SELECT procname, procschema, implementaion FROM SYSCAT.PROCEDURES

check if your SP is there (and has the right implementation).
Then call the SP with CALL <procschema>.<procname> with the values from the
db catalogs - in case you cataloged it. If not the library should match the
SP name (and the SP entry point should be the same name)

Your procedure implemention is listed on time as
/home/db2inst1/sqllib/function/SP_INS_MALL (PHP case) and on time as
/home/db2inst1/sqllib/function/sp_ins_mall (CLI case) . Did you change
anything there?

Regards, Dirk


Quote:
> Hi all

> I have a stored procedure that utilizes IDENTITY_VAL_LOCAL() function for
> multiple table inserts...
> If i execute CALL from CLP or Command center it works flawlessly.
> It also works if I call it from the client machine that has the database
> cataloged....

> However on the client machine I have a web server running that uses PHP to
> produce dynamic web pages by querying database....
> Trying to execute a CALL from a PHP script , however, failed with message
:

> Warning: SQL error: [IBM][CLI Driver][DB2/6000] SQL10013N The specified
> library "/home/db2inst1/sqllib/function/SP_INS_MALL" could not be loaded.
,
> SQL state S1000 in SQLExecDirect in /usr/local/apache/htdocs/DB2test.php
on
> line 24

> OK so i figure out DB CLI that i binded to PHP is using CLI function
> SQLExecDirect
> So i looked at CLI Guide and Reference if CLI supports this Function
> . In appendix I states that it does with a footnote :

> 3. An X indicates that you can execute this statement using either
> SQLExecDirect()or SQLPrepare()
> and SQLExecute(). If there is an equivalent DB2 CLI function, the function
> name is listed.

> Next I tried writing a small C app utilizing DB CLI that will call my proc
> ... App compiled fine
> but I get the SAME error running the app on server or on the client side

> /*code i used....*/
>    /* select all malls */
>    printf(">>> Calling sp_ins_mall stored proc... \n");
>    if (SQLExecDirect(hstmt,ins_mall,SQL_NTS) != SQL_SUCCESS)
>     {
>        SQLCHAR      SQLState[6];
>        SQLINTEGER   NativeErrorPtr;
>        SQLCHAR      MessageText[1024];

>        printf(">>> ERROR while calling stored procedure %s\n", ins_malls)

SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,SQLState,&NativeErrorPtr,MessageText,1

- Show quoted text -

Quote:
> 024,NULL);
>        printf("%s",MessageText);
>        SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
>        return(SQL_ERROR);
>     }

> ERROR PRODUCED BY SQLGetDiagRec() is :
> [IBM][CLI Driver][DB2/6000] SQL10013N  The specified library
> "/home/db2inst1/sqllib/function/sp_ins_mall" could not be loaded.

> This is the same error as before .....
> 1. Why is CALL working from CLP or command Center and not from CLI as
stated
> in Manuals?
> 2. HOW CAN I FIX THIS.....

> Please help....

> Thanks



Tue, 01 Jul 2003 03:53:48 GMT
 CALL stored_proc from CLI problem...
Hi Dirk

Sorry about the case...I was trying both ways and it does not work.
The query you posted returns the following

PROCNAME         PROCSCHEMA    IMPLEMENTATION
---------------        ------------------    -----------------------
SP_INS_MALL    DB2INST1             P0226690!pgsjmp

I Also checked the the folowing directory where DB2 puts compiled procs :

-=[/home/db2inst1]==>ls -la sqllib/function/routine/sqlproc/SAMPLE/DB2INST1/
total 34
drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:02 .
drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:01 ..
-rwxr-xr-x   1 db2inst1 db2iadm1     9319 Jan 11 11:02 P0226690
-rw-r-----   1 db2inst1 db2iadm1    13034 Jan 11 11:02 P0226690.c
-rw-r-----   1 db2inst1 db2iadm1     1187 Jan 11 11:02 P0226690.log
-rw-r-----   1 db2inst1 db2iadm1     9131 Jan 11 11:02 P0226690.sqc
drwxrwsr-x   2 db2inst1 db2iadm1      512 Jan 11 11:02 tmp

I am still not able to CALL this sp from the CLI app. It returns the same
error...
Also  here is the sp I created ...Maybe I am missing something ...Any other
ideas????
---snip---

CREATE PROCEDURE DB2INST1.SP_INS_MALL ( IN sn varchar(30),
                                        IN bn varchar(20),
                                        IN fn varchar(30),
                                        IN ln varchar(30) )
    RESULT SETS 0
    LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
BEGIN
    -- Declare variable
    DECLARE last_id INT;

    INSERT INTO PARTY (FNAME,LNAME) VALUES (fn,ln);
    VALUES(IDENTITY_VAL_LOCAL()) INTO last_id;
    INSERT INTO MALL (PARTY_ID,SHORT_NAME,BANK_NAME) VALUES (last_id,sn,bn);



---snip---

Thank you


Quote:
> Do a

>     SELECT procname, procschema, implementaion FROM SYSCAT.PROCEDURES

> check if your SP is there (and has the right implementation).
> Then call the SP with CALL <procschema>.<procname> with the values from
the
> db catalogs - in case you cataloged it. If not the library should match
the
> SP name (and the SP entry point should be the same name)

> Your procedure implemention is listed on time as
> /home/db2inst1/sqllib/function/SP_INS_MALL (PHP case) and on time as
> /home/db2inst1/sqllib/function/sp_ins_mall (CLI case) . Did you change
> anything there?

> Regards, Dirk



> > Hi all

> > I have a stored procedure that utilizes IDENTITY_VAL_LOCAL() function
for
> > multiple table inserts...
> > If i execute CALL from CLP or Command center it works flawlessly.
> > It also works if I call it from the client machine that has the database
> > cataloged....

> > However on the client machine I have a web server running that uses PHP
to
> > produce dynamic web pages by querying database....
> > Trying to execute a CALL from a PHP script , however, failed with
message
> :

> > Warning: SQL error: [IBM][CLI Driver][DB2/6000] SQL10013N The specified
> > library "/home/db2inst1/sqllib/function/SP_INS_MALL" could not be
loaded.
> ,
> > SQL state S1000 in SQLExecDirect in /usr/local/apache/htdocs/DB2test.php
> on
> > line 24

> > OK so i figure out DB CLI that i binded to PHP is using CLI function
> > SQLExecDirect
> > So i looked at CLI Guide and Reference if CLI supports this Function
> > . In appendix I states that it does with a footnote :

> > 3. An X indicates that you can execute this statement using either
> > SQLExecDirect()or SQLPrepare()
> > and SQLExecute(). If there is an equivalent DB2 CLI function, the
function
> > name is listed.

> > Next I tried writing a small C app utilizing DB CLI that will call my
proc
> > ... App compiled fine
> > but I get the SAME error running the app on server or on the client side

> > /*code i used....*/
> >    /* select all malls */
> >    printf(">>> Calling sp_ins_mall stored proc... \n");
> >    if (SQLExecDirect(hstmt,ins_mall,SQL_NTS) != SQL_SUCCESS)
> >     {
> >        SQLCHAR      SQLState[6];
> >        SQLINTEGER   NativeErrorPtr;
> >        SQLCHAR      MessageText[1024];

> >        printf(">>> ERROR while calling stored procedure %s\n",
ins_malls)

SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,SQLState,&NativeErrorPtr,MessageText,1

- Show quoted text -

Quote:
> > 024,NULL);
> >        printf("%s",MessageText);
> >        SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
> >        return(SQL_ERROR);
> >     }

> > ERROR PRODUCED BY SQLGetDiagRec() is :
> > [IBM][CLI Driver][DB2/6000] SQL10013N  The specified library
> > "/home/db2inst1/sqllib/function/sp_ins_mall" could not be loaded.

> > This is the same error as before .....
> > 1. Why is CALL working from CLP or command Center and not from CLI as
> stated
> > in Manuals?
> > 2. HOW CAN I FIX THIS.....

> > Please help....

> > Thanks



Tue, 01 Jul 2003 05:13:05 GMT
 CALL stored_proc from CLI problem...
What happens if you call the SP in you CLI app with DB2INST1.SP_INS_MALL?
The error message that you get shows that the function resolution fails and
that it tries to treat the sp name as a library. Here's the description from
the SQL Reference

-- snip--
If the library or function could not be found, the procedure-name is used to
search the defined procedures (in SYSCAT.PROCEDURES) for a matching
procedure. A matching procedure is determined using the steps that follow.
Find the procedures from the catalog (SYSCAT.PROCEDURES) where the PROCNAME
matches the procedure-name specified and the PROCSCHEMA is a schema name in
the SQL path (CURRENT PATH special register). If the schema name is
explicitly specified, the SQL path is ignored and only procedures with the
specified schema name are considered.
Next, eliminate any of these procedures that do not have the same number of
parameters as the number of arguments specified in the CALL statement.
Chose the remaining procedure that is earliest in the SQL path.
If there are no remaining procedures after step 2, an error is returned
(SQLSTATE 42884).
-- snip--

So check that the names (including schema) and the parameters match.

Regards, Dirk


Quote:
> Hi Dirk

> Sorry about the case...I was trying both ways and it does not work.
> The query you posted returns the following

> PROCNAME         PROCSCHEMA    IMPLEMENTATION
> ---------------        ------------------    -----------------------
> SP_INS_MALL    DB2INST1             P0226690!pgsjmp

> I Also checked the the folowing directory where DB2 puts compiled procs :

> -=[/home/db2inst1]==>ls -la

sqllib/function/routine/sqlproc/SAMPLE/DB2INST1/
Quote:
> total 34
> drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:02 .
> drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:01 ..
> -rwxr-xr-x   1 db2inst1 db2iadm1     9319 Jan 11 11:02 P0226690
> -rw-r-----   1 db2inst1 db2iadm1    13034 Jan 11 11:02 P0226690.c
> -rw-r-----   1 db2inst1 db2iadm1     1187 Jan 11 11:02 P0226690.log
> -rw-r-----   1 db2inst1 db2iadm1     9131 Jan 11 11:02 P0226690.sqc
> drwxrwsr-x   2 db2inst1 db2iadm1      512 Jan 11 11:02 tmp

> I am still not able to CALL this sp from the CLI app. It returns the same
> error...
> Also  here is the sp I created ...Maybe I am missing something ...Any
other
> ideas????
> ---snip---

> CREATE PROCEDURE DB2INST1.SP_INS_MALL ( IN sn varchar(30),
>                                         IN bn varchar(20),
>                                         IN fn varchar(30),
>                                         IN ln varchar(30) )
>     RESULT SETS 0
>     LANGUAGE SQL
> ------------------------------------------------------------------------
> -- SQL Stored Procedure
> ------------------------------------------------------------------------
> BEGIN
>     -- Declare variable
>     DECLARE last_id INT;

>     INSERT INTO PARTY (FNAME,LNAME) VALUES (fn,ln);
>     VALUES(IDENTITY_VAL_LOCAL()) INTO last_id;
>     INSERT INTO MALL (PARTY_ID,SHORT_NAME,BANK_NAME) VALUES
(last_id,sn,bn);



> ---snip---

> Thank you



> > Do a

> >     SELECT procname, procschema, implementaion FROM SYSCAT.PROCEDURES

> > check if your SP is there (and has the right implementation).
> > Then call the SP with CALL <procschema>.<procname> with the values from
> the
> > db catalogs - in case you cataloged it. If not the library should match
> the
> > SP name (and the SP entry point should be the same name)

> > Your procedure implemention is listed on time as
> > /home/db2inst1/sqllib/function/SP_INS_MALL (PHP case) and on time as
> > /home/db2inst1/sqllib/function/sp_ins_mall (CLI case) . Did you change
> > anything there?

> > Regards, Dirk



> > > Hi all

> > > I have a stored procedure that utilizes IDENTITY_VAL_LOCAL() function
> for
> > > multiple table inserts...
> > > If i execute CALL from CLP or Command center it works flawlessly.
> > > It also works if I call it from the client machine that has the
database
> > > cataloged....

> > > However on the client machine I have a web server running that uses
PHP
> to
> > > produce dynamic web pages by querying database....
> > > Trying to execute a CALL from a PHP script , however, failed with
> message
> > :

> > > Warning: SQL error: [IBM][CLI Driver][DB2/6000] SQL10013N The
specified
> > > library "/home/db2inst1/sqllib/function/SP_INS_MALL" could not be
> loaded.
> > ,
> > > SQL state S1000 in SQLExecDirect in

/usr/local/apache/htdocs/DB2test.php

- Show quoted text -

Quote:
> > on
> > > line 24

> > > OK so i figure out DB CLI that i binded to PHP is using CLI function
> > > SQLExecDirect
> > > So i looked at CLI Guide and Reference if CLI supports this Function
> > > . In appendix I states that it does with a footnote :

> > > 3. An X indicates that you can execute this statement using either
> > > SQLExecDirect()or SQLPrepare()
> > > and SQLExecute(). If there is an equivalent DB2 CLI function, the
> function
> > > name is listed.

> > > Next I tried writing a small C app utilizing DB CLI that will call my
> proc
> > > ... App compiled fine
> > > but I get the SAME error running the app on server or on the client
side

> > > /*code i used....*/
> > >    /* select all malls */
> > >    printf(">>> Calling sp_ins_mall stored proc... \n");
> > >    if (SQLExecDirect(hstmt,ins_mall,SQL_NTS) != SQL_SUCCESS)
> > >     {
> > >        SQLCHAR      SQLState[6];
> > >        SQLINTEGER   NativeErrorPtr;
> > >        SQLCHAR      MessageText[1024];

> > >        printf(">>> ERROR while calling stored procedure %s\n",
> ins_malls)

SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,SQLState,&NativeErrorPtr,MessageText,1

- Show quoted text -

Quote:
> > > 024,NULL);
> > >        printf("%s",MessageText);
> > >        SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
> > >        return(SQL_ERROR);
> > >     }

> > > ERROR PRODUCED BY SQLGetDiagRec() is :
> > > [IBM][CLI Driver][DB2/6000] SQL10013N  The specified library
> > > "/home/db2inst1/sqllib/function/sp_ins_mall" could not be loaded.

> > > This is the same error as before .....
> > > 1. Why is CALL working from CLP or command Center and not from CLI as
> > stated
> > > in Manuals?
> > > 2. HOW CAN I FIX THIS.....

> > > Please help....

> > > Thanks



Wed, 02 Jul 2003 03:15:36 GMT
 CALL stored_proc from CLI problem...
Dirk
I have used in my CLI app exact same SQL statement as I did using
interactive Command Center
or CLP....Exact same statement with fully qualifing schema name works
interactivelly but not from my app. I double checked that all my env vars
are correct (DB2PATH, DB2INST...etc..etc...)  but to no avail.....Again I
also checked SYSCAT.PROCEDURES and it is registered....it seems like CLI
does not looks first in catalog .....

First thing Monday when I return to work I would like to email You the :
1. DDL for two tables I use in SAMPLE Database
2. DDL for the stored proc I use
3. C source and Makefile of my CLI app (It works with C for AIX v 5.))

Perhaps , You can figure out why id does not work.....?????
Once again I appreciate your efforts...
Amir Mistric


Quote:
> What happens if you call the SP in you CLI app with DB2INST1.SP_INS_MALL?
> The error message that you get shows that the function resolution fails
and
> that it tries to treat the sp name as a library. Here's the description
from
> the SQL Reference

> -- snip--
> If the library or function could not be found, the procedure-name is used
to
> search the defined procedures (in SYSCAT.PROCEDURES) for a matching
> procedure. A matching procedure is determined using the steps that follow.
> Find the procedures from the catalog (SYSCAT.PROCEDURES) where the
PROCNAME
> matches the procedure-name specified and the PROCSCHEMA is a schema name
in
> the SQL path (CURRENT PATH special register). If the schema name is
> explicitly specified, the SQL path is ignored and only procedures with the
> specified schema name are considered.
> Next, eliminate any of these procedures that do not have the same number
of
> parameters as the number of arguments specified in the CALL statement.
> Chose the remaining procedure that is earliest in the SQL path.
> If there are no remaining procedures after step 2, an error is returned
> (SQLSTATE 42884).
> -- snip--

> So check that the names (including schema) and the parameters match.

> Regards, Dirk



> > Hi Dirk

> > Sorry about the case...I was trying both ways and it does not work.
> > The query you posted returns the following

> > PROCNAME         PROCSCHEMA    IMPLEMENTATION
> > ---------------        ------------------    -----------------------
> > SP_INS_MALL    DB2INST1             P0226690!pgsjmp

> > I Also checked the the folowing directory where DB2 puts compiled procs
:

> > -=[/home/db2inst1]==>ls -la
> sqllib/function/routine/sqlproc/SAMPLE/DB2INST1/
> > total 34
> > drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:02 .
> > drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:01 ..
> > -rwxr-xr-x   1 db2inst1 db2iadm1     9319 Jan 11 11:02 P0226690
> > -rw-r-----   1 db2inst1 db2iadm1    13034 Jan 11 11:02 P0226690.c
> > -rw-r-----   1 db2inst1 db2iadm1     1187 Jan 11 11:02 P0226690.log
> > -rw-r-----   1 db2inst1 db2iadm1     9131 Jan 11 11:02 P0226690.sqc
> > drwxrwsr-x   2 db2inst1 db2iadm1      512 Jan 11 11:02 tmp

> > I am still not able to CALL this sp from the CLI app. It returns the
same
> > error...
> > Also  here is the sp I created ...Maybe I am missing something ...Any
> other
> > ideas????
> > ---snip---

> > CREATE PROCEDURE DB2INST1.SP_INS_MALL ( IN sn varchar(30),
> >                                         IN bn varchar(20),
> >                                         IN fn varchar(30),
> >                                         IN ln varchar(30) )
> >     RESULT SETS 0
> >     LANGUAGE SQL
> > ------------------------------------------------------------------------
> > -- SQL Stored Procedure
> > ------------------------------------------------------------------------
> > BEGIN
> >     -- Declare variable
> >     DECLARE last_id INT;

> >     INSERT INTO PARTY (FNAME,LNAME) VALUES (fn,ln);
> >     VALUES(IDENTITY_VAL_LOCAL()) INTO last_id;
> >     INSERT INTO MALL (PARTY_ID,SHORT_NAME,BANK_NAME) VALUES
> (last_id,sn,bn);



> > ---snip---

> > Thank you



> > > Do a

> > >     SELECT procname, procschema, implementaion FROM SYSCAT.PROCEDURES

> > > check if your SP is there (and has the right implementation).
> > > Then call the SP with CALL <procschema>.<procname> with the values
from
> > the
> > > db catalogs - in case you cataloged it. If not the library should
match
> > the
> > > SP name (and the SP entry point should be the same name)

> > > Your procedure implemention is listed on time as
> > > /home/db2inst1/sqllib/function/SP_INS_MALL (PHP case) and on time as
> > > /home/db2inst1/sqllib/function/sp_ins_mall (CLI case) . Did you change
> > > anything there?

> > > Regards, Dirk



> > > > Hi all

> > > > I have a stored procedure that utilizes IDENTITY_VAL_LOCAL()
function
> > for
> > > > multiple table inserts...
> > > > If i execute CALL from CLP or Command center it works flawlessly.
> > > > It also works if I call it from the client machine that has the
> database
> > > > cataloged....

> > > > However on the client machine I have a web server running that uses
> PHP
> > to
> > > > produce dynamic web pages by querying database....
> > > > Trying to execute a CALL from a PHP script , however, failed with
> > message
> > > :

> > > > Warning: SQL error: [IBM][CLI Driver][DB2/6000] SQL10013N The
> specified
> > > > library "/home/db2inst1/sqllib/function/SP_INS_MALL" could not be
> > loaded.
> > > ,
> > > > SQL state S1000 in SQLExecDirect in
> /usr/local/apache/htdocs/DB2test.php
> > > on
> > > > line 24

> > > > OK so i figure out DB CLI that i binded to PHP is using CLI function
> > > > SQLExecDirect
> > > > So i looked at CLI Guide and Reference if CLI supports this Function
> > > > . In appendix I states that it does with a footnote :

> > > > 3. An X indicates that you can execute this statement using either
> > > > SQLExecDirect()or SQLPrepare()
> > > > and SQLExecute(). If there is an equivalent DB2 CLI function, the
> > function
> > > > name is listed.

> > > > Next I tried writing a small C app utilizing DB CLI that will call
my
> > proc
> > > > ... App compiled fine
> > > > but I get the SAME error running the app on server or on the client
> side

> > > > /*code i used....*/
> > > >    /* select all malls */
> > > >    printf(">>> Calling sp_ins_mall stored proc... \n");
> > > >    if (SQLExecDirect(hstmt,ins_mall,SQL_NTS) != SQL_SUCCESS)
> > > >     {
> > > >        SQLCHAR      SQLState[6];
> > > >        SQLINTEGER   NativeErrorPtr;
> > > >        SQLCHAR      MessageText[1024];

> > > >        printf(">>> ERROR while calling stored procedure %s\n",
> > ins_malls)

SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,SQLState,&NativeErrorPtr,MessageText,1

- Show quoted text -

Quote:
> > > > 024,NULL);
> > > >        printf("%s",MessageText);
> > > >        SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
> > > >        return(SQL_ERROR);
> > > >     }

> > > > ERROR PRODUCED BY SQLGetDiagRec() is :
> > > > [IBM][CLI Driver][DB2/6000] SQL10013N  The specified library
> > > > "/home/db2inst1/sqllib/function/sp_ins_mall" could not be loaded.

> > > > This is the same error as before .....
> > > > 1. Why is CALL working from CLP or command Center and not from CLI
as
> > > stated
> > > > in Manuals?
> > > > 2. HOW CAN I FIX THIS.....

> > > > Please help....

> > > > Thanks



Wed, 02 Jul 2003 08:26:14 GMT
 CALL stored_proc from CLI problem...
Amir,

I looked at your application. It seems OK to me. The two things that you
might check are:
1. You don't need the SET SCHEMA statement. Try it without it.
2. Your makefile uses gcc in some cases. Is the compiler you use one that's
officially supported for SQL procedures? This info should be in the release
notes.

Regards, Dirk


Quote:
> Dirk
> I have used in my CLI app exact same SQL statement as I did using
> interactive Command Center
> or CLP....Exact same statement with fully qualifing schema name works
> interactivelly but not from my app. I double checked that all my env vars
> are correct (DB2PATH, DB2INST...etc..etc...)  but to no avail.....Again I
> also checked SYSCAT.PROCEDURES and it is registered....it seems like CLI
> does not looks first in catalog .....

> First thing Monday when I return to work I would like to email You the :
> 1. DDL for two tables I use in SAMPLE Database
> 2. DDL for the stored proc I use
> 3. C source and Makefile of my CLI app (It works with C for AIX v 5.))

> Perhaps , You can figure out why id does not work.....?????
> Once again I appreciate your efforts...
> Amir Mistric



> > What happens if you call the SP in you CLI app with

DB2INST1.SP_INS_MALL?

- Show quoted text -

Quote:
> > The error message that you get shows that the function resolution fails
> and
> > that it tries to treat the sp name as a library. Here's the description
> from
> > the SQL Reference

> > -- snip--
> > If the library or function could not be found, the procedure-name is
used
> to
> > search the defined procedures (in SYSCAT.PROCEDURES) for a matching
> > procedure. A matching procedure is determined using the steps that
follow.
> > Find the procedures from the catalog (SYSCAT.PROCEDURES) where the
> PROCNAME
> > matches the procedure-name specified and the PROCSCHEMA is a schema name
> in
> > the SQL path (CURRENT PATH special register). If the schema name is
> > explicitly specified, the SQL path is ignored and only procedures with
the
> > specified schema name are considered.
> > Next, eliminate any of these procedures that do not have the same number
> of
> > parameters as the number of arguments specified in the CALL statement.
> > Chose the remaining procedure that is earliest in the SQL path.
> > If there are no remaining procedures after step 2, an error is returned
> > (SQLSTATE 42884).
> > -- snip--

> > So check that the names (including schema) and the parameters match.

> > Regards, Dirk



> > > Hi Dirk

> > > Sorry about the case...I was trying both ways and it does not work.
> > > The query you posted returns the following

> > > PROCNAME         PROCSCHEMA    IMPLEMENTATION
> > > ---------------        ------------------    -----------------------
> > > SP_INS_MALL    DB2INST1             P0226690!pgsjmp

> > > I Also checked the the folowing directory where DB2 puts compiled
procs
> :

> > > -=[/home/db2inst1]==>ls -la
> > sqllib/function/routine/sqlproc/SAMPLE/DB2INST1/
> > > total 34
> > > drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:02 .
> > > drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:01 ..
> > > -rwxr-xr-x   1 db2inst1 db2iadm1     9319 Jan 11 11:02 P0226690
> > > -rw-r-----   1 db2inst1 db2iadm1    13034 Jan 11 11:02 P0226690.c
> > > -rw-r-----   1 db2inst1 db2iadm1     1187 Jan 11 11:02 P0226690.log
> > > -rw-r-----   1 db2inst1 db2iadm1     9131 Jan 11 11:02 P0226690.sqc
> > > drwxrwsr-x   2 db2inst1 db2iadm1      512 Jan 11 11:02 tmp

> > > I am still not able to CALL this sp from the CLI app. It returns the
> same
> > > error...
> > > Also  here is the sp I created ...Maybe I am missing something ...Any
> > other
> > > ideas????
> > > ---snip---

> > > CREATE PROCEDURE DB2INST1.SP_INS_MALL ( IN sn varchar(30),
> > >                                         IN bn varchar(20),
> > >                                         IN fn varchar(30),
> > >                                         IN ln varchar(30) )
> > >     RESULT SETS 0
> > >     LANGUAGE SQL

> ------------------------------------------------------------------------
> > > -- SQL Stored Procedure

> ------------------------------------------------------------------------
> > > BEGIN
> > >     -- Declare variable
> > >     DECLARE last_id INT;

> > >     INSERT INTO PARTY (FNAME,LNAME) VALUES (fn,ln);
> > >     VALUES(IDENTITY_VAL_LOCAL()) INTO last_id;
> > >     INSERT INTO MALL (PARTY_ID,SHORT_NAME,BANK_NAME) VALUES
> > (last_id,sn,bn);



> > > ---snip---

> > > Thank you



> > > > Do a

> > > >     SELECT procname, procschema, implementaion FROM
SYSCAT.PROCEDURES

> > > > check if your SP is there (and has the right implementation).
> > > > Then call the SP with CALL <procschema>.<procname> with the values
> from
> > > the
> > > > db catalogs - in case you cataloged it. If not the library should
> match
> > > the
> > > > SP name (and the SP entry point should be the same name)

> > > > Your procedure implemention is listed on time as
> > > > /home/db2inst1/sqllib/function/SP_INS_MALL (PHP case) and on time as
> > > > /home/db2inst1/sqllib/function/sp_ins_mall (CLI case) . Did you
change
> > > > anything there?

> > > > Regards, Dirk



> > > > > Hi all

> > > > > I have a stored procedure that utilizes IDENTITY_VAL_LOCAL()
> function
> > > for
> > > > > multiple table inserts...
> > > > > If i execute CALL from CLP or Command center it works flawlessly.
> > > > > It also works if I call it from the client machine that has the
> > database
> > > > > cataloged....

> > > > > However on the client machine I have a web server running that
uses
> > PHP
> > > to
> > > > > produce dynamic web pages by querying database....
> > > > > Trying to execute a CALL from a PHP script , however, failed with
> > > message
> > > > :

> > > > > Warning: SQL error: [IBM][CLI Driver][DB2/6000] SQL10013N The
> > specified
> > > > > library "/home/db2inst1/sqllib/function/SP_INS_MALL" could not be
> > > loaded.
> > > > ,
> > > > > SQL state S1000 in SQLExecDirect in
> > /usr/local/apache/htdocs/DB2test.php
> > > > on
> > > > > line 24

> > > > > OK so i figure out DB CLI that i binded to PHP is using CLI
function
> > > > > SQLExecDirect
> > > > > So i looked at CLI Guide and Reference if CLI supports this
Function
> > > > > . In appendix I states that it does with a footnote :

> > > > > 3. An X indicates that you can execute this statement using either
> > > > > SQLExecDirect()or SQLPrepare()
> > > > > and SQLExecute(). If there is an equivalent DB2 CLI function, the
> > > function
> > > > > name is listed.

> > > > > Next I tried writing a small C app utilizing DB CLI that will call
> my
> > > proc
> > > > > ... App compiled fine
> > > > > but I get the SAME error running the app on server or on the
client
> > side

> > > > > /*code i used....*/
> > > > >    /* select all malls */
> > > > >    printf(">>> Calling sp_ins_mall stored proc... \n");
> > > > >    if (SQLExecDirect(hstmt,ins_mall,SQL_NTS) != SQL_SUCCESS)
> > > > >     {
> > > > >        SQLCHAR      SQLState[6];
> > > > >        SQLINTEGER   NativeErrorPtr;
> > > > >        SQLCHAR      MessageText[1024];

> > > > >        printf(">>> ERROR while calling stored procedure %s\n",
> > > ins_malls)

SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,SQLState,&NativeErrorPtr,MessageText,1

- Show quoted text -

Quote:
> > > > > 024,NULL);
> > > > >        printf("%s",MessageText);
> > > > >        SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
> > > > >        return(SQL_ERROR);
> > > > >     }

> > > > > ERROR PRODUCED BY SQLGetDiagRec() is :
> > > > > [IBM][CLI Driver][DB2/6000] SQL10013N  The specified library
> > > > > "/home/db2inst1/sqllib/function/sp_ins_mall" could not be loaded.

> > > > > This is the same error as before .....
> > > > > 1. Why is CALL working from CLP or command Center and not from CLI
> as
> > > > stated
> > > > > in Manuals?
> > > > > 2. HOW CAN I FIX THIS.....

> > > > > Please help....

> > > > > Thanks



Sat, 05 Jul 2003 02:25:27 GMT
 CALL stored_proc from CLI problem...
Hi Dirk

I commented SET SCHEMA statements and recompiled.
However I still get the same error...For some reason it is not finding sp in
the catalog or it is not looking in the catalog.....

I use C for AIX v 5.0...the gcc entry is commented in Makefile.
Here is the output....


-=[/home/db2inst1/DDL_scripts]==>make

    /usr/vac/bin/cc -I/usr/lpp/db2_07_01/include -L/usr/lpp/db2_07_01/lib -c
main.c

    /usr/vac/bin/cc -I/usr/lpp/db2_07_01/include -L/usr/lpp/db2_07_01/lib -o
main main.o -ldb2

-=[/home/db2inst1/DDL_scripts]==>main

Quote:
>>> Successful Connect to SAMPLE
>>> Calling sp_ins_mall stored proc...
>>> ERROR while calling stored procedure CALL

DB2INST1.SP_INS_MALL('TSTMALL','First Test Bank','John','Doe')
[IBM][CLI Driver][DB2/6000] SQL10013N  The specified library
"/home/db2inst1/sqllib/function/DB2INST1.SP_INS_MALL" could not be loaded.

I have tried everything at this point...You said app looks OK. Have you had
a chance to run it actually?Did it execute on your side?

Thanks
Amir


Quote:
> Amir,

> I looked at your application. It seems OK to me. The two things that you
> might check are:
> 1. You don't need the SET SCHEMA statement. Try it without it.
> 2. Your makefile uses gcc in some cases. Is the compiler you use one
that's
> officially supported for SQL procedures? This info should be in the
release
> notes.

> Regards, Dirk



> > Dirk
> > I have used in my CLI app exact same SQL statement as I did using
> > interactive Command Center
> > or CLP....Exact same statement with fully qualifing schema name works
> > interactivelly but not from my app. I double checked that all my env
vars
> > are correct (DB2PATH, DB2INST...etc..etc...)  but to no avail.....Again
I
> > also checked SYSCAT.PROCEDURES and it is registered....it seems like CLI
> > does not looks first in catalog .....

> > First thing Monday when I return to work I would like to email You the :
> > 1. DDL for two tables I use in SAMPLE Database
> > 2. DDL for the stored proc I use
> > 3. C source and Makefile of my CLI app (It works with C for AIX v 5.))

> > Perhaps , You can figure out why id does not work.....?????
> > Once again I appreciate your efforts...
> > Amir Mistric



> > > What happens if you call the SP in you CLI app with
> DB2INST1.SP_INS_MALL?
> > > The error message that you get shows that the function resolution
fails
> > and
> > > that it tries to treat the sp name as a library. Here's the
description
> > from
> > > the SQL Reference

> > > -- snip--
> > > If the library or function could not be found, the procedure-name is
> used
> > to
> > > search the defined procedures (in SYSCAT.PROCEDURES) for a matching
> > > procedure. A matching procedure is determined using the steps that
> follow.
> > > Find the procedures from the catalog (SYSCAT.PROCEDURES) where the
> > PROCNAME
> > > matches the procedure-name specified and the PROCSCHEMA is a schema
name
> > in
> > > the SQL path (CURRENT PATH special register). If the schema name is
> > > explicitly specified, the SQL path is ignored and only procedures with
> the
> > > specified schema name are considered.
> > > Next, eliminate any of these procedures that do not have the same
number
> > of
> > > parameters as the number of arguments specified in the CALL statement.
> > > Chose the remaining procedure that is earliest in the SQL path.
> > > If there are no remaining procedures after step 2, an error is
returned
> > > (SQLSTATE 42884).
> > > -- snip--

> > > So check that the names (including schema) and the parameters match.

> > > Regards, Dirk



> > > > Hi Dirk

> > > > Sorry about the case...I was trying both ways and it does not work.
> > > > The query you posted returns the following

> > > > PROCNAME         PROCSCHEMA    IMPLEMENTATION
> > > > ---------------        ------------------    -----------------------
> > > > SP_INS_MALL    DB2INST1             P0226690!pgsjmp

> > > > I Also checked the the folowing directory where DB2 puts compiled
> procs
> > :

> > > > -=[/home/db2inst1]==>ls -la
> > > sqllib/function/routine/sqlproc/SAMPLE/DB2INST1/
> > > > total 34
> > > > drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:02 .
> > > > drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:01 ..
> > > > -rwxr-xr-x   1 db2inst1 db2iadm1     9319 Jan 11 11:02 P0226690
> > > > -rw-r-----   1 db2inst1 db2iadm1    13034 Jan 11 11:02 P0226690.c
> > > > -rw-r-----   1 db2inst1 db2iadm1     1187 Jan 11 11:02 P0226690.log
> > > > -rw-r-----   1 db2inst1 db2iadm1     9131 Jan 11 11:02 P0226690.sqc
> > > > drwxrwsr-x   2 db2inst1 db2iadm1      512 Jan 11 11:02 tmp

> > > > I am still not able to CALL this sp from the CLI app. It returns the
> > same
> > > > error...
> > > > Also  here is the sp I created ...Maybe I am missing something
...Any
> > > other
> > > > ideas????
> > > > ---snip---

> > > > CREATE PROCEDURE DB2INST1.SP_INS_MALL ( IN sn varchar(30),
> > > >                                         IN bn varchar(20),
> > > >                                         IN fn varchar(30),
> > > >                                         IN ln varchar(30) )
> > > >     RESULT SETS 0
> > > >     LANGUAGE SQL

> > ------------------------------------------------------------------------
> > > > -- SQL Stored Procedure

> > ------------------------------------------------------------------------
> > > > BEGIN
> > > >     -- Declare variable
> > > >     DECLARE last_id INT;

> > > >     INSERT INTO PARTY (FNAME,LNAME) VALUES (fn,ln);
> > > >     VALUES(IDENTITY_VAL_LOCAL()) INTO last_id;
> > > >     INSERT INTO MALL (PARTY_ID,SHORT_NAME,BANK_NAME) VALUES
> > > (last_id,sn,bn);



> > > > ---snip---

> > > > Thank you



> > > > > Do a

> > > > >     SELECT procname, procschema, implementaion FROM
> SYSCAT.PROCEDURES

> > > > > check if your SP is there (and has the right implementation).
> > > > > Then call the SP with CALL <procschema>.<procname> with the values
> > from
> > > > the
> > > > > db catalogs - in case you cataloged it. If not the library should
> > match
> > > > the
> > > > > SP name (and the SP entry point should be the same name)

> > > > > Your procedure implemention is listed on time as
> > > > > /home/db2inst1/sqllib/function/SP_INS_MALL (PHP case) and on time
as
> > > > > /home/db2inst1/sqllib/function/sp_ins_mall (CLI case) . Did you
> change
> > > > > anything there?

> > > > > Regards, Dirk



> > > > > > Hi all

> > > > > > I have a stored procedure that utilizes IDENTITY_VAL_LOCAL()
> > function
> > > > for
> > > > > > multiple table inserts...
> > > > > > If i execute CALL from CLP or Command center it works
flawlessly.
> > > > > > It also works if I call it from the client machine that has the
> > > database
> > > > > > cataloged....

> > > > > > However on the client machine I have a web server running that
> uses
> > > PHP
> > > > to
> > > > > > produce dynamic web pages by querying database....
> > > > > > Trying to execute a CALL from a PHP script , however, failed
with
> > > > message
> > > > > :

> > > > > > Warning: SQL error: [IBM][CLI Driver][DB2/6000] SQL10013N The
> > > specified
> > > > > > library "/home/db2inst1/sqllib/function/SP_INS_MALL" could not
be
> > > > loaded.
> > > > > ,
> > > > > > SQL state S1000 in SQLExecDirect in
> > > /usr/local/apache/htdocs/DB2test.php
> > > > > on
> > > > > > line 24

> > > > > > OK so i figure out DB CLI that i binded to PHP is using CLI
> function
> > > > > > SQLExecDirect
> > > > > > So i looked at CLI Guide and Reference if CLI supports this
> Function
> > > > > > . In appendix I states that it does with a footnote :

> > > > > > 3. An X indicates that you can execute this statement using
either
> > > > > > SQLExecDirect()or SQLPrepare()
> > > > > > and SQLExecute(). If there is an equivalent DB2 CLI function,
the
> > > > function
> > > > > > name is listed.

> > > > > > Next I tried writing a small C app utilizing DB CLI that will
call
> > my
> > > > proc
> > > > > > ... App compiled fine
> > > > > > but I get the SAME error running the app on server or on the
> client
> > > side

> > > > > > /*code i used....*/
> > > > > >    /* select all malls */
> > > > > >    printf(">>> Calling sp_ins_mall stored proc... \n");
> > > > > >    if (SQLExecDirect(hstmt,ins_mall,SQL_NTS) != SQL_SUCCESS)
> > > > > >     {
> > > > > >        SQLCHAR      SQLState[6];
> > > > > >        SQLINTEGER   NativeErrorPtr;
> > > > > >        SQLCHAR      MessageText[1024];

> > > > > >        printf(">>> ERROR while calling stored procedure %s\n",
> > > > ins_malls)

SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,SQLState,&NativeErrorPtr,MessageText,1

- Show quoted text -

Quote:
> > > > > > 024,NULL);
> > > > > >        printf("%s",MessageText);
> > > > > >        SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
> > > > > >        return(SQL_ERROR);
> > > > > >     }

> > > > > > ERROR PRODUCED BY

...

read more »



Sat, 05 Jul 2003 02:46:33 GMT
 CALL stored_proc from CLI problem...
Amir,
I can reproduce your problem. Please contact DB2 service so that they can
determine if it's a DB2 bug or a mistake on your side that we've missed.

Regards, Dirk


Quote:
> Hi Dirk

> I commented SET SCHEMA statements and recompiled.
> However I still get the same error...For some reason it is not finding sp
in
> the catalog or it is not looking in the catalog.....

> I use C for AIX v 5.0...the gcc entry is commented in Makefile.
> Here is the output....


> -=[/home/db2inst1/DDL_scripts]==>make

    /usr/vac/bin/cc -I/usr/lpp/db2_07_01/include -L/usr/lpp/db2_07_01/lib -c
Quote:
> main.c

    /usr/vac/bin/cc -I/usr/lpp/db2_07_01/include -L/usr/lpp/db2_07_01/lib -o

- Show quoted text -

Quote:
> main main.o -ldb2

> -=[/home/db2inst1/DDL_scripts]==>main
> >>> Successful Connect to SAMPLE
> >>> Calling sp_ins_mall stored proc...
> >>> ERROR while calling stored procedure CALL
> DB2INST1.SP_INS_MALL('TSTMALL','First Test Bank','John','Doe')
> [IBM][CLI Driver][DB2/6000] SQL10013N  The specified library
> "/home/db2inst1/sqllib/function/DB2INST1.SP_INS_MALL" could not be loaded.

> I have tried everything at this point...You said app looks OK. Have you
had
> a chance to run it actually?Did it execute on your side?

> Thanks
> Amir



> > Amir,

> > I looked at your application. It seems OK to me. The two things that you
> > might check are:
> > 1. You don't need the SET SCHEMA statement. Try it without it.
> > 2. Your makefile uses gcc in some cases. Is the compiler you use one
> that's
> > officially supported for SQL procedures? This info should be in the
> release
> > notes.

> > Regards, Dirk



> > > Dirk
> > > I have used in my CLI app exact same SQL statement as I did using
> > > interactive Command Center
> > > or CLP....Exact same statement with fully qualifing schema name works
> > > interactivelly but not from my app. I double checked that all my env
> vars
> > > are correct (DB2PATH, DB2INST...etc..etc...)  but to no
avail.....Again
> I
> > > also checked SYSCAT.PROCEDURES and it is registered....it seems like
CLI
> > > does not looks first in catalog .....

> > > First thing Monday when I return to work I would like to email You the
:
> > > 1. DDL for two tables I use in SAMPLE Database
> > > 2. DDL for the stored proc I use
> > > 3. C source and Makefile of my CLI app (It works with C for AIX v 5.))

> > > Perhaps , You can figure out why id does not work.....?????
> > > Once again I appreciate your efforts...
> > > Amir Mistric



> > > > What happens if you call the SP in you CLI app with
> > DB2INST1.SP_INS_MALL?
> > > > The error message that you get shows that the function resolution
> fails
> > > and
> > > > that it tries to treat the sp name as a library. Here's the
> description
> > > from
> > > > the SQL Reference

> > > > -- snip--
> > > > If the library or function could not be found, the procedure-name is
> > used
> > > to
> > > > search the defined procedures (in SYSCAT.PROCEDURES) for a matching
> > > > procedure. A matching procedure is determined using the steps that
> > follow.
> > > > Find the procedures from the catalog (SYSCAT.PROCEDURES) where the
> > > PROCNAME
> > > > matches the procedure-name specified and the PROCSCHEMA is a schema
> name
> > > in
> > > > the SQL path (CURRENT PATH special register). If the schema name is
> > > > explicitly specified, the SQL path is ignored and only procedures
with
> > the
> > > > specified schema name are considered.
> > > > Next, eliminate any of these procedures that do not have the same
> number
> > > of
> > > > parameters as the number of arguments specified in the CALL
statement.
> > > > Chose the remaining procedure that is earliest in the SQL path.
> > > > If there are no remaining procedures after step 2, an error is
> returned
> > > > (SQLSTATE 42884).
> > > > -- snip--

> > > > So check that the names (including schema) and the parameters match.

> > > > Regards, Dirk



> > > > > Hi Dirk

> > > > > Sorry about the case...I was trying both ways and it does not
work.
> > > > > The query you posted returns the following

> > > > > PROCNAME         PROCSCHEMA    IMPLEMENTATION

--------------        ------------------    -----------------------

- Show quoted text -

Quote:
> > > > > SP_INS_MALL    DB2INST1             P0226690!pgsjmp

> > > > > I Also checked the the folowing directory where DB2 puts compiled
> > procs
> > > :

> > > > > -=[/home/db2inst1]==>ls -la
> > > > sqllib/function/routine/sqlproc/SAMPLE/DB2INST1/
> > > > > total 34
> > > > > drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:02 .
> > > > > drwxrwsr-x   3 db2inst1 db2iadm1      512 Jan 11 11:01 ..
> > > > > -rwxr-xr-x   1 db2inst1 db2iadm1     9319 Jan 11 11:02 P0226690
> > > > > -rw-r-----   1 db2inst1 db2iadm1    13034 Jan 11 11:02 P0226690.c
> > > > > -rw-r-----   1 db2inst1 db2iadm1     1187 Jan 11 11:02
P0226690.log
> > > > > -rw-r-----   1 db2inst1 db2iadm1     9131 Jan 11 11:02
P0226690.sqc
> > > > > drwxrwsr-x   2 db2inst1 db2iadm1      512 Jan 11 11:02 tmp

> > > > > I am still not able to CALL this sp from the CLI app. It returns
the
> > > same
> > > > > error...
> > > > > Also  here is the sp I created ...Maybe I am missing something
> ...Any
> > > > other
> > > > > ideas????
> > > > > ---snip---

> > > > > CREATE PROCEDURE DB2INST1.SP_INS_MALL ( IN sn varchar(30),
> > > > >                                         IN bn varchar(20),
> > > > >                                         IN fn varchar(30),
> > > > >                                         IN ln varchar(30) )
> > > > >     RESULT SETS 0
> > > > >     LANGUAGE SQL

> ------------------------------------------------------------------------
> > > > > -- SQL Stored Procedure

> ------------------------------------------------------------------------
> > > > > BEGIN
> > > > >     -- Declare variable
> > > > >     DECLARE last_id INT;

> > > > >     INSERT INTO PARTY (FNAME,LNAME) VALUES (fn,ln);
> > > > >     VALUES(IDENTITY_VAL_LOCAL()) INTO last_id;
> > > > >     INSERT INTO MALL (PARTY_ID,SHORT_NAME,BANK_NAME) VALUES
> > > > (last_id,sn,bn);



> > > > > ---snip---

> > > > > Thank you



> > > > > > Do a

> > > > > >     SELECT procname, procschema, implementaion FROM
> > SYSCAT.PROCEDURES

> > > > > > check if your SP is there (and has the right implementation).
> > > > > > Then call the SP with CALL <procschema>.<procname> with the
values
> > > from
> > > > > the
> > > > > > db catalogs - in case you cataloged it. If not the library
should
> > > match
> > > > > the
> > > > > > SP name (and the SP entry point should be the same name)

> > > > > > Your procedure implemention is listed on time as
> > > > > > /home/db2inst1/sqllib/function/SP_INS_MALL (PHP case) and on
time
> as
> > > > > > /home/db2inst1/sqllib/function/sp_ins_mall (CLI case) . Did you
> > change
> > > > > > anything there?

> > > > > > Regards, Dirk



> > > > > > > Hi all

> > > > > > > I have a stored procedure that utilizes IDENTITY_VAL_LOCAL()
> > > function
> > > > > for
> > > > > > > multiple table inserts...
> > > > > > > If i execute CALL from CLP or Command center it works
> flawlessly.
> > > > > > > It also works if I call it from the client machine that has
the
> > > > database
> > > > > > > cataloged....

> > > > > > > However on the client machine I have a web server running that
> > uses
> > > > PHP
> > > > > to
> > > > > > > produce dynamic web pages by querying database....
> > > > > > > Trying to execute a CALL from a PHP script , however, failed
> with
> > > > > message
> > > > > > :

> > > > > > > Warning: SQL error: [IBM][CLI Driver][DB2/6000] SQL10013N The
> > > > specified
> > > > > > > library "/home/db2inst1/sqllib/function/SP_INS_MALL" could not
> be
> > > > > loaded.
> > > > > > ,
> > > > > > > SQL state S1000 in SQLExecDirect in
> > > > /usr/local/apache/htdocs/DB2test.php
> > > > > > on
> > > > > > > line 24

> > > > > > > OK so i figure out DB CLI that i binded to PHP is using CLI
> > function
> > > > > > > SQLExecDirect
> > > > > > > So i looked at CLI Guide and Reference if CLI supports this
> > Function
> > > > > > > . In appendix I states that it does with a footnote :

> > > > > > > 3. An X indicates that you can execute this statement using
> either
> > > > > > > SQLExecDirect()or SQLPrepare()
> > > > > > > and SQLExecute(). If there is an equivalent DB2 CLI function,
> the
> > > > > function
> > > > > > > name is listed.

> > > > > > > Next I tried writing a small C app utilizing DB CLI that will
> call
> > > my
> > > > > proc
> > > > > > > ... App compiled fine
> > > > > > > but I get the SAME error running the app on server or on the
> > client
> > > > side

> > > > > > > /*code i

...

read more »



Sun, 06 Jul 2003 02:51:43 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Problem executing stored_proc

2. CLI call sequence

3. cann't run stored_proc on a transfered DB

4. Entended SP calls DLL which calls MSMQ -- problem

5. Called and Calling store procedure problem !!

6. Problem with ReportSmith and Informix CLI/ODBC

7. Problem with php3+Informix CLI+Apache

8. Informix CLI 2.0 : problem with ODBC driver using MS-ACCESS

9. Informix CLI 2.0 : problem with ODBC driver using MS-ACCESS and MS-QUERY

10. Informix CLI 2.0 : problem with ODBC driver using MS-ACCESS and MS-QUERY

11. Informix CLI lock mode problem

12. Problems with password in CLI 3.31 CSDK 3.40 TC1 NT


 
Powered by phpBB® Forum Software