Задача:
Получение данных из БД на MS SQL сервере, в том числе получение Dataset-а из хранимой процедуры
Ландшафт:
SAP на oracle под Linux
MS SQL сервер по виндой
Решение:
Собственно простое, был сделан линк из oracl-а на sql сервер - как это делалось не знаю, это к админам
а вот дальше начинаются танцы с программистским бубном:
В принципе если надо было бы только простые запросы на выборку делать ... как-то пережить можно было бы, хотя правильно нарисовать запрос для линка ещё то удовольствие
Но вот получение Dataset-а возвращаемого хранимкой, задача ... однозначно без бубна не разрешимая
В конце концов было сделано такое, более менее универсальное решение:
в sql сервере созданы две таблицы sap_query и sap_result,
SAP вставляет запись в таблицу sap_query, в качестве данных передаётся текст sql запроса
на таблицу sap_query навешен тригер срабатывающий при вставке, он выполняет запрос передаваемый в вставляемой записи, результат выполнения запроса помещается в sap_result
SAP считывает результат из таблицы sap_result
таблица sap_query
Code:
CREATE TABLE [dbo].[sap_query](
[sap_id] [int] NOT NULL,
[sql] [varchar](max) NOT NULL,
[ret_structure] [varchar](1000) NOT NULL,
[ret_fields] [varchar](1000) NULL,
[Base64] [bit] NULL,
CONSTRAINT [PK_sap_query] PRIMARY KEY CLUSTERED
(
[sap_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
тригер на вставку для sap_query
Code:
CREATE TRIGGER [dbo].[exec_sap_sql]
ON [dbo].[sap_query]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @sap_id int
declare @sql nvarchar(max)
declare @ret_structure varchar(1100)
declare @ret_fields varchar(1100)
declare @str varchar(max)
declare @Base64 bit
declare @fsBase64 varchar(15)
declare @pos int
declare @column_name varchar(50)
declare @Delimiter varchar(1) = ','
if ((OBJECT_ID ('tempdb.dbo.##tmp_exec_sap_sql')) IS NOT NULL) drop table ##tmp_exec_sap_sql
declare ins_cur cursor for
select sap_id, sql, ret_structure, ret_fields, Base64
from inserted
open ins_cur
fetch next from ins_cur into @sap_id, @sql, @ret_structure, @ret_fields, @Base64
while (@@FETCH_STATUS <> -1) begin
set @str = 'create table ##tmp_exec_sap_sql(' + @ret_structure + ')'
exec(@str)
if IsNull(@Base64, 0) = 1 begin
set @sql = dbo.FromBase64(@sql)
set @fsBase64 = 'dbo.ToBase64'
end
else set @fsBase64 = ''
insert into ##tmp_exec_sap_sql exec sp_executesql @sql
set @str = ''
if @ret_fields is Null begin
select @str = @str + 'convert(varchar(200),IsNull(['+column_name+'],''''))+''|''+'
from tempdb.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = '##tmp_exec_sap_sql'
order by ORDINAL_POSITION
end
else begin
set @ret_fields = @ret_fields + ','
while Len(@ret_fields) > 0 begin
set @pos = CharIndex(@Delimiter, @ret_fields)
set @column_name = RTrim(LTrim(left(@ret_fields, @pos - 1)))
if Len(@column_name) > 0 begin
set @str = @str + 'convert(varchar(200),IsNull(['+ @column_name +'],''''))+''|''+'
end
set @ret_fields = SubString(@ret_fields, @pos + 1, len(@ret_fields))
end
end
set @str = 'select '''+ convert(varchar(10), @sap_id) +''' as sap_id, '+ @fsBase64+'(' + @str + ''''') as res from ##tmp_exec_sap_sql'
insert into sap_result (sap_id, res)
exec (@str)
drop table ##tmp_exec_sap_sql
fetch next from ins_cur into @sap_id, @sql, @ret_structure, @ret_fields, @Base64
end
close ins_cur
deallocate ins_cur
END
таблица sap_result
Code:
CREATE TABLE [dbo].[sap_result](
[id] [int] IDENTITY(1,1) NOT NULL,
[sap_id] [int] NOT NULL,
[res] [varchar](max) NOT NULL
) ON [PRIMARY]
обнаружилась проблема с кодировкой... она где-то терялась при взаимодействии систем
возможно это можно как-то настроить/исправит ... в общем решил сделать с помощью base64 кодировки
Code:
-- Author: V.U.M.Sastry Sagi
-- Create date: 09/24/2010
-- Description: Decrypts the string from Base64 Format
CREATE FUNCTION [dbo].[FromBase64] (@data varchar(max))
RETURNS varchar(max)
AS
BEGIN
DECLARE
@Output varchar(max),
@Bits varbinary(3)
-- declare vars.
DECLARE @XmlData xml
-- construct an xml var.
SET @XmlData = CAST('<data>' + @data + '</data>' as xml)
-- base64 decode the @data.
SELECT @Output= CONVERT(varchar(max),
@XmlData.value('(data)[1]', 'varbinary(max)'))
RETURN @Output
END
CREATE FUNCTION [dbo].[ToBase64] (@Input varchar(max))
RETURNS varchar(max)
AS
BEGIN
DECLARE
@Output varchar(max),
@Bits varbinary(3),
@Pos int
SET @Pos = 1
SET @Output = ''
WHILE @Pos <= Len(@Input) BEGIN
SET @Bits = Convert(varbinary(3), Substring(@Input, @Pos, 3))
SET @Output = @Output +
Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
Substring(@Bits, 1, 1) / 4 + 1, 1)
SET @Output = @Output +
Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
Substring(@Bits, 1, 1) % 4 * 16 +
Substring(@Bits, 2, 1) / 16 + 1, 1)
SET @Output = @Output +
Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
Substring(@Bits, 2, 1) % 16 * 4 +
Substring(@Bits, 3, 1) / 64 + 1, 1)
SET @Output = @Output +
Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
Substring(@Bits, 3, 1) % 64 + 1, 1)
SET @Pos = @Pos + 3
END
RETURN (Left(@Output, Len(@Output) - 3 + Len(@Bits)) + Replicate('=', 3 - Len(@Bits)))
END
в SAPe для удобства работы со всем этим был написан ФМ
Code:
FUNCTION zlink_execute_query.
*"----------------------------------------------------------------------
*"*"Локальный интерфейс:
*" IMPORTING
*" REFERENCE(CON_NAME) TYPE DBCON-CON_NAME OPTIONAL
*" REFERENCE(LINK)
*" REFERENCE(SQL)
*" REFERENCE(SQL_RET_STRUCTURE)
*" REFERENCE(RET_FIELDS) OPTIONAL
*" CHANGING
*" REFERENCE(RET)
*"----------------------------------------------------------------------
* SQL_RET_STRUCTURE - структура результата запроса
* она нужна для того чтоб создать временную таблицу в которую будет помещён результат запроса
* а затем перегружен в sap_result
* RET_FIELDS - не обязательное - возвращаемые поля из запроса
* нужно в тех случаях когда надо вернуть не все поля возвращаемые запросом,
* актуально в основном для хранимых процедур
PERFORM link_select USING con_name link sql sql_ret_structure ret_fields ret.
ENDFUNCTION.
-------------------------
FUNCTION-POOL zlink_to_mssql. "MESSAGE-ID ..
*&---------------------------------------------------------------------*
*& Include ZLINK2MSSQL
*&---------------------------------------------------------------------*
* для преобразования из Base64 FORM convert
DATA: utils TYPE REF TO cl_http_utility.
DATA: conv_int TYPE REF TO cl_abap_conv_obj.
DATA: conv_out TYPE REF TO cl_abap_conv_obj.
DATA: con_ref TYPE REF TO cl_sql_connection.
DATA: qry TYPE REF TO cl_sql_statement.
*&---------------------------------------------------------------------*
*& Form init_link
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM init_link USING con_name.
CHECK qry IS INITIAL.
IF con_name IS INITIAL.
CREATE OBJECT qry.
ELSE.
con_ref = cl_sql_connection=>get_connection( con_name ).
qry = con_ref->create_statement( ).
ENDIF.
CREATE OBJECT utils.
CREATE OBJECT conv_int
EXPORTING
incode = '1504'
miss = 'S' " prc_miss
broken = 'M' " prc_broken
use_f1 = 'X'
substc = '00035' " prc_sapno
EXCEPTIONS
invalid_codepage = 1
internal_error = 2.
CREATE OBJECT conv_out
EXPORTING
outcode = '1504'
miss = 'S' " prc_miss
broken = 'M' " prc_broken
use_f1 = 'X'
substc = '00035' " prc_sapno
EXCEPTIONS
invalid_codepage = 1
internal_error = 2.
ENDFORM. "init
*&---------------------------------------------------------------------*
*& Form link_select
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->SQL text
* -->SQL_RET_STRUCTURE text
* -->SQL_RET_FIELDS text
* -->RET text
*----------------------------------------------------------------------*
FORM link_select USING con_name link sql sql_ret_structure ret_fields ret.
* Так уж получилось что по нормальному, данные из MS SQL Сервера через link от oracle не взять
* причин две:
* 1 DataSet от хранимой процедуры получить не возможно
* 2 Кодировка данных, возможно это можно как-то настроить, но наш админ не смог, для того чтоб победить эту проблему решил использовать Base64 кодировку
* Работает след образом:
DATA: sap_id(10) TYPE c.
DATA: xsql TYPE string.
DATA: qry_res TYPE REF TO cl_sql_result_set.
DATA: BEGIN OF itres OCCURS 0,
val TYPE string,
END OF itres.
DATA: res_ref TYPE REF TO data.
FIELD-SYMBOLS <res> LIKE LINE OF itres.
DATA: eres TYPE i.
DATA: btyp TYPE c.
DATA: typ TYPE c.
DATA: itw TYPE STANDARD TABLE OF string.
FIELD-SYMBOLS <w> LIKE LINE OF itw.
DATA: ret_wa_ref TYPE REF TO data.
FIELD-SYMBOLS <ret_tab> TYPE STANDARD TABLE.
FIELD-SYMBOLS <ret_wa> TYPE ANY.
FIELD-SYMBOLS <fs> TYPE ANY.
PERFORM init_link USING con_name.
sap_id(4) = sy-datum+4.
sap_id+4(6) = sy-uzeit.
xsql = sql.
PERFORM to_base64 USING xsql.
IF ret_fields IS INITIAL.
CONCATENATE 'insert into "sap_query"@' link ' ("sap_id", "Base64", "sql", "ret_structure") values (`' sap_id '`, 1,`' xsql '`,`' sql_ret_structure '`)' INTO xsql.
ELSE.
CONCATENATE 'insert into "sap_query"@' link ' ("sap_id", "Base64", "sql", "ret_structure", "ret_fields") values (`' sap_id '`, 1,`' xsql '`,`' sql_ret_structure '`,`' ret_fields '`)' INTO xsql.
ENDIF.
TRANSLATE xsql USING '`'''.
eres = qry->execute_update( xsql ).
CONCATENATE 'select "res" from "sap_result"@' link ' where "sap_id" =#' sap_id INTO xsql.
TRANSLATE xsql USING '# '.
GET REFERENCE OF itres[] INTO res_ref.
qry_res = qry->execute_query( xsql ).
qry_res->set_param_table( res_ref ).
qry_res->next_package( ).
qry_res->close( ).
CONCATENATE 'delete from "sap_query"@tpp where "sap_id" =' sap_id INTO xsql SEPARATED BY space.
eres = qry->execute_update( xsql ).
CONCATENATE 'delete from "sap_result"@tpp where "sap_id" =' sap_id INTO xsql SEPARATED BY space.
eres = qry->execute_update( xsql ).
* Обрабатываем результат
DESCRIBE FIELD ret TYPE btyp.
IF btyp = 'h'.
ASSIGN ret TO <ret_tab>.
CREATE DATA ret_wa_ref LIKE LINE OF <ret_tab>.
ASSIGN ret_wa_ref->* TO <ret_wa>.
DESCRIBE FIELD <ret_wa> TYPE typ.
ELSE.
typ = btyp.
ASSIGN ret TO <ret_wa>.
ENDIF.
IF typ CA 'uv'. " Структуры
typ = 'u'.
ENDIF.
LOOP AT itres ASSIGNING <res>.
IF btyp = 'h'.
APPEND INITIAL LINE TO <ret_tab> ASSIGNING <ret_wa>.
ENDIF.
PERFORM from_base64 USING <res>-val.
SPLIT <res>-val AT '|' INTO TABLE itw.
LOOP AT itw ASSIGNING <w>.
IF typ = 'u'. " Структура
ASSIGN COMPONENT sy-tabix OF STRUCTURE <ret_wa> TO <fs>.
IF sy-subrc <> 0. EXIT. ENDIF.
<fs> = <w>.
ELSE.
<ret_wa> = <w>.
EXIT.
ENDIF.
ENDLOOP.
IF btyp <> 'h'. EXIT. ENDIF.
ENDLOOP.
ENDFORM. "link_select
*&---------------------------------------------------------------------*
*& Form from_base64
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->VAL text
*----------------------------------------------------------------------*
FORM from_base64 USING val.
DATA: sin TYPE string.
DATA: s TYPE string.
DATA: sout TYPE string.
CHECK NOT val IS INITIAL.
sin = val.
CALL METHOD utils->decode_base64
EXPORTING
encoded = sin
RECEIVING
decoded = s.
CALL METHOD conv_int->convert
EXPORTING
inbuff = s
inbufflg = 0
outbufflg = 0
IMPORTING
outbuff = sout
EXCEPTIONS
internal_error = 1
OTHERS = 2.
val = sout.
ENDFORM. "convert
*&---------------------------------------------------------------------*
*& Form to_base64
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->VAL text
*----------------------------------------------------------------------*
FORM to_base64 USING val.
DATA: sin TYPE string.
DATA: s TYPE string.
DATA: sout TYPE string.
CHECK NOT val IS INITIAL.
sin = val.
CALL METHOD conv_out->convert
EXPORTING
inbuff = sin
inbufflg = 0
outbufflg = 0
IMPORTING
outbuff = s
EXCEPTIONS
internal_error = 1
OTHERS = 2.
CALL METHOD utils->encode_base64
EXPORTING
unencoded = s
RECEIVING
encoded = sout.
val = sout.
ENDFORM. "to_base64
пример использования
Code:
CONCATENATE 'EXEC t_proc_get_mat_from_plan @mode = 3, @item_kind = NULL, @start_date = `' sbudat-low '`, @end_date = `' sbudat-high '`' INTO sql.
TRANSLATE sql USING '`'''.
CONCATENATE
' order_code VARCHAR(20)'
', item_number INT'
', item_code VARCHAR(80)'
', obj_uom_code VARCHAR(3)'
', obj_uom VARCHAR(15)'
', line_kind INT'
', quantity NUMERIC(15,3) NOT NULL'
', Price NUMERIC(15,2) NOT NULL'
INTO srs.
CALL FUNCTION 'ZLINK_EXECUTE_QUERY'
EXPORTING
con_name = 'ZZZ'
link = 'zzz'
sql = sql
sql_ret_structure = srs
* RET_FIELDS =
CHANGING
ret = itk[].
нюанс:
обязательно сделайте в DBCO отдельное соединения с базой oracle, иначе ошибка которая может возникнуть при выполнении запроса через линк, может поставит раком всю базу oracle
Вход
Регистрация
FAQ пользователя
Поиск
Попытка не пытка