Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007

    Unanswered: system comman can't execute ,SQL ERR 668 ISAM ERR 127

    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);
    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 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.

  2. #2
    Join Date
    Aug 2006
    The Netherlands

    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.


  3. #3
    Join Date
    Jun 2007
    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)


  4. #4
    Join Date
    Aug 2006
    The Netherlands

    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:
    '(. ${HOME}/.profile;WORKDIR=/home/ap/test;export WORKDIR;cd /home/ap/test/bin;./' || cmd || ')'
    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)
    Last edited by Tyveleyn; 06-03-07 at 05:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts