1. litmis Avatar litmis
  2. IBM i Collaboration
  3. nodejs
  4. Issues

SQLSTATE=PAERR SQLCODE=-1

Issue #5 resolved
Roger Nassar created an issue

Hi, I took Aaron's sample of calling to a program & replaced it by one of ours. I get the below error: [Error: SQLSTATE=PAERR SQLCODE=-1 SQLPrepare() failed.]
When I turned debug on, I get the following: INPUT XML
============
<?xml version='1.0'?><myscript><pgm name='CDSS01' lib='BLOAD' error='fast'><parm><ds><data type='3A'>USD</data><data type="3A"> </data><data type='1A'> </data></ds></parm></pgm></myscript>
============
OUTPUT XML
============
[Error: SQLSTATE=PAERR SQLCODE=-1 SQLPrepare() failed.]

How can I get more details to know why it is failing? I know it is failing at the call & before we go into the callback. Thanks, Roger

Comments (30)

  1. Roger Nassar reporter

    Just FYI, the pgm in question is an COBOL OPM program with a linkage. When I check the usage, it was not accessed (or used), ie date last used is not today. Also, noticed that in the xml generated, the 2nd data type had a double quote while the other 2 has a single quote. I checked my source & here is the

    pgm.addParam(
    [
    [ycu,"3A"],
    [bmcu,"3A"],
    [retflag, "1A"],
    ])
    Thanks, Roger

  4. Roger Nassar reporter

    /
    Adapted from Aaron Bartell sample
    /
    var xt = require('/QOpenSys/QIBM/ProdData/Node/os400/xstoolkit/lib/itoolkit');
    var conn = new xt.iConn("*LOCAL");
    var pgm = new xt.iPgm("CDSB01", {"lib":"BLOAD"});
    conn.add(pgm.toXML());
    function my_call_back(str) {
    console.log("result:" + str);
    }
    console.log("bfr call");
    conn.debug(true);
    conn.run(my_call_back);

    In this case, there is no interface (nothing passed or returned) just for testing.

  5. Aaron Bartell

    In this case, there is no interface (nothing passed or returned) just for testing.

    I wonder if that is the issue. Could you put together a small COBOL program that has a simple interface and see if you can call it? Or even better, post the sample COBOL program here with the corresponding compile command and I will try it on my system.

  6. Roger Nassar reporter

    I was able to work on this tonight & made some progress. It turned out that SI55638 was not applied. When I applied it, the PAERR is gone. Now, I get a diff error: SQLSTATE=08001 SQLCODE=-30082 Authorization failure on distributed database connection attempt I tried adding a user & pwd to the connect() but it did not make any difference I changed to LOCAL to the RDBDIRE name & it made no diff I googled it & somebody was suggesting to "crtdtaara dtaara(qsys/qsqclicon) type(char) len(7) value(*prvchk)", i tried it & it made no difference. Also, what is strange is that the sample to execute an SQL select works fine. so, it seems that the db.conn is diff from the toolkit iConn. Thanks, Roger

  8. Aaron Bartell

    Thanks for the update, Roger. If the PTFs fix the issue please respond back to this issue and close it.

  9. Sebastian Misiewicz

    Hi Roger Did you solve your problem? I've got all latest PTFs installed but the error still remains "SQLSTATE=PAERR SQLCODE=-1 SQLPrepare() failed". Here is list of mine PTFs PTF
    ID Status
    SI55763 Temporarily applied
    SI55747 Superseded
    SI55638 Permanently applied
    SI55499 Superseded
    I'm not sure if 'Temporarily applied' changes anything. Any idea?

  10. Roger Nassar reporter

    Sebastian, There seem to be an issue on V7R2, it is ok on V7R1 (I did not test that myself on V7R1). If you are running on V7R2, you will probably need to wait for IBM to release a fix.

  11. Sebastian Misiewicz

    Hi Roger thanks for your reply.

    I'm running V7R1. But I made it work. I've sent an email to Mr Xu Meng and he asked me to (re)register SQL procedure ( QXMLSERV.iPLUG512K ). Now works like a charm.

  14. Aaron Bartell

    I ran into this same issue on a v7.2 machine with the 5733OPS version of XMLSERIVCE installed (i.e. *LIB QXMLSERV). Not knowing what the issue was I also had all the latest 5733OPS PTFs applied but still no go. My solution was to grab the latest from (YiPs)[http://youngiprofessionals.com/wiki/index.php/XMLService/XMLService] and install it into *LIB XMLSERVICE.

    Then alter your connection to point at the new xslib:

    var option = {xslib : "XMLSERVICE"};
var conn = new xt.iConn("*LOCAL",null,null,option);
  16. Robert Brown

    I am working on a shared development remote site and I cannot install the latest XMLSERVICE. All PTF's are up to date as of this morning and I am still getting the error.Picture1.png

  17. Aaron Bartell

    How do you do this? (re)register SQL procedure ( QXMLSERV.iPLUG512K )

    I believe re-register means to recreate it. You can learn more about that here.

    Do you have authority to install the version of XMLSERVICE from YiPs? That's the version I use.

  18. Roger Nassar reporter

    Hi, yes, Aron is correct. register means to let SQL know about the procedure by creating the procedure. See sample below: CREATE PROCEDURE QXMLSERV.IPLUG512K ( IN IPC CHAR(1024) , IN CTL CHAR(1024) , IN CI CLOB(524288) , OUT CO CLOB(524288) ) LANGUAGE RPGLE SPECIFIC QXMLSERV.IPLUG512K NOT DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'QXMLSERV/XMLSTOREDP(iPLUG512K)' PARAMETER STYLE GENERAL ;

    GRANT EXECUTE
    ON SPECIFIC PROCEDURE QXMLSERV.IPLUG512K TO PUBLIC ;

    GRANT ALTER , EXECUTE
    ON SPECIFIC PROCEDURE QXMLSERV.IPLUG512K TO QSYS ;

    Roger

  20. Robert Brown

    Aaron, I attached the test nodes code that I have been working with. As you can see some work and others don’t. If you have anything ideas that you think might work please let me know.

    Thanks Bob Brown

  21. Aaron Bartell

    Hi Robert,

    I am not seeing your test Node.js code. I don't believe you can attach documents here.

  25. Robert Brown 
    // TEST various SQL Statements
 var db = require('/QOpenSys/QIBM/ProdData/Node/os400/db2i/lib/db2');
//  var xt = require('/QOpenSys/QIBM/ProdData/Node/os400/xstoolkit/lib/itoolkit');

var DBname = "*LOCAL";
var myIP = "24.115.110.36";
var  selectVar;
// selectVar = "'QEZJOBLOG'";
// selectVar = "'LOADED'";
// selectVar = "'V7R2M0'";
selectVar = "'%MAX%'";


// (FAILED 'QEZJOBLOG')  var sqlSTM = String('SELECT * FROM QSYS2.OUTPUT_QUEUE_ENTRIES WHERE OUTPUT_QUEUE_NAME = ' + selectVar + ' FETCH FIRST 10 ROWS ONLY');

//  (WORKS) var sqlSTM = String('SELECT * FROM QSYS2.NETSTAT_ROUTE_INFO')

//(WORKS 'LOADED') var sqlSTM = String('SELECT PTF_IDENTIFIER, PTF_IPL_REQUIRED, A.* FROM QSYS2.PTF_INFO A WHERE PTF_LOADED_STATUS = ' + selectVar + ' ORDER BY PTF_PRODUCT_ID'); 

//(WORKS 'NONE') var sqlSTM = String('SELECT PTF_IDENTIFIER, PTF_IPL_REQUIRED, A.* FROM QSYS2.PTF_INFO A WHERE PTF_IPL_ACTION <>  ' + selectVar + ' ORDER BY PTF_PRODUCT_ID'); 

// (WORKS 'V7R2M0') var sqlSTM = String('SELECT PTF_IDENTIFIER, PTF_IPL_REQUIRED, A.* FROM QSYS2.PTF_INFO A WHERE PTF_RELEASE_LEVEL =  ' + selectVar + ' ORDER BY PTF_PRODUCT_ID'); 

// (FAILED) var sqlSTM = String('SELECT * FROM QSYS2.LIBRARY_LIST_INFO');

var sqlSTM = String('SELECT * FROM QSYS2.SYSTEM_VALUE_INFO WHERE SYSTEM_VALUE_NAME LIKE ' + selectVar);

function testSimpleSQL() {
    console.log("\n  testSimpleSQL()\n----------------------------------");
    try{
        db.debug(true);
        db.init(function(){
            db.serverMode(true);
        });
        db.conn(DBname, 'IUSR0042', 'PASSWORD', function(){
            db.autoCommit(true);
        });




        db.exec(sqlSTM , 
                function(jsonObj) {
                    console.log("Result: %s", JSON.stringify(jsonObj));
                    var fieldNum = db.numFields();
                    console.log("There are %d fields in each row.", fieldNum);
                    console.log("Name | Length | Type | Precise | Scale | Null");
                    for(var i = 0; i < fieldNum; i++)
                        console.log("%s | %d | %d | %d | %d | %d", db.fieldName(i), db.fieldWidth(i), db.fieldType(i), db.fieldPrecise(i), db.fieldScale(i), db.fieldNullable(i));
                }
            );
        db.close();
        return 0;
    } catch(e) {
        console.log(e);
        console.log("\n  FAILED!!! testSimpleSQL()\n----------------------------------");
        return -1;
    }
};



testSimpleSQL();
  26. Aaron Bartell

    I simplified your example to the following. I paste this into a Node.js REPL and it was successful.

    var db = require('/QOpenSys/QIBM/ProdData/Node/os400/db2i/lib/db2');
try{
  db.debug(true);
  db.init(function(){
    db.serverMode(true);
  });
  db.conn('*LOCAL', function(){
    db.autoCommit(true);
  });
  db.exec("SELECT * FROM QSYS2.SYSTEM_VALUE_INFO WHERE SYSTEM_VALUE_NAME LIKE '%MAX%'", 
    function(jsonObj) {
      console.log("Result: %s", JSON.stringify(jsonObj));
      var fieldNum = db.numFields();
      console.log("There are %d fields in each row.", fieldNum);
      console.log("Name | Length | Type | Precise | Scale | Null");
      for(var i = 0; i < fieldNum; i++)
        console.log(
          "%s | %d | %d | %d | %d | %d", 
          db.fieldName(i), db.fieldWidth(i), db.fieldType(i), db.fieldPrecise(i), db.fieldScale(i), db.fieldNullable(i)
        );
    }
  );
  db.close();
} catch(e) {
  console.log(e);
  console.log("\n  FAILED!!! testSimpleSQL()\n----------------------------------");
}

    My output:

    SetEnvAttr() attr = 10004, value = 1, rc = 0
Connect to *LOCAL
SetConnAttr() attr = 10003, value = 1, rc = 0
Connected to *LOCAL with ID .
SQL: SELECT * FROM QSYS2.SYSTEM_VALUE_INFO WHERE SYSTEM_VALUE_NAME LIKE '%MAX%'
Result: [{"SYSTEM_VALUE_NAME":"QMAXACTLVL","CURRENT_NUMERIC_VALUE":"32767","CURRENT_CHARACTER_VALUE":""},{"SYSTEM_VALUE_NAME":"QMAXSIGN","CURRENT_NUMERIC_VALUE":"0","CURRENT_CHARACTER_VALUE":"000003"},{"SYSTEM_VALUE_NAME":"QPWDMAXLEN","CURRENT_NUMERIC_VALUE":"8","CURRENT_CHARACTER_VALUE":""},{"SYSTEM_VALUE_NAME":"QMAXSGNACN","CURRENT_NUMERIC_VALUE":"0","CURRENT_CHARACTER_VALUE":"3"},{"SYSTEM_VALUE_NAME":"QMAXJOB","CURRENT_NUMERIC_VALUE":"163520","CURRENT_CHARACTER_VALUE":""},{"SYSTEM_VALUE_NAME":"QMAXSPLF","CURRENT_NUMERIC_VALUE":"9999","CURRENT_CHARACTER_VALUE":""}]
There are 3 fields in each row.
Name | Length | Type | Precise | Scale | Null
SYSTEM_VALUE_NAME | 18 | 12 | 10 | 0 | 1
CURRENT_NUMERIC_VALUE | 22 | 19 | 8 | 0 | 1
CURRENT_CHARACTER_VALUE | 24 | 18 | 1280 | 0 | 1

    What error are you getting?

  28. Aaron Bartell

    Hi Robert,

    Did you paste my code into a REPL and run it? Note this code isn't using XMLSERVICE and instead DB2 for i Services. Which left me a little confused concerning your earlier references to XMLSERVICE.

  29. Robert Brown

    My comments on XMLSERVICE were based on your solution to the problem which I understand is installing the version found on YiPs. Maybe I was mistaken. I am currently puzzled why some of the DB2 services for i work and others produce the error. All seem to work as expected from the strsql green screen. I am accessing a remote IBM i and I don't have authority to rstlib, so I am not able to try the solutions provided here by you and others. Unless someone else is experiencing the same issue, I am fine to leave this as a resolved issue. I did not type in the commands directly. I will try that. Thank you for checking it out for me.

  30. Aaron Bartell

    Hi Robert,

    Definitely don't need XMLSERVICE for DB2 for i Services to work, so no need for RSTLIB.

    I will mark this as Resolved for now. If you continue to have issues then open a new issue.

  31. Log in to comment
Assignee
Type
bug
Priority
major
Status
resolved
Votes
0
Watchers
3
Jira: the preferred issue tracker for Bitbucket. Join the team!