January 19th, 2014, 07:31 AM
How to simulate oracle rowtype in firebird?
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):
After that I can code as follows:
declare myRec myTable%rowtype%;
select * into myRec from myTable where ID=[ID of some record];
Is there something similar in Firebird or is it necessary to do it like this:
myRec.myValue := 'dummy1';
myRec.myValue2 := 12345;
myRec.ID := [create a new unique ID];
Insert into myTable values myRec;
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?
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);
Last edited by sglodek; January 19th, 2014 at 08:07 AM.
Reason: inserted additional text
January 19th, 2014, 01:09 PM
I do it now with a workaround:
- 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
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;