Native SQL Exceptions

[GrahamRobbo]

Hi there,

I am finding some anomalies when trying to handle exceptions with the ADBC native SQL interface and wondered if anyone else has experienced these issues.

I have built a native SQL interface to perform SQL queries via RFC - a bit like the RFC_READ_TABLE function module but with native SQL. ( I know - but trust me I need to do it this way.)

So here is a simple code fragment that shows what I want to do.

  DATA:
         lv_stmt         TYPE  string,
         lo_exc          TYPE REF TO cx_root,
         lo_stmt         TYPE REF TO cl_sql_statement,
         lo_result       TYPE REF TO cl_sql_result_set,
         lo_itab         TYPE REF TO data,
         lv_row_count    TYPE i,
         lv_xmldata      TYPE xstring.
 
   FIELD-SYMBOLS:  <itab>   TYPE ANY TABLE.
 
   TYPES:
     BEGIN OF result_struct,
       mandt TYPE t000-mandt,
       mtext TYPE t000-mtext,
       ort01 TYPE t000-ort01,
     END OF result_struct.
 
   lv_stmt = 'SELECT MANDT, MTEXT, ORT01 FROM T000'.
 
   TRY.
       CREATE OBJECT lo_stmt.
 
       lo_result = lo_stmt->execute_query( lv_stmt ).
 
       CREATE DATA lo_itab TYPE TABLE OF result_struct.
 
       lo_result->set_param_table( lo_itab ).
 
       lv_row_count = lo_result->next_package( ).
 
     CATCH cx_sql_exception INTO lo_exc.
       MESSAGE lo_exc TYPE 'I' DISPLAY LIKE 'E'.
       RETURN.
   ENDTRY.
 
   ASSIGN lo_itab->* TO <itab>.
 
   CALL TRANSFORMATION id
     SOURCE table = <itab>
     RESULT XML lv_xmldata.
 
   CALL FUNCTION 'DISPLAY_XML_STRING'
     EXPORTING
       xml_string = lv_xmldata. 

This is all pretty straight forward. I am using the ADBC interface to pass (and parse) the SQL statement and return the results into the itab that is referenced by lo_itab. Then I transform the results into XML and display them.

If I change the SQL statement to something invalid - like ’SELECT JUNK FROM T000’ - exception CX_SQL_EXCEPTION is raised and I can display the error details. The interesting thing is that the exception is triggered at a different place depending upon the database I am running on.

If I am running on MS SQL Server the exception is triggered on the call to the EXECUTE_QUERY method of the CL_SQL_STATEMENT class.

But on an Oracle DB the exception is triggered on the later call to the NEXT_PACKAGE method of the CL_SQL_RESULT_SET class.

*Note - these are the only databases I have available to me at the moment so if someone can try on some others I would appreciate it.

In my simple example this doesn’t make a lot of difference as we can still trap the DB-specific error and return it. But I can’t expect to know exactly what SQL query will be passed so that means I can’t hardcode data structures to hold the results of the query - I need to dynamically create the itab to hold the result set.

So my code will look more like this…

DATA:
         lv_stmt         TYPE  string,
         lo_exc          TYPE REF TO cx_root,
         lo_stmt         TYPE REF TO cl_sql_statement,
         lr_param        TYPE REF TO data,
         lo_result       TYPE REF TO cl_sql_result_set,
         lo_structdescr  TYPE REF TO cl_abap_structdescr,
         lo_tabledescr   TYPE REF TO cl_abap_tabledescr,
         lo_itab         TYPE REF TO data,
         lv_row_count    TYPE i,
         lv_xmldata      TYPE xstring.

FIELD-SYMBOLS:  <itab>   TYPE ANY TABLE.

   lv_stmt = 'SELECT MANDT, MTEXT, ORT01 FROM T000'.

TRY.
CREATE OBJECT lo_stmt.
       lo_result = lo_stmt->execute_query( lv_stmt ).
TRY.
           lo_structdescr ?= cl_abap_structdescr=>describe_by_data_ref(
             lo_result->get_struct_ref( lo_result->get_metadata( ) ) ).

           lo_tabledescr = cl_abap_tabledescr=>create( lo_structdescr ).

CREATE DATA lo_itab TYPE HANDLE lo_tabledescr.

CATCH cx_sy_struct_attributes cx_sy_table_creation cx_sy_create_data_error.
CREATE DATA lo_itab TYPE stringtab.
ENDTRY.

       lo_result->set_param_table( lo_itab ).

       lv_row_count = lo_result->next_package( ).

CATCH cx_sql_exception INTO lo_exc.
MESSAGE lo_exc TYPE 'I' DISPLAY LIKE 'E'.
RETURN.
ENDTRY.

ASSIGN lo_itab->* TO <itab>.

CALL TRANSFORMATION id
     SOURCE table = <itab>
     RESULT XML lv_xmldata.

CALL FUNCTION 'DISPLAY_XML_STRING'
EXPORTING
       xml_string = lv_xmldata.

Now I can pass in any SQL query and the itab will be dynamically created to hold the query results. For example if I change my SQL query to ’SELECT MANDT AS CLIENT, MTEXT AS DESCRIPTION, ORT01 AS CITY FROM T000’ it will dynamically create the itab with columns CLIENT, DESCRIPTION and CITY to hold the results. This works great!

But now the different places the CX_SQL_EXCEPTION is raised cause me a problem on the Oracle DB.

