Recently I've seen the question about using Table type parameter in DB stored procedure call using SAP JDBC receiver adapter.
According to SAP's help, following SQL data types are supported: INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output), CLOB (input and output), CURSOR (output; only in connection with the Oracle JDBC driver).
It means that we can't use any other type for parameters passed to stored procedure.
But let's take a look at very interesting types existing in famous database systems, such as MS SQL and Oracle (not sure about others) - "XML" type for MS SQL and "XMLType" type for Oracle. Generaly speaking these types hold XML structures and make it available for SQL.
I would want to show how we can use XML data types to fulfill above requirement.
Let's take a test scenario where we want to insert multiple customer records in MS SQL Server database with one call using stored procedure. Our stored procedure will look like:
USE [Test_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertCustomers]
@XMLCustomers as XML,
@ProcessedFlag as nvarchar(1),
@InsertedBy as nvarchar(10)
AS
BEGIN
SET NOCOUNT ON;
INSERT Customers (CustomerID, CustomerName, ProcessedFlag, InsertedBy)
SELECT
CUST.item.value('(ID)[1]', 'nvarchar(10)') as 'CustomerID',
CUST.item.value('(Name)[1]', 'nvarchar(10)') as 'CustomerName',
@ProcessedFlag as ProcessedFlag,
@InsertedBy as InsertedBy
FROM
@XMLCustomers.nodes('/Customers/Customer') as CUST(item)
END
Here you can see that "XML" type parameter XMLCustomers is used for retrieving customers records from XML string with SQL statement.
I won't describe all my test JDBC scenario as it's quite simple and there are plenty of JDBC scenario guides on SDN.
Our request message looks like:
<MT_JDBC_Customers action="insert" insertedby="PID">
<Customers>
<Customer>
<ID>500</ID>
<Name>Item 500</Name>
</Customer>
<Customer>
<ID>501</ID>
<Name>Item 501</Name>
</Customer>
</Customers>
</MT_JDBC_Customers>
After request mapping we get JDBC message format for stored procedure call:
<ns0:MT_JDBC_StoredProc_Request xmlns:ns0="urn://train">
<Statement>
<dbTableName action="EXECUTE">
<table>InsertCustomers</table>
<XMLCustomers type="CLOB" isInput="true">![CDATA[<Customers> <Customer> <ID>500</ID> <Name>Item 500</Name> </Customer> <Customer> <ID>501</ID> <Name>Item 501</Name> </Customer> </Customers>]]</XMLCustomers>
<ProcessedFlag type="VARCHAR" isInput="true">I</ProcessedFlag>
<InsertedBy type="VARCHAR" isInput="true">PID</InsertedBy>
</dbTableName>
</Statement>
</ns0:MT_JDBC_StoredProc_Request>
As you can see, I've put Customers recordset into XMLCustomers parameter value with type "CLOB" and wrapped it with CDATA tag (special thanks to "Return as XML" option in graphical mapping :smile: ).
After performing stored procedure call we can see result in target DB:
That's all. We've just inserted recordset in target database table with one shot. I think it was not so difficult :wink: .
For Oracle database I found the following example (but didn't try it myself):
<?xml version="1.0"?>
<ROWSET>
<ROW num="1">
<EMPID>10</EMPID>
<EMPNAME>Perry Smith</EMPNAME>
<EMPJOB>Manager</EMPJOB>
<EMPSAL>800</EMPSAL>
</ROW>
<ROW num="1">
<EMPID>20</EMPID>
<EMPNAME>John Calvach</EMPNAME>
<EMPJOB>Principal Support Consultant</EMPJOB>
<EMPSAL>900</EMPSAL>
</ROW>
</ROWSET>
create or replace procedure InsertXML(xmlDoc IN VARCHAR2, tableName IN VARCHAR2) is
insCtx DBMS_XMLSave.ctxType;
rows number;
begin
insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the doent
dbms_output.put_line(to_char(rows) || ' rows inserted');
DBMS_XMLSave.closeContext(insCtx); -- this closes the handle
end;
Regards, Evgeniy.
P.S. I would like to thank amit.srivastava8 for pointing me at this approach :smile:
- SAP Managed Tags:
