Query SQL Statement & Update SQL Statement

[Former Member]

Hi!

I configure the JDBC adapter sender (XI) to take data from MSSQL database.

I have to run select like this:

SELECT

tblMilestone.Site,

tblMilestone.Revision,

tblMilestone.MilestoneNameID,

tblMilestone.ApprovedDate,

tblMilestoneName.MilestoneName

FROM

tblMilestoneName

INNER JOIN tblMilestone ON tblMilestoneName.MilestoneNameID = tblMilestone.MilestoneNameID

WHERE tblMilestone.StatusCode = 1;

My question is what "Update SQL Statement" I should use in communication channel definition? I only need to update tblMilestone or this two tables?

Maybe you give me some example.

[Former Member · Accepted solution]

Hi,

As i can see in your case update of tblMilestone should be enough.

Regards,

Wojciech

[Former Member]

"As i can see in your case update of tblMilestone should be enough."

I don't test it yet but maybe something like this will be ok

UPDATE tblMilestone SET <added_updating_kolumn_name>=11

WHERE tblMilestone.StatusCode = 1;

What are you thinking?

[Former Member]

Check this from SAP help...

*******************************

Update SQL Statement

You have the following options:

&#9679; Enter a valid SQL statement that is to be applied to the database once the data (determined from the Query SQL Statement) has been successfully sent to the Integration Server/PCK.

It must be an INSERT, UPDATE, or DELETE statement.

&#9679; In place of the SQL statement, you can also enter <TEST>. Once the data determined from Query SQL Statement has been successfully sent, the data in the database remains unaltered.

This is recommended if the data has not only been read, but also changed by a stored procedure entered under Query SQL Statement.

[deviprasad_pothireddy]

Hi Grzegorz,

also refer this blog

/people/alessandro.berta/blog/2005/10/04/save-time-with-generalized-jdbc-datatypes

Regards,

Deviprasad.

[Former Member]

" The Update statement is not mandatory and you can use <test> if you do not want any updations to happen."

So You mean that i don't have to put any Update statement in my situation? For me will be better doing no updates.

I can write <test> and will be working?

[Former Member]

Hi,

But remember that this adapter works in poll mode. It means that it will select periodicly. So in some cases you need update in order to avoid multi selection of the same data.

Regards,

Wojciech

[bhavesh_kantilal]

Hi,

><i>So You mean that i don't have to put any Update statement in my situation? For me will be better doing no updates.

I can write <test> and will be working?</i>

yes, if you use <b><test></b> in update statement , it will work fine. But, your updation of the table will need to be handled else where else you will have multiple messages every polling interval.

Regards,

Bhavesh

[bhavesh_kantilal]

Hi,

the update statement allows you to update the selected rows in your DB so that the JDBC adapter does not select the same rows all over again during every polling interval and so as to avoid duplication of messages.

The Update statement is not mandatory and you can use <b><test></b> if you do not want any updations to happen.

The actual query depends on what you want to update in the table after the data has been selected in your sender JDBC adapter.

Regards,

Bhavesh

[Former Member]

Hi ,

You can use any valid SQL statement which is applied to the data base once Query SQL Statement has been processed succesful.

Check Update SQL statement in this link ...

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

Sekhar