cancel
Showing results for 
Search instead for 
Did you mean: 

Synchronous JDBC select

Former Member
0 Kudos

hi,

Based on the call from webservice I need to select data from two tables and send back. Expected output is one row from one table and multiple rows from the other. So I created a structure as shown:

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:MT_DB_PS_Req xmlns:ns0="http://timken.com/poc/webservices">

- <Statement>

- <Table action="SELECT">

<table>poc.order_operation</table>

- <access>

<plant />

<work_center_id />

<work_center />

<operation_status />

<material_produced />

<order_quantity />

<quantity_uom />

<quantity_completed />

<quantity_scrapped />

<activity />

<std_activity_quantity />

<actual_activity_quantity />

<activity_quantity_uom />

<activity_start_time />

<activity_end_time />

<description />

</access>

- <key>

<order_number>String</order_number>

<operation_id>String</operation_id>

<routing_seq_num>int</routing_seq_num>

</key>

</Table>

</Statement>

- <Statement1>

- <Table action="SELECT">

<table>poc.operation_material</table>

- <access>

<material_id />

<material_source />

<std_material_usage />

<actual_material_usage />

<usage_uom />

</access>

- <key1>

<order_number>String</order_number>

<operation_id>String</operation_id>

<routing_seq_num>int</routing_seq_num>

</key1>

</Table>

</Statement1>

</ns0:MT_DB_PS_Req>

I am unable to get any output. When I check in RWB the ouptput is shown as:

<?xml version="1.0" encoding="utf-8" ?>

- <ns0:MT_DB_PS_Req_response xmlns:ns0="http://timken.com/poc/webservices">

<Statement_response />

<Statement1_response />

</ns0:MT_DB_PS_Req_response>

Can someone help me with this.

thank you

Accepted Solutions (1)

Accepted Solutions (1)

bhavesh_kantilal
Active Contributor
0 Kudos

Hi,

Check if the Response Messge Type is of this format.

<?xml version="1.0" encoding="UTF-8" ?> 
- <ns0:MT_DB_PS_Req<b>_response</b> xmlns:ns0="http://timken.com/poc/webservices">
 <Statement<b>_response</b>>
<b><row></b>
<plant /> 
<work_center_id /> 
<work_center /> 
<operation_status /> 
<material_produced /> 
<order_quantity /> 
<quantity_uom /> 
<quantity_completed /> 
<quantity_scrapped /> 
<activity /> 
<std_activity_quantity /> 
<actual_activity_quantity /> 
<activity_quantity_uom /> 
<activity_start_time /> 
<activity_end_time /> 
<description /> 
<b></row></b>
</Statement<b>_response</b>>
<Statement1<b>_response</b>>
<b><row></b>
<material_id /> 
<material_source /> 
<std_material_usage /> 
<actual_material_usage /> 
<usage_uom /> 
</row>
</Statement1<b>_response</b>>
</ns0:MT_DB_PS_Req>

Regards,

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

I wasnt sure whether we can give two response statements. But, yes it did work and my problem is solved.

Thank you

Answers (2)

Answers (2)

moorthy
Active Contributor
0 Kudos

HI,

CHeck this blog for message type structure etc-

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

First try to get the output for one table - to debug,

Hope this helps,

Regards,

Moorthy

Former Member
0 Kudos

In the response structure I have like:

<Statement_response>

<row> (1)

.

.

.

</row>

<row1> (1..undounded)

.

.

</row1>

</Statement_response>

Is this a valid structure??

agasthuri_doss
Active Contributor
0 Kudos

Hi Raj,

Try out

>>><row1> (1..undounded)

As (0 unbound )

Regards

Agasthuri Doss

Former Member
0 Kudos

hi Agasthuri Doss,

I tried giving 0..unbounded. I got the result but it is giving the output from only one table, meaning the first <row> structure in <Statement_response> is working fine but for the second one I am not getting anything...

help is appreciated

agasthuri_doss
Active Contributor
0 Kudos

Hi raj,

So half way Sucess.. Check out the other Table --<table>poc.operation_material</table>

Has the values for all the fields which you fetch.

Regards

Agasthuri Doss

Former Member
0 Kudos

Halfway thru,s.

I checked the other table and the content, it should return some fields but i am unable to get any..

I have a doubt whether we can send two select statements like as i did ....

agasthuri_doss
Active Contributor
0 Kudos

Hi,

Great , Why can't you try with one select statement,

Regards

Agasthuri Doss

Former Member
0 Kudos

hi,

I want data from two different tables and I am not sure whether we can use one select statment to get data from two different tables.

Any ideas pls...

Former Member
0 Kudos

Raj

You are trying to retrieve 2 different result sets using single JDBC select.Instead write a stored proc, which will pull the information from the 2 tables into a cursor/temporary table and do a JDBC retrieval on the stored proc.With this design Life will get much easier..

agasthuri_doss
Active Contributor
0 Kudos

Hi Raj,

Since two different table as Sri Ram said go for a temporary table where two tables values are temperoary stored and reterive it. That is a fine idea to deal it.

Regards

Agasthuri Doss

Former Member
0 Kudos

hi Sriram,

Thanks for the response, but can you point me to any blogs or other documents that explain how to deal with stored procedures in synchronous reciever JDBC adapter.

I have no idea how to do that.

thank you

agasthuri_doss
Active Contributor
0 Kudos

Hi Raj,

This will help you

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

Regards

Agasthuri Doss

agasthuri_doss
Active Contributor
0 Kudos

Hi raj,

1) Check whether the Table has the values,

2) use occurance 0:unbounded for access element.

3)Whether the Table has many records..and not Indexed.

http://help.sap.com/saphelp_erp2004/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Regards

Agasthur iDoss