SQL Statement out of BPM

[Former Member]

Hi all,

maybe it will be a quick hit for you, but I searched hours to find the solution.

My scenario is the following:

I receive a resultset from an JDBC Adapter. The requested Table has only four rows

with to coulums. The table refelects the status of four scanning devices and has

different statuses like 1,2,3, etc. Therefore I get back a message like described

in the help (http://help.sap.com/saphelp_nw04/helpdata/de/7e/5df96381ec72468a00815dd80f8b63/frameset.htm)

<resultset>

<row>

<ScannerNb>1</ ScannerNb>

<Status>2</ Status>

</row>

<row>

<ScannerNb>2</ ScannerNb>

<Status>0</ Status>

</row>

<row>

<ScannerNb>3</ ScannerNb>

<Status>2</ Status>

</row>

<row>

<ScannerNb>4</ ScannerNb>

<Status>0</ Status>

</row>

</resultset>

This message is forwarded to a BPM which first do a switch. If all Status are 0

then send and update Statement to the database and set all statuses to 4, otherwise

cancel.

To do that I would like to send just an update statement like described in the help

(http://help.sap.com/saphelp_nw04/helpdata/de/43/6211331c895f6ce10000000a1553f6/frameset.htm)

<root>

<stmt>

<ScanningStatus action="SQL_DML">

<access> UPDATE ScanningStatus SET Status=4

</access>

</Customers>

</stmt>

</root>

The Weblog "Save time with generalized JDBC datatypes"

(/people/alessandro.berta/blog/2005/10/04/save-time-with-generalized-jdbc-datatypes) describes how to do a mapping

between these both messages but the source message will not transfer any values to the target

message. Can I send a fixed message, here my SQL Statement, directly to the database out of the

BPM without doing an unneeded mapping before?

Regards Oliver

[Former Member · Accepted solution]

Hi,

all you need to insert a new field with name NEW when you useing sql server while jdbc adapter.

Hope these help you.

Regds's

Hari

[bhavesh_kantilal · Accepted solution]

hi,

am not able to understand the need for a BPM in your scenario.

use a Sender JDBC adapter to collect the data, check all status and if they match your criteria, create the Destination SQL statement as needed.

If they do not match the criteria, then create the destination message with an Update statement with a where clause that will always habe no success records.

Say you have a primary key in your DB, you can use that in your where clause as

UPDATE TABLENAME SET STATUS= 5 where PRIMARYKEYABC IS NULL.

This will not Update any rows ion your table and you can avoid a BPM too.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Hari

I am very sorry but could you please explain a little bit more detailed what you meant with "all you need to insert a new field with name NEW when you useing sql server while jdbc adapter"? In deed, I work with an SQL server and may be this can be helpful.

Regards Oliver

[thorsten_nordholmsbirk]

Hi Oliver,

You cannot avoid a mapping, but you can avoid a tranformation step in the BPM. After doing the check, simply send the original message from the BPM, routing it back to the database. For this message, specify a mapping from your original format to the format containing the update statement. In this mapping you do not actually have to use any of the values from the source document - just create the target document using constant values.

Best regards,

Thorsten

[Former Member]

Hi Thorsten,

The reason why I use a BPM is because further follow on steps will occur after the update. I.e. the next stept after the synchronous update will be to check if the update occurs correctly or if a time out will occurre. Therefore the BPM. Anyhow the information that I have to do a mapping is useful. I do not need to investigate further on this.

Thanks and Regards Oliver