Updating 2 databases

Leo

Delphi Developer

Updating 2 databases

2004-11-19 08:50:15 PM
delphi270
I have 2 connections to 2 databases (one MS SQL and one Oracle) - I have
to update a table on MS SQL and, IF that update work, I have to update an
table on the Oracle db. But I have to do that on a "while" loop, and
update more than 1 record (in both db). I tried to open transaction to
the 2 databases, do the updates and if nothing goes wrong, commit both,
or rollback. Well, it is not working - It gives me the error:
"Error creating cursor handle" - I can not create two transactions, even
in diferrent connections?
how can I correctly manage that operation?
I'm connecting to Oracle in runtime using only a tdatabase.

Jan Doggen

Delphi Developer

2004-11-19 09:29:10 PM

Re:Updating 2 databases

Hello Leo,
- Do you have different transaction ID's?
- Do you set GlobalID as well (set it to 0 for both MS SQL and Oracle, Orcale
needs that, and MS SQL does not care)
jan
"Leo" <"ateista[AT]gmail.com">writes

Quote

Leo writes:
>I have 2 connections to 2 databases (one MS SQL and one Oracle) - I have
>to update a table on MS SQL and, IF that update work, I have to update an
>table on the Oracle db. But I have to do that on a "while" loop, and
>update more than 1 record (in both db). I tried to open transaction to
>the 2 databases, do the updates and if nothing goes wrong, commit both,
>or rollback. Well, it is not working - It gives me the error:
>"Error creating cursor handle" - I can not create two transactions, even
>in diferrent connections?
>how can I correctly manage that operation?
>
>I'm connecting to Oracle in runtime using only a tdatabase.

I discover where i was doing wrong, was using [Active := True;] instead
of [ExecSQL;] - know it only "freeze" when I do the ExecSQL... My
application just stop responding... if i do not use any
[startTransaction/commit/rollback] all works fine, but I need to control
if one update goes wrong, rollback all changes before.
Need some advice on that...

Leo

Delphi Developer

2004-11-19 10:01:51 PM

Re:Updating 2 databases

Leo writes:

Quote

I have 2 connections to 2 databases (one MS SQL and one Oracle) - I have
to update a table on MS SQL and, IF that update work, I have to update an
table on the Oracle db. But I have to do that on a "while" loop, and
update more than 1 record (in both db). I tried to open transaction to
the 2 databases, do the updates and if nothing goes wrong, commit both,
or rollback. Well, it is not working - It gives me the error:
"Error creating cursor handle" - I can not create two transactions, even
in diferrent connections?
how can I correctly manage that operation?

I'm connecting to Oracle in runtime using only a tdatabase.
I discover where i was doing wrong, was using [Active := True;] instead

of [ExecSQL;] - know it only "freeze" when I do the ExecSQL... My
application just stop responding... if i do not use any
[startTransaction/commit/rollback] all works fine, but I need to control
if one update goes wrong, rollback all changes before.
Need some advice on that...