Skip to Content
0
Former Member
Oct 26, 2009 at 04:18 PM

Calling Stored Proc. Explicitly(using EXECUTE stmt) v/s Using ADBC classes

190 Views

Dear SAP DB Gurus,

I have encountered a strange problem.

When i call a Stored Procedure using Native SQL i am able to fetch records from the SP (even if the SP has a problem):

TRY.
*   execute SP_IMDL_PERSONINFO
    DATA L_TYPE  LIKE ACCTTY .
    L_TYPE = 'USR'.
    EXEC SQL.
      OPEN C FOR
      execute SP_EXECUTE @ACCTTY = :l_type
    ENDEXEC.
 
    DO.
      EXEC SQL.
        FETCH NEXT C INTO :lstruc-acct,
        :lstruc-acctsta
      ENDEXEC.
      IF SY-SUBRC  0.
        EXIT.
      ENDIF.
      APPEND LSTRUC TO LTAB. 
    ENDDO.
 
    EXEC SQL.
      CLOSE C
    ENDEXEC.
 
    DATA L_LINES TYPE I.
    DESCRIBE TABLE LTAB LINES L_LINES.
    WRITE: L_LINES.
 
ENDTRY.

But if i use SP, i am getting an EXCEPTION:

DATA L_PROC_NAME TYPE CHAR120.
 
  L_PROC_NAME = 'SP_EXECUTE'.
 
  DATA: L_TYPE    LIKE LSTRUC-ACCTTY,
        L_ACCT    LIKE LSTRUC-ACCT,
        L_ACCTSTA LIKE LSTRUC-ACCTSTA.
 
* create a statement object
  L_STMT_REF = P_CON_REF->CREATE_STATEMENT( ).
 
* bind input variables
  L_TYPE = 'USR'.
  GET REFERENCE OF L_TYPE INTO L_DREF.
  L_STMT_REF->SET_PARAM( L_DREF ).
 
* bind output variables
  GET REFERENCE OF L_ACCT INTO L_DREF.
  L_STMT_REF->SET_PARAM( DATA_REF = L_DREF
  INOUT    = CL_SQL_STATEMENT=>C_PARAM_OUT ).
 
  GET REFERENCE OF L_ACCTSTA INTO L_DREF.
  L_STMT_REF->SET_PARAM( DATA_REF = L_DREF
  INOUT    = CL_SQL_STATEMENT=>C_PARAM_OUT ).
 
 
* set the input values and execute the Stored Procedure
  PERFORM TRACE_2 USING 'EXECUTE_PROCEDURE' L_STMT L_COL1 L_COL2.
 
  L_STMT_REF->EXECUTE_PROCEDURE( L_PROC_NAME ).
 
* set output table
  GET REFERENCE OF L_ITAB INTO L_DREF.
  L_RES_REF->SET_PARAM_TABLE( L_DREF ).
 
* get the complete result set
  L_ROW_CNT = L_RES_REF->NEXT_PACKAGE( ).

SQL error occured: 8.114 Error converting data type nvarchar to numeric.

I have used the program ADBC_DEMO as reference for using the ADBC classes.

My question is: Even though the Stored Proc. has an error, the Native SQL does not throw an exception whereas ADBC does.

What can be the reason?

BR,

Suhas

PS: FYI, I have checked in DBCO & the connection type is MSS