项目中使用的接口取数采用的是DBLink的方式,对方提供的表名太长,超过标准程序ADBC_QUERY的参数长度,于是写了一份简单的测试代码用来测试连接和取数。
DBCO配置:
测试程序:
程序源码:
*&---------------------------------------------------------------------*
*& Report ZTEST_ADBC_QUERY
*&
*&---------------------------------------------------------------------*
*&
*& 测试DBLink数据读取
*&---------------------------------------------------------------------*
REPORT ztest_adbc_query.
DATA:
gv_container TYPE REF TO cl_gui_custom_container,
gv_text_editor TYPE REF TO cl_gui_textedit,
gv_text_newtxt TYPE string,
gv_text_oldtxt TYPE string,
gt_texttab TYPE soli_tab,
gs_texttab TYPE soli.
DATA:
lo_adbc_connect TYPE REF TO cl_sql_connection,
lo_adbc_query TYPE REF TO cl_sql_statement,
lo_adbc_result TYPE REF TO cl_sql_result_set,
lo_metadata TYPE REF TO data,
ls_metadata TYPE adbc_rs_metadata_descr,
lt_metadata TYPE adbc_rs_metadata_descr_tab.
DATA:
lo_sql_exception TYPE REF TO cx_sql_exception,
lo_parameter_invalid TYPE REF TO cx_parameter_invalid,
lo_parameter_invalid_range TYPE REF TO cx_parameter_invalid_range,
lo_parameter_invalid_type TYPE REF TO cx_parameter_invalid_type,
lo_struct_creation TYPE REF TO cx_sy_struct_creation,
lo_table_creation TYPE REF TO cx_sy_table_creation,
lo_exception TYPE REF TO data,
lv_err_text TYPE string.
DATA:
lo_structdescr TYPE REF TO cl_abap_structdescr,
lo_tabletype TYPE REF TO cl_abap_tabledescr,
lo_result_ref TYPE REF TO data.
FIELD-SYMBOLS:
<lt_itab> TYPE STANDARD TABLE.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS:
p_conn TYPE dbcon-con_name.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(31) t_sql FOR FIELD p_sql.
PARAMETERS:
p_sql TYPE zbce_string.
SELECTION-SCREEN PUSHBUTTON 79(4) sql USER-COMMAND ltxt VISIBLE LENGTH 2.
SELECTION-SCREEN END OF LINE.
PARAMETERS:
p_upto TYPE i DEFAULT 10.
PARAMETERS:
p_alv AS CHECKBOX DEFAULT 'X'.
SELECTION-SCREEN END OF BLOCK b1.
INITIALIZATION.
PERFORM frm_initial_screen.
AT SELECTION-SCREEN.
IF sy-ucomm = 'LTXT'.
CALL SCREEN 9000 STARTING AT 10 5.
ENDIF.
START-OF-SELECTION.
* 获取连接
TRY .
lo_adbc_connect = cl_sql_connection=>get_connection( con_name = p_conn ).
CATCH cx_sql_exception INTO lo_sql_exception.
lv_err_text = lo_sql_exception->get_text( ).
WRITE lv_err_text.
RETURN.
ENDTRY.
* 创建连接对象
lo_adbc_query = lo_adbc_connect->create_statement( ).
* 执行Sql
TRY .
lo_adbc_result = lo_adbc_query->execute_query( p_sql ).
CATCH cx_sql_exception INTO lo_sql_exception.
lv_err_text = lo_sql_exception->get_text( ).
WRITE lv_err_text.
RETURN.
CATCH cx_parameter_invalid INTO lo_parameter_invalid.
lv_err_text = lo_parameter_invalid->get_text( ).
WRITE lv_err_text.
RETURN.
ENDTRY.
* 获取元数据
TRY .
lt_metadata = lo_adbc_result->get_metadata( ).
CATCH cx_sql_exception.
lv_err_text = lo_sql_exception->get_text( ).
WRITE lv_err_text.
RETURN.
ENDTRY.
* F类型修改成P类型
LOOP AT lt_metadata INTO ls_metadata WHERE data_type = 'F'.
ls_metadata-data_type = 'P'.
MODIFY lt_metadata FROM ls_metadata TRANSPORTING data_type.
ENDLOOP.
* 获取元数据字段信息
TRY .
lo_metadata = lo_adbc_result->get_struct_ref( md_tab = lt_metadata
string_only = 'X'
p_strict = abap_false
).
CATCH cx_sy_struct_creation INTO lo_struct_creation.
lv_err_text = lo_struct_creation->get_text( ).
WRITE lv_err_text.
RETURN.
CATCH cx_parameter_invalid_range INTO lo_parameter_invalid_range.
lv_err_text = lo_parameter_invalid_range->get_text( ).
WRITE lv_err_text.
RETURN.
ENDTRY.
* 创建结构类型对象
lo_structdescr ?= cl_abap_typedescr=>describe_by_data_ref( lo_metadata ).
* 创建表类型对象
TRY .
lo_tabletype = cl_abap_tabledescr=>create( p_line_type = lo_structdescr
p_table_kind = cl_abap_tabledescr=>tablekind_std ).
CATCH cx_sy_table_creation INTO lo_table_creation.
lv_err_text = lo_table_creation->get_text( ).
WRITE lv_err_text.
RETURN.
ENDTRY.
* 创建表对象引用
CREATE DATA lo_result_ref TYPE HANDLE lo_tabletype.
* 传递sql结果至表对象引用
TRY .
lo_adbc_result->set_param_table( itab_ref = lo_result_ref ).
CATCH cx_parameter_invalid INTO lo_parameter_invalid.
lv_err_text = lo_parameter_invalid->get_text( ).
WRITE lv_err_text.
RETURN.
ENDTRY.
* 获取所有数据
TRY .
lo_adbc_result->next_package( EXPORTING upto = p_upto ).
CATCH cx_sql_exception INTO lo_sql_exception.
lv_err_text = lo_sql_exception->get_text( ).
WRITE lv_err_text.
RETURN.
CATCH cx_parameter_invalid_type INTO lo_parameter_invalid_type.
lv_err_text = lo_parameter_invalid_type->get_text( ).
WRITE lv_err_text.
RETURN.
ENDTRY.
* 关闭连接
lo_adbc_result->close( ).
* 赋值到动态内表
ASSIGN lo_result_ref->* TO <lt_itab>.
IF p_alv IS INITIAL.
cl_demo_output=>display( <lt_itab> ).
ELSE.
* ALV展示
PERFORM frm_display_alv.
ENDIF.
*&---------------------------------------------------------------------*
*& Form FRM_INITIAL_SCREEN
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM frm_initial_screen .
t_sql = text-002.
sql = icon_abap.
ENDFORM. " FRM_INITIAL_SCREEN
*&---------------------------------------------------------------------*
*& Form FRM_DISPLAY_ALV
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM frm_display_alv.
DATA:
ls_layout TYPE lvc_s_layo,
lt_fieldcat TYPE lvc_t_fcat,
ls_fieldcat TYPE lvc_s_fcat,
lt_excluding TYPE slis_t_extab,
lt_event TYPE slis_t_event,
ls_event TYPE slis_alv_event,
lt_event_exit TYPE slis_t_event_exit,
ls_event_exit TYPE slis_event_exit,
ls_ddval TYPE lvc_s_drop,
ls_grid_setting TYPE lvc_s_glay.
FIELD-SYMBOLS:
<ls_fieldcat> TYPE lvc_s_fcat.
* 设置布局控制
ls_layout-zebra = 'X'. "颜色间隔
ls_layout-sel_mode = 'D'. "选择模式
ls_layout-cwidth_opt = 'X'. "列宽自适应
* 设置字段控制
LOOP AT lt_metadata INTO ls_metadata.
ls_fieldcat-fieldname = ls_metadata-column_name.
ls_fieldcat-reptext = ls_metadata-column_name.
APPEND ls_fieldcat TO lt_fieldcat.
CLEAR ls_fieldcat.
ENDLOOP.
* 展示ALV
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
EXPORTING
i_callback_program = sy-repid
* i_callback_pf_status_set = 'FRM_STATUS_SET'
* i_callback_user_command = 'FRM_USER_COMMAND'
is_layout_lvc = ls_layout
it_fieldcat_lvc = lt_fieldcat
it_excluding = lt_excluding
i_grid_settings = ls_grid_setting
* IT_SORT_LVC =
i_save = 'A'
* IS_VARIANT =
it_events = lt_event
it_event_exit = lt_event_exit
TABLES
t_outtab = <lt_itab>
EXCEPTIONS
program_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
ENDFORM. "FRM_DISPLAY_ALV
*&---------------------------------------------------------------------*
*& Module STATUS_9000 OUTPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE status_9000 OUTPUT.
SET PF-STATUS 'STATUS_9000'.
IF gv_container IS INITIAL.
CREATE OBJECT gv_container
EXPORTING
container_name = 'GV_CONTAINER'
EXCEPTIONS
cntl_error = 1
cntl_system_error = 2
create_error = 3
lifetime_error = 4
lifetime_dynpro_dynpro_link = 5.
ENDIF.
CREATE OBJECT gv_text_editor
EXPORTING
parent = gv_container
wordwrap_mode = '2'
wordwrap_position = '100'
wordwrap_to_linebreak_mode = cl_gui_textedit=>true
EXCEPTIONS
error_cntl_create = 1
error_cntl_init = 2
error_cntl_link = 3
error_dp_create = 4
gui_type_not_supported = 5.
gv_text_newtxt = p_sql.
IF gv_text_newtxt <> gv_text_oldtxt.
REFRESH gt_texttab.
APPEND gv_text_newtxt TO gt_texttab.
ENDIF.
gv_text_editor->set_text_as_r3table( EXPORTING table = gt_texttab ).
gv_text_oldtxt = gv_text_newtxt.
CALL METHOD cl_gui_docking_container=>set_focus
EXPORTING
control = gv_text_editor.
CALL METHOD gv_text_editor->set_readonly_mode
EXPORTING
readonly_mode = 0
EXCEPTIONS
error_cntl_call_method = 1
invalid_parameter = 2
OTHERS = 3.
ENDMODULE. " STATUS_9000 OUTPUT
*&---------------------------------------------------------------------*
*& Module USER_COMMAND_9000 INPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE user_command_9000 INPUT.
CASE sy-ucomm.
WHEN 'OK'.
gv_text_editor->get_text_as_r3table(
IMPORTING
table = gt_texttab
).
CLEAR p_sql.
LOOP AT gt_texttab INTO gs_texttab.
p_sql = p_sql && ` ` && gs_texttab-line.
ENDLOOP.
p_sql = shift_left( val = p_sql sub = ` ` ).
CALL METHOD cl_gui_cfw=>flush
EXCEPTIONS
OTHERS = 3.
LEAVE TO SCREEN 0.
WHEN 'CANCEL'.
LEAVE TO SCREEN 0.
ENDCASE.
ENDMODULE. " USER_COMMAND_9000 INPUT