callablestatement to as400 not working

Question

On a domino server I have an odbc connection to an as400 system. I can run a program on the as400 and send an input parameter by :

var sql:string ="CALL QSYS.QCMDEXC('SBMJOB CMD(CALL PGM(DEMO/TESTDEMO) PARM(ABCDEF)) ',0000000048.00000)";
ps = con.prepareCall(sql);
ps.execute();

Nice , works, but now I would like to get some data back. I thought I had to do this with a callablestatement, so I tried :

var con:Connection = null;
var cs:CallableStatement = null;
try {
  java.lang.Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  var con:java.sql.Connection=java.sql.DriverManager.getConnection(url,usr,pwd);
    sessionScope.error = sessionScope.error +"connection set /";
  var sql:string ="CALL QSYS.QCMDEXC('CALL PGM(DEMO/TESTDEMO) (?,?))";
    sessionScope.error = sessionScope.error +"sql set /";
  cs = con.prepareCall (sql);
    sessionScope.error = sessionScope.error +"cs set /";
  cs.setString (1,'test');
    sessionScope.error = sessionScope.error +"input param set /";
  cs.registerOutParameter (2, Types.VARCHAR);
    sessionScope.error = sessionScope.error +"output param set /";
  cs.execute ();
    sessionScope.error = sessionScope.error +"executed /";
  var retour = cs.getInt (2);
    sessionScope.error = sessionScope.error +"output /"+retour;
  if (cs != null) {cs.close();sessionScope.error = sessionScope.error + "/ cs closed"}
  if (con != null) {con.close();sessionScope.error = sessionScope.error + "/ con closed"}
  }
catch (e){
  sessionScope.error = sessionScope.error+"Sql error ="+e.toString();
  if (cs != null) {cs.close();sessionScope.error = sessionScope.error + "/ cs closed"}
  if (con != null) {con.close();sessionScope.error = sessionScope.error + "/ con closed"}
  return;
}

When running this I get as error :

connection set /sql set /cs set /Sql error =java.lang.NullPointerException/ cs closed/ con closed

So the problem is about setting the input parameter.

Answer 1

You can't return values using QCMDEXC, but any program on IBM i can be turned into a stored procedure which can then return values either in a result set or a parameter.

CREATE PROCEDURE ASSEMBLY_PARTS (IN  ASSEMBLY_NUM  DEC(7,0), 
                                 OUT NUM_PARTS     DEC(7,0), 
                                 OUT COST          DEC(9,2))
      LANGUAGE RPG
      PARAMETER STYLE GENERAL 
      FENCED
      EXTERNAL NAME ASSEMBLY

This defines a stored procedure for an RPG program named ASSEMBLY with one input parameter, and two output parameters. This is a modified example from the SQL reference found here: https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzcrtpef.htm

You then call it similarly to the way you call the QSYS.QCMDEXC stored procedure. But the call looks more like CALL ASSEMBLY_PARTS(ID, PARTS, COST). That is the closest I can get you right now since I don't have an instance of Domino to test with at the moment.