C stored Proc problem on AIX

Nyul

Gurus,

I have a verb big problem which I'm unable to explain.
We have a DB2 V6.1.0 on AIX 4.3
I want to make a C stored procedure which at the end will be called by a PHP
script.
The development server was an UDB V6.1.0 on W2K.
Everything went well. I was able to call the stored proc from C and from
Delphi.

After we have installed the proc on the production machine.
Compiled, linked well after copied to the right place SQLLIB/function

Declared the proc:
CREATE PROCEDURE DB2NC.S_GETPRODUCTLIST
(IN BS_BSREF INTEGER,
IN DATEPARAM DATE
)
DYNAMIC RESULT SETS 1
EXTERNAL NAME 'sbprogs1!S_GetProductList'
LANGUAGE C
PARAMETER STYLE DB2SQL
DETERMINISTIC
FENCED
NO DBINFO
CALLED ON NULL INPUT
PROGRAM TYPE SUB

Here is the C header:
SQL_API_RC SQL_API_FN S_GetProductList(SQLUDF_INTEGER *BS_BSREF,

SQLUDF_CHAR DateParam1[11],

SQLUDF_NULLIND *BS_BSREFNullInd,

SQLUDF_NULLIND *DateParam1NullInd,

SQLUDF_TRAIL_ARGS) ;

From the PHP we have got this error message:
Warning: odbc_execute(): SQL error: [IBM][CLI Driver][DB2/6000] SQL10010N
The specified library, "sbprogs1", was loaded, but the function
"S_GetProductList" could not be executed. , SQL state S1000 in SQLExecute

