SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Demo Program for Using ADBC API



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> SQL and Database Changes
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1639

PostPosted: Mon Apr 07, 2008 5:26 pm    Post subject: Demo Program for Using ADBC API Reply with quote

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.

************************************************************************
START-OF-SELECTION.
************************************************************************

  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.


************************************************************************
END-OF-SELECTION.
************************************************************************


************************************************************************
* 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.

* create a prepared statement object
  l_prepstmt_ref = p_con_ref->prepare_statement( l_stmt ).

* 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.

  PERFORM trace_0 USING p_method p_stmt.
  WRITE: '<== (?1 =', p_v1 NO-GAP, ')'.                     "#EC NOTEXT

ENDFORM.                                                    "trace_1



*---------------------------------------------------------------------*
*  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.

  PERFORM trace_0 USING p_method p_stmt.
  WRITE: '<== (?1 =', p_v1, ', ?2 =', p_v2 NO-GAP, ')'.     "#EC NOTEXT

ENDFORM.                                                    "trace_2


*---------------------------------------------------------------------*
*  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.

  WRITE: / 'NEXT' COLOR COL_KEY,
           '==> {' NO-GAP, (3) p_v1 NO-GAP, ',',
           p_v2 NO-GAP, '}'.                                "#EC NOTEXT

ENDFORM.                    "trace_next_vars


*---------------------------------------------------------------------*
*  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.

  WRITE: / 'NEXT' COLOR COL_KEY,
           '==> {' NO-GAP, (3) p_struct-col1 NO-GAP, ',',
           p_struct-col2 NO-GAP, '}'.                       "#EC NOTEXT

ENDFORM.                    "trace_next_struct



*---------------------------------------------------------------------*
*  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'.

ENDFORM.                    "update_rows
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> SQL and Database Changes All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
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.