Because the exception is not triggered until much later in the code I need to create the itab to hold the results - even though it will never be used. This is done by the CREATE DATA lo_itab TYPE stringtab statement that is in executed in the CATCH block when there is an exception calling the RTTC services to create the dynamic itab.

When I call the NEXT_PACKAGE method I still get the CX_SQL_EXCEPTION raised, but now it is flagged as an internal error instead of a database error so the DB-specific details are not populated in the exception.

It appears that the earlier call to the GET_METADATA method of the CL_SQL_RESULT_SET class has somehow changed the state of the instance.

I would appreciate any and all ideas on this.

Cheers

Graham Robbo

[former_member182048]

Hi Graham

I recently wrote similar code, granted it was HANA not Oracle or MSSql, no issues though

only significant difference is in bold

  DATA:

   lr_metadata    TYPE REF TO data,

   lr_tabletype   TYPE REF TO cl_abap_tabledescr.

  TRY.

      "execute SQL statement

      CREATE OBJECT mo_sql_statement.

      mo_sql_result = mo_sql_statement->execute_query( mv_sql_statement ).

*    "get result structure definition from metadata

      mt_metadata =  mo_sql_result->get_metadata( ).

      lr_metadata = mo_sql_result->get_struct_ref( md_tab = mt_metadata p_strict = abap_false ).

      mo_rtti ?= cl_abap_typedescr=>describe_by_data_ref( lr_metadata  ).

      "create table type to hold result

      lr_tabletype   = cl_abap_tabledescr=>create( p_line_type  = mo_rtti

                                                   p_table_kind = cl_abap_tabledescr=>tablekind_std ).

      CREATE DATA ro_results TYPE HANDLE lr_tabletype.

      "get results

      mo_sql_result->set_param_table( itab_ref = ro_results ).

      mo_sql_result->next_package( ).

      mo_sql_result->close( ).

    CATCH cx_root.

*      CLEAR e_result.

  ENDTRY.

hth

jsp

[GrahamRobbo]

Thanks John,

can you confirm on HANA that an invalid SQL statement raises an exception when you call the CL_SQL_STATEMENT->EXECUTE_QUERY method?

Cheers

Graham Robbo

[former_member182048]

it does, put a catch in between statements

[GrahamRobbo]

Looking more and more like it is just an issue with Oracle DB.

[custodio_deoliveira]

Hi Robbo,

Just tested in MaxDB and got the error on CL_SQL_STATEMENT->EXECUTE_QUERY( ).

Cheers,

Custodio

[GrahamRobbo]

Thanks mate - looking more and more like it is just Oracle that has the issue.

[GrahamRobbo]

Thanks for you help with this guys. I will log a support call. In the meantime I have changed my code so that it redoes the complete ADBC setup and method calls to trap Oracle Db errors. The code now looks like this...

   DATA:
         lv_stmt         TYPE  string,
         lo_exc          TYPE REF TO cx_root,
         lo_stmt         TYPE REF TO cl_sql_statement,
         lr_param        TYPE REF TO data,
         lo_result       TYPE REF TO cl_sql_result_set,
         lo_structdescr  TYPE REF TO cl_abap_structdescr,
         lo_tabledescr   TYPE REF TO cl_abap_tabledescr,
         lo_itab         TYPE REF TO data,
         lv_row_count    TYPE i,
         lv_xmldata      TYPE xstring.

   FIELD-SYMBOLS:  <itab>   TYPE ANY TABLE.

   lv_stmt = 'SELECT MANDT, MTEXT, ORT01 FROM T000'.

   TRY.
       CREATE OBJECT lo_stmt.

       lo_result = lo_stmt->execute_query( lv_stmt ).

       TRY.
           lo_structdescr ?= cl_abap_structdescr=>describe_by_data_ref(
             lo_result->get_struct_ref( lo_result->get_metadata( ) ) ).

           lo_tabledescr = cl_abap_tabledescr=>create( lo_structdescr ).

           CREATE DATA lo_itab TYPE HANDLE lo_tabledescr.

         CATCH cx_sy_struct_attributes cx_sy_table_creation cx_sy_create_data_error INTO lo_exc.
           IF sy-dbsys = 'ORACLE'. "Redo ABDC call to get Oracle DB error message
             TRY.
                 CREATE OBJECT lo_stmt.
                 lo_result = lo_stmt->execute_query( lv_stmt ).
                 CREATE DATA lo_itab TYPE stringtab.
                 lo_result->set_param_table( lo_itab ).
                 lv_row_count = lo_result->next_package( ).
               CATCH cx_sql_exception INTO lo_exc.
             ENDTRY.
           ENDIF.

           MESSAGE lo_exc TYPE 'I' DISPLAY LIKE 'E'.
           RETURN.
       ENDTRY.

       lo_result->set_param_table( lo_itab ).

       lv_row_count = lo_result->next_package( ).

     CATCH cx_sql_exception INTO lo_exc.
       MESSAGE lo_exc TYPE 'I' DISPLAY LIKE 'E'.
       RETURN.
   ENDTRY.

   ASSIGN lo_itab->* TO <itab>.

   CALL TRANSFORMATION id
     SOURCE table = <itab>
     RESULT XML lv_xmldata.

   CALL FUNCTION 'DISPLAY_XML_STRING'
     EXPORTING
       xml_string = lv_xmldata.

Cheers

Graham Robbo