"For Each" loop to call web service

Question

So I have two systems (ARCHIBUS and MAXIMO) that I need to synchronize some data between. System#1 (ARCHIBUS) has a "transfer table" that data is being dumped into from triggers on other tables. Every night, I need to take the rows in System#1's transfer table and make web service calls to System#2 (MAXIMO). Here's a simplified version of the transfer table:

ACTION  APPROVED    BUILDINGID  FLOORID ROOMID  DESCRIPTION               TRANSFERDATE
INSERT  1           7000                        New Engineering Building    
INSERT  1           7000        1               1st Floor   
UPDATE  1           6808                        Old Engineering Building    
DELETE  1           5401                
UPDATE  0           2387        3       326     Joe's Office    
UPDATE  1           3491        2       219     Bob's Office

If we look at the table, we'll see there are 3 different "actions": INSERT, UPDATE, and DELETE. If the action is INSERT, then we'd need to send a POST with a body message like follows:

Web Service URL:

http://maximoserver:9080/meaweb/services/LOCATIONSYNC

Message:

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:max="http://www.ibm.com/maximo">
   <soap:Header/>
   <soap:Body>
      <max:CreateLOCATIONSYNC>
         <max:LOCATIONSYNCSet>
            <max:LOCATIONS action="Add">
               <max:DESCRIPTION>New Engineering Building</max:DESCRIPTION>
               <max:HIERARCHYPATH>7000</max:HIERARCHYPATH>
               <max:LOCATION>7000</max:LOCATION>
               <max:PARENT></max:PARENT>
               <max:STATUS>OPERATING</max:STATUS>
            </max:LOCATIONS>
         </max:LOCATIONSYNCSet>
      </max:CreateLOCATIONSYNC>
   </soap:Body>
</soap:Envelope>

For an UPDATE action the message would be as follows:

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:max="http://www.ibm.com/maximo">
   <soap:Header/>
   <soap:Body>
      <max:UpdateLOCATIONSYNC>
         <max:LOCATIONSYNCSet>
            <max:LOCATIONS action="Change">
               <max:DESCRIPTION>Old Engineering Building</max:DESCRIPTION>
               <max:LOCATION>6808</max:LOCATION>
            </max:LOCATIONS>
         </max:LOCATIONSYNCSet>
      </max:UpdateLOCATIONSYNC>
   </soap:Body>
</soap:Envelope>

And finally for a DELETE action, the message would be:

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:max="http://www.ibm.com/maximo">
   <soap:Header/>
   <soap:Body>
      <max:UpdateLOCATIONSYNC>
         <max:LOCATIONSYNCSet>
            <max:LOCATIONS action="Change">
               <max:LOCATION>5401</max:LOCATION>
               <max:STATUS>DECOMMISSIONED</max:STATUS>
            </max:LOCATIONS>
         </max:LOCATIONSYNCSet>
      </max:UpdateLOCATIONSYNC>
   </soap:Body>
</soap:Envelope>

After the message is sent, if a HTTP "200" response is received, it should then set the TRANSFERDATE field to the current date/timestamp. If it gets anything else other than a 200 success message, it should set the TRANSFERDATE to null.

My thought is that this would HAVE to be done with a FOR EACH loop to create a message for each row and get a response and update the TRANSFERDATE field. Could anyone confirm this or provide some other method to do this? Also any issues I should be aware of?

Essentially I am, but I'd prefer to keep it within our current toolset which is just SQL Server and the web services right now. I don't want a separate program running on a server to query data and send it over. It's easier to keep it within the database. — D.R., Dec 15 '14 at 20:41
I'd suggest you learn to write simple programs outside of the database. Much easier to debug, and even easier to talk about. Most readers hearing that you want to write web service calls inside of the database will run away screaming in horror. Or at least turn away in disbelief. — John Saunders, Dec 15 '14 at 20:43
You can also sync using an Interface table. The application itself handles Insert, Update, or Delete and will let you skip over errors (with logging), or stop at the error point. Some fields in the Maximo table already have some sort of transaction date field that will contain your TRANSFERDATE such as TRANSDATE, CHANGEDATE, or something similar. — Sun, Dec 17 '14 at 22:11
@sunk818 - Can you give me a sample of how to set up the Interface tables for Maximo to do this? — D.R., Dec 29 '14 at 20:18
@D.R. You can try this link: http://www.xcentricway.ca/tutorial02.htm — Sun, Feb 23 '17 at 16:12

"For Each" loop to call web service

0 Answers0