Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Bulgaria
    Posts
    2

    Unanswered: Transaction parameters in interbase

    I ve got the following SQL statement in a Interbase transaction:

    Select max(field1) from table1
    Into :max
    Insert into table1 (field1) values(:max+1)

    What parameters of the transaction should I use, to assure the value in field1 to be unique and subsequent, when several transactions are simultaneously running.
    For DB access I use Delphi + TIBTransaction.

  2. #2
    Join Date
    May 2002
    Location
    Budapest - Hungary
    Posts
    12
    Hi Amarinov,
    My tip is that you can use generators. Here are two sample. First one is using procedure, second one is using trigger. If you are use procedure based version than you can have a return value for example record ID.

    Attila


    /*Generators*/

    CREATE GENERATOR ATID_GEN ;
    SET GENERATOR ATID_GEN TO 0;

    /*Create table*/

    CREATE TABLE TBL_ADR_TYP (
    ATID INTEGER NOT NULL,
    ATCODE VARCHAR (5),
    ATYP VARCHAR (150));

    /*Create unique primary key*/

    ALTER TABLE TBL_ADR_TYP ADD CONSTRAINT PK_TBL_ADR_TYP PRIMARY KEY (ATID);

    /*Create Autoincrement procedure*/

    CREATE PROCEDURE ATID_INC
    RETURNS (
    NEW_VALUE INTEGER)
    AS BEGIN NEW_VALUE = GEN_ID(ATID_GEN , 1);
    END

    /*Option 1A - Autoincrement via stored procedure*/

    CREATE PROCEDURE TBL_ADR_TYP_I (
    ATCODE VARCHAR (5),
    ATYP VARCHAR (150))
    AS
    BEGIN
    INSERT INTO TBL_ADR_TYP (
    ATID,
    ATCODE,
    ATYP)
    VALUES (
    :GEN_ID(ATID_GEN , 1),
    :ATCODE,
    :ATYP);
    END

    /*Option 1B - Autoincrement via stored procedure with return value*/

    CREATE PROCEDURE TBL_ADR_TYP_I (
    ATCODE VARCHAR (5),
    ATYP VARCHAR (150))
    RETURNS (
    ATID INTEGER)
    AS
    BEGIN
    ATID = GEN_ID(ATID_GEN , 1);
    INSERT INTO TBL_ADR_TYP (
    ATID,
    ATCODE,
    ATYP)
    VALUES (
    :ATID,
    :ATCODE,
    :ATYP);
    END

    /*Option 2 - Autoincrement via trigger*/

    CREATE PROCEDURE TBL_ADR_TYP_I (
    ATCODE VARCHAR (5),
    ATYP VARCHAR (150))
    AS
    BEGIN
    INSERT INTO TBL_ADR_TYP (
    ATCODE,
    ATYP)
    VALUES (
    :ATCODE,
    :ATYP);
    END

    CREATE TRIGGER ATID_BI_INC FOR TBL_ADR_TYP INACTIVE
    BEFORE INSERT POSITION 0
    AS
    BEGIN
    IF (NEW.ATID IS NULL)
    THEN NEW.ATID = GEN_ID(ATID_GEN, 1);
    END

Posting Permissions

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