Solved

Calling SQL Stored Procedure from within XML Map

  • 1 November 2023
  • 2 replies
  • 64 views
ericbux
Userlevel 3
Badge

I don’t know if this is possible but I have a standard 850 map that I am able to use with all of our trading partners.  I am using the SQL connector to return the data of a simple Header/Detail table relationship.  Up until now, the data from the Detail table has always been sufficient for all of our current connections, but I have a new connection i’m setting up and they require additional data.

Currently, I am creating POLoop1 > PO1and sending the following:
PO101 linenumber
PO102 Quantity
PO103 Identifier (EA)
PO104 Price
PO105 (empty)
PO106 Identifier (N4)
PO107 UniqueID

This new setup requires PID (which I have added) but they only need PID05 (item description).

My question is: Is is possible to add some arc script within my loop where I can call a SQL stored proc and pass it the value of PO107 and map the result to PID05?

Thanks

icon

Best answer by James B 1 November 2023, 18:08

View original

2 replies

ericbux
Userlevel 3
Badge

UPDATE: I did make some progress.  I added a script inside the PO1Loop1

<arc:set attr="db.conn" value="server=connstring" />
<arc:set attr="db.driver" value="System.Data.CData.Sql" />
<arc:set attr="db.sp" value="MyStoredProc" />
<arc:set attr="db.paramname#" value="UNIQUEID" />
<arc:set attr="db.paramvalue#" value="[xpath(UNIQUEID)]" />
<arc:call op="dbCall" item="db">
	<arc:set attr="_map.ItemDescription">[db.db:ItemDescription | def]</arc:set>
</arc:call>

However, it returns the result for the first procedure call for all PID05 elements.  I suppose this code needs expanded to loop over each UNIQUEID?

Any ideas would be appreciated.

James B
Userlevel 6
Badge

Hi Eric, 

 

The best designer-driven way to approach this is to leverage a feature that was added in the most recent quarterly update to CData Arc 2023 at https://arc.cdata.com/support/builds/ which now includes a new Lookup Stored Procedure action in the SQL connector: 

https://cdn.cdata.com/help/AZJ/mft/SQL-Server-LSP.html

 

This was recently added, but it’s a nice designer driven way to enrich an existing XML document with values that would be returned from the lookup to a stored procedure. 

 

With that said, the way that you are doing this now will also work just as well - you can use code script to execute the stored procedure call as you are looping over each element of the Detail table.

As long as you are entering the code block for the dbCall operation within the Foreach Detail so that: 

  1. You are issuing that call n times for each detail and
  2. [xpath(UNIQUEID)] is giving you a unique value for each detail

That should work just fine. 

 

I see that you are storing the return in the _map item, but if you’re doing no further manipulation with that variable, you could also use a custom script in PO105 and set the value in result.text, like this:

 

<arc:set attr="db.conn" value="server=connstring" />
<arc:set attr="db.driver" value="System.Data.CData.Sql" />
<arc:set attr="db.sp" value="MyStoredProc" />
<arc:set attr="db.paramname#" value="UNIQUEID" />
<arc:set attr="db.paramvalue#" value="[xpath(UNIQUEID)]" />
<arc:call op="dbCall" item="db">
	<arc:set attr="result.text">[db.db:ItemDescription | def]</arc:set>
</arc:call>

 

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings