Receiver JDBC CC with UPDATE after SELECT query

[Former Member]

Hi all,

I want to realize a Receiver JDBC CC that must perform before a SELECT query statement and after an UPDATE statement.

Any ideas??

Thank you in advance,

GB

[Former Member]

In addition my SELECT query must have the ORDER BY clause for the resultset. How can I perfrom this?

Thanks

[Former Member]

Hi,

What is the where clause of the select query. If it does not depend on some input and can be static, you can use jdbc sender adapter where you can also specify the update statement after select.

otherwise, you may write stored procedure.

What is your sender system?

Thanks,

Beena.

[Former Member]

The using of Receiver JDBC CC is mandatory.

Here an example of the query I must realize:

SELECT col1, col2, col3, ..., col10, col11 FROM table WHERE col10 = 'X' ORDER BY col1,col3,col2.

[Former Member]

Hi,

I am not sure if you can use order by in select.

[http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm]

But you can use action= SQL_QUERY and specify the query.

Also check SAP Note: 831162

Transaction Handling (Receiver)

As per this, I think if you will specify 2 statements in the structure (action = SQL_QUERY and action = UPDATE) and if auto commit is disabled, they will be executed in one transaction.

Thanks,

Beena.

[Former Member]

Hi Beena,

thanks for your suggestion.

Only one doubt.

Since I'm using a synchronous MI to perform the SQL_QUERY, where I obtain the resultset?

Before SQL_DML (Update) statement or after it?

<root>
<StatementName1>
<anyName action=u201D SQL_QUERYu201D>
<access>SELECT SQL-String with optional placeholder(s)</access>
<key>
  <placeholder1>value1</placeholder1>
  <placeholder2>value2<placeholder2>
</key>
</anyName > 
</StatementName1>
<StatementName2>
<anyName action=u201DSQL_DMLu201D>
<access>UPDATE SQL-String with optional placeholder(s)</access>
<key>
  <placeholder1>value1</placeholder1>
  <placeholder2>value2<placeholder2>
</key>
</anyName > 
</StatementName2>
</root>

Also, how can I disabling the DB auto commit?

Thanks,

GB

[Former Member]

Hi Gianluca,

the resultset is obtained automatically, it has the name of your query+ response (query_response)

you need 2 message types for them, query and query_response, between musst be the mapping etc..

if this answer doesnt help, specify your question please

2nd question:

auto-commit is default disabled, you can see it in receiver options under parameter and advanced (you must activate advanced options, then is there a field db auto commit)

Database u2018Auto-Commitu2019 Enabled (No Transaction Handling)

Set the indicator if you want to deactivate the logical unit of work, which the JDBC adapter requires to guarantee that data in the database is consistent.

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

Regards, CC