JDBC Stored Procedure - Problem with - update_count

[Former Member]

I have the following scenario ABAP Proxy -> XI -> Database and I solved it as described in Sivas blog.

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

However I have one problem. In dependence of the stored procedure input parameters the result message isn't always of the same type because the stored procedure makes sometimes some update operation. If this is the case my result looks like this:

- <statement_response>

- <response_1>

<update_count>1126</update_count>

</response_1>

- <response_2>

<update_count>1101</update_count>

</response_2>

- <response_3>

<update_count>1114</update_count>

</response_3>

- <response_4>

<update_count>1101</update_count>

</response_4>

- <response_5>

<update_count>1092</update_count>

</response_5>

- <response_6>

<update_count>1126</update_count>

</response_6>

- <response_7>

<update_count>1126</update_count>

</response_7>

- <response_8>

<update_count>1092</update_count>

</response_8>

- <response_9>

<update_count>1107</update_count>

</response_9>

- <response_10>

<update_count>7</update_count>

</response_10>

- <response_11>

- <row>

CONTENT

</row>

</response_11>

- <response_12>

<update_count>1</update_count>

</response_12>

</statement_response>

Otherwise I have the following result:

- <statement_response>

- <response_1>

- <row>

CONTENT

</row>

</response_1>

</statement_response>

I would like to deal only with the second one because I have some trouble with mapping. Is there any opportunity to suppress these "update_count" –response nodes?

Regards Gil

[Former Member]

I will try to explain my problem again.

I call a stored procedure with an input parameter. If this parameter is 1 the procedure does some update operations at the database. After that the procedure delivers the result set. In this case I have the result set in the node <response_11> as you can see in my first post.

If I call the procedure with value 0 the procedure delivers only some records. So the result set is in the node <response_1>.

If I want to define a message mapping I need the <row> node but I never know which <response_??> node contains my result set.

I hope, you understand my problem now.

[Former Member]

Hi,

Can you post the message structure for JDBC response with cardinality.

Also, try this in Message mapping.

select "row" node and change context to statement_response. Add a RemoveContext after this row node. This will return all selected rows.

Regards,

Uma

[Former Member]

<xsd:element name="MT_BEW_DATEN_SP_response" type="DT_BEW_DATEN_SP_response" />

<xsd:complexType name="DT_BEW_DATEN_SP_response">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

9aa61b10b50611dbc67d005056896de9

</xsd:appinfo>

</xsd:annotation>

<xsd:sequence>

<xsd:element name="statement_response">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

1d057da0b2b711db95a7ea340a2042cd

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="response_1">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

1d057da1b2b711dbb854ea340a2042cd

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="row" minOccurs="0" maxOccurs="unbounded">

....

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

Removing context doesn't work. I've tried it already.

[Former Member]

Hi,

I think in your stored procedure, you are updating the tables based on input value.

i.e., by using if statements update statements will execute.

Here is one way to solve the problem.

Modify your stored procedure, so that even for input value with 0 all update statements will execute but with some false condition. So all update statements will return 0 as update_count and response_11 will have row element.

Regards,

Uma

[Former Member]

> I think in your stored procedure, you are updating

> the tables based on input value.

> i.e., by using if statements update statements will

> execute.

>

> Here is one way to solve the problem.

> Modify your stored procedure, so that even for input

> value with 0 all update statements will execute but

> with some false condition. So all update statements

> will return 0 as update_count and response_11 will

> have row element.

Yes, you've nailed the point and your idea is nice but the stored procedure is not in my hand. Otherwise I would map nodes response_1 and response_11. But if the "owner" of the stored procedure changes the code I will get the result set perhaps in node response_9.

That's the reason why I want to find a way to get rid of these update information.

[Former Member]

Hi,

If you are not able to modify stored procedure, then you can go for Java Mapping.

Using java code you can find out which response will have row element.

Or, if you know exactly which response will have row element when your input parameter is 1, then using If else function you can map either row element of response_1 or row element of response_9

Regards,

Uma

[Former Member]

Hi Gil,

create a MessageType for the response where your elements are optional (0..1).

Create a mapping with dependency wether a element is available or not.

For this use the function CREATEIF

Regards Mario

[Former Member]

I had a similar problem when executing a stored procedure on SQL server from XI.

The issue was that it was returning back on a successful update/insert with the count....Then there was a PL-SQL statement that we added in the stored procedure to wait until the resultlist was formed....

So from what you are telling, you only want the result list...is that correct ?

Unfortunatley, i had a person in the team who was an expert on DB/Stroed procedure/SQL server stuff...so although i am aware of what we did, i cannot recollect the statement/parameter that we set..

I feel this can be achieved by doing something in the stored procedutre so that it returns only the resultlist...

Thanks.

[bhavesh_kantilal]

Gil, The query is not very clear.

Can you explain further what the requirement is?

Regards

Bhavesh