Two stage COMMIT needed with JDBC adapter

[Former Member]

I have the following scenario to execute using the JDBC adapter:

1) INSERT several hundred records into a database

2) COMMIT the records; only one commit after all records have been inserted.

3) Run a stored procedure that will process the records in the database, including those just committed.

The crucial thing here is running the COMMIT between the INSERT statements and the Stored procedure. I now know how to do points 1) and 3), but how to make sure that a COMMIT is performed between the inserts and execution of the stored procedure?

Can anybody tell me how to do this?

BR,

Tony.

[Former Member]

Anthony,

I think its better to write a single wrapper Stored procedure(SP) inside which you can call anothe store procedure. You can do whatever you want ( inside first SP) and commit. In this way you will have only one call to database and process would be much faster.

Just one more thought / approach.

Hope this will help.

Thanks,

Nilesh

[Former Member]

Hi Anthony,

By using normal insert statement we can insert the records to the databse. if the JDBC adapter is at receiver side or sender side go to the Advanced tab and select Advanced mode check box and select the Database Auto commit-Enabled check box, then after the data inserted to the database it automatically commits.

For stored procedures

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

Regards

Ramesh

[aashish_sinha]

Hi,

At the time of configuration of Sender JDBC adapter, you can use db.autoCommit=NO|YES

Use this parameter to deactivate the logical unit of work required by the JDBC adapter to guarantee that the data in the database is consistent. This option is required for JDBC drivers that do not support transactions. To avoid data inconsistencies in the database when the logical unit of work is deactivated, ensure that multiple database transactions cannot access the database simultaneously.

The default value is NO.

Also db.transactionIsolation=0|1|2|4|8

use 2 for TRANSACTION_READ_COMMITTED.

regards

Aashish Sinha