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

SuhaSaha — Mon, 26 Oct 2009 15:06:18 GMT

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

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

ThomasZloch — Mon, 26 Oct 2009 15:46:45 GMT

I have no clue, but if you don't get an answer here, you could try again in forums "ABAP performance" or "SAP on <insert your DB here>", there might be better experts in this matter.

Thomas

topic Re: Calling Stored Proc. Explicitly(using EXECUTE stmt) v/s Using ADBC classes in Application Development and Automation Discussions

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

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