Hi everyone :

I'm using informix 9.4 FC5 on hp-ux 11.11

I have a Stored Procedure which executes a System command to call a EC program.
When executing the stored procedure directly on the server using DBACCESS,
everything works correctly.

the SP is:

create procedure sp_test(cmd char(200)) returning int;
define SQL_err integer;
define ISAM_err integer;
define v_ret int;
define cmds char(256);
begin
on exception set SQL_err,ISAM_err end exception with resume;
set debug file to "/tmp/yy.txt";
trace on;
let SQL_err=0;
let v_ret=0;
system 'echo "'||cmd||'" >> /home/ap/test/test.log';
system 'WORKDIR=/home/ap/test;export WORKDIR;cd /home/ap/test/bin;'||cmd;
if SQL_err<0 then
let v_ret=-ISAM_err;
end if
return v_ret;

end
end procedure;

the cmd is execute program named "test"

I have written a Java application which running in weblogic8.14 and
calls the Stored Procedure using JDBC ,the jdbc version is informix 9.3 ,
The application works on cluster

I get this error :

SQL ERR 668: System command cannot be executed.
ISAM ERR 127: no primary key

I haved revised permissions in subdirectories, PATH and LIB haved configured
in .profile , all is correct.


Thanks in advance.

Hi,

First: 'SET DEBUG FILE TO...' and 'TRACE ON' are declarative statements and should be called before any regular statement block. So place these statemenrs directly after the variable declarations.

Second: An executable file in UNIX with the name 'test' will not work because the shell interprets it as the builtin test-command.

Regards

THX,Rotterdam's friends

excuteable program has renamed cinc

I think another error is:
the user informix's DB_LOCAL is diffrent with user CRREW's(who created the databse)

WELCOME TO BEIJING!

Hi,

Previously I missed your remark about succesfully executing the SP from dbaccess. This suggests that the shell commands invoked by the SYSTEM statement can only be executed once the environment for a login shell is set. Keep in mind that, like with a crontab job, in this case the invoked shell is not a login shell and therefore hasn't got the settings from a .profile. So make the shell that's started by SYSTEM selfcontaining by including all the necesary parameters in the argumentstring. (You could source the users .profile in the string like:but I would set the necesary variables explicitly in the script.)
Here I've also added './' to the invocation of cinc, presuming it resides in /home/ap/test/bin, and put braces around the series of commands for some UNIX systems require that for running multiple commands on the command line.

BTW. I would run a series of OS commands from a SP by first creating a shell script in the way you append a line to your /home/ap/test/test.log file. Next I would run the shell script with a new SYSTEM statement and finally remove the shell script.

And about the locale's I only can say that formally the DB_LOCALE of the server and DB_LOCALE of the client have to match so the driver (JDBC/ODBC) can make the conversions from the CLIENT_LOCALE to the DB_LOCALE. Recently (yesterday) I experienced that our new IDS10 server complained about the mismatch in my ODBC driver where the old IDS9.2 didn't, thus refusing connection from my application.

Good luck, and let the games begin... (old Roman proverb)