JDBC Sender MSSQL Stored Procedure - Multiple Select Statements

[Former Member]

Hello all,

I will proceed to tell you my problem, for which solution I request your kind advice:

Im working in a project for a retailer, which consists in sending the information from erp and sql server to pos thru XI interfaces.

One of the interfaces is about sending items from sql server to a file so the pos can load it into the system. For doing so I have devloped an stored procedure which function is to return several select statements as many stores the retailer might have, so they can have a different file per store along with its corresponding items in it.

The thing is that XI just gets the first select statement and creates the corresponding file, but it seems to ignore the remaining responses as I'm neither getting any file nor an error afterwards.

So, my question is: is XI capable of handling multiple select responses from an Stored Procedure in graphical mapping??? Or am I just wasting my time trying?

Thanks in advice for your help.

Regards.

[Former Member · Accepted solution]

Hi

You have to modify your Stored procedure as discussed in the below blog,

Regards

Ramg

[Former Member · Accepted solution]

Hello,

Thank you both for asking.

Ramkumar:

I had laready checked that blog, however, as my understanding this applies when you are aware of the statements you want to execute. In my scenario my statements are determined by the number of stores; the more stores, the more select will be executed.

Neetesh:

What do you mean with "Make sure your multiple select queries are being executed.". Are not all the statements under execution when they are within an stored procedure??

Thanks.

Regards.

[Former Member · Accepted solution]

Anibal,

That's how it is expected to work. My point is that there can be a possibility that there is some problem in the SP and that only first select is being executed.

You can check your SP stand-alone or use the log SQL parameter to check how your query is being formed. I believe this parameter can be used even in the case of SP.

Hope this clarifies.

Regards,

Neetesh

[Former Member · Accepted solution]

Ok Neetesh, I get it now.

I test it directly in query analyzer and is retrieving the information just fine according to the stores I have.

Maybe is there something special that needs to be done at graphical mapping to make this work?? My source mapping structure is very simple having just 2 contexts: First the main one and second the row that returns from the SP.

Thanks.

Regards.

[Former Member · Accepted solution]

Hi

what will be the output from your procedure?

Ater executing the procedure will it return multiple results with diff parameters for different stores or same parameters?

If this is case it wont work with PI.

Regards

Ramg

[Former Member · Accepted solution]

Ramkumar:

For example, let's say there is the following data in a table:

STOREITEM--

1001 10000001

1002 10000001

1003 10000001

1001 10000002

1002 10000002

1003 10000002

1001 10000003

1002 10000003

1003 10000003

The stored procedure will read data and revert the outcome by store:

SELECT NUMBER 1

1001 10000001

1001 10000002

1001 10000003

SELECT NUMBER 2

1002 10000001

1002 10000002

1002 10000003

SELECT NUMBER 3

1003 10000001

1003 10000002

1003 10000003

Of course in a real scenario there's no way for us to know how many selects will take place beforehand as this will just be given once the stored procedure has count the number of stores.

Thanks.

Regards.

[Former Member · Accepted solution]

Anibal

Multiple select statement doent work with XI.

  Specify an SQL EXECUTE statement to execute a stored procedure, which contains *exactly one SELECT* statement.

http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

Instead you can modify your procedure to return all stores in single statement and after receving the datas in PI you can Multi mapping to separate the data based on diff stores.

Regards

Ramg

[Former Member · Accepted solution]

Ramkumar,

I also tried to handle it like that, but as there is no context change at store level within the select structure there is no way I can create files per store with graphical mapping. Is there some special feature that allows me to do so?

Thanks.

Anibal.

[Former Member · Accepted solution]

Anibal

Try using sort node function and use Splitbyvalue (value changed).

Regards

Ramg

[Former Member · Accepted solution]

Ramkumar:

Ill try and let you know. Thanks.

Anibal.

[Former Member · Accepted solution]

Hi

Try the below mapping!!

Store Number->RemoveContext->Sort--->SplitByValue (value change) -

---

> CollapseContext->Target

Regards

Ramg

[Former Member · Accepted solution]

Hello Ramkumar,

After 5 days trying, I finally made it work out applying your advice. Below the short explanation of what I did:

My Source structure is: Main Node->Row->Records (Material Number, StoreNum, Price, Status)

My Target structure is: Main Node->File Node->Record Node->Records ( Material Number, Price, Status)

The key was to make all the occurrences happen against StoreNum node. So, based on what you adviced these two where the key mappings:

1) The Mapping that will create a new file for each different store that comes in the query (In my case, query was already sort by store using an sql "order by" function, if not you can also use xi node function "sort" as Ramkumar suggested)

StoreNum->RemoveContext->SplitbyValue (Value Changed)->Collapse Contexts->File Node

2) The Mapping that will create each record in its corresponding store file:

StoreNum->RemoveContext->SplitbyValue (Value Changed)->Record Node

And Voilá !!! It worked.

Thanks very much Ramkumar.

Regards.

[Former Member]

Hi,

So, my question is: is XI capable of handling multiple select responses from an Stored Procedure in graphical mapping??? Or am I just wasting my time trying?

Yes, PI is capable of handling multiple selects. Make sure your multiple select queries are being executed.

Regards,

Neetesh