CLI0119E from "call sqlj_install_jar()" in PHP

[Larry Menard]

Test code:

$dbconn = odbc_connect($dbname, $username, $password);
$path = "C:\Temp\myJar.jar";
$statement = "CALL SQLJ.INSTALL_JAR('file://$path', 'myJarId')";
$result = odbc_exec($dbconn, $statement);

Result:

PHP Warning: odbc_exec(): SQL error: [IBM][CLI Driver] CLI0119E Unexpected system failure.
SQLSTATE=58004, SQL state 58004 in SQLExecDirect in C:\temp\CLI0119E.php on line 17

Warning: odbc_exec(): SQL error: [IBM][CLI Driver] CLI0119E Unexpected system failure.
SQLSTATE=58004, SQL state 58004 in SQLExecDirect in C:\temp\CLI0119E.php on line 17

It doesn't seem to matter what the URL is, even if it's not correct... the error never changes, so I presume it's dying before it even gets that far.

2005-11-25-11.09.14.856000-300 I21235H391 LEVEL: Info
PID : 1328 TID : 3168 PROC : php.exe
INSTANCE: DB2 NODE : 000
APPID : *LOCAL.DB2.051125160914
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
MESSAGE : CCI Error:
DATA #1 : Hexdump, 4 bytes
0x0012F86C : 4700 0000 G...

2005-11-25-11.09.14.856000-300 I21628H377 LEVEL: Info
PID : 1328 TID : 3168 PROC : php.exe
INSTANCE: DB2 NODE : 000
APPID : *LOCAL.DB2.051125160914
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
RETCODE : ZRC=0x8136001C=-2127167460=SQLZ_RC_NO_CONNECTION, SQLT_SQLJC
"No connection"

I gather from Dan's article that there are known issues calling SPs with OUT or INOUT parms from PHP, but this doesn't seem to be the case here.

Anyone recognize this? Any suggestions are appreciated.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

[Rhino]

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:9Y******************************@rogers.com.. .
Test code:

$dbconn = odbc_connect($dbname, $username, $password);
$path = "C:\Temp\myJar.jar";
$statement = "CALL SQLJ.INSTALL_JAR('file://$path', 'myJarId')";
$result = odbc_exec($dbconn, $statement);

Result:

PHP Warning: odbc_exec(): SQL error: [IBM][CLI Driver] CLI0119E Unexpected system failure.
SQLSTATE=58004, SQL state 58004 in SQLExecDirect in C:\temp\CLI0119E.php on line 17

Warning: odbc_exec(): SQL error: [IBM][CLI Driver] CLI0119E Unexpected system failure.
SQLSTATE=58004, SQL state 58004 in SQLExecDirect in C:\temp\CLI0119E.php on line 17

It doesn't seem to matter what the URL is, even if it's not correct... the error never changes, so I presume it's dying before it even gets that far.

2005-11-25-11.09.14.856000-300 I21235H391 LEVEL: Info
PID : 1328 TID : 3168 PROC : php.exe
INSTANCE: DB2 NODE : 000
APPID : *LOCAL.DB2.051125160914
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
MESSAGE : CCI Error:
DATA #1 : Hexdump, 4 bytes
0x0012F86C : 4700 0000 G...

2005-11-25-11.09.14.856000-300 I21628H377 LEVEL: Info
PID : 1328 TID : 3168 PROC : php.exe
INSTANCE: DB2 NODE : 000
APPID : *LOCAL.DB2.051125160914
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
RETCODE : ZRC=0x8136001C=-2127167460=SQLZ_RC_NO_CONNECTION, SQLT_SQLJC
"No connection"

I gather from Dan's article that there are known issues calling SPs with OUT or INOUT parms from PHP, but this doesn't seem to be the case here.

Anyone recognize this? Any suggestions are appreciated.

I hate those CLIxxxx errors; they always seem to be extremely vague, like the one you're experiencing, and often don't suggest any path whatsoever to resolve them. I had a CLI0125E once that kept me hopping for hours; it eventually turned out to be something fairly simple like a version issue that was being masked by the CLI00125E.

Out of sympathy with your situation, I just did a bit of Googling on CLI0119E - both web and groups - and didn't find anything very promising.

One odd thing strikes me looking at your code though. I don't know PHP so maybe this makes perfect sense but I though jars only got installed for Java code? Why woulod PHP code need a jar installed?

Rhino

[Larry Menard]

Hi, Rhino.

