Calling stored procs in MaxDb using SAP Xi

[Former Member]

I had been going thro' quite a lot of posts in Xi forums over ways to access Databases using SAPXi.Having worked with other databases like Oracle and SQL,I was quite curious to use MaxDb in a an integration scenario. I got this oppurtunity during a story board demo we are preparing for our competency in Wipro, wherein we tried to evaluate a "DB access scenario" by creating stored procs in MaxDB using SQL Studio and accessing the same using SAP Xi.

I took hold of a standard example which I was building for a storyboard interface for the Competency center, the business case is irrelevant here, but the overall approach was just to upload XML data from a file onto a database.The actual implementation used Webdynpros/EP for the GUI and relevant screen information was tranformed into XML using a JAXB transformation layer and pushed thro' Xi to MaxDB.In a stripped down version for the weblog, I have used a simple File- DB scenario, with a file adapter pushing an XML file to XI and routed thro' a JDBC adapter to MaxDB by calling a simple Stored proc

In the MaxDB end, the work was simple , all I had to do was insert some Demand forecast information into a table. The Table structure is shown below

Fig1: Image shows the TABLE structure of the DemandForecast Table in MaxDB

Instead of directly inserting the data into the table from Xi, a stored proc will be called which contains the insert logic

CREATE DBPROC DEMOFORECASTINSERTPROC(IN FORECASTNUMBER NUMERIC(10) ,IN SERIALNO NUMERIC(10) ,IN ITEMCODE VARCHAR(50),IN ITEMNAME VARCHAR(50),IN SONUMBER VARCHAR(50),IN DEMANDQTY NUMERIC(10),IN SUPPLYQTY NUMERIC(10),IN OUTSOURCEDQTY NUMERIC(10)) ASTRYINSERT INTO "DEMOUSER"."DemandForecastQty"VALUES(:ForecastNumber,:SerialNo,:Itemcode,:ItemName,:SONumber,:demandQty,:supplyQty,:OutsourcedQty);    CATCHIF $RC = 0 THEN STOP ($RC, 'unexpected error');    

For the outbound interface, i have used a simple XML contain Forecast information which would be picked up by a fileadapter and posted to Xi.The challenge that we face now in pushing the Source data to the database is in creating the XSD which would replicate the procedure relevant structure in XI.

The inbound datatype structure to be used by the JDBC adapter channel for the inbound message interface is shown below

Fig2: Image shows the Inbound Interface Datatype representation in SAP XI

The important tags in the structure are the Stored procedurename element, action attribute and table element. Rest of the elements are the field tags corresponding to the table structure. Another important tag is the "type" tag which refers to the SQLDATATYPE type of the element being supplied to the database.

The relevant values to be filled in these datatype tags and stored procedure name to be used are filled in as part of the 'message mapping exercise' as shown below.

Fig3: Image shows the SQLDATATYPE being filled as a constant in the TYPE attribute

Fig4: Image shows the Stored procedure name being filled as a constant in the TABLE attribute

Fig5: Image shows the mapping between the source outbound interface structure and inbound interface

Finally the sender file adapter and JDBC file adapter configuration are done.After this the usual XI configuration such as sender, receiver agreements and th interface determinations are created and all trhe objects are activated.

Fig6: Image shows the JDBC Adapter configuration in the integration directory

Finally the src xml is placed in the folder polled by the file adapter and data is pushed to XI.We can monitor the message processing in "adaptermonitor screen" to check if JDBC adapter has received the data and called the stored procedure.Insert failures due to any datatype mismatch issues or data related problems can be seen in the monitor screen.We can check up if the data has been properly inserted into the MaxDB by using the "Select" query in SQL Studio screen.

Fig7: Image shows the Resulting of the SQL "Select" query in MaxDB