JDBC --> Idoc scenario.

[Former Member]

Hello experts.

I have next scenario JDBC --> Idoc.

I have a sender JDBC adapter who reads information of BD table. This information is passed to Idoc, and update DB records, for next iteration don´t touch this records.

JDBC adapter reads in 5000 sec interval, but how I can do that JDBC adapter only read when another condition?

For example if read process is more large then 5000 secons, JDBC adapter reads the same information but my process until don´t update BD table, and this is a BIG problem , but two identicals Idocs are generated.

Some one know how I can solve this problem?

Thanks.

[bhavesh_kantilal · Accepted solution]

Hi,

In the sender JDBC adapter you have 2 fields,

1. Select Query ---> This is where you mention what is the data to be select with the condition.

2. Update Query ---> This is where you write a Update Statement to update the fields that are selected in the Selec Query.

All tables would need to have a flag variable so that select query will look like,

select * from table, where conditon and FLAG = TRUE

update table set FLAG = False where condtion as in select query.

Regards

Bhavesh

[Former Member · Accepted solution]

Hello Bhavesh!! thanks for answer..

My problem is that JDBC adapter reads only (funtional specification) 1500 records of BD table.

Then if I used UPDATE, all records of table will be updatedand I only want update selected records.

How i can do it?. I can do it with JDBC adapter?

Data that adapter reads are passed to Idoc.

Problem appears when JDBC adapter throw a new polling process. In this case same records are read, and appear repeat idocs...

Thanks.

[bhavesh_kantilal · Accepted solution]

Julian,

In the sender JDBC adapter you have an option called ,

Transaction Handling--> Make it to serializable and thereby only records read in select query will be updated.

Regards

Bhavesh

[Former Member · Accepted solution]

Transaction isolation level in advanced Mode?

[bhavesh_kantilal · Accepted solution]

Yes

Regards

Bhavesh

[Former Member · Accepted solution]

Bhavesh

My SQL query is

select id_dia, id_centro, id_referencia, id_promo, inf_regalo, f_uds_kgs_venta, f_imp_venta_iva, f_imp_venta_neta, to_char(id_fecha_val,'yyyymmddhh24miss') id_fecha_val from xi_i3006_f_dwhvtacst where inf_fecha_xi_env = to_date('01011900', 'ddmmyyyy') and (id_dia||lpad(id_centro,10,'0')) in (select * from (select distinct id_dia||lpad(id_centro,10,'0') from xi_i3006_f_dwhvtacst where inf_fecha_xi_

the update clausule how i can to mount...?¿

Are you sure that this works?....

[bhavesh_kantilal · Accepted solution]

Julian,

Am not a DB expert but a SQL guru should be able to help you frame the Update statement.

And yes, if you make the Transaction Handling as serializable, then the database will be locked for write between the Select and Update queries exectuon and so you can be assured that no new data is updated before it is selected.

Regards

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh.

Thanks for reply. We test with Transaction Handling as serializable option, and update query, do the update for all records of table, not only for records selected in Query proces.....

I do select clausule with record delimiter (1500) records. Is possible that don´t work fine for this reason?.

One process full BD table every day and then we have to read this records in groups of 1500 records. Using Transaction Handling as serializable all records of table updated.

Please.. I need one solucion...

Thanks for all.

Regards.

[bhavesh_kantilal · Accepted solution]

Julian,

When you make the option as serializable,the update query will update all records that satisfy the update query but it will ensure that no new data is entered in to the table between the execution of the select and update query.

In your case as I guess you have given maxRecords in the select Queryt ( only 1500 ) records were selected , but as you aren ot using this same criteria in the update query all rows have been updated.

Remember Serializable ensure that no new data is enetered into the table between the select and update query's execution. You should ensure that the update query only updates the data selected in the Select Query.

A DB expert should be able to helo your forumuale the required querry.

regards

Bhavesh

[Former Member · Accepted solution]

Thanks Bhavesh for reply..

Now Works fine...I mount same query for select and for update clausule, but what happens if new records enter in BD table between select and update Query? system failed? or BD is locked when records are readed and anything success?

Thanks for all.

[bhavesh_kantilal · Accepted solution]

><i>but what happens if new records enter in BD table between select and update Query?</i>

When the database transaction isolation level is Serializable this wil not be allowed.

The database will be locked for Writing / new insertions within the exectuion of select and update queries . This is hardly milli seconds and will not be noticable but you can be assued that if you make the transaction handling as Serializable such a problem will not occur.

Regards

Bhavesh

[Former Member · Accepted solution]

> if you make the transaction handling as Serializable such a problem will not occur

And then if process that update DB table and insert new records, works when JDBC adapter read table.... what happens in this case... new records waiting to be inserted until XI finish.. or shurt dump in BD table occurs..

Thanks for all.

[prabhu_s2]

in ur JDBC sender channel u have an option called UPDATE...there u can use a update query which can set the flag like 'X' for the data that are read....ur orinigial select query would read if the flag is not set