If after that I call the same proc from a C client prog I have this error
message:
SQLSTATE: HY010
Native Error Code: -99999
[IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010
What is wrong?
How can I get the proc running from a C or a PHP code?
What is the difference between the PARAMETER STYLE GENERAL AND DB2SQL?

Thank you for help. I'm really in a big trouble because the SQL code is very
complicated and it could be done only in a stored proc.

Gabor

Nov 12 '05 #1

Sean McKeough

Note that v6 has been out of service for a long time, so you probably
won't get too many suggestions on this...

The second failure looks like a coding problem in your CLI application.

The PHP failure could be a few things...did you declare your function as
'extern C'? If not, the symbol may be getting mangled by the C++
compiler. To check, you can dump the symbols in your library.

Nyul wrote:

Gurus,

I have a verb big problem which I'm unable to explain.
We have a DB2 V6.1.0 on AIX 4.3
I want to make a C stored procedure which at the end will be called by a PHP
script.
The development server was an UDB V6.1.0 on W2K.
Everything went well. I was able to call the stored proc from C and from
Delphi.

After we have installed the proc on the production machine.
Compiled, linked well after copied to the right place SQLLIB/function

Declared the proc:
CREATE PROCEDURE DB2NC.S_GETPRODUCTLIST
(IN BS_BSREF INTEGER,
IN DATEPARAM DATE
)
DYNAMIC RESULT SETS 1
EXTERNAL NAME 'sbprogs1!S_GetProductList'
LANGUAGE C
PARAMETER STYLE DB2SQL
DETERMINISTIC
FENCED
NO DBINFO
CALLED ON NULL INPUT
PROGRAM TYPE SUB

Here is the C header:
SQL_API_RC SQL_API_FN S_GetProductList(SQLUDF_INTEGER *BS_BSREF,

SQLUDF_CHAR DateParam1[11],

SQLUDF_NULLIND *BS_BSREFNullInd,

SQLUDF_NULLIND *DateParam1NullInd,

SQLUDF_TRAIL_ARGS) ;

From the PHP we have got this error message:
Warning: odbc_execute(): SQL error: [IBM][CLI Driver][DB2/6000] SQL10010N
The specified library, "sbprogs1", was loaded, but the function
"S_GetProductList" could not be executed. , SQL state S1000 in SQLExecute

If after that I call the same proc from a C client prog I have this error
message:
SQLSTATE: HY010
Native Error Code: -99999
[IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010
What is wrong?
How can I get the proc running from a C or a PHP code?
What is the difference between the PARAMETER STYLE GENERAL AND DB2SQL?

Thank you for help. I'm really in a big trouble because the SQL code is very
complicated and it could be done only in a stored proc.

Gabor

Nov 12 '05 #2

Nyul

Sean,

Thanks for reply.
coming back to your suggestions:
- I've checked the internet and I've seen that both of the error messages
are appearing on DB2 V7.x too. The problem is that non of thos messages has
been succesfully solved. At least there was no answer on.
- If it would be a coding error why is it running without problem on my W2K
test machine?
- the stored procedure is written in C not in C++ so no extern "C" is
required.

I really suspect a kind of declaration or parameter problem if not a
configureation parameter setting problem.
Do you know if it exists any DBM CFG which doesn't allow stored procedures
to run?

bye

Gabor

"Sean McKeough" <mc******@nospam.ca.ibm.com> az alábbiakat írta a következo
üzenetben news:bu**********@hanover.torolab.ibm.com...

Note that v6 has been out of service for a long time, so you probably
won't get too many suggestions on this...

The second failure looks like a coding problem in your CLI application.

The PHP failure could be a few things...did you declare your function as
'extern C'? If not, the symbol may be getting mangled by the C++
compiler. To check, you can dump the symbols in your library.

Nyul wrote:

Gurus,

I have a verb big problem which I'm unable to explain.
We have a DB2 V6.1.0 on AIX 4.3
I want to make a C stored procedure which at the end will be called by a PHP script.
The development server was an UDB V6.1.0 on W2K.
Everything went well. I was able to call the stored proc from C and from
Delphi.

After we have installed the proc on the production machine.
Compiled, linked well after copied to the right place SQLLIB/function

Declared the proc:
CREATE PROCEDURE DB2NC.S_GETPRODUCTLIST
(IN BS_BSREF INTEGER,
IN DATEPARAM DATE
)
DYNAMIC RESULT SETS 1
EXTERNAL NAME 'sbprogs1!S_GetProductList'
LANGUAGE C
PARAMETER STYLE DB2SQL
DETERMINISTIC
FENCED
NO DBINFO
CALLED ON NULL INPUT
PROGRAM TYPE SUB

Here is the C header:
SQL_API_RC SQL_API_FN S_GetProductList(SQLUDF_INTEGER *BS_BSREF,

SQLUDF_CHAR DateParam1[11],

SQLUDF_NULLIND *BS_BSREFNullInd,

SQLUDF_NULLIND *DateParam1NullInd,

SQLUDF_TRAIL_ARGS) ;

From the PHP we have got this error message:
Warning: odbc_execute(): SQL error: [IBM][CLI Driver][DB2/6000] SQL10010N The specified library, "sbprogs1", was loaded, but the function
"S_GetProductList" could not be executed. , SQL state S1000 in SQLExecute
If after that I call the same proc from a C client prog I have this error message:
SQLSTATE: HY010
Native Error Code: -99999
[IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010
What is wrong?
How can I get the proc running from a C or a PHP code?
What is the difference between the PARAMETER STYLE GENERAL AND DB2SQL?

Thank you for help. I'm really in a big trouble because the SQL code is very complicated and it could be done only in a stored proc.

Gabor

Nov 12 '05 #3

Knut Stolze

Nyul <ny***@largon.hu> wrote:

- the stored procedure is written in C not in C++ so no extern "C" is
required.

The question is here which compiler you used. Nearly all C code is also
valid C++ code. And if you use a C++ compiler, you can get C++ name
mangling.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Nov 12 '05 #4

Mike

"Nyul" <ny***@largon.hu> wrote in message news:<qF*********************@news.chello.at>...

<snip>

We have a DB2 V6.1.0 on AIX 4.3
The development server was an UDB V6.1.0 on W2K. Warning: odbc_execute(): SQL error: [IBM][CLI Driver][DB2/6000] SQL10010N
The specified library, "sbprogs1", was loaded, but the function
"S_GetProductList" could not be executed. , SQL state S1000 in SQLExecute

<snip>
You say it Works OK in Windows , but fails on AIX 4.3.

What were the command-line options/switches that you gave
to the "ld" program (or "cc" ) on AIX ?

Did you verify that the executable really contains an
exported symbol identical to what you expect ?
(On AIX, Use commands "dump" (with options) and "nm" (with options)
to help in this area).

If you cannot get it to work with *your* stored procedure
on AIX, then verify that you can get the IBM supplied sample
stored procedures to work on AIX (with the sample database).

If you still cannot proceed, use DIAGLEVEL 4, and retry, then
check the most recent messages in db2diag.log to see if it
gives any more details. But in my opinion it seems likely
to be a build problem.

Good luck.

Nov 12 '05 #5