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
Instead of directly inserting the data into the table from Xi, a stored proc will be called which contains the insert logic
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
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.
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.
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.