1

I am trying to call my Teradata stored procedure using simpleJDBCcall but I always get ZERO result though data is available in my table. I tried to debug but no luck. here is my stored procedure. is there any thing I am doing wrong here?

REPLACE PROCEDURE MYPROC
    (
        IN ID VARCHAR(50)
         
    )
    DYNAMIC RESULT SETS 1
    MAIN : BEGIN
        DECLARE ltype VARCHAR(256);
        
        DECLARE mainSql  VARCHAR (5000);
        DECLARE emptySql VARCHAR(5000);
        DECLARE mainCur CURSOR WITH RETURN ONLY FOR mainStmt;
        DECLARE emptyCur CURSOR WITH RETURN ONLY FOR emptyStmt;
        
    
    
        SET mainSql =   'LOCKING ROW FOR ACCESS SELECT * FROM MYTABLE where ID = ''' || ID || ''';'; 
        
        SET emptySQL = 'SELECT NULL AS ID;';
                            
   
        PREPARE mainStmt FROM mainSql;
        PREPARE emptyStmt FROM emptySql;    
        
        BEGIN TRANSACTION;
    
            SET statementNo = 100;
              OPEN mainCur;
    
        END TRANSACTION;
    END MAIN;

and here is my simpleJDBCCall

 simpleJdbcCall.withProcedureName("MYPROC").returningResultSet("mainCur",
                BeanPropertyRowMapper.newInstance(MYCLASS.class) ).declareParameters(new SqlParameter(ID, Types.VARCHAR) );
        SqlParameterSource namedParameters = new MapSqlParameterSource("ID", transId);

        try {
            Map<String, Object> out = simpleJdbcCall.execute(namedParameters);
            System.out.println(out);
        } catch (Exception e) {
            System.err.println(e);
        }
user1591156
  • 1,945
  • 4
  • 18
  • 31
  • The `END TRANSACTION;` closes the cursor. Why do you have a multi-statement transaction here? Get rid of the `BEGIN TRANSACTION;` as well. – Fred Mar 11 '21 at 16:18
  • @Fred, Thanks for your help, removing ```END TRANSACTION & BEGIN TRANSACTION``` works. Do you know which scenario we need to use ```END TRANSACTION & BEGIN TRANSACTION```? Since this ```stored procedure``` wrote 10 years ago and my old code using Spring 2.x works BUT in recent time when i upgraded my Technology to use Springboot then it does not work with ```END TRANSACTION & BEGIN TRANSACTION```.. do you know why? – user1591156 Mar 15 '21 at 19:11
  • Explicit BT / ET are used mainly when there are multiple inserts, updates, and/or deletes being done and you want to be sure either all of them complete or all of them are rolled back. I can explain why it was failing now; can't explain how it appeared to work before. – Fred Mar 15 '21 at 20:38

0 Answers0