The PHP application is trying to catalog a java UDF that it will call later on.

FYI, I also tried using Dan Scott's 'ibm_db2' PHP extension,. and at his suggestion I added a print db2_stmt_errormsg() call, which yielded:

PHP Warning: db2_execute(): Describe Param Failed in C:\temp\test-ibm_db2.php on line 22

Warning: db2_execute(): Describe Param Failed in C:\temp\test-ibm_db2.php on line 22
PHP Warning: db2_execute(): Binding Error in C:\temp\test-ibm_db2.php on line 22

Warning: db2_execute(): Binding Error in C:\temp\test-ibm_db2.php on line 22
[IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00

So even his 'ibm_db2' driver has problems with it. Starting to smell like the problem is with CLI.

I'm able to run other SPs, so there's something specific about that SP in particular.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message news:HH********************@news20.bellglobal.com. ..

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:9Y******************************@rogers.com.. .
Test code:

$dbconn = odbc_connect($dbname, $username, $password);
$path = "C:\Temp\myJar.jar";
$statement = "CALL SQLJ.INSTALL_JAR('file://$path', 'myJarId')";
$result = odbc_exec($dbconn, $statement);

Result:

PHP Warning: odbc_exec(): SQL error: [IBM][CLI Driver] CLI0119E Unexpected system failure.
SQLSTATE=58004, SQL state 58004 in SQLExecDirect in C:\temp\CLI0119E.php on line 17

Warning: odbc_exec(): SQL error: [IBM][CLI Driver] CLI0119E Unexpected system failure.
SQLSTATE=58004, SQL state 58004 in SQLExecDirect in C:\temp\CLI0119E.php on line 17

It doesn't seem to matter what the URL is, even if it's not correct... the error never changes, so I presume it's dying before it even gets that far.

2005-11-25-11.09.14.856000-300 I21235H391 LEVEL: Info
PID : 1328 TID : 3168 PROC : php.exe
INSTANCE: DB2 NODE : 000
APPID : *LOCAL.DB2.051125160914
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
MESSAGE : CCI Error:
DATA #1 : Hexdump, 4 bytes
0x0012F86C : 4700 0000 G...

2005-11-25-11.09.14.856000-300 I21628H377 LEVEL: Info
PID : 1328 TID : 3168 PROC : php.exe
INSTANCE: DB2 NODE : 000
APPID : *LOCAL.DB2.051125160914
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
RETCODE : ZRC=0x8136001C=-2127167460=SQLZ_RC_NO_CONNECTION, SQLT_SQLJC
"No connection"

I gather from Dan's article that there are known issues calling SPs with OUT or INOUT parms from PHP, but this doesn't seem to be the case here.

Anyone recognize this? Any suggestions are appreciated.

I hate those CLIxxxx errors; they always seem to be extremely vague, like the one you're experiencing, and often don't suggest any path whatsoever to resolve them. I had a CLI0125E once that kept me hopping for hours; it eventually turned out to be something fairly simple like a version issue that was being masked by the CLI00125E.

Out of sympathy with your situation, I just did a bit of Googling on CLI0119E - both web and groups - and didn't find anything very promising.

One odd thing strikes me looking at your code though. I don't know PHP so maybe this makes perfect sense but I though jars only got installed for Java code? Why woulod PHP code need a jar installed?

Rhino

[Larry Menard]

I just took the CLI sample program "dbconn.c" and added a call to 'sqlj.install_jar()', and lo and behold... CLI0119E.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:kd********************@rogers.com...
Hi, Rhino.

The PHP application is trying to catalog a java UDF that it will call later on.

FYI, I also tried using Dan Scott's 'ibm_db2' PHP extension,. and at his suggestion I added a print db2_stmt_errormsg() call, which yielded:

PHP Warning: db2_execute(): Describe Param Failed in C:\temp\test-ibm_db2.php on line 22

Warning: db2_execute(): Describe Param Failed in C:\temp\test-ibm_db2.php on line 22
PHP Warning: db2_execute(): Binding Error in C:\temp\test-ibm_db2.php on line 22

Warning: db2_execute(): Binding Error in C:\temp\test-ibm_db2.php on line 22
[IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00

So even his 'ibm_db2' driver has problems with it. Starting to smell like the problem is with CLI.

I'm able to run other SPs, so there's something specific about that SP in particular.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message news:HH********************@news20.bellglobal.com. ..

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:9Y******************************@rogers.com.. .
Test code:

$dbconn = odbc_connect($dbname, $username, $password);
$path = "C:\Temp\myJar.jar";
$statement = "CALL SQLJ.INSTALL_JAR('file://$path', 'myJarId')";
$result = odbc_exec($dbconn, $statement);

Result:

PHP Warning: odbc_exec(): SQL error: [IBM][CLI Driver] CLI0119E Unexpected system failure.
SQLSTATE=58004, SQL state 58004 in SQLExecDirect in C:\temp\CLI0119E.php on line 17

Warning: odbc_exec(): SQL error: [IBM][CLI Driver] CLI0119E Unexpected system failure.
SQLSTATE=58004, SQL state 58004 in SQLExecDirect in C:\temp\CLI0119E.php on line 17

It doesn't seem to matter what the URL is, even if it's not correct... the error never changes, so I presume it's dying before it even gets that far.

2005-11-25-11.09.14.856000-300 I21235H391 LEVEL: Info
PID : 1328 TID : 3168 PROC : php.exe
INSTANCE: DB2 NODE : 000
APPID : *LOCAL.DB2.051125160914
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
MESSAGE : CCI Error:
DATA #1 : Hexdump, 4 bytes
0x0012F86C : 4700 0000 G...

2005-11-25-11.09.14.856000-300 I21628H377 LEVEL: Info
PID : 1328 TID : 3168 PROC : php.exe
INSTANCE: DB2 NODE : 000
APPID : *LOCAL.DB2.051125160914
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
RETCODE : ZRC=0x8136001C=-2127167460=SQLZ_RC_NO_CONNECTION, SQLT_SQLJC
"No connection"

I gather from Dan's article that there are known issues calling SPs with OUT or INOUT parms from PHP, but this doesn't seem to be the case here.

Anyone recognize this? Any suggestions are appreciated.

I hate those CLIxxxx errors; they always seem to be extremely vague, like the one you're experiencing, and often don't suggest any path whatsoever to resolve them. I had a CLI0125E once that kept me hopping for hours; it eventually turned out to be something fairly simple like a version issue that was being masked by the CLI00125E.

Out of sympathy with your situation, I just did a bit of Googling on CLI0119E - both web and groups - and didn't find anything very promising.

One odd thing strikes me looking at your code though. I don't know PHP so maybe this makes perfect sense but I though jars only got installed for Java code? Why woulod PHP code need a jar installed?

Rhino

[Rhino]

That sounds like nature's way of telling you that something is going wrong with the sqlj.install_jar(). :-)
Just like my CLI0125E, CLI0119E is probably masking what is really wrong under the covers.

Does the error in your original program go away if you remove sqlj.install_jar()?

You very likely have a very simple problem.

I wonder if it could be something as simple as a privilege issue? Does your ID have the CREATEIN privilege for the schema? Do you have SYSADM or DBADM? Or maybe the path you specified doesn't exist? Or maybe your jar name is misspelled?

Or maybe it is a syntax issue? When I do sqlj.install_jar() commands in the Ant scripts that prepare my code, I use all forward slashes, not back slashes. For example:
call sqlj.install_jar("file:///e:/eclipse/3.0.1/eclipse/workspace/Foo/jar/Convert_Java.jar", "Convert_Java");

This works very well for me. I'm also using Windows (XP Professional).

Try that basic stuff and see what happens....

Rhino
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:iO********************@rogers.com...
I just took the CLI sample program "dbconn.c" and added a call to 'sqlj.install_jar()', and lo and behold... CLI0119E.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:kd********************@rogers.com...
Hi, Rhino.

The PHP application is trying to catalog a java UDF that it will call later on.

FYI, I also tried using Dan Scott's 'ibm_db2' PHP extension,. and at his suggestion I added a print db2_stmt_errormsg() call, which yielded:

PHP Warning: db2_execute(): Describe Param Failed in C:\temp\test-ibm_db2.php on line 22

Warning: db2_execute(): Describe Param Failed in C:\temp\test-ibm_db2.php on line 22
PHP Warning: db2_execute(): Binding Error in C:\temp\test-ibm_db2.php on line 22

Warning: db2_execute(): Binding Error in C:\temp\test-ibm_db2.php on line 22
[IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00

So even his 'ibm_db2' driver has problems with it. Starting to smell like the problem is with CLI.

I'm able to run other SPs, so there's something specific about that SP in particular.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message news:HH********************@news20.bellglobal.com. ..

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:9Y******************************@rogers.com.. .
Test code:

$dbconn = odbc_connect($dbname, $username, $password);
$path = "C:\Temp\myJar.jar";
$statement = "CALL SQLJ.INSTALL_JAR('file://$path', 'myJarId')";
$result = odbc_exec($dbconn, $statement);

Result:

PHP Warning: odbc_exec(): SQL error: [IBM][CLI Driver] CLI0119E Unexpected system failure.
SQLSTATE=58004, SQL state 58004 in SQLExecDirect in C:\temp\CLI0119E.php on line 17

Warning: odbc_exec(): SQL error: [IBM][CLI Driver] CLI0119E Unexpected system failure.
SQLSTATE=58004, SQL state 58004 in SQLExecDirect in C:\temp\CLI0119E.php on line 17

It doesn't seem to matter what the URL is, even if it's not correct... the error never changes, so I presume it's dying before it even gets that far.

2005-11-25-11.09.14.856000-300 I21235H391 LEVEL: Info
PID : 1328 TID : 3168 PROC : php.exe
INSTANCE: DB2 NODE : 000
APPID : *LOCAL.DB2.051125160914
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
MESSAGE : CCI Error:
DATA #1 : Hexdump, 4 bytes
0x0012F86C : 4700 0000 G...

2005-11-25-11.09.14.856000-300 I21628H377 LEVEL: Info
PID : 1328 TID : 3168 PROC : php.exe
INSTANCE: DB2 NODE : 000
APPID : *LOCAL.DB2.051125160914
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
RETCODE : ZRC=0x8136001C=-2127167460=SQLZ_RC_NO_CONNECTION, SQLT_SQLJC
"No connection"

I gather from Dan's article that there are known issues calling SPs with OUT or INOUT parms from PHP, but this doesn't seem to be the case here.

Anyone recognize this? Any suggestions are appreciated.

I hate those CLIxxxx errors; they always seem to be extremely vague, like the one you're experiencing, and often don't suggest any path whatsoever to resolve them. I had a CLI0125E once that kept me hopping for hours; it eventually turned out to be something fairly simple like a version issue that was being masked by the CLI00125E.

Out of sympathy with your situation, I just did a bit of Googling on CLI0119E - both web and groups - and didn't find anything very promising.

One odd thing strikes me looking at your code though. I don't know PHP so maybe this makes perfect sense but I though jars only got installed for Java code? Why woulod PHP code need a jar installed?

Rhino

[Dan Scott]

I had suggested the forward slashes URL technique to Larry, but forgot
to mention the 'file:///' prefix. That's worth a try. Good suggestion
Rhino!

Dan

[Larry Menard]

Would that it were that simple. ;-) My account has full admin privileges; I've tried backslashes, escaped backslashes, forward slashes, and using the 'file:///' prefix (actually I stumbled onto that one a few days ago... I thought the syntax called for two slashes, but three slashes worked (in CLP).

Something else weird... I tried adding a CALL to the SP in "dbconn.sqc":

EXEC SQL CALL SQLJ.INSTALL_JAR('C:\\Temp\\myJar.jar', 'myJarId');

... but the PREP fails:

SQL0440N No authorized routine named "SQLJ.INSTALL_JAR" of
type "PROCEDURE" having compatible arguments was found.

Again, everything works fine via CLP, so I'm still 99.44% sure this isn't a user error.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Dan Scott" <de*****@gmail.com> wrote in message news:11*********************@g49g2000cwa.googlegro ups.com...

I had suggested the forward slashes URL technique to Larry, but forgot
to mention the 'file:///' prefix. That's worth a try. Good suggestion
Rhino!

Dan

[Rhino]

I still have a gut feel that the problem is something fairly mundane, like the command syntax or the permissions you have but if you've tried everything that I suggested and still can't make it work, I'm not sure what else to suggest at the moment. I'll come back if I think of anything....

Unfortunately, your experience is mirroring my own in working with stored procedures: when things work, life is good but when things don't work, it can be _very_ hard to figure out what is going wrong. The CLIxxxx messages are particularly cryptic and virtually useless; if I had it in my power, those would get significant reworking so that they actually say a lot more than "something's broken".

As for the failure of sqlj.install_jar to work inside your C program, that doesn't really surprise me. I always do my sqlj.install_jar() commands on the command line or in an Ant script; I really don't know if they _should_ work inside the program itself....

Hmm, I just did a search on sqlj.install_jar in the Information Center and came up with this interesting fact, which I'd never noticed before (probably because I learned how to write stored procedures in V7.x):
---
Note:
When invoked from applications, the stored procedures sqlj.install_jar and sqlj.remove_jar have an additional parameter. It is an integer value that dictates the use of the deployment descriptor in the specified JAR file. At present, the deployment parameter is not supported, and any invocation specifying a nonzero value will be rejected.
---
Apparently, it _is_ okay to invoke sqlj.install_jar from within a program. Unfortunately, it is not particularly clear how to do so. Presumably, this extra parameter is the third parameter and needs to be zero. Why don't you try adding a third parameter with a value of zero in your call to sqlj.install_jar() and see if it compiles? Perhaps the solution is that simple!

Rhino
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:nu******************************@rogers.com.. .
Would that it were that simple. ;-) My account has full admin privileges; I've tried backslashes, escaped backslashes, forward slashes, and using the 'file:///' prefix (actually I stumbled onto that one a few days ago... I thought the syntax called for two slashes, but three slashes worked (in CLP).

Something else weird... I tried adding a CALL to the SP in "dbconn.sqc":

EXEC SQL CALL SQLJ.INSTALL_JAR('C:\\Temp\\myJar.jar', 'myJarId');

... but the PREP fails:

SQL0440N No authorized routine named "SQLJ.INSTALL_JAR" of
type "PROCEDURE" having compatible arguments was found.

Again, everything works fine via CLP, so I'm still 99.44% sure this isn't a user error.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Dan Scott" <de*****@gmail.com> wrote in message news:11*********************@g49g2000cwa.googlegro ups.com...

I had suggested the forward slashes URL technique to Larry, but forgot
to mention the 'file:///' prefix. That's worth a try. Good suggestion
Rhino!

Dan

[Larry Menard]

Thanks Rhino, but it still doesn't work in PHP or CLI.

At Dan's suggestion I switched from ODBC to his 'ibm_db2' PHP extension, and used 'db2_stmt_errormsg()' to get more info. It returns "CLI0150E Driver not capable. SQLSTATE=HYC00".

C:\temp>db2 ? CLI0150E
CLI0150E Driver not capable.

Explanation:

The operation is valid but not supported by either the driver or
the data source.

User Response:

Specify a valid operation.

I think I'm snookered. My app is currently getting round this by invoking CLP, but there must be a better way.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message news:a9*******************@news20.bellglobal.com.. .
I still have a gut feel that the problem is something fairly mundane, like the command syntax or the permissions you have but if you've tried everything that I suggested and still can't make it work, I'm not sure what else to suggest at the moment. I'll come back if I think of anything....

Unfortunately, your experience is mirroring my own in working with stored procedures: when things work, life is good but when things don't work, it can be _very_ hard to figure out what is going wrong. The CLIxxxx messages are particularly cryptic and virtually useless; if I had it in my power, those would get significant reworking so that they actually say a lot more than "something's broken".

As for the failure of sqlj.install_jar to work inside your C program, that doesn't really surprise me. I always do my sqlj.install_jar() commands on the command line or in an Ant script; I really don't know if they _should_ work inside the program itself....

Hmm, I just did a search on sqlj.install_jar in the Information Center and came up with this interesting fact, which I'd never noticed before (probably because I learned how to write stored procedures in V7.x):
---
Note:
When invoked from applications, the stored procedures sqlj.install_jar and sqlj.remove_jar have an additional parameter. It is an integer value that dictates the use of the deployment descriptor in the specified JAR file. At present, the deployment parameter is not supported, and any invocation specifying a nonzero value will be rejected.
---
Apparently, it _is_ okay to invoke sqlj.install_jar from within a program. Unfortunately, it is not particularly clear how to do so. Presumably, this extra parameter is the third parameter and needs to be zero. Why don't you try adding a third parameter with a value of zero in your call to sqlj.install_jar() and see if it compiles? Perhaps the solution is that simple!

Rhino
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:nu******************************@rogers.com.. .
Would that it were that simple. ;-) My account has full admin privileges; I've tried backslashes, escaped backslashes, forward slashes, and using the 'file:///' prefix (actually I stumbled onto that one a few days ago... I thought the syntax called for two slashes, but three slashes worked (in CLP).

Something else weird... I tried adding a CALL to the SP in "dbconn.sqc":

EXEC SQL CALL SQLJ.INSTALL_JAR('C:\\Temp\\myJar.jar', 'myJarId');

... but the PREP fails:

SQL0440N No authorized routine named "SQLJ.INSTALL_JAR" of
type "PROCEDURE" having compatible arguments was found.

Again, everything works fine via CLP, so I'm still 99.44% sure this isn't a user error.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Dan Scott" <de*****@gmail.com> wrote in message news:11*********************@g49g2000cwa.googlegro ups.com...

I had suggested the forward slashes URL technique to Larry, but forgot
to mention the 'file:///' prefix. That's worth a try. Good suggestion
Rhino!

Dan

[Rhino]

I've got to believe that _someone_ out there - or in the Toronto lab! - has figured out how to make php and DB2 play nicely together. Finding that someone would appear to be the big challenge. Maybe you should post this question to the best php forums you can find and try to get some satisfaction that way....

Then again, maybe you need to wait until the next version of DB2 and/or php. Perhaps the interoperability between the two is just emerging and needs to mature a bit before it works as seamlessly as it should. That's a total guess, by the way, since I've never used php and have no idea how long DB2 has been supporting it.

Or, you could just do the sqlj.install_jar() and similar steps in scripts/Ant instead of directly within the program. But then I suppose you are trying to compose examples that illustrate that you _can_ prepare the stored procedure from within the program....

