При внедрении информационных решений на базе SAP ERP, как правило, разворачиваются три системы:
1. Система разработки.
2. Система тестирования.
3. Система продуктивной эксплуатации.
В процессе разработки программ очень часто возникает необходимость оперативно протестировать SQL-запросы в продуктивной или тестовой системе, так как система разработки обычно содержит минимум данных и их не всегда достаточно. Давайте рассмотрим существующие для этого варианты, оценим их недостатки и в итоге разработаем свой инструмент.
Мне удалось насчитать 5 доступных вариантов:
1. Транзакция SE16/SE16N
С помощью этой транзакции можно делать выборку только с одной таблицы. Не подходит для запросов с несколькими таблицами.
2. Транзакция ST04 (Additional functions -> SQL Command Editor)
Этот инструмент позволяет выполнять SQL-запросы любой сложности, но имеет 2 недостатка:
- во-первых, воспринимает только Native SQL-запросы (синтаксис СУБД), что накладывает некоторые неудобства, так как при разработке программ на ABAP для универсальности используются Open SQL-запросы, несколько отличающиеся синтаксисом, но это можно было бы пережить, если бы не «во-вторых»;
- во-вторых, работает только в том случае, если в качестве СУБД используется Oracle.
3. Транзакция SQVI
В транзакции нельзя писать напрямую SQL-запросы, но можно с помощью конструктора строить достаточно сложные выборки из нескольких таблиц с JOIN`ами. Не умеет работать с подзапросами и к тому же в конструкторе приходится выполнять слишком много манипуляций мышкой, поэтому для тестирования запросов не подходит.
4. Написать простенькую программу с тестируемым запросом и перенести ее в тестовую систему
Процесс переноса измененного кода в тестовую (продуктивную) систему требует выполнения некоторых рутинных манипуляций и занимает в среднем 5-7 минут, поэтому данный вариант тоже не подходит, так как никакого терпения не хватит проделывать всё это после каждой правки запроса.
5. Прямой доступ к СУБД
В большинстве случаев получить разработчикам такой доступ на проектах не представляется возможным, поэтому данный вариант не подходит.
Вывод
Получается, что удобного универсального инструмента, который бы позволял оперативно тестировать SQL-запросы любой сложности в SAP, не существует. Придя к такому выводу, я решил разработать такой инструмент.
Приступаем к разработке
Для начала в транзакции SE80 создаем программу ZSQL, GUI-статус MAIN100 с кнопкой «Выполнить» и Экран 0100.
Укрупнённо алгоритм программы выглядит так:
Получение SQL-запроса SELECT
Для получения SQL-запроса будем использовать текстовый редактор, который создадим на экране с помощью класса CL_GUI_TEXTEDIT. Для этого добавим на Экран 0100 пустой контейнер с именем MYEDIT, в который будем выводить редактор.
Фрагмент кода, создающий текстовый редактор на экране
data: g_editor type ref to cl_gui_textedit,
g_editor_container type ref to cl_gui_custom_container.
if g_editor is initial.
create object g_editor_container
exporting
container_name = `MYEDIT`
exceptions
cntl_error = 1
cntl_system_error = 2
create_error = 3
lifetime_error = 4
lifetime_dynpro_dynpro_link = 5.
create object g_editor
exporting
parent = g_editor_container
wordwrap_mode = cl_gui_textedit=>wordwrap_at_fixed_position
wordwrap_to_linebreak_mode = cl_gui_textedit=>true
exceptions
others = 1.
if sy-subrc <> 0.
leave program.
endif.
endif.
Парсинг SQL-запроса
Из введенного SQL-запроса нам необходимо получить список выбираемых полей и таблиц для того, чтобы в дальнейшем на основании этого списка динамически сгенерировать структуру ALV-Grid для вывода результата.
Фрагмент кода, анализирующий запрос
types: ty_simple_tab type standard table of ty_simple_struc.
types: ty_t_code type standard table of rssource-line.
data lt_sql_query type ty_simple_tab.
lt_fields type ty_simple_tab,
lt_tables type ty_simple_tab,
l_use_cnt(1) type c.
" Анализируем запрос построчно"
loop at lt_sql_query assigning <fs_sql_query>.
" Удаляем нечитаемые спец-символы
replace all occurrences of con_tab in <fs_sql_query>-line with space.
concatenate ` ` <fs_sql_query>-line ` ` into <fs_sql_query>-line.
" Разбиваем строку на отдельные слова"
refresh lt_parsed_sql_line.
split <fs_sql_query>-line at space into table lt_parsed_sql_line.
delete lt_parsed_sql_line where line = ''.
loop at lt_parsed_sql_line assigning <fs_parsed_sql_line>.
translate <fs_parsed_sql_line>-line to upper case.
if <fs_parsed_sql_line>-line = 'SELECT'.
continue.
endif.
" Если дошли до * - считаем, что все выбираемые поля получены"
if <fs_parsed_sql_line>-line = '*'.
l_field_names_obtained = 'X'.
continue.
endif.
" Если дошли до FROM или JOIN - считаем, что все выбираемые поля получены.
" Следующее слово будет названием таблицы"
if <fs_parsed_sql_line>-line = 'FROM' or <fs_parsed_sql_line>-line = 'JOIN'.
l_field_names_obtained = 'X'.
l_is_tabname = 'X'.
continue.
endif.
" Получаем названия полей"
if l_field_names_obtained is initial.
" Ищем конструкцию COUNT()"
find 'COUNT(' in <fs_parsed_sql_line>-line ignoring case.
if sy-subrc = 0.
l_use_cnt = 'X'.
continue.
endif.
" Название поля указано с названием таблицы через ~"
search <fs_parsed_sql_line>-line for '~'.
if sy-subrc = 0.
add 1 to sy-fdpos.
endif.
append <fs_parsed_sql_line>-line+sy-fdpos to lt_fields.
endif.
" Получаем названия таблиц"
if l_is_tabname = 'X'.
append <fs_parsed_sql_line>-line to lt_tables.
clear l_is_tabname.
endif.
endloop.
endloop.Выполнение SQL-запроса
Чтобы выполнить наш запрос, воспользуемся оператором generate subroutine pool, который позволяет динамически генерировать временные ABAP-программы на основании переданного в качестве параметра исходного кода, которым мы подготовим из введенного SQL-запроса.
Фрагмент кода, генерирующий ABAP-программу
types: ty_t_code type standard table of rssource-line.
data: code type ty_t_code,
prog(8) type c,
msg(120) type c,
lt_parsed_sql_line type ty_simple_tab,
l_sub_order(1) type c.
field-symbols: <fs_sql_query> type ty_simple_struc,
<fs_parsed_sql_line> type ty_simple_struc.
append `program z_sql.` to code.
append `form get_data using fs_data type standard table.` to code.
append `try.` to code.
loop at lt_sql_query assigning <fs_sql_query>.
clear: lt_parsed_sql_line.
split <fs_sql_query>-line at space into table lt_parsed_sql_line.
delete lt_parsed_sql_line where line = ''.
loop at lt_parsed_sql_line assigning <fs_parsed_sql_line>.
concatenate ` ` <fs_parsed_sql_line>-line ` ` into <fs_parsed_sql_line>-line.
translate <fs_parsed_sql_line>-line to upper case.
" добавляем into… только 1 раз, иначе будет добавляться во все подзапросы"
if <fs_parsed_sql_line>-line = ' FROM ' and l_sub_order is initial.
append `into corresponding fields of table fs_data` to code.
l_sub_order = 'X'.
endif.
append <fs_parsed_sql_line>-line to code.
endloop.
endloop.
append `.` to code.
append `rollback work.` to code.
append `catch cx_root.` to code.
append `rollback work.` to code.
append `message ``Что-то пошло не так, проверьте запрос`` type ``i``.` to code.
append `endtry.` to code.
append `endform.` to code.
generate subroutine pool code name prog
message msg.
Вывод результата на экран
Так как состав полей и их тип нам заранее неизвестны, то для получения результата и вывода его на экран нам необходимо динамически сгенерировать внутреннюю таблицу и структуру ALV-Grid на основании выбираемых в запросе полей. Для этого будем использовать метод create_dynamic_table класса cl_alv_table_create.
Фрагмент кода, генерирующий структуру ALV-Grid
data: ref_table_descr type ref to cl_abap_structdescr,
lt_tab_struct type abap_compdescr_tab,
ls_fieldcatalog type slis_fieldcat_alv.
field-symbols: <fs_tab_struct> type abap_compdescr,
<fs_tables> type ty_simple_struc,
<fs_fields> type ty_simple_struc.
loop at lt_tables assigning <fs_tables>.
refresh lt_tab_struct.
" Получаем все поля для выбираемой таблицы"
ref_table_descr ?= cl_abap_typedescr=>describe_by_name( <fs_tables>-line ).
lt_tab_struct[] = ref_table_descr->components[].
loop at lt_tab_struct assigning <fs_tab_struct>.
" если поля нет среди выбираемых в SQL-запросе - не выводим его не экран"
if lines( lt_fields ) > 0.
read table lt_fields transporting no fields with key line = <fs_tab_struct>-name.
if sy-subrc <> 0.
continue.
endif.
endif.
" если поле с таким именем уже есть, то не добавляем повторно"
read table lt_fieldcatalog transporting no fields with key fieldname = <fs_tab_struct>-name.
if sy-subrc = 0.
continue.
endif.
clear ls_fieldcatalog.
ls_fieldcatalog-fieldname = <fs_tab_struct>-name.
ls_fieldcatalog-ref_tabname = <fs_tables>-line.
append ls_fieldcatalog to lt_fieldcatalog.
endloop.
endloop.
" В запросе есть конструкция COUNT() – добавляем колонку с именем CNT и типом INT"
if l_use_cnt = 'X'.
clear ls_fieldcatalog.
ls_fieldcatalog-fieldname = 'CNT'.
ls_fieldcatalog-seltext_l = 'Кол-во'.
ls_fieldcatalog-seltext_m = 'Кол-во'.
ls_fieldcatalog-seltext_s = 'Кол-во'.
ls_fieldcatalog-datatype = 'INT4'.
if p_tech_names = 'X'.
ls_fieldcatalog-seltext_l = 'CNT'.
ls_fieldcatalog-seltext_m = 'CNT'.
ls_fieldcatalog-seltext_s = 'CNT'.
ls_fieldcatalog-reptext_ddic = 'CNT'.
endif.
append ls_fieldcatalog to lt_fieldcatalog.
endif.
Фрагмент кода, создающий динамическую таблицу
data: dyn_table type ref to data,
dyn_line type ref to data,
lt_lvc_fieldcatalog type lvc_t_fcat,
ls_lvc_fieldcatalog type lvc_s_fcat.
field-symbols: <fs_fieldcatalog> type slis_fieldcat_alv.
" Преобразуем данные в другой тип"
loop at lt_fieldcatalog assigning <fs_fieldcatalog>.
clear ls_lvc_fieldcatalog.
move-corresponding <fs_fieldcatalog> to ls_lvc_fieldcatalog.
ls_lvc_fieldcatalog-ref_table = <fs_fieldcatalog>-ref_tabname.
append ls_lvc_fieldcatalog to lt_lvc_fieldcatalog.
endloop.
" Создаем динамически таблицу"
call method cl_alv_table_create=>create_dynamic_table
exporting
it_fieldcatalog = lt_lvc_fieldcatalog
importing
ep_table = dyn_table.
assign dyn_table->* to <fs_data>.
create data dyn_line like line of <fs_data>.
assign dyn_line->* to <fs_wa_data>.
Полный листинг исходного кода программы ZSQL:
Раскрыть
type-pools: slis.
types: begin of ty_simple_struc,
line(255) type c,
end of ty_simple_struc.
types: ty_simple_tab type standard table of ty_simple_struc.
types: ty_t_code type standard table of rssource-line.
data: g_editor type ref to cl_gui_textedit,
g_editor_container type ref to cl_gui_custom_container,
g_ok_code like sy-ucomm,
p_tech_names(1) type c.
field-symbols: <fs_data> type standard table,
<fs_wa_data> type any.
call screen 100.
module pbo output.
set pf-status `MAIN100`.
" Выводим на форму текстовый редактор для SQL-запроса"
if g_editor is initial.
create object g_editor_container
exporting
container_name = `MYEDIT`
exceptions
cntl_error = 1
cntl_system_error = 2
create_error = 3
lifetime_error = 4
lifetime_dynpro_dynpro_link = 5.
create object g_editor
exporting
parent = g_editor_container
wordwrap_mode = cl_gui_textedit=>wordwrap_at_fixed_position
wordwrap_to_linebreak_mode = cl_gui_textedit=>true
exceptions
others = 1.
if sy-subrc <> 0.
leave program.
endif.
endif.
endmodule.
module pai input.
case sy-ucomm.
when `EXIT`.
leave program.
when `EXEC`. " Нажатие кнопки «Выполнить»
perform exec.
endcase.
endmodule.
form exec.
" Получаем введенный запрос с формы"
data lt_sql_query type ty_simple_tab.
clear lt_sql_query.
call method g_editor->get_text_as_r3table
importing
table = lt_sql_query
exceptions
others = 1.
delete lt_sql_query where line = ''.
" Парсим запрос и получаем названия выбираемых полей и таблиц"
data: lt_fields type ty_simple_tab,
lt_tables type ty_simple_tab,
l_use_cnt(1) type c.
clear: lt_fields, lt_tables, l_use_cnt.
perform parse_sql_query
using
lt_sql_query
changing
lt_fields
lt_tables
l_use_cnt.
" Генерируем ABAP-программу из полученного SQL-запроса"
data: code type ty_t_code,
prog(8) type c,
msg(120) type c.
clear: code, prog, msg.
perform create_get_function
using
lt_sql_query
changing
code.
generate subroutine pool code name prog
message msg.
if sy-subrc <> 0.
message msg type 'I'.
return.
endif.
" Формируем структуру ALV-Grid на основе выбираемых полей и таблиц"
data: lt_fieldcatalog type slis_t_fieldcat_alv.
refresh: lt_fieldcatalog.
perform get_fieldcat
using
lt_tables
lt_fields
p_tech_names
l_use_cnt
changing
lt_fieldcatalog.
" Динамически, на основе выбираемых полей и таблиц, создаем таблицу <fs_data>,
" в которую будем помещать результат выполнения запроса"
perform create_itab_dynamically
using
lt_fieldcatalog.
" Выполняем SQL-запрос, вызывая функцию из сгенерированной программы"
perform get_data in program (prog)
using
<fs_data>.
" Выводим результат на экран"
perform show_alv
using
lt_fieldcatalog.
endform.
" Функция разбора запроса"
form parse_sql_query
using
lt_sql_query type ty_simple_tab
changing
lt_fields type ty_simple_tab
lt_tables type ty_simple_tab
l_use_cnt.
data: l_field_names_obtained(1) type c,
l_is_tabname(1) type c,
lt_parsed_sql_line type ty_simple_tab.
clear: l_field_names_obtained, l_is_tabname.
field-symbols: <fs_sql_query> type ty_simple_struc,
<fs_parsed_sql_line> type ty_simple_struc.
constants: con_tab type c value cl_abap_char_utilities=>horizontal_tab.
" Анализируем запрос построчно"
loop at lt_sql_query assigning <fs_sql_query>.
" Удаляем нечитаемые спец-символы
replace all occurrences of con_tab in <fs_sql_query>-line with space.
concatenate ` ` <fs_sql_query>-line ` ` into <fs_sql_query>-line.
" Разбиваем строку на отдельные слова"
refresh lt_parsed_sql_line.
split <fs_sql_query>-line at space into table lt_parsed_sql_line.
delete lt_parsed_sql_line where line = ''.
loop at lt_parsed_sql_line assigning <fs_parsed_sql_line>.
translate <fs_parsed_sql_line>-line to upper case.
if <fs_parsed_sql_line>-line = 'SELECT'.
continue.
endif.
" Если дошли до * - считаем, что все выбираемые поля получены"
if <fs_parsed_sql_line>-line = '*'.
l_field_names_obtained = 'X'.
continue.
endif.
" Если дошли до FROM или JOIN - считаем, что все выбираемые поля получены.
" Следующее слово будет названием таблицы"
if <fs_parsed_sql_line>-line = 'FROM' or <fs_parsed_sql_line>-line = 'JOIN'.
l_field_names_obtained = 'X'.
l_is_tabname = 'X'.
continue.
endif.
" Получаем названия полей"
if l_field_names_obtained is initial.
" Ищем конструкцию COUNT()"
find 'COUNT(' in <fs_parsed_sql_line>-line ignoring case.
if sy-subrc = 0.
l_use_cnt = 'X'.
continue.
endif.
" Название поля указано с названием таблицы через ~"
search <fs_parsed_sql_line>-line for '~'.
if sy-subrc = 0.
add 1 to sy-fdpos.
endif.
append <fs_parsed_sql_line>-line+sy-fdpos to lt_fields.
endif.
" Получаем названия таблиц"
if l_is_tabname = 'X'.
append <fs_parsed_sql_line>-line to lt_tables.
clear l_is_tabname.
endif.
endloop.
endloop.
endform.
" Функция создания исходного кода ABAP-программы для последующей генерации"
form create_get_function
using
lt_sql_query type ty_simple_tab
changing
code type ty_t_code.
data: lt_parsed_sql_line type ty_simple_tab,
l_sub_order(1) type c.
clear l_sub_order.
field-symbols: <fs_sql_query> type ty_simple_struc,
<fs_parsed_sql_line> type ty_simple_struc.
append `program z_sql.` to code.
append `form get_data using fs_data type standard table.` to code.
append `try.` to code.
loop at lt_sql_query assigning <fs_sql_query>.
clear: lt_parsed_sql_line.
split <fs_sql_query>-line at space into table lt_parsed_sql_line.
delete lt_parsed_sql_line where line = ''.
loop at lt_parsed_sql_line assigning <fs_parsed_sql_line>.
concatenate ` ` <fs_parsed_sql_line>-line ` ` into <fs_parsed_sql_line>-line.
translate <fs_parsed_sql_line>-line to upper case.
" добавляем into… только 1 раз, иначе будет добавляться во все подзапросы"
if <fs_parsed_sql_line>-line = ' FROM ' and l_sub_order is initial.
append `into corresponding fields of table fs_data` to code.
l_sub_order = 'X'.
endif.
append <fs_parsed_sql_line>-line to code.
endloop.
endloop.
append `.` to code.
append `rollback work.` to code.
append `catch cx_root.` to code.
append `rollback work.` to code.
append `message ``Что-то пошло не так, проверьте запрос`` type ``i``.` to code.
append `endtry.` to code.
append `endform.` to code.
endform.
" Функция генерации структуры ALV-грида"
form get_fieldcat
using
lt_tables type ty_simple_tab
lt_fields type ty_simple_tab
p_tech_names
l_use_cnt
changing
lt_fieldcatalog type slis_t_fieldcat_alv.
data: ref_table_descr type ref to cl_abap_structdescr,
lt_tab_struct type abap_compdescr_tab,
ls_fieldcatalog type slis_fieldcat_alv.
field-symbols: <fs_tab_struct> type abap_compdescr,
<fs_tables> type ty_simple_struc,
<fs_fields> type ty_simple_struc.
loop at lt_tables assigning <fs_tables>.
refresh lt_tab_struct.
" Получаем все поля для выбираемой таблицы"
ref_table_descr ?= cl_abap_typedescr=>describe_by_name( <fs_tables>-line ).
lt_tab_struct[] = ref_table_descr->components[].
loop at lt_tab_struct assigning <fs_tab_struct>.
" если поля нет среди выбираемых в SQL-запросе - не выводим его не экран"
if lines( lt_fields ) > 0.
read table lt_fields transporting no fields with key line = <fs_tab_struct>-name.
if sy-subrc <> 0.
continue.
endif.
endif.
" если поле с таким именем уже есть, то не добавляем повторно"
read table lt_fieldcatalog transporting no fields with key fieldname = <fs_tab_struct>-name.
if sy-subrc = 0.
continue.
endif.
clear ls_fieldcatalog.
ls_fieldcatalog-fieldname = <fs_tab_struct>-name.
ls_fieldcatalog-ref_tabname = <fs_tables>-line.
append ls_fieldcatalog to lt_fieldcatalog.
endloop.
endloop.
" В запросе есть конструкция COUNT() – добавляем колонку с именем CNT и типом INT"
if l_use_cnt = 'X'.
clear ls_fieldcatalog.
ls_fieldcatalog-fieldname = 'CNT'.
ls_fieldcatalog-seltext_l = 'Кол-во'.
ls_fieldcatalog-seltext_m = 'Кол-во'.
ls_fieldcatalog-seltext_s = 'Кол-во'.
ls_fieldcatalog-datatype = 'INT4'.
if p_tech_names = 'X'.
ls_fieldcatalog-seltext_l = 'CNT'.
ls_fieldcatalog-seltext_m = 'CNT'.
ls_fieldcatalog-seltext_s = 'CNT'.
ls_fieldcatalog-reptext_ddic = 'CNT'.
endif.
append ls_fieldcatalog to lt_fieldcatalog.
endif.
endform.
" Функция создания динамической внутренней таблицы"
form create_itab_dynamically
using
lt_fieldcatalog type slis_t_fieldcat_alv.
data: dyn_table type ref to data,
dyn_line type ref to data,
lt_lvc_fieldcatalog type lvc_t_fcat,
ls_lvc_fieldcatalog type lvc_s_fcat.
field-symbols: <fs_fieldcatalog> type slis_fieldcat_alv.
" Преобразуем данные в другой тип"
loop at lt_fieldcatalog assigning <fs_fieldcatalog>.
clear ls_lvc_fieldcatalog.
move-corresponding <fs_fieldcatalog> to ls_lvc_fieldcatalog.
ls_lvc_fieldcatalog-ref_table = <fs_fieldcatalog>-ref_tabname.
append ls_lvc_fieldcatalog to lt_lvc_fieldcatalog.
endloop.
" Создаем динамически таблицу"
call method cl_alv_table_create=>create_dynamic_table
exporting
it_fieldcatalog = lt_lvc_fieldcatalog
importing
ep_table = dyn_table.
assign dyn_table->* to <fs_data>.
create data dyn_line like line of <fs_data>.
assign dyn_line->* to <fs_wa_data>.
endform.
" Функция отображения ALV-Grid на экране"
form show_alv
using
lt_fieldcatalog type slis_t_fieldcat_alv.
data: ls_event type slis_alv_event,
lt_event type slis_t_event,
ls_layout type slis_layout_alv,
l_repid like sy-repid.
ls_layout-colwidth_optimize = 'X'.
l_repid = sy-repid.
call function 'REUSE_ALV_GRID_DISPLAY'
exporting
i_callback_program = l_repid
is_layout = ls_layout
it_fieldcat = lt_fieldcatalog
i_save = 'X'
tables
t_outtab = <fs_data>
exceptions
program_error = 1
others = 2.
if sy-subrc <> 0.
leave program.
endif.
endform.
Разработанная программа позволяет выполнять Open SQL-запросы SELECT любой сложности. Только нужно соблюдать одно правило при написании запроса: если используется конструкция COUNT(), то после нее нужно дописывать «AS cnt», чтобы корректно сгенерировался ALV-Grid.
Программу, по идее, можно немного доработать и использовать не только для тестирования запросов, но и для формирования пользовательских отчетов.
В статье я не затрагивал вопросы безопасности. Входящий запрос никак не проверяется на корректность, после него можно написать любой ABAP-код и он будет выполняться. Для исключения такой возможности достаточно дописать несложные проверки.
Пользуйтесь!
Автор: irvil
