Topic
2 replies Latest Post - ‏2013-10-09T10:27:22Z by willor
willor
willor
12 Posts
ACCEPTED ANSWER

Pinned topic ISIM 6.0, Custom Adapter Stored Procedure Calling

‏2013-10-08T09:33:36Z |

Hi, I am having trouble when calling stored procedure, before I have tested (add) the functionality is intact, before proceeding to call the stored procedure

I put this code inside add connector in Override Add Hooks

and i have remove the assignment attribute to the component attribute

 

Connector name : Add

Database name : ODI   (MSSQL)

Stored Procedure name: shTestProcedure

 

Override Add Script

var con = thisConnector.getConnector().getConnection();
// Stored procedure call
var command = "{call ODI.dbo.shTestProcedure(?,?,?,?,?)}";
var cstmt = null;
try {
    cstmt = con.prepareCall(command);
 
var id = work.getString("eruid");
var first = work.getString("FirstName");
var last = work.getString("LastName");
var dept = work.getString("Department");
var mail = work.getString("EMAILADDRESS");
 
 
    // Assign IN parameters (use positional placement)
    cstmt.setString(1, id);
    cstmt.setString(2, mail);
    cstmt.setString(3, first);
    cstmt.setString(4, last);
    cstmt.setString(5, "deptTesting");
 
// make the call
    cstmt.execute();
 
}
 
catch(e) {
    main.logmsg(e);
}

 

however when i request an account there is an error in the View Request states that

CTGIMT205E  The request failed due to a system error: add Error: {  "status": "fail",  "connectorname": "Add",  "operation": "addonly",  "exception": "com.ibm.jscript.InterpretException: Script interpreter error, line=28, col=10: Can't convert com.ibm.jscript.std.ErrorObject$ReferenceErrorObject to java.lang.String",  "message": "Script interpreter error, line=28, col=10: Can't convert com.ibm.jscript.std.ErrorObject$ReferenceErrorObject to java.lang.String",  "class": "com.ibm.jscript.InterpretException" }

 

Does anyone knows what wrong with this?
 

Best Regards,

Willor

Updated on 2013-10-09T10:27:01Z at 2013-10-09T10:27:01Z by willor
  • mark99
    mark99
    26 Posts
    ACCEPTED ANSWER

    Re: ISIM 6.0, Custom Adapter, Stored Procedure Calling

    ‏2013-10-08T13:19:33Z  in response to willor

    I use the same thechnique as you do

    This is what I put in the override add also going to MSQL database

    trim just returns the string value without leading/trailing spaces

     

     
     
    var connection=thisConnector.connector.getConnection();
    var sql="exec myStoredProc @loginID=?,@dienstID=?,@relatieID=?,@startdatum=?";
    var preparedStatement=connection.prepareStatement(sql);
     
    preparedStatement.setObject(1, trim(work.getAttribute("uzlloginID")) ,java.sql.Types.NUMERIC)
    preparedStatement.setObject(2, trim(work.getAttribute("uzldienst")) ,java.sql.Types.NUMERIC)
    preparedStatement.setObject(3, trim(work.getAttribute("uzlrelatieid")) ,java.sql.Types.NUMERIC)
    preparedStatement.setTimestamp(4,timestamp)
     
     
    var rs=preparedStatement.executeQuery( );
     
    while (rs.next()) {
        work.uzlrelatieid = rs.getString("relatieID");
    }
     
    • willor
      willor
      12 Posts
      ACCEPTED ANSWER

      Re: ISIM 6.0, Custom Adapter, Stored Procedure Calling

      ‏2013-10-09T10:27:22Z  in response to mark99

      Hi, Thanks Mark :)

      Solved~