Topic
  • 2 replies
  • Latest Post - ‏2014-02-13T02:10:04Z by WWilliam
willor
willor
12 Posts

Pinned topic ISIM 6.0 (Custom Adapter) TDI hook Stored Procedure calling

‏2014-02-12T07:58:46Z |

Hi, I need help on development of custom adapter

I am trying to call stored procedure to create database login on sql server

however i met with this error

 

This is my code in override add hook 

createDBLogin_command = "{call CNMS.dbo.CREATEDBLOGIN(?,?,?,?)}";

varsqlStrOut;

// open connection from JDBC Connector

var con = thisConnector.getConnector().getConnection();

try {

      // Execution of createDBLogin stored procedure

      createDBLogin_cstmt = con.prepareCall(createDBLogin_command);

      // assigning all the parameters

      // user id, password, OUTPUT PARAMETER, RoleType

      createDBLogin_cstmt.setString(1, "shUserTest20");

      createDBLogin_cstmt.setString(2, "shUserTest");

      createDBLogin_cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);

      createDBLogin_cstmt.setString(4, "1");

      // make the call

      var resultsAvailable = createDBLogin_cstmt.execute();

     

      // get results after 

   sqlStrOut = createDBLogin_cstmt.getString(3);

     

      // print them out

      task.logmsg("== createDBLogin SQLSTROUT == "+ sqlStrOut);

      createDBLogin_cstmt.close();

     

} catch (e) {

      main.logmsg(e);

      task.logmsg("==CREATEDBLOGIN STORED PROCEDURE FAILED WHEN CALLING");

      task.logmsg("==EXCEPTION== "+ e);

      task.logmsg("==CREATEDBLOGIN SQLSTROUT== "+ sqlStrOut);

      task.logmsg(e.getMessage());

 

}

 

IF THE ACCOUNT DOES NOT EXIST -> IT WILL POP OUT EXCEPTION STATING SERVER FAILED TO RESUME TRANSACTION

15:23:11,017 INFO  - CTGDIS115I Starting scripting hook of type prolog0.

15:23:11,032 INFO - Adapter Version - 6.0

15:23:11,032 INFO  - CTGDIS116I Scripting hook of type prolog0 finished.

15:23:11,063 INFO  - CTGDIS087I Iterating.

15:23:11,063 INFO  - CTGDIS086I No iterator in AssemblyLine, will run single pass only.

15:23:11,063 INFO  - CTGDIS092I Using runtime provided entry as working entry (first pass only).

15:23:11,453 INFO  - ==CREATEDBLOGIN STORED PROCEDURE FAILED WHEN CALLING

15:23:11,453 INFO  - ==EXCEPTION== com.microsoft.sqlserver.jdbc.SQLServerException: The server failed to resume the transaction. Desc:5500000008.

15:23:11,453 INFO  - ==CREATEDBLOGIN SQLSTROUT== undefined

15:23:11,453 INFO  - The server failed to resume the transaction. Desc:5500000008.

15:23:11,453 DEBUG - AddOnly OK Hook

15:23:11,453 INFO  - CTGDIS088I Finished iterating.

15:23:11,469 INFO  - CTGDIS100I Printing the Connector statistics.

15:23:11,469 INFO  -  [Add] Add:1

15:23:11,469 INFO  - CTGDIS104I Total: Add:1.

15:23:11,469 INFO  - CTGDIS101I Finished printing the Connector statistics.

15:23:11,469 INFO  - CTGDIS080I Terminated successfully (0 errors).

 

IF THE ACCOUNT DOES EXISTS THEN IT WILL PROCEED SUCCESSFULLY WITH PARAMETER RETURN

14:38:29,326 INFO  - CTGDIS115I Starting scripting hook of type prolog0.

14:38:29,326 INFO - Adapter Version - 6.0

14:38:29,326 INFO  - CTGDIS116I Scripting hook of type prolog0 finished.

14:38:29,357 INFO  - CTGDIS087I Iterating.

14:38:29,357 INFO  - CTGDIS086I No iterator in AssemblyLine, will run single pass only.

14:38:29,357 INFO  - CTGDIS092I Using runtime provided entry as working entry (first pass only).

14:38:29,373 INFO  - == createDBLogin SQLSTROUT == Server: Msg 15025, Level 16. The login shUserTest20 already exist.

14:38:29,373 DEBUG - AddOnly OK Hook

14:38:29,373 INFO  - CTGDIS088I Finished iterating.

14:38:29,373 INFO  - CTGDIS100I Printing the Connector statistics.

14:38:29,373 INFO  -  [Add] Add:1

14:38:29,373 INFO  - CTGDIS104I Total: Add:1.

14:38:29,373 INFO  - CTGDIS101I Finished printing the Connector statistics.

14:38:29,373 INFO  - CTGDIS080I Terminated successfully (0 errors).

 

IN JDBC connector Commit state is "After every database operation", I tried to change it to manual and put con.commit(); in the script, however it still does the samething "The server failed to resume the transaction", 

I have also tried to change sqljdbc driver to version 3.0 and version 2.0 from version 4.0.

 

I have tried to read microsoft blog regarding this error, however i am unable to find anything wrong on it

http://blogs.msdn.com/b/jdbcteam/archive/2009/02/24/the-server-failed-to-resume-the-transaction-why.aspx

 

Regards,

Willor

Updated on 2014-02-12T08:01:28Z at 2014-02-12T08:01:28Z by willor
  • mark99
    mark99
    26 Posts

    Re: ISIM 6.0 (Custom Adapter) TDI hook Stored Procedure calling

    ‏2014-02-12T12:54:53Z  

    Hi

     

    I don't now if it will help you

    I use a sybase server and had some problems using chained en unchained mode

    In the advance connector parameters I had to set  commit mode on manual 

    And I had to put the following in the after initialize hook 

    var sybaseConnection=thisConnector.connector.getConnection();
    sybaseConnection.setAutoCommit(true);
  • WWilliam
    WWilliam
    12 Posts

    Re: ISIM 6.0 (Custom Adapter) TDI hook Stored Procedure calling

    ‏2014-02-13T02:10:04Z  

    Hi Mark, Thanks for your help!,

    it finally able to call stored procedure correctly after i set autoCommit(true)

    I thought that the database transactions are committed automatically when I set the commit mode into "After every database operation"

     

    Updated on 2014-02-13T02:10:27Z at 2014-02-13T02:10:27Z by WWilliam