Posted: Mon Apr 07, 2008 5:24 pm Post subject: SQL query tool using ABAP Database Connectivity (ADBC)
Code:
*&---------------------------------------------------------------------*
*& Report ADBC_QUERY *
*& *
*&---------------------------------------------------------------------*
*& *
*& This program realizes a rather simple SQL query tool. There are *
*& some benefits compared to similar tools (e.g. SE16) *
*& *
*& - the table against which the query should run may reside on a *
*& remote database; a connection description of the remote database *
*& must be specified in the connection table DBCON; *
*& *
*& - the table must not necessarily be defined in the ABAP data *
*& dictionary; hence, the program can be used to query the database's*
*& catalogue tables, for example. *
*& *
*& - the queries WHERE condition is free formatted, i.e. it can be *
*& entered as plain text- *
*& *
*& The program takes 3 parameters: *
*& *
*& CON_NAME - the logical connection name as defined in table DBCON; *
*& if this parameter remains initial then the query is *
*& executed against the default connection of the WAS. *
*& SCHEMA - the name of the schema where the table is searched for; *
*& if this parameter remains initial then the table *
*& is expected to be contained in the default schema *
*& associated with the database connection. *
*& TABNAME - the name of the table which is to be queried. *
*& *
*& After a table has been selected and the program is executed it *
*& will display a list of the table's columns together with an input *
*& area where the queries WHERE condition may be specfied in text *
*& form. Before running the query the user may choose the columns to *
*& be selected from the column list displayed. *
*& *
*&---------------------------------------------------------------------*
PROGRAM adbc_query NO STANDARD PAGE HEADING LINE-SIZE 255.
PARAMETERS:
con_name TYPE dbcon-con_name,
schema TYPE adbc_name,
tabname TYPE adbc_name.
************************************************************************
* Global constants and variables
************************************************************************
CONSTANTS:
c_where_lines TYPE i VALUE 5.
DATA:
_qualified_tabname TYPE string,
_con_ref TYPE REF TO cl_sql_connection,
_column_tab TYPE adbc_tabcol_descr_tab,
_col_cnt LIKE sy-tabix,
_subrc LIKE sy-subrc,
_markfield TYPE flag,
_selectable TYPE flag,
_where(80) TYPE c,
_first_col_linno LIKE sy-linno,
_first_where_linno LIKE sy-linno,
_last_where_linno LIKE sy-linno,
BEGIN OF _where_save_tab OCCURS 20,
line LIKE _where,
END OF _where_save_tab.
* only system adminstrators are allowed to run this program
AUTHORITY-CHECK OBJECT 'S_ADMI_FCD'
ID 'S_ADMI_FCD' FIELD 'ST0M'.
IF sy-subrc <> 0.
MESSAGE
'Sorry, you are not authorized to run this program' "#EC NOTEXT
TYPE 'I'.
LEAVE PROGRAM.
ENDIF.
************************************************************************
AT USER-COMMAND.
************************************************************************
CASE sy-ucomm.
WHEN 'RUN_QUERY'.
* read the specified WHERE condition and run the query
PERFORM read_input_and_run_query.
WHEN 'ALL_COLS'.
* select all_columns
PERFORM set_checkboxes USING 'X'.
WHEN 'NO_COLS'.
* deselect all columns
PERFORM set_checkboxes USING space.
WHEN 'MORE_LINES'.
* add some lines to the WHERE condition's input area
PERFORM more_lines.
ENDCASE.
***********************************************************************
* 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 get_connection USING p_con_name TYPE dbcon_name
p_con_ref TYPE REF TO cl_sql_connection.
DATA:
sqlerr_ref TYPE REF TO cx_sql_exception.
* create a connection object
IF p_con_name IS INITIAL.
* default connection
CREATE OBJECT p_con_ref.
ELSE.
* open a secondary connection
TRY.
p_con_ref = cl_sql_connection=>get_connection( p_con_name ).
CATCH cx_sql_exception INTO sqlerr_ref.
PERFORM handle_sql_exception(adbc_demo) USING sqlerr_ref.
RETURN.
ENDTRY.
ENDIF.
ENDFORM. "get_connection
*---------------------------------------------------------------------*
* FORM execute_query
*---------------------------------------------------------------------*
* Read the WHERE condition from the input area, create a SQL
* statement and execute it. If the query was executed successfully
* the form returns a pointer to CL_SQL_RESULT_SET object which might
* be used afterwards to fetch the rows of the result set.
*---------------------------------------------------------------------*
* --> P_COLUMNS the list of columns to be selected
* <-- P_SQL the SQL statement created
* <-- P_RES_REF a reference to the result set object
*---------------------------------------------------------------------*
FORM execute_query USING p_columns TYPE adbc_column_tab
p_sql TYPE string
p_res_ref TYPE REF TO cl_sql_result_set.
DATA:
stmt_ref TYPE REF TO cl_sql_statement,
sqlerr_ref TYPE REF TO cx_sql_exception,
linno LIKE sy-linno.
FIELD-SYMBOLS:
<colname> LIKE LINE OF p_columns.
* concatenate selected columns into sql
p_sql = `SELECT`.
LOOP AT p_columns ASSIGNING <colname>.
IF sy-tabix > 1.
CONCATENATE p_sql ',' INTO p_sql.
ENDIF.
CONCATENATE p_sql <colname> INTO p_sql SEPARATED BY space.
ENDLOOP.
* concatenate from-clause
CONCATENATE p_sql 'FROM' _qualified_tabname INTO p_sql
SEPARATED BY space.
linno = _first_where_linno.
WHILE linno <= _last_where_linno.
READ LINE linno FIELD VALUE _where INTO _where.
IF sy-subrc = 0 AND _where IS NOT INITIAL.
CONCATENATE p_sql _where INTO p_sql SEPARATED BY space.
ENDIF.
ADD 1 TO linno.
ENDWHILE.
CONDENSE p_sql.
TRY.
stmt_ref = _con_ref->create_statement( ).
p_res_ref = stmt_ref->execute_query( p_sql ).
CATCH cx_sql_exception INTO sqlerr_ref.
PERFORM handle_sql_exception(adbc_demo) USING sqlerr_ref.
ENDTRY.
ENDFORM. "execute_wuery
*---------------------------------------------------------------------*
* FORM more_lines
*---------------------------------------------------------------------*
* Add some more lines to the input area.
*---------------------------------------------------------------------*
FORM more_lines.
DATA:
linno LIKE sy-linno.
* save the actual content of the WHERE lines into an internal table
linno = _first_where_linno.
REFRESH _where_save_tab.
WHILE linno <= _last_where_linno.
READ LINE linno FIELD VALUE _where INTO _where.
CHECK sy-subrc = 0.
APPEND _where TO _where_save_tab.
ADD 1 TO linno.
ENDWHILE.
* rewrite the current list
SUBTRACT 1 FROM sy-lsind.
PERFORM write_input_area.
PERFORM write_columns.
ENDFORM. "more_lines
*---------------------------------------------------------------------*
* FORM get_columns
*---------------------------------------------------------------------*
* Get the table's columns from the database catalogue using the
* CL_SQL_METADATA methods.
*---------------------------------------------------------------------*
* --> TABNAME (global) table name
* <-- _COLUMN_TAB (global) an internal table containing TABNAME's
* columns
* <-- P_SUBRC 0 if the table was found and it's columns could
* be retrieved, <>0 otherwise.
*---------------------------------------------------------------------*
FORM get_columns USING p_subrc LIKE sy-subrc.
DATA:
sqlerr_ref TYPE REF TO cx_sql_exception,
md_ref TYPE REF TO cl_sql_metadata,
tabdescr_tab TYPE adbc_table_descr_tab,
schema_rgtab TYPE adbc_name_rgtab,
table_rgtab TYPE adbc_name_rgtab.
FIELD-SYMBOLS:
<tab_rg> LIKE LINE OF table_rgtab,
<tabdescr> LIKE LINE OF tabdescr_tab,
<schema> LIKE schema,
<tabname> LIKE tabname.
* create a metadata object
md_ref = _con_ref->get_metadata( ).
* search for the specified table in the database catalog; because
* the method GET_TABLES expects a range table for the table seatch
* we create one with an EQ-condition
TRY.
IF schema IS INITIAL.
APPEND INITIAL LINE TO table_rgtab ASSIGNING <tab_rg>.
<tab_rg>-sign = 'I'.
<tab_rg>-option = 'EQ'.
<tab_rg>-low = tabname.
CALL METHOD md_ref->get_tables
EXPORTING
schema_rgtab = schema_rgtab
table_rgtab = table_rgtab
IMPORTING
table_descr_tab = tabdescr_tab.
* check if the table was found in the catalog (this means that the
* internal table returned contains at least one row)
READ TABLE tabdescr_tab INDEX 1 ASSIGNING <tabdescr>.
IF sy-subrc <> 0.
WRITE: 'Table not found:', tabname. "#EC NOTEXT
p_subrc = 1.
RETURN.
ENDIF.
ASSIGN <tabdescr>-schema TO <schema>.
ASSIGN <tabdescr>-table_name TO <tabname>.
_qualified_tabname = tabname.
ELSE.
ASSIGN schema TO <schema>.
ASSIGN tabname TO <tabname>.
CONCATENATE schema '.' tabname INTO _qualified_tabname.
ENDIF.
* get all columns of the table/view from the database catalog
CALL METHOD md_ref->get_columns
EXPORTING
schema_name = <schema>
table_name = <tabname>
IMPORTING
column_tab = _column_tab.
DESCRIBE TABLE _column_tab LINES _col_cnt.
IF _col_cnt = 0.
WRITE: 'Table not found:', tabname. "#EC NOTEXT
p_subrc = 1.
RETURN.
ENDIF.
CATCH cx_sql_exception INTO sqlerr_ref.
PERFORM handle_sql_exception(adbc_demo) USING sqlerr_ref.
p_subrc = 2.
RETURN.
ENDTRY.
p_subrc = 0.
ENDFORM. "get_columns
*---------------------------------------------------------------------*
* FORM get_selected_columns
*---------------------------------------------------------------------*
* Get the columns selected by the user.
*---------------------------------------------------------------------*
* --> P_COLUMNS internal table containing the selected columns
*---------------------------------------------------------------------*
FORM get_selected_columns USING p_columns TYPE adbc_column_tab.
DATA:
p_linno LIKE sy-linno.
FIELD-SYMBOLS:
<coldescr> LIKE LINE OF _column_tab.
p_linno = _first_col_linno.
REFRESH p_columns.
DO _col_cnt TIMES.
READ LINE p_linno FIELD VALUE _markfield INTO _markfield.
IF _markfield IS NOT INITIAL.
READ TABLE _column_tab INDEX sy-index ASSIGNING <coldescr>.
CHECK sy-subrc = 0.
APPEND <coldescr>-column_name TO p_columns.
ENDIF.
ADD 1 TO p_linno.
ENDDO.
ENDFORM. "get_selected_columns
*---------------------------------------------------------------------*
* FORM set_checkboxes
*---------------------------------------------------------------------*
* Set the checkboxes either completely on or off.
*---------------------------------------------------------------------*
FORM set_checkboxes USING p_markfield TYPE flag.
DATA:
p_linno LIKE sy-linno.
p_linno = _first_col_linno.
DO _col_cnt TIMES.
READ LINE p_linno.
IF sy-subrc = 0 AND _selectable IS NOT INITIAL.
MODIFY CURRENT LINE FIELD VALUE _markfield FROM p_markfield.
ENDIF.
ADD 1 TO p_linno.
ENDDO.
ENDFORM. "set_checkboxes
*---------------------------------------------------------------------*
* FORM define_output_fields
*---------------------------------------------------------------------*
* Creates appropriate (i.e. type specific) data objects for each
* of the selected columns and defines these objects as output values
* for the given result set object.
*---------------------------------------------------------------------*
* --> P_RES_REF reference to result set object
* --> P_COLUMNS list of selected columns
* <-- P_DREF_TAB list of references to the created data objects
*---------------------------------------------------------------------*
FORM define_output_fields USING p_res_ref TYPE REF TO cl_sql_result_set
p_columns TYPE adbc_column_tab
p_dref_tab TYPE t_dref_tab.
FIELD-SYMBOLS:
<dref> TYPE REF TO data,
<colname> LIKE LINE OF p_columns,
<coldescr> LIKE LINE OF _column_tab.
REFRESH p_dref_tab.
LOOP AT p_columns ASSIGNING <colname>.
READ TABLE _column_tab WITH KEY column_name = <colname>
ASSIGNING <coldescr>.
APPEND INITIAL LINE TO p_dref_tab ASSIGNING <dref>.
CASE <coldescr>-ddic_type.
WHEN 'CHAR'.
CREATE DATA <dref> TYPE c LENGTH <coldescr>-ddic_length.
WHEN 'INT2' OR 'INT4' OR 'INT1'.
CREATE DATA <dref> TYPE i.
WHEN 'DATS'.
CREATE DATA <dref> TYPE c LENGTH 14.
WHEN 'DEC'.
CREATE DATA <dref> TYPE p LENGTH <coldescr>-ddic_length
DECIMALS <coldescr>-ddic_decimals.
WHEN 'FLTP'.
CREATE DATA <dref> TYPE f.
WHEN 'STRG'.
CREATE DATA <dref> TYPE string.
WHEN 'RSTR'.
CREATE DATA <dref> TYPE xstring.
WHEN 'RAW'.
CREATE DATA <dref> TYPE x LENGTH <coldescr>-ddic_length.
WHEN OTHERS.
* shouldn't occur because columns with inappropriate types
* should have been filtered by CHECK_TYPE routine
ENDCASE.
p_res_ref->set_param( <dref> ).
ENDLOOP.
ENDFORM. "define_output_fields
*---------------------------------------------------------------------*
* FORM check_type
*---------------------------------------------------------------------*
* Checks if the given column has an appropriate database type, i.e.
* a type that can be mapped to an equivalent ABAP type
*---------------------------------------------------------------------*
* --> P_COLDESCR columns descriptor
* <-- P_OK 'X' if the type can be mapped, SPACE otherwise
*---------------------------------------------------------------------*
FORM check_type USING p_coldescr LIKE LINE OF _column_tab
p_ok TYPE flag.
CASE p_coldescr-ddic_type.
WHEN 'CHAR' OR
'INT2' OR 'INT4' OR 'INT1' OR
'DATS' OR
'DEC' OR
'FLTP' OR
'STRG' OR
'RSTR' OR
'RAW'.
p_ok = 'X'.
WHEN OTHERS.
CLEAR p_ok.
ENDCASE.
ENDFORM. "check_type
*---------------------------------------------------------------------*
* FORM write_result_heading
*---------------------------------------------------------------------*
* Writes column headings for the selected columns
*---------------------------------------------------------------------*
* --> P_COLUMNS list of selected columns
* --> P_DREF_TAB list of references to the output variables
*---------------------------------------------------------------------*
FORM write_result_heading USING p_columns TYPE adbc_column_tab
p_dref_tab TYPE t_dref_tab.
DATA:
flen TYPE i,
max_colno LIKE sy-colno.
FIELD-SYMBOLS:
<colname> LIKE LINE OF p_columns,
<dref> LIKE LINE OF p_dref_tab,
<f> TYPE ANY.
LOOP AT p_dref_tab ASSIGNING <dref>.
READ TABLE p_columns INDEX sy-tabix ASSIGNING <colname>.
CHECK sy-subrc = 0.
ASSIGN <dref>->* TO <f>.
DESCRIBE FIELD <f> OUTPUT-LENGTH flen.
WRITE: AT (flen) <colname> COLOR COL_HEADING.
IF sy-colno > max_colno.
max_colno = sy-colno.
ENDIF.
ENDLOOP.
SUBTRACT 1 FROM max_colno.
ULINE AT /(max_colno).
ENDFORM. "write_result_heading
*---------------------------------------------------------------------*
* FORM write_result_line
*---------------------------------------------------------------------*
* Writes the column values of a result set row to the output list.
*---------------------------------------------------------------------*
* --> P_DREF_TAB list of references to the output variables
*---------------------------------------------------------------------*
FORM write_result_line USING p_dref_tab TYPE t_dref_tab.
STATICS:
flag.
FIELD-SYMBOLS:
<dref> LIKE LINE OF p_dref_tab,
<f> TYPE ANY.
IF flag = space.
FORMAT INTENSIFIED ON.
flag = 'X'.
ELSE.
FORMAT INTENSIFIED OFF.
CLEAR flag.
ENDIF.
NEW-LINE.
LOOP AT p_dref_tab ASSIGNING <dref>.
ASSIGN <dref>->* TO <f>.
WRITE: <f> COLOR COL_NORMAL.
ENDLOOP.
ENDFORM. "write_result_line
*---------------------------------------------------------------------*
* FORM read_input_and_run_query
*---------------------------------------------------------------------*
* Reads the specified WHERE clause from the list's input area,
* creates and executes the query and displays the result.
*---------------------------------------------------------------------*
FORM read_input_and_run_query.
DATA:
sql TYPE string,
colrefs TYPE t_dref_tab,
colnames TYPE adbc_column_tab,
res_ref TYPE REF TO cl_sql_result_set.
* get the list of selected columns
PERFORM get_selected_columns USING colnames.
* read the specified WHERE condition and create the statement string
PERFORM execute_query USING colnames sql res_ref.
CHECK res_ref IS BOUND.
* define output fields
PERFORM define_output_fields USING res_ref colnames colrefs.
* fetch and display query result
PERFORM write_query_result USING colnames res_ref colrefs.
ENDFORM. "read_input_and_run_query
*---------------------------------------------------------------------*
* FORM write_columns
*---------------------------------------------------------------------*
* Displays all columns contained in _COLUMN_TAB together with
* checkboxes that allows to select some of them for retrieval.
* Initially, all columns are displayed as "selected".
*---------------------------------------------------------------------*
FORM write_columns.
DATA:
type_ok TYPE flag,
len TYPE i.
CONSTANTS:
frame_sz TYPE i VALUE 79.
FIELD-SYMBOLS:
<coldescr> LIKE LINE OF _column_tab.
* display a header
ULINE AT (frame_sz).
len = frame_sz - 2.
WRITE:
/ sy-vline NO-GAP,
AT (len) text-001 COLOR COL_GROUP NO-GAP,
sy-vline. "#EC NOTEXT
ULINE AT /(frame_sz).
WRITE:
/ sy-vline, AT 5 sy-vline NO-GAP,
AT (30) text-002 COLOR COL_HEADING NO-GAP, sy-vline NO-GAP,
AT (30) text-003 COLOR COL_HEADING NO-GAP, sy-vline NO-GAP,
AT (11) text-004 COLOR COL_HEADING NO-GAP, sy-vline.
ULINE AT /(frame_sz).
* remember the line number of the first column displayed
_first_col_linno = sy-linno + 1.
* display columns
LOOP AT _column_tab ASSIGNING <coldescr>.
WRITE / sy-vline.
* check if the current columns has an appropriate database type, i.e.
* one that can be mapped to an ABAP type; if this check fails the
* column is displayed, but not marked as selectable; technically
* this is achieved by hiding a _SELECTABLE flag in the current line.
PERFORM check_type USING <coldescr> type_ok.
IF type_ok IS INITIAL.
CLEAR: _markfield, _selectable.
WRITE _markfield AS CHECKBOX INPUT OFF.
ELSE.
_markfield = _selectable = 'X'.
WRITE _markfield AS CHECKBOX.
ENDIF.
HIDE _selectable.
* display the column together with its type and length
WRITE:
sy-vline NO-GAP,
<coldescr>-column_name COLOR COL_KEY NO-GAP, sy-vline NO-GAP,
<coldescr>-data_type COLOR COL_NORMAL NO-GAP, sy-vline NO-GAP,
<coldescr>-data_length COLOR COL_NORMAL NO-SIGN NO-GAP,
sy-vline.
ENDLOOP.
ULINE AT /(frame_sz).
ENDFORM. "write_columns
*---------------------------------------------------------------------*
* FORM write_input_area
*---------------------------------------------------------------------*
* Display the "input area", i.e. some input lines where the user
* can specify the queries WHERE condition
*---------------------------------------------------------------------*
FORM write_input_area.
DATA:
init_first_line TYPE flag VALUE 'X',
flen TYPE i.
DESCRIBE FIELD _where LENGTH flen IN CHARACTER MODE.
ADD 2 TO flen.
ULINE AT (flen).
WRITE:
/ sy-vline NO-GAP,
'SELECT <selected columns> FROM', _qualified_tabname, "#EC NOTEXT
AT flen sy-vline.
* remember the number of the first input line
_first_where_linno = sy-linno + 1.
* if more input lines were requested (user command MORE_LINES) the
* actual contents of the input area was saved to the internal table
* _WHERE_SAVE_TAB; this contents must no be displayed again.
LOOP AT _where_save_tab INTO _where.
WRITE:
/ sy-vline NO-GAP, _where NO-GAP INPUT ON, sy-vline.
CLEAR init_first_line.
ENDLOOP.
REFRESH _where_save_tab.
IF init_first_line = 'X'.
* Display the keyword WHERE in the first input line
_where = 'WHERE'.
ELSE.
CLEAR _where.
ENDIF.
DO c_where_lines TIMES.
WRITE: / sy-vline NO-GAP, _where NO-GAP INPUT ON, sy-vline.
CLEAR _where.
ENDDO.
* remember the number of the last input line
_last_where_linno = sy-linno.
ULINE AT /(flen).
SKIP 1.
ENDFORM. "write_input_area
*---------------------------------------------------------------------*
* FORM write_query_result
*---------------------------------------------------------------------*
* Fetch the rows from the result set and display them.
*---------------------------------------------------------------------*
* --> P_COLUMNS list of selected columns
* --> P_RES_REF reference to result set object
* --> P_DREF_TAB list of references to the output variables
*---------------------------------------------------------------------*
FORM write_query_result USING p_columns TYPE adbc_column_tab
p_res_ref TYPE REF TO cl_sql_result_set
p_dref_tab TYPE t_dref_tab.
DATA:
sqlerr_ref TYPE REF TO cx_sql_exception.
TRY.
WHILE p_res_ref->next( ) > 0.
IF sy-index = 1.
* write column headings
PERFORM write_result_heading USING p_columns p_dref_tab.
ENDIF.
PERFORM write_result_line USING p_dref_tab.
ENDWHILE.
FORMAT COLOR COL_TOTAL.
IF p_res_ref->rows_fetched = 0.
WRITE: 'no rows selected'. "#EC NOTEXT
ELSE.
SKIP.
WRITE: p_res_ref->rows_fetched, 'rows selected'. "#EC NOTEXT
ENDIF.
p_res_ref->close( ).
CATCH cx_sql_exception INTO sqlerr_ref.
PERFORM handle_sql_exception(adbc_demo) USING sqlerr_ref.
ENDTRY.
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.
