How to call multiple SQL using the JDBC adapter

[Former Member]

Hi,

I have a scenario where i have to do 4 different select queries from the DB.

How this should be done using the JDBC adapter ?

create 4 different comunication channels?

[Former Member]

When I query the 4 tables using an INNER JOIN I end up with XML like this

 <?xml version="1.0" encoding="utf-8" ?> 
- <ns:MT_DB_XX1 xmlns:ns="http://mynamespace/Data">
- <row>
  <Unit>MP3</Unit> 
  <Date_Time>2007-04-12 21:21:00.0</Date_Time> 
  <Capacity>4.000</Capacity> 
  <Processed>N</Processed> 
  <Unit>MP3</Unit> 
  <Date_Time>2007-04-12 21:21:00.0</Date_Time> 
  <Capacity>33.000</Capacity> 
  <Fcst_Min_output>3.000</Fcst_Min_output> 
  <Processed>N</Processed> 
  <Unit>MP3</Unit> 
  <Date_Time>2007-04-12 21:21:00.0</Date_Time> 
  <UnitOut>0</UnitOut> 
  <Hours>4</Hours> 
  <DerateMW>4.00</DerateMW> 
  <Processed>N</Processed> 
  <UNIT>MP3</UNIT> 
  <DATE_TIME>2007-04-12 21:21:00.0</DATE_TIME> 
  <NominationValue>3.000</NominationValue> 
  <Start_Date>2007-11-12 00:00:00.0</Start_Date> 
  <End_Date>2007-11-13 00:00:00.0</End_Date> 
  <Processed>N</Processed> 
  <SAPXI_Flag>0</SAPXI_Flag> 
  </row>
  </ns:MT_DB_XX1>

What I am ideally looking for is

 <?xml version="1.0" encoding="utf-8" ?> 
- <ns:MT_DB_XX1 xmlns:ns="http://mynamespace/Data">
- <row>
    <Unit>MP3</Unit> 
    <Date_Time>2007-04-12 21:21:00.0</Date_Time> 
    <Capacity>4.000</Capacity> 
    <Processed>N</Processed> 
 </row>
  <row2>
     <Unit>MP3</Unit> 
     <Date_Time>2007-04-12 21:21:00.0</Date_Time> 
     <Capacity>33.000</Capacity> 
     <Fcst_Min_output>3.000</Fcst_Min_output> 
     <Processed>N</Processed> 
  </row2>
  <row3>  
    <Unit>MP3</Unit> 
    <Date_Time>2007-04-12 21:21:00.0</Date_Time> 
    <UnitOut>0</UnitOut> 
    <Hours>4</Hours> 
    <DerateMW>4.00</DerateMW> 
    <Processed>N</Processed> 
  </row3>  
 </row4>
   <UNIT>MP3</UNIT> 
   <DATE_TIME>2007-04-12 21:21:00.0</DATE_TIME> 
   <NominationValue>3.000</NominationValue> 
   <Start_Date>2007-11-12 00:00:00.0</Start_Date> 
   <End_Date>2007-11-13 00:00:00.0</End_Date> 
   <Processed>N</Processed> 
   <SAPXI_Flag>0</SAPXI_Flag> 
  </row4>
  </ns:MT_DB_XX1>

Is there anyway of doing this as I can't change the Source Message Type?

[Former Member]

Hi Udi,

Its is not possible to have multiple SELECT statements, but instead you can use a inner join.

Example of such a statement is :

select ekko_{ebeln ekpo}matnr makt_{maktx ekpo}menge

from ekko

inner join ekpo

on ekko_{ebeln = ekpo}ebeln

inner join makt

on ekpo_{matnr = makt}matnr

where ekko~ebeln = p_po.

Thanks,

abhy

[Former Member]

Thank.

I have 4 different queries

so i guess i will have 4 different adapters.

[Former Member]

> Hi Udi,

> Its is not possible to have multiple SELECT

> statements, but instead you can use a inner join.

>

> Example of such a statement is :

>

> select ekko_{ebeln ekpo}matnr makt_{maktx ekpo}menge

> from ekko

> inner join ekpo

> on ekko_{ebeln = ekpo}ebeln

> ner join makt

> on ekpo_{matnr = makt}matnr

> re ekko~ebeln = p_po.

>

> Thanks,

> abhy

If you use an INNER JOIN in the select statement query, how do you manage the Update statement? I have to query 4 different tables and using a join is no problem. However, I need to Update a field in each of these tables but I can only update one table in the "Update SQL statement" field.

I've set up 4 seperate communication channels but I don't know how to set up the Sender Agreement in the Integration Scenario.

Can anyone help?

[Former Member]

Hi Damien,

>>have to query 4 different tables and using a join is no problem. However, I need to Update a field in each of these tables

Why do you need this. Usually the requirement is to prevent reprocessing of the same records. Correct? For this purpose have the status field in the primary table. In the above example <b>ekko</b>. Now you can update the status field in this table and prevent processing of the same records again.

Regards,

Jai Shankar

[Former Member]

I never thought of that!! That will do it. I can delete 3 of the communication channels too. I can't see the option to award points. Thanks anyway Jai Shankar!

[Former Member]

Most welcome

Regards,

Jai Shankar

[bhavesh_kantilal]

Hi Udi,

If you want to SELECT something from your Database, then you will have to go for a JDBC sender adapter.

But, if you want to insert/update your Database, then you can go for a JDBC receiver adapter.

Multiple insertions are possible for a JDBC receiver, but multiple SELECTIOn queries (different select queries) are not possible for a single JDBC sender adapter.

Just check these links to understand how JDBC adapters work,

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

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

Regards,

bhavesh

[Former Member]

Thank you al for the answers.

For unknown reason i can't give points.

The option does not exist

[bhavesh_kantilal]

Hi Udi,

your JDBC sender adapter has 2 essential fields,

1. SQL Statement

2. Update Statement

Your SQL statement wil contain your SELECT statement and once your SQL statement is executed, UPDATE statement is executed. This is done so that records that have been SELECTed by the SQL statement should not be selcted again as the JDBC sender adapter will poll over the database for evey poll interval.

I dont think multiple SQL Select statements can be given for a single communication channel and also, I dont think Stored Procedure execution is possible for a SENDER Jdbc adapter ( possible for a receiver). So, i guess you will have to go for four communication channels.

Regards,

bhavesh

[udo_martens]

Hi Udi,

no, just use 4 statements in one message (JDBC receiver adapter).

Regards,

Udo

[Former Member]

Thanks!

[udo_martens]

Hi Udi,

take the JDBC sender:

if you want to poll data periodicly. In this case create 4 communication channels (or build a complex SQL statement).

take the JDBC receiver adapter:

if you want to trigger the data base operation by a message. You can put 4 statements into one message.

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

Regards,

Udo