> -----Original Message-----
> From: IB-Conversions@yahoogroups.com
> [mailto:IB-Conversions@yahoogroups.com]On Behalf Of Martijn van der
> Kooij
>
> Hello,
>
> I try to convert a SQL data export to a Firebird version. De SQL
> code I generate is at the bottom of this message.
>
> Currently I use variables and @@IDENTITY to refer to FK-PK
> relations, is there a way to do this in Firebird? I do not want to
> hard-code the ID's.

You will have to replace @@IDENTITY by generators.
The code translation won't be 1:1, however. There's no direct equivalent of
transact-SQL here. You won't be the able to set variables on the fly unless
inside a procedure. There's another way, that may be closer than what you
have: EXECUTE BLOCK, but it only works on FB2.

F:\fb2dev\fbbuild\firebird2\temp\debug\firebird\bin>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database 'tsql.fdb';
SQL> create table a(pk int primary key);
SQL> create table b(fk int references a(pk));
SQL> create generator a_gen;
SQL> set term ^;
SQL> execute block as declare v int; begin v = gen_id(a_gen, 1); insert into
a values(:v); insert into b values(:v); end^
SQL> set term ;^
SQL> select * from a join b on a.pk = b.fk;

PK FK
============ ============
1 1

You can also put a trigger on "a" (the typical way) to fill the pk with the
generator. Getting the generator's value later is not safe in concurrent
usage, so you can use instead the RETURNING clause that follows an INSERT
statement:

F:\fb2dev\fbbuild\firebird2\temp\debug\firebird\bin>isql tsql.fdb
Database: tsql.fdb
SQL> set term ^;
SQL> create trigger a_tr for a before insert as begin new.pk = gen_id(a_gen,
1); end^
SQL> execute block as declare v int; begin insert into a values(NULL)
returning pk into :v; insert into b values(:v); end^
SQL> set term ;^
SQL> select * from a join b on a.pk = b.fk;

PK FK
============ ============
1 1
2 2

SQL> ^Z

Here you get some ideas.

C.
---
Claudio Valderrama C.
SW developer, consultant.
http://www.cvalde.net - http://www.firebirdsql.org