SQL Query in JDBC

[Former Member]

Can somebody guide me on this Q i have:

1. can we write 2 separate SQL statements when we configure a JDBC adapter?

[Former Member]

Hi Aarthi,

It is possible to select fields more than two tables using JDBC adapter ,

Just follow following link:

<a href="/people/alessandro.berta/blog/2005/10/04/save-time-with-generalized-jdbc-datatypes XML FORMAT</a>

<a href="http://help.sap.com/saphelp_nw2004s/helpdata/en/4d/8c103e05df2e4b95cbcc68fed61705/frameset.htm">http://help.sap.com/saphelp_nw2004s/helpdata/en/4d/8c103e05df2e4b95cbcc68fed61705/frameset.htm</a>

here u can create appropriate message and specify action = 'SQL_QUERY' and write u r query in access field.

Regards,

Keith.

[Former Member]

Krishnamoorthy,

so can we read from two different tables at the same time when we configure a receiver adapter?

we have a scenario where we shoul dbe able to read 2 fields from 2 diffreent tables and do some simultaneous processing...can we achieve this..

[moorthy]

Hi ,

<i>so can we read from two different tables at the same time when we configure a receiver adapter?</i>

In receiver JDBC Adapter you can do the insert/update/delete the data and you can execute the stored procedures.

If you want to retrieve the data from the table, then use Sender JDBC adapter. In this case you can use 2 SQL queries but it is something like update the table etc.. and it is not like selecting the data from 2 tables.Go thru given help link in my earlier post.

To read the data from 2 different table you need to use <b>Sender JDBC adapter</b>, not receiver JDBC Adapter.

Option1 ) ideal way is to use Join conditions so that you will get one message with data from 2 different tables.

Option2) Use Stored Procedures ..

More-

Hope this helps,

Regards,

Moorthy

[bhavesh_kantilal]

Arathi,

Can you be more detailed in your requirements?

It is possible to also use a SELECT in the receiver JDBC adapter, but whether you need a SENDER / RECEIVER JDBC adapter depends on your requirements.

More detail can help us nail the issue.

Regards,

Bhavesh

[Former Member]

Hello all,

i have this scenario:where orders idocs are created in SAP and sent to Database.

1. we can configure a reveiver jdbc adapter to dump the orders into the database table, table1

2. simultaneously, there are 2 more tables table 2, and table3. we have to check for the presence of a record A in table 2. If that record A exists, we EXIT the scenario else

3. we go to table3 and check for the presence of a flag(it can be yes or no)if it is YES we wait for s seconds, if the flag is NO we set the flag to YES and insert the Record A into table2.

We can do this using BPM but my question is since we are reading two different tables at the same time i want to know whether we can configure sender jdbc adapter to read at the same time from two different tables???

4. i am also working on another scenario where i have to read 50 fields from 15 different tables and create an idoc...my understanding is that we can have stored proceedures and have a custom table in the database to store those values...but i was curious whether we can read those 5o fields from those 15 tables at the same time(multiple SQL statements)

Thanks

[Former Member]

Also people,

where do i assign points for helpful answer..if i dont find that selection box?

[bhavesh_kantilal]

Hello Aarthi,

<i>but my question is since we are reading two different tables at the same time i want to know whether we can configure sender jdbc adapter to read at the same time from two different tables???</i>

You can use the SELECT query or youu can use a STORED PROCEDURE <b>which contains exactly one SELECT statement</b>.</i> If you can use a JOIN and collect the data from the 2 tables then it is possible. But, you cannot have more than one select statemtent.

<i>but i was curious whether we can read those 5o fields from those 15 tables at the same time(multiple SQL statements)</i>

If you are worried about TRANSACTION HANDLING, then use the SERIALIZABLE Transaction setting. This way, your DB will be write locked when XI is accessing it until it does not finish its entire process.

Regards,

Bhavesh

[bhavesh_kantilal]

Aarthi,

You have a star across every answer that you have got for this thread.

You can assign pints by selecting the stars useful answer, very useful answer and solved problem.

Regards,

Bhavesh

[moorthy]

Hi Aarthi,

In Sender JDBC adapter you can write 2 sql statements..

For more- Check this -

http://help.sap.com/saphelp_nw2004s/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/frameset.htm

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

Hope this helps,

Regards,

Moorthy