JDBC sender

[Former Member]

Hi all,

I need a clarification on what will be the exact purpose of the processsing parameter (UPDATE SQL STATEMENT) in the JDBC sender.

Regards,

Nithiyanandam

[Former Member]

The main purpose is to update a table in the database using the sql statement use in the parameter.

[Former Member]

Update command is typically helpful when you read certain records from a DB table and you dont want to read those records again. So, for this purpose, you use select query to fetch certain records based on some selection criterion and then you check a field(let say, flag) for those records using UPDATE parameter and then make sure that in the selection criterion you mention this in the sql query that the records with checked flag field should not be picked again.

[GabrielSagaya]

There are 2 methods

1)

update <tablename> set flag='Y' where flag='N'

Select * from <tablename> where flag='N'

This means in order to avoid selecting old records from database, we are using flag (Y/N) to indicate that new records updated in the database should be read only one time that should be passed to xi server during the polling interval

2)update sql = <test>

Select * from <tablename> where flag='N'

once the data has been successfully sent, the system does not execute a database operation and the data remains unaltered in the database.

http://help.sap.com/saphelp_nw04/helpdata/en/1d/756b3c0d592c7fe10000000a11405a/frameset.htm

[Former Member]

HI

The JDBC Adapter allows access to any DB that allows a JDBC driver to access the DB. This driver is not supplied with the adapter but must come from the DB provider or a 3rd party. The adapter converts database content to XML and vice versa.

Database content can be read with any SQL statement using a special XML format. This format enables SQL INSERT, UPDATE, SELECT, DELETE or even stored procedures. A message is always processed in one database transaction.

A JDBC sender adapter has 2 essential fields,

1. SQL Statement

2. Update Statement

Your SQL statement will contain your SELECT statement and once your SQL statement is executed, UPDATE statement is executed. This is done so that records that have been selected by the SQL statement should not be selected again as the JDBC sender adapter will poll over the database for every poll interval.

If you want to SELECT something from your Database, then you will have to go for a JDBC sender adapter.

But, if you want to insert/update your Database, then you can go for a JDBC receiver adapter.

Multiple insertions are possible for a JDBC receiver, but multiple Selection queries (different select queries) are not possible for a single JDBC sender adapter.

The receiver Datatype when dealing with databases and JDBC adapter should be of a specific format so that the data can converted into an SQL statement by the JDBC adapter.

In the case of Insertion , the format wil be like how Anirban has shown in his blog.

In the case of others, the format is on this link,

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

Meanwhile, while creating the datatype, ACTION is an attribute.

To do this, in your datatype editor, instead of clicking Insert Element, you should select INSERT ATTRIBUTE

In receiver side, you can either select,insert or update. To specify the type of operation "action" attribute is needed.

cheers

[aashish_sinha]

Hi,

db.confirmDBSQLStatement=<SQL-Update-Statement>

Specify a valid SQL statement that can be applied to the database once the data (determined with db.processDBSQLStatement) has been successfully sent to the Integration Engine. This must be an INSERT, UPDATE, or DELETE statement. Examples of typical scenarios would be deleting the sent data from the database with a DELETE statement or selecting the data with an UPDATE statement. Instead of the SQL statement, you could also specify <TEST> (including the pointed parentheses). In this case, once the data has been successfully sent, the system does not execute a database operation and the data remains unaltered in the database.

The default value is <TEST>. This may be useful (besides for test purposes) when, for example, the data has not only been read by a stored procedure defined in the statement db.processDBSQLStatement, but has already been changed.

Refer http://help.sap.com/saphelp_nw04/helpdata/en/1d/756b3c0d592c7fe10000000a11405a/content.htm

Regards

Aashish Sinha

PS : reward points if helpful

[former_member240483]

If u picking a record if some x field is set to 'new'..after picking particular record adapter updates that X field status 'old' to avoid duplication.

Regards

Rao

[Former Member]

Hi,

The Update operator will parallely updates the records which are selected by the SELECT statement.

Thanks,

Kiran

[Former Member]

Hi,

The UPDATE SQL STATEMENT is used to update the existing row from the Data base.

Thus in JDBC Sender, when you will read the particular row, to avoid it from again reading the same file based on select statement, you need to mark the flag for the particular row as it is already read.

This is possible with the UPDATE SQL STATEMENT in Sender JDBC.

The UPDATE statement must alter exactly those data records that have been selected by the SELECT statement. You can ensure this is the case by using an identical WHERE clause. (See Processing Parameters, SQL Statement for Query, and SQL Statement for Update below).

With this you will add some kind of flag or Timestamp for that row. For this you need to maintain one column in Data base table.

Refer

Thanks

swarup

Edited by: Swarup Sawant on Jun 24, 2008 9:36 AM