Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Posts
    12

    Unanswered: Across Database Transactions

    Hi everyone.
    I have developed a program in VB that updates 2 databases (residing on the same DB Server).
    My question is how am I to ensure the transaction is 'transactional'.

    Example.
    I need to ensure that my update to database1 is successful as well as my update to database2 is successful or nothing at all.

    I was tempted to put in my code the following but I doubt it will work.

    db1.begin transaction
    update db1.table1 set field1 = 'x' where ....
    db2.begin transaction
    update db2.table1 set field1 = 'x' where ...
    db2.commit transaction
    db1.commit transaction

    Does anyone have any solutions to this at all?
    Thanking everyone in anticipation.

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    The transaction can span databases with no problems.

    You only need to do something like this (add error checking and rollbacks as necessary...)

    Code:
    begin transaction
    
    update db1..the_table set foo = bar
    
    update db2..the_other_table set bar = baz
    
    commit transaction
    Michael

  3. #3
    Join Date
    Apr 2006
    Posts
    12
    Hi Michael,
    How can that be possible in a VB program?
    I will need 2 separate ado connections to connect to 2 differrent databases.

    Therefore to start a transaction I will need to specify the connection.
    ie. adoconnection1.begintrans or adoconnection2.begintrans

    How can I start a outer transaction which will include both these connections?

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    I don't know VB, but I suspect that you could use a single connection, and do the operations on each of the tables sequentially by using fully qualified table names (i.e. dbname..table_name).

    Michael

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    43
    If at all u can not use single connection for both sybase databases then alternatively u can use :

    @@error sybase global variable. The @@error global variable is commonly used to check the error status of the most recently executed batch in the current user session.

    @@error contains 0 if the last transaction succeeded; otherwise @@error contains the last error number generated by the system

    So if @@error = 0 means previous trasaction succeeded.


    Execute First Query

    if (@@error = 0 )
    execute second query
    else
    do not execute second query.
    end.

  6. #6
    Join Date
    Apr 2006
    Posts
    12
    Dear pangup_74,

    Thanks for you reply.
    The @@error flag works fine. It does not however cater for cases when there is a power failure or network connection problem just after the first commit..
    eg
    begin tran (connection 1)
    update ....

    if @@error = 0 then
    (
    begin tran (connection 2)
    update ...
    commit tran (connection 2)
    --- HERE POWER FAILURES or NETWORK CONNECTION BREAKS CAUSES A 1 sided update..the following commit will never be executed...

    commit tran (connection 1)

    )
    else
    rollback tran (connection 1)

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    43
    In this case the first commit tran (connection 2)
    will not really commit the transaction in the database since it is a nested transaction. It just decrease the @@transcount by 1 and whenevr u finally say commit tran commit tran (connection 1) it will actually commit the transaction. If u rollback the transaction the entire transaction will be roll- backed.


    begin tran (connection 1)
    update ....

    if @@error = 0 then
    (
    begin tran (connection 2)
    update ...
    commit tran (connection 2)
    --- HERE POWER FAILURES or NETWORK CONNECTION BREAKS CAUSES A 1 sided update..the following commit will never be executed...

    commit tran (connection 1)

    )
    else
    rollback tran (connection 1)

  8. #8
    Join Date
    Apr 2006
    Posts
    12
    Dear Pang_up74,

    In VB, how can I do that when I am connecting using 2 different connections?
    Do I need to connect using 1 connection and ensuring that in this connection I have all rights to the table I am insert/updating in the other database?
    That way if I were to rollback I will rollback everything.
    Or do you have any other better suggestions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •