Thread: Set Transaction

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    14
    Rep Power
    0

    Question Set Transaction


    I've found out that 1.5 and 2.0 Firebird versins support "START TRANSACTION"-statement though it's called "SET TRANSACTION". I've tried it, but i'm encountering an Error:
    Code:
    invalid transaction handle (expecting explicit transaction start)
    when i'm doing it from a java-application(jaybird jdbc-driver) or
    Code:
    Not permitted
    when i'm trying it from an sql-editor. Basically i just want a bunch of INSERTs to go in: all-or-nothing. That's how it basically looks like:
    Code:
    SET TRANSACTION READ WRITE;
    INSERT INTO ...
    INSERT INTO ...
    if(all_OK){
      COMMIT;
    }else{
      ROLLBACK;
    }
    What am i doing wrong?
    Any help is appreciated.
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    538
    You are mixing transaction isolation levels (see here ) and transactions.
    You can explicitly start a transaction or set a savepoint, but this is not needed, you are always into a transaction, see this example.
    Code:
    C:\Program Files\Firebird\Firebird_2_1\bin>isql
    Use CONNECT or CREATE DATABASE to specify a database
    SQL> connect "localhost:employee" user 'sysdba' password 'masterkey';
    Database:  "localhost:employee", User: sysdba
    SQL> select * from country;
    
    COUNTRY         CURRENCY
    =============== ==========
    USA             Dollar
    England         Pound
    Canada          CdnDlr
    Switzerland     SFranc
    Japan           Yen
    Italy           Lira
    France          FFranc
    Germany         D-Mark
    Australia       ADollar
    Hong Kong       HKDollar
    Netherlands     Guilder
    Belgium         BFranc
    Austria         Schilling
    Fiji            FDollar
    
    SQL> insert into country (country, currency) values ('AAA', 'aaa');
    SQL> select * from country;
    
    COUNTRY         CURRENCY
    =============== ==========
    USA             Dollar
    England         Pound
    Canada          CdnDlr
    Switzerland     SFranc
    Japan           Yen
    Italy           Lira
    France          FFranc
    Germany         D-Mark
    Australia       ADollar
    Hong Kong       HKDollar
    Netherlands     Guilder
    Belgium         BFranc
    Austria         Schilling
    Fiji            FDollar
    AAA             aaa
    
    SQL> rollback;
    SQL> select * from country;
    
    COUNTRY         CURRENCY
    =============== ==========
    USA             Dollar
    England         Pound
    Canada          CdnDlr
    Switzerland     SFranc
    Japan           Yen
    Italy           Lira
    France          FFranc
    Germany         D-Mark
    Australia       ADollar
    Hong Kong       HKDollar
    Netherlands     Guilder
    Belgium         BFranc
    Austria         Schilling
    Fiji            FDollar
    
    SQL>
    You can find an example of Firebird transactions usage in ASP code in my blog and an example in .NET here
    for java refer to the standard here
    Last edited by pabloj; July 4th, 2008 at 10:52 AM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    14
    Rep Power
    0

    Yor are right - i've totaly mixed up these both things. Thank you very much for pointing this out!

IMN logo majestic logo threadwatch logo seochat tools logo