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

    Join Date
    Jan 2014
    Posts
    2
    Rep Power
    0

    How to simulate oracle rowtype in firebird?


    Hello,

    I try to do the following job in Firebird knowing how to do it in Oracle but don't know the best way in Firebird. I think it will be possible to do it with a cursor, but can't find any suitable help for it.

    I have one record that is used like a pattern for multiple inserts in a loop, altering only some column-values (three values in about 80 columns). This is used to make copies of the "source-record" and change only some consecutive numbers.
    In oracle I do this job as follows (all code is only metacode as example, not valid):

    Code:
    declare myRec myTable%rowtype%;
    select * into myRec from myTable where ID=[ID of some record];
    After that I can code as follows:
    Code:
    myRec.myValue := 'dummy1';
    myRec.myValue2 := 12345;
    myRec.ID := [create a new unique ID];
    Insert into myTable values myRec;
    Is there something similar in Firebird or is it necessary to do it like this:
    Code:
    select column1, column2, column3,... column80 from myTable into var1, var2, var3,... var80 
    var1 = 'dummy';
    var2 = 12335;
    ID = [create a new unique ID];
    Insert into myTable (column1, column2, column3,...column80) values (var1, var2, var3, ... var80);
    Is there an elegant way to do the same job in Firebird without declaring each column as variable, writing every column in the Select and also in the Insert-statement?

    Thanks,
    Siegbert
    Last edited by sglodek; January 19th, 2014 at 08:07 AM. Reason: inserted additional text
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    2
    Rep Power
    0
    I do it now with a workaround:

    Preparation:
    • create a new table BOOKS_COPY from the DDL of the source-table BOOKS
    • remove the "not null" from the ID-field

    in the stored procedure:
    • insert the source record as a dummy into the copy-table
    • set the id to null and some other fields to the desired values
    • insert the dummy-record into the source-table


    Code:
    delete from BOOKS_COPY;
    insert into BOOKS_COPY select * from books where id = :P_ID;           
    update BOOKS_COPY set id = null, barcode = :v_Barcode where id = :P_ID;          
    insert into BOOKS select * from BOOKS_COPY;

IMN logo majestic logo threadwatch logo seochat tools logo