JDBC Query

[Former Member]

Hi All,

Could you please tell me if we can have multiple select, insert, delete etc quesry's in one database fetch using JDBC adapter.

Is multiple UPDATE commands possible.

Also why is update command used??

Regards,

Ashish

[Former Member · Accepted solution]

>>Could you please tell me if we can have multiple select, insert, delete etc quesry's in one database fetch using JDBC adapter.

No. This is not possible directly

But you can make use of stored procedure that reurns resultsets.

>>Also why is update command used??

Update command is used to prevent reprocessing of the same msg.

Regards,

Jai Shankar

[bhavesh_kantilal · Accepted solution]

Jai,

This is certainly possible.

Create multiple STATEMENT level tags for every action you want with the appropriate format, and you can perfrom all the functionalities in one call.

Regards

Bhavesh

[Former Member · Accepted solution]

Bhavesh,

Sorry. I was mentioning only about Sender JDBC adapter.

Yes, this is very much possible in receiver JDBC adapter to insert or update or select multiple records at time.

Sorry for the confusion.

Regards,

Jai Shankar

[bhavesh_kantilal · Accepted solution]

Jai,

I got the hint from others reply as well that we are all confused wherther the question is for sender or receievr JDBC adapter! no issues..

Ashish,

To sum up,

Sender JDBC Adapter --> The JDBC adapter will poll every poll interval and execute the select statement to check if there are rows that satisfy the Select Statement in the database. Now, this can lead to redundancy of data and to avoid this, you use an UPDATE statement to Update the database so that the same data is not selected again.

Receiver JDBC adapter --> My earlier reply should help hopefully

Regards

Bhavesh

[Former Member · Accepted solution]

What is a stored procedure call?

Ashish

[Former Member · Accepted solution]

Hi,

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server.

Stored procedures can be executed with different parameters and results.

You can call stored procedure both in sender and receiver JDBC adapter.

In sender JDBC adapter, the stored procedure must contains exactly one SELECT statement.

Regards,

Uma

[bhavesh_kantilal · Accepted solution]

Ashish,

Stored Procedure is a Database concept and equivalent to say a Function / Method In Java that perfroms some series of steps.

When you wnat to perform a series of steps on the database like insert , update with condtions and return some value back, Stored Procedures help.

Regards

Bhavesh

[Former Member · Accepted solution]

Is QOS BE, EO and EOIO possible with JDBC adapter

Ashish

[bhavesh_kantilal · Accepted solution]

Ashish,

Sender --> Only EO and EOIO is possible.

Receiver --> all three are possible.

Regards

Bhavesh

[Former Member]

Hi,

~~~<i>Could you please tell me if we can have multiple select, insert, delete etc quesry's in one database fetch using JDBC adapter.</i>

Within one database message you can add <b>multiple statements</b> to perform select, insert, delete etc.

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

~~~<i>Is multiple UPDATE commands possible.</i>

In sender JDBC communication channel, One update command can be entered. It can be Insert, Select or Delete

~~~<i>why is update command used??</i>

To make sure each record in database will be selected only once for XI scenario execution.

Regards,

Uma

Message was edited by:

Uma Maheswari

[bhavesh_kantilal]

Ashsih,

Sure we can do everything in one shot.

For the receievr JDBC adapter , the datatype that you create matters a lot.

If you look into <a href="http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm">this</a> link and see the datatype, you have multiple STATEMENT tags and each of these perfroms a separate function like Insert, Update , Delete etc. So, you can do all these function using a JDBC receiver adapter in one shot or in multiple calls as per requirement.

Multiple UPDATE is also possible. Update command as the name implies is used to Update the database with some values for rows satisfying some condition.

Please look into these blogs on Receiver JDBC adapter,

<a href="/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30">FILE to JDBC Adapter using SAP XI 3.0</a>

<a href="/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step">JDBC Receiver Adapter -- Synchronous Select – Step by Step</a>

Regards

Bhavesh

[Former Member]

Hi,

you acheive this by using stored procedures,Please refer this below links gives you better idea.

http://help.sap.com/saphelp_nw2004s/helpdata/en/45/023c41325fa831e10000000a1550b0/frameset.htm

http://help.sap.com/saphelp_nw2004s/helpdata/en/41/5a805f85833e17e10000000a114cbd/frameset.htm

Thanks and Regards,

Sekhar