Hmmm, one crazy thought just came to me. Is it possible that the CLI0119E occurs because the jar is already there and you are trying to overlay it with each subsequent execution? Maybe you just need to change sqlj.install_jar() to sqlj.replace_jar() and all your problems will go away??

Rhino
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:pd********************@rogers.com...
Thanks Rhino, but it still doesn't work in PHP or CLI.

At Dan's suggestion I switched from ODBC to his 'ibm_db2' PHP extension, and used 'db2_stmt_errormsg()' to get more info. It returns "CLI0150E Driver not capable. SQLSTATE=HYC00".

C:\temp>db2 ? CLI0150E
CLI0150E Driver not capable.

Explanation:

The operation is valid but not supported by either the driver or
the data source.

User Response:

Specify a valid operation.

I think I'm snookered. My app is currently getting round this by invoking CLP, but there must be a better way.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message news:a9*******************@news20.bellglobal.com.. .
I still have a gut feel that the problem is something fairly mundane, like the command syntax or the permissions you have but if you've tried everything that I suggested and still can't make it work, I'm not sure what else to suggest at the moment. I'll come back if I think of anything....

Unfortunately, your experience is mirroring my own in working with stored procedures: when things work, life is good but when things don't work, it can be _very_ hard to figure out what is going wrong. The CLIxxxx messages are particularly cryptic and virtually useless; if I had it in my power, those would get significant reworking so that they actually say a lot more than "something's broken".

