Skip to Content
3
Apr 24, 2014 at 05:14 AM

Native SQL Exceptions

1396 Views

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

Attachments

mssql.png (9.4 kB)
ora.png (10.1 kB)
last.png (8.5 kB)