cl_sql_statement code sample

[Former Member]

Hi,

Somebody have a code sample of cl_sql_statement <b>using host variables</b>?

The program below raise a dump error.

REPORT  zdarley_dyn_sql.

DATA: v_name1 TYPE kna1-name1 VALUE '%SAP%',
      v_mandt TYPE kna1-mandt.

DATA: o_adapter   TYPE REF TO cl_sql_statement,
      o_result    TYPE REF TO cl_sql_result_set,
      o_ref_kna1  TYPE REF TO data,
      o_ref_mandt TYPE REF TO data,
      o_ref_name1 TYPE REF TO data,
      wa_kna1     TYPE kna1.

v_mandt = sy-mandt.

GET REFERENCE OF v_mandt INTO o_ref_mandt.
GET REFERENCE OF v_name1 INTO o_ref_name1.
GET REFERENCE OF wa_kna1 INTO o_ref_kna1.

CREATE OBJECT o_adapter.

o_adapter->set_param( o_ref_mandt ).
o_adapter->set_param( o_ref_name1 ).

o_result = o_adapter->execute_query( 'select * from kna1 where mandt = v_mandt and upper(name1) like :v_name1' ).

o_result->set_param_struct( o_ref_kna1 ).

WHILE o_result->next( ) > 0.

  WRITE: / wa_kna1-kunnr, wa_kna1-name1.

ENDWHILE.

[Former Member]

Hello Darley,

Look at the following code. This may be helpful.

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. In case of the default
*  connection this can be omitted.
*---------------------------------------------------------------------*
FORM disconnect
  USING   p_con_ref TYPE REF TO cl_sql_connection
  RAISING cx_sql_exception.

  DATA: l_con_name TYPE dbcon-con_name.

  l_con_name = p_con_ref->get_con_name( ).
  CHECK l_con_name <> cl_sql_connection=>c_default_connection.
  PERFORM trace_0 USING 'CLOSE CONNECTION' l_con_name.
  p_con_ref->close( ).
  PERFORM trace_result USING l_con_name 'closed'.

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_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 l_wa INTO l_dref.
  l_prepstmt_ref->set_param_struct( l_dref ).

* execute the statement 10 times, this means inserting 10 rows
  DO 10 TIMES.
    l_wa-col1 = sy-index.
    l_wa-col2 = sy-index.
    PERFORM trace_2 USING 'EXECUTE_UPDATE' l_stmt l_wa-col1 l_wa-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

Rgds,

Naren

[Former Member]

I found the error:

Host variables are identified by ?

I just replace

o_result = o_adapter->execute_query( 'select * from kna1 where mandt = :v_mandt and upper(name1) like :v_name1' ).

by

o_result = o_adapter->execute_query( 'select * from kna1 where mandt = ? and upper(name1) like ?' ).

then works.

thanks.

Darley

[Former Member]

Hi,

Is it possible to use set_param for statements like call procedure in execute_query?

Ex:

GET REFERENCE OF sales_tab INTO d_ref.

       stmt_ref->set_param(

                   EXPORTING

                     data_ref = d_ref

                     inout    = cl_sql_statement=>c_param_in

                 ).

* execute the statement

       DATA query      TYPE string.

       query = |'call prakashk.create_sales( ? )' |.

       res_ref = stmt_ref->execute_query( query ).

When i run this, i get exception (sy-subrc is 8). can anyone explain me what is the problem or how can i set input param for the call procedure statement ?

Thanks,

Prakash K

[Former Member]

I have some doubt, where i have to call EXECUTE_UPDATE (Insert Statement .....) in loop for many times. This statement is inserting record directly in HANA database table.

Now i want to remove this loop because the performance is drastically impacted during the loop exection. Is there any way to transfer huge amount of data from abap internal table in sap hana database table without using loop?