As for the failure of sqlj.install_jar to work inside your C program, that doesn't really surprise me. I always do my sqlj.install_jar() commands on the command line or in an Ant script; I really don't know if they _should_ work inside the program itself....

Hmm, I just did a search on sqlj.install_jar in the Information Center and came up with this interesting fact, which I'd never noticed before (probably because I learned how to write stored procedures in V7.x):
---
Note:
When invoked from applications, the stored procedures sqlj.install_jar and sqlj.remove_jar have an additional parameter. It is an integer value that dictates the use of the deployment descriptor in the specified JAR file. At present, the deployment parameter is not supported, and any invocation specifying a nonzero value will be rejected.
---
Apparently, it _is_ okay to invoke sqlj.install_jar from within a program. Unfortunately, it is not particularly clear how to do so. Presumably, this extra parameter is the third parameter and needs to be zero. Why don't you try adding a third parameter with a value of zero in your call to sqlj.install_jar() and see if it compiles? Perhaps the solution is that simple!

Rhino
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:nu******************************@rogers.com.. .
Would that it were that simple. ;-) My account has full admin privileges; I've tried backslashes, escaped backslashes, forward slashes, and using the 'file:///' prefix (actually I stumbled onto that one a few days ago... I thought the syntax called for two slashes, but three slashes worked (in CLP).

