Posted: Mon Apr 07, 2008 5:26 pm Post subject: Demo Program for Using ADBC API
SQL-оператор EXEC позволяет разработчикам выполнять команды SQL, ограниченные одной базой данных и не представленные в функциональности Open SQL. Таким образом, они могут обращаться к таблицам БД, не входящим в ABAP-словарь. Однако на практике имеется ряд ограничений, поскольку такой подход позволяет выполнять только статичные SQL-операторы, снижает возможность обработки ошибок и допускает возникновение проблем в случае многочисленных подключений к базам данных. Интерфейс ABAP Database Connectivity (ADBC) был разработан как дополнение к EXEC SQL и представляет собой объектно-ориентированный API уровня вызовов, устраняющий эти ограничения и обеспечивающий доступ Native SQL ко всем функциональным возможностям SQL базы данных.
Code:
*&---------------------------------------------------------------------*
*& Report ADBC_DEMO *
*& *
*&---------------------------------------------------------------------*
*& This is a demo program that demonstrates the usage of the ADBC
*& API consisting of the classes CL_SQL_CONNECTION, CL_SQL_STATEMENT,
*& CL_SQL_PREPARED_STATEMENT and CL_SQL_RESULT_SET.
*&
*& The program creates a test table 'ADBC_DEMO__' and executes some
*& DML statemens and queries on that table. As its output the program
*& traces the execution of the ADBC methods and their results into
*& an ABAP list which will be displayed as the program's result.
*&
*& Before the program finishes the test table is dropped again.
*&
*& The program may run against the application server's default
*& connection or against any other database connection that must have
*& been defined in table DBCON before. This is controlled by the
*& input parameter CON_NAME. If this parameter is left initial then
*& the program runs against the default connection, otherwise it
*& tries to connect and execute on the connection specified.
*&
*&---------------------------------------------------------------------*
REPORT adbc_demo.
************************************************************************
* Parameters and Select Options
************************************************************************
PARAMETERS:
con_name TYPE dbcon-con_name.
************************************************************************
* Type definitions
************************************************************************
TYPES:
BEGIN OF adbc_demo_t,
col1 TYPE i,
col2(4) TYPE n,
END OF adbc_demo_t.
************************************************************************
* Global constants and variables
************************************************************************
CONSTANTS:
c_tabname TYPE string VALUE `ADBC_DEMO__`,
c_coldefs TYPE string VALUE `(COL1 integer, COL2 char(4))`.
DATA:
sqlerr_ref TYPE REF TO cx_sql_exception,
con_ref TYPE REF TO cl_sql_connection.
TRY.
PERFORM:
connect USING con_name con_ref,
create_table USING con_ref c_tabname c_coldefs,
insert_rows USING con_ref,
select_into_variables USING con_ref,
update_rows USING con_ref,
select_into_structure USING con_ref,
delete_rows USING con_ref,
select_into_table USING con_ref,
drop_table USING con_ref c_tabname,
disconnect USING con_ref.
CATCH cx_sql_exception INTO sqlerr_ref.
PERFORM handle_sql_exception USING sqlerr_ref.
ENDTRY.
************************************************************************
* Forms
************************************************************************
*----------------------------------------------------------------------*
* FORM connect
*----------------------------------------------------------------------*
* Connects to the database specified by the logical connection name
* P_CON_NAME which is expected to be specified in table DBCON. In case
* of success the form returns in P_CON_REF a reference to a connection
* object of class CL_SQL_CONNECTION.
*----------------------------------------------------------------------*
* --> P_CON_NAME logical connection name
* <-- P_CON_REF reference to a CL_SQL_CONNECTION object
*----------------------------------------------------------------------*
FORM connect USING p_con_name TYPE dbcon-con_name
p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.
* if CON_NAME is not initial then try to open the connection, otherwise
* create a connection object representing the default connection.
IF p_con_name IS INITIAL.
CREATE OBJECT p_con_ref.
ELSE.
PERFORM trace_0 USING 'GET_CONNECTION' p_con_name.
p_con_ref = cl_sql_connection=>get_connection( p_con_name ).
PERFORM trace_result USING p_con_name 'opened'.
ENDIF.
ENDFORM. " connect
*---------------------------------------------------------------------*
* FORM create_table
*---------------------------------------------------------------------*
* Creates or replaces the test table C_TABNAME with columns COL1
* (integer) and COL2 (CHAR(4)).
*---------------------------------------------------------------------*
FORM create_table
USING p_con_ref TYPE REF TO cl_sql_connection
p_tabname TYPE string
p_coldefs TYPE string
RAISING cx_sql_exception.
DATA:
l_sqlerr_ref TYPE REF TO cx_sql_exception,
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement.
* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).
* create the statement string
CONCATENATE
'create table' p_tabname p_coldefs
INTO l_stmt SEPARATED BY space. "#EC NOTEXT
* execute the DDL command; catch the exception in order to handle the
* case if the table already exists
TRY.
PERFORM trace_0 USING 'EXECUTE_DDL' l_stmt.
l_stmt_ref->execute_ddl( l_stmt ).
CATCH cx_sql_exception INTO l_sqlerr_ref.
IF l_sqlerr_ref->dbobject_exists = 'X'
OR l_sqlerr_ref->internal_error = 1024.
* table already exists => drop it and try it again
WRITE:
/ c_tabname,
'already exists => drop table and try again'. "#EC NOTEXT
PERFORM:
drop_table USING p_con_ref p_tabname,
create_table USING p_con_ref p_tabname p_coldefs.
ELSE.
RAISE EXCEPTION l_sqlerr_ref.
ENDIF.
ENDTRY.
IF l_sqlerr_ref IS INITIAL.
PERFORM trace_result USING c_tabname 'created'.
ENDIF.
ENDFORM. " create_table
*---------------------------------------------------------------------*
* FORM disconnect
*---------------------------------------------------------------------*
* Disconnect from the given connection except for the default
* connection.
*---------------------------------------------------------------------*
FORM disconnect
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.
* if a non-default connection has been opened we close it now
IF p_con_ref->get_con_name( ) IS NOT INITIAL.
data: l_con_name type dbcon-con_name.
l_con_name = p_con_ref->get_con_name( ).
PERFORM trace_0 USING 'CLOSE CONNECTION' l_con_name.
p_con_ref->close( ).
PERFORM trace_result USING l_con_name 'closed'.
ENDIF.
ENDFORM. "disconnect
*---------------------------------------------------------------------*
* FORM drop_table
*---------------------------------------------------------------------*
* Drops the test table C_TABNAME.
*---------------------------------------------------------------------*
FORM drop_table
USING p_con_ref TYPE REF TO cl_sql_connection
p_tabname TYPE string
RAISING cx_sql_exception.
DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement.
* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).
* create the statement string
CONCATENATE
'drop table' p_tabname
INTO l_stmt SEPARATED BY space.
* execute the DDL command
PERFORM trace_0 USING 'EXECUTE_DDL' l_stmt.
l_stmt_ref->execute_ddl( l_stmt ).
PERFORM trace_result USING c_tabname 'dropped'.
ENDFORM. "drop_table
*---------------------------------------------------------------------*
* FORM delete_rows
*---------------------------------------------------------------------*
* Deletes all rows from the test table C_TABNAME having COL2 = 1000.
*---------------------------------------------------------------------*
FORM delete_rows
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.
DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_col2 TYPE adbc_demo_t-col2,
l_row_cnt TYPE i.
* create the statement string
CONCATENATE
'delete from' c_tabname 'where COL2 = ?'
INTO l_stmt SEPARATED BY space. "#EC NOTEXT
* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).
* bind input variable
GET REFERENCE OF l_col2 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
* set input value and execute the statement
l_col2 = 1000.
PERFORM trace_1 USING 'EXECUTE_UPDATE' l_stmt l_col2.
l_row_cnt = l_stmt_ref->execute_update( l_stmt ).
PERFORM trace_result USING l_row_cnt 'rows deleted'.
ENDFORM. "delete_rows
*---------------------------------------------------------------------*
* FORM handle_sql_exception
*---------------------------------------------------------------------*
* Write appropriate error messages when a SQL exception has occured
*---------------------------------------------------------------------*
* --> P_SQLERR_REF reference to a CX_SQL_EXCEPTION object
*---------------------------------------------------------------------*
FORM handle_sql_exception
USING p_sqlerr_ref TYPE REF TO cx_sql_exception.
FORMAT COLOR COL_NEGATIVE.
IF p_sqlerr_ref->db_error = 'X'.
WRITE: / 'SQL error occured:', p_sqlerr_ref->sql_code,
/ p_sqlerr_ref->sql_message. "#EC NOTEXT
ELSE.
WRITE:
/ 'Error from DBI (details in dev-trace):',
p_sqlerr_ref->internal_error. "#EC NOTEXT
ENDIF.
ENDFORM. "handle_sql_exception
*---------------------------------------------------------------------*
* FORM insert_rows
*---------------------------------------------------------------------*
* Inserts 10 rows into the test table with columns values COL1 and
* COL2 set to SY-INDEX each where SY-INDEX runs from 1 to 10. Because
* the same (INSERT-)statement has to be executed 10 times (only
* differing in its input values) we make use of a prepared statement
* object (instance of CL_SQL_PREPARED_STATEMENT) and prepare the
* statement only once but execute it 10 times.
*---------------------------------------------------------------------*
FORM insert_rows
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.
DATA:
l_stmt TYPE string,
l_prepstmt_ref TYPE REF TO cl_sql_prepared_statement,
l_dref TYPE REF TO data,
l_col2 TYPE adbc_demo_t-col2,
l_wa TYPE adbc_demo_t.
* create the statement string
CONCATENATE
'insert into' c_tabname 'values (?,?)'
INTO l_stmt SEPARATED BY space.
* bind input variables
GET REFERENCE OF sy-index INTO l_dref.
l_prepstmt_ref->set_param( l_dref ).
GET REFERENCE OF l_col2 INTO l_dref.
l_prepstmt_ref->set_param( l_dref ).
* execute the statement 10 times, this means inserting 10 rows
DO 10 TIMES.
l_col2 = sy-index.
PERFORM trace_2 USING 'EXECUTE_UPDATE' l_stmt sy-index l_col2.
l_prepstmt_ref->execute_update( ).
ENDDO.
PERFORM trace_result USING 10 'rows inserted'.
* don't forget to close the prepared statement in order to free
* resources on the database
l_prepstmt_ref->close( ).
ENDFORM. "insert_rows
*---------------------------------------------------------------------*
* FORM select_into_variables
*---------------------------------------------------------------------*
* Selects some rows from the test table and fetches the result rows
* columnwise in appropriate output variables.
*---------------------------------------------------------------------*
FORM select_into_variables
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.
DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_res_ref TYPE REF TO cl_sql_result_set,
l_col1 TYPE adbc_demo_t-col1,
l_col2 TYPE adbc_demo_t-col2.
* create the query string
CONCATENATE
'select * from' c_tabname 'where COL1 >= ?'
INTO l_stmt SEPARATED BY space. "#EC NOTEXT
* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).
* bind input variable
GET REFERENCE OF l_col1 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
* set the input value and execute the query
l_col1 = 6.
PERFORM trace_1 USING 'EXECUTE_QUERY' l_stmt l_col1.
l_res_ref = l_stmt_ref->execute_query( l_stmt ).
* set output variables
GET REFERENCE OF l_col1 INTO l_dref.
l_res_ref->set_param( l_dref ).
GET REFERENCE OF l_col2 INTO l_dref.
l_res_ref->set_param( l_dref ).
* loop over the result set and trace the output rows
WHILE l_res_ref->next( ) > 0.
PERFORM trace_next_vars USING l_col1 l_col2.
ENDWHILE.
PERFORM trace_result USING l_res_ref->rows_fetched
'rows fetched'.
* don't forget to close the result set object in order to free
* resources on the database
l_res_ref->close( ).
ENDFORM. "select_into_variables
*---------------------------------------------------------------------*
* FORM select_into_structure
*---------------------------------------------------------------------*
* Selects some rows from the test table and fetches the result rows
* in a structure that corresponds to the queries select list columns.
*---------------------------------------------------------------------*
FORM select_into_structure
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.
DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_res_ref TYPE REF TO cl_sql_result_set,
l_col1 TYPE adbc_demo_t-col1,
l_wa TYPE adbc_demo_t.
* create the query string
CONCATENATE
'select * from' c_tabname 'where COL1 > ?'
INTO l_stmt SEPARATED BY space. "#EC NOTEXT
* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).
* bind input variable
GET REFERENCE OF l_col1 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
* set the input value and execute the query
l_col1 = 5.
l_res_ref = l_stmt_ref->execute_query( l_stmt ).
PERFORM trace_1 USING 'EXECUTE_QUERY' l_stmt l_col1.
* set output structure
GET REFERENCE OF l_wa INTO l_dref.
l_res_ref->set_param_struct( l_dref ).
* loop over the result set and trace the output rows
WHILE l_res_ref->next( ) > 0.
PERFORM trace_next_struct USING l_wa.
ENDWHILE.
PERFORM trace_result USING l_res_ref->rows_fetched
'rows fetched'.
* don't forget to close the result set object in order to free
* resources on the database
l_res_ref->close( ).
ENDFORM. "select_into_structure
*---------------------------------------------------------------------*
* FORM select_into_table
*---------------------------------------------------------------------*
* Selects some rows from the test table and fetches the result rows
* into an internal table whose row structure corresponds to the
* queries select list columns.
*---------------------------------------------------------------------*
FORM select_into_table
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.
DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_res_ref TYPE REF TO cl_sql_result_set,
l_col1 TYPE adbc_demo_t-col1,
l_col2 TYPE adbc_demo_t-col2,
l_itab TYPE STANDARD TABLE OF adbc_demo_t,
l_row_cnt TYPE i.
* create the query string
CONCATENATE
'select * from' c_tabname 'where COL1 <= ? and COL2 >= ?'
INTO l_stmt SEPARATED BY space. "#EC NOTEXT
* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).
* bind input variables
GET REFERENCE OF l_col1 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
GET REFERENCE OF l_col2 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
* set the input values and execute the query
l_col1 = 7.
l_col2 = 1.
PERFORM trace_2 USING 'EXECUTE_QUERY' l_stmt l_col1 l_col2.
l_res_ref = l_stmt_ref->execute_query( l_stmt ).
* 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( ).
* display the contents of the output table
PERFORM trace_next_package USING l_itab.
PERFORM trace_result USING l_row_cnt 'rows fetched'.
* don't forget to close the result set object in order to free
* resources on the database
l_res_ref->close( ).
ENDFORM. "select_into_table
*---------------------------------------------------------------------*
* FORM trace_0
*---------------------------------------------------------------------*
* Trace the execution of a statement without input variables.
*---------------------------------------------------------------------*
FORM trace_0
USING p_method TYPE c
p_stmt.
WRITE:
/ p_method COLOR COL_KEY, p_stmt COLOR COL_NORMAL. "#EC NOTEXT
ENDFORM. "trace_0
*---------------------------------------------------------------------*
* FORM trace_1
*---------------------------------------------------------------------*
* Trace the execution of a statement with one input variable.
*---------------------------------------------------------------------*
FORM trace_1
USING p_method TYPE c
p_stmt TYPE string
p_v1.
*---------------------------------------------------------------------*
* FORM trace_2
*---------------------------------------------------------------------*
* Trace the execution of a statement with two input variables
*---------------------------------------------------------------------*
FORM trace_2
USING p_method TYPE c
p_stmt TYPE string
p_v1
p_v2.
*---------------------------------------------------------------------*
* FORM trace_next_vars
*---------------------------------------------------------------------*
* Trace the execution of a NEXT method together with the values
* fetched.
*---------------------------------------------------------------------*
FORM trace_next_vars
USING p_v1 TYPE adbc_demo_t-col1
p_v2 TYPE adbc_demo_t-col2.
*---------------------------------------------------------------------*
* FORM trace_next_struct
*---------------------------------------------------------------------*
* Trace the execution of a NEXT method together with the output
* workarea where the result row was fetched
*---------------------------------------------------------------------*
FORM trace_next_struct
USING p_struct TYPE adbc_demo_t.
*---------------------------------------------------------------------*
* FORM trace_next_package
*---------------------------------------------------------------------*
* Trace the execution of a NEXT_PACKAGE method together with
* the contents of the internal table where the result were returned.
*---------------------------------------------------------------------*
FORM trace_next_package
USING p_itab TYPE table.
FIELD-SYMBOLS:
<l_line> TYPE adbc_demo_t.
WRITE:
/ 'NEXT_PACKAGE' COLOR COL_KEY,
'==> {' . "#EC NOTEXT
LOOP AT p_itab ASSIGNING <l_line>.
IF sy-tabix > 1.
NEW-LINE.
POSITION 20.
ENDIF.
WRITE:
'{' NO-GAP, (3) <l_line>-col1 NO-GAP, ',',
<l_line>-col2 NO-GAP, '}'. "#EC NOTEXT
ENDLOOP.
WRITE: '}'.
ENDFORM. "trace_next_package
*---------------------------------------------------------------------*
* FORM trace_result
*---------------------------------------------------------------------*
* Trace a summary for a statement execution.
*---------------------------------------------------------------------*
FORM trace_result USING p_1
p_2.
FORMAT COLOR COL_TOTAL.
WRITE: / '==>', p_1, p_2.
FORMAT COLOR OFF.
ULINE.
ENDFORM. "trace_result
*---------------------------------------------------------------------*
* FORM update_rows
*---------------------------------------------------------------------*
* Updates COL2 for some rows of the test table.
*---------------------------------------------------------------------*
FORM update_rows
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.
DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_col1 TYPE adbc_demo_t-col1,
l_col2 TYPE adbc_demo_t-col2,
l_row_cnt TYPE i.
* create the statement string
CONCATENATE
'update' c_tabname 'set COL2 = ? where COL1 >= ?'
INTO l_stmt SEPARATED BY space. "#EC NOTEXT
* create a prepared statement object
l_stmt_ref = p_con_ref->create_statement( ).
* bind input variables
GET REFERENCE OF l_col2 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
GET REFERENCE OF l_col1 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
* set input values and execute the statement
l_col1 = 8.
l_col2 = 1000.
PERFORM trace_2 USING 'EXECUTE_UPDATE' l_stmt l_col2 l_col1.
l_row_cnt = l_stmt_ref->execute_update( l_stmt ).
PERFORM trace_result USING l_row_cnt 'rows updated'.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