Something else weird... I tried adding a CALL to the SP in "dbconn.sqc":

EXEC SQL CALL SQLJ.INSTALL_JAR('C:\\Temp\\myJar.jar', 'myJarId');

... but the PREP fails:

SQL0440N No authorized routine named "SQLJ.INSTALL_JAR" of
type "PROCEDURE" having compatible arguments was found.

Again, everything works fine via CLP, so I'm still 99.44% sure this isn't a user error.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Dan Scott" <de*****@gmail.com> wrote in message news:11*********************@g49g2000cwa.googlegro ups.com...

I had suggested the forward slashes URL technique to Larry, but forgot
to mention the 'file:///' prefix. That's worth a try. Good suggestion
Rhino!

Dan

[Larry Menard]

I agree, it's just a matter of getting the attention of the right person in DB2 Development. (I recently left IBM, so even though I know most of the developers, my contacting them directly might no longer be warmly received. ;-) ) As I said, I am working with Dan (Scott) on this, and he's assured me he'll follow up on this soon (he's rather busy moving his home right now).

(I already make sure the jar is not yet installed. I even get the error even on a freshly-created database.)

Hopefully Dan can channel this to the right people.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message news:dO*******************@news20.bellglobal.com.. .
I've got to believe that _someone_ out there - or in the Toronto lab! - has figured out how to make php and DB2 play nicely together. Finding that someone would appear to be the big challenge. Maybe you should post this question to the best php forums you can find and try to get some satisfaction that way....

Then again, maybe you need to wait until the next version of DB2 and/or php. Perhaps the interoperability between the two is just emerging and needs to mature a bit before it works as seamlessly as it should. That's a total guess, by the way, since I've never used php and have no idea how long DB2 has been supporting it.

Or, you could just do the sqlj.install_jar() and similar steps in scripts/Ant instead of directly within the program. But then I suppose you are trying to compose examples that illustrate that you _can_ prepare the stored procedure from within the program....

Hmmm, one crazy thought just came to me. Is it possible that the CLI0119E occurs because the jar is already there and you are trying to overlay it with each subsequent execution? Maybe you just need to change sqlj.install_jar() to sqlj.replace_jar() and all your problems will go away??

Rhino
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:pd********************@rogers.com...
Thanks Rhino, but it still doesn't work in PHP or CLI.

At Dan's suggestion I switched from ODBC to his 'ibm_db2' PHP extension, and used 'db2_stmt_errormsg()' to get more info. It returns "CLI0150E Driver not capable. SQLSTATE=HYC00".

C:\temp>db2 ? CLI0150E
CLI0150E Driver not capable.

Explanation:

The operation is valid but not supported by either the driver or
the data source.

User Response:

Specify a valid operation.

I think I'm snookered. My app is currently getting round this by invoking CLP, but there must be a better way.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message news:a9*******************@news20.bellglobal.com.. .
I still have a gut feel that the problem is something fairly mundane, like the command syntax or the permissions you have but if you've tried everything that I suggested and still can't make it work, I'm not sure what else to suggest at the moment. I'll come back if I think of anything....

Unfortunately, your experience is mirroring my own in working with stored procedures: when things work, life is good but when things don't work, it can be _very_ hard to figure out what is going wrong. The CLIxxxx messages are particularly cryptic and virtually useless; if I had it in my power, those would get significant reworking so that they actually say a lot more than "something's broken".

As for the failure of sqlj.install_jar to work inside your C program, that doesn't really surprise me. I always do my sqlj.install_jar() commands on the command line or in an Ant script; I really don't know if they _should_ work inside the program itself....

Hmm, I just did a search on sqlj.install_jar in the Information Center and came up with this interesting fact, which I'd never noticed before (probably because I learned how to write stored procedures in V7.x):
---
Note:
When invoked from applications, the stored procedures sqlj.install_jar and sqlj.remove_jar have an additional parameter. It is an integer value that dictates the use of the deployment descriptor in the specified JAR file. At present, the deployment parameter is not supported, and any invocation specifying a nonzero value will be rejected.
---
Apparently, it _is_ okay to invoke sqlj.install_jar from within a program. Unfortunately, it is not particularly clear how to do so. Presumably, this extra parameter is the third parameter and needs to be zero. Why don't you try adding a third parameter with a value of zero in your call to sqlj.install_jar() and see if it compiles? Perhaps the solution is that simple!

Rhino
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:nu******************************@rogers.com.. .
Would that it were that simple. ;-) My account has full admin privileges; I've tried backslashes, escaped backslashes, forward slashes, and using the 'file:///' prefix (actually I stumbled onto that one a few days ago... I thought the syntax called for two slashes, but three slashes worked (in CLP).

Something else weird... I tried adding a CALL to the SP in "dbconn.sqc":

EXEC SQL CALL SQLJ.INSTALL_JAR('C:\\Temp\\myJar.jar', 'myJarId');

... but the PREP fails:

SQL0440N No authorized routine named "SQLJ.INSTALL_JAR" of
type "PROCEDURE" having compatible arguments was found.

Again, everything works fine via CLP, so I'm still 99.44% sure this isn't a user error.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Dan Scott" <de*****@gmail.com> wrote in message news:11*********************@g49g2000cwa.googlegro ups.com...

I had suggested the forward slashes URL technique to Larry, but forgot
to mention the 'file:///' prefix. That's worth a try. Good suggestion
Rhino!

Dan

[Dan Scott]

I will guarantee you this has nothing to do with PHP, or DB2/PHP
interaction. From Larry's tests using CLI and Embedded SQL it sounds
like it's a deeper problem on his system; it just surfaced when he was
trying to use PHP.

Larry -- does calling sqlj.install_jar() work if you try it in a Java
program? So far it seems that it's failing for you through any
programmatic interface. I assume you're using exactly the same username
and password for all of these tests...

When I've got a few more moments I'll try to reproduce the problem
using my Windows FP10 installation.

Dan

[Larry Menard]

>I will guarantee you this has nothing to do with PHP, or DB2/PHP

interaction.
I'm not saying it does. I'm sure DB2/PHP is just a victim. If I were a betting man, I'd put money on CLI being the villain.
like it's a deeper problem on his system
Gasp!! Them's fightin' words!
Larry -- does calling sqlj.install_jar() work if you try it in a Java
program?
Seems to. No exception is thrown. For convenience I modified the sample application 'SpClient.java':

try
{
Db db = new Db(argv);
boolean rc = false;

System.out.println("HOW TO CALL VARIOUS STORED PROCEDURES.\n");

// connect to the 'sample' database
db.connect();
con = db.con;

String procName = "SQLJ.INSTALL_JAR";
String sql = "CALL " + procName + "(?, ?)";
CallableStatement callStmt = con.prepareCall(sql);

// register the output parameter
callStmt.setString(1, "file:///c:\\temp\\myJar.jar");
callStmt.setString(2, "myJarId");

// call the stored procedure
System.out.println();
System.out.println("Calling stored procedure named " + procName);
rc = callStmt.execute();

System.out.println("rc = " + rc + ".\n");

// clean up resources
callStmt.close();

// roll back any changes to the database made by this sample
con.rollback();
con.close();
}
catch (Exception e)
{
try
{
con.rollback();
con.close();
}
catch (Exception x)
{ }

e.printStackTrace();
}

Result:

C:\temp>java SpClient
HOW TO CALL VARIOUS STORED PROCEDURES.

Connect to 'sample' database using JDBC type 2 driver.

Calling stored procedure named SQLJ.INSTALL_JAR
rc = false.

C:\temp>

So it is not 'all' programmatic interfaces that are failing.

Yes, the database, userid, password, everything else is consistent.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Dan Scott" <de*****@gmail.com> wrote in message news:11********************@f14g2000cwb.googlegrou ps.com...I will guarantee you this has nothing to do with PHP, or DB2/PHP
interaction. From Larry's tests using CLI and Embedded SQL it sounds
like it's a deeper problem on his system; it just surfaced when he was
trying to use PHP.

Larry -- does calling sqlj.install_jar() work if you try it in a Java
program? So far it seems that it's failing for you through any
programmatic interface. I assume you're using exactly the same username
and password for all of these tests...

When I've got a few more moments I'll try to reproduce the problem
using my Windows FP10 installation.

Dan

[Rhino]

Fair enough.

You and Larry have a much better chance to getting to the bottom of the
problem with your PHP knowledge and all of the lab's resources than I do. I
was just trying to brainstorm along with Larry in the hope that we might
stumble on something fairly routine. I'll simply bow out at this point,
then.

Good luck!

Rhino
"Dan Scott" <de*****@gmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

I will guarantee you this has nothing to do with PHP, or DB2/PHP
interaction. From Larry's tests using CLI and Embedded SQL it sounds
like it's a deeper problem on his system; it just surfaced when he was
trying to use PHP.

Larry -- does calling sqlj.install_jar() work if you try it in a Java
program? So far it seems that it's failing for you through any
programmatic interface. I assume you're using exactly the same username
and password for all of these tests...

When I've got a few more moments I'll try to reproduce the problem
using my Windows FP10 installation.

Dan