>
>Adomas,

>Of your suggestions, the only multi-user "safe" method is the SP, 2
>and 3 both have the same problem.


>We either use a SP, or do it using queries. You just have to fix up
>the order a bit.

>Rather than inserting a value, then saying to yourself, "what on earth
>did I just insert?", think ahead and say "I need to insert a record,
>but I need to know the generated value because I need to insert some
>other records with a foreign key link to this record I am inserting"

>So simply get the generated value first

>Select gen_id(my_gen,1) from rdb$database

>Store that number in a variable, and use it in the respective insert
>statements.

>insert into mastertable (ID, name) values (:MyVariable, :TheName);
>insert into detailtable (ID, MasterTableID, SomethingElse) values
>(gen_id(my_detailgen,1), :MyVariable, ....);

>Pretty simple. Obviously this approach is an additional "round trip"
>to the database, but it will be a very quick trip. The insert into ...
>returning syntax isn't in 1.5, so the only single trip approach is a SP.

>Adam

This looks very good, but you cant include this into plain sql script (insert into..insert into..). And this is not very comfortable.
You cant adjust automatically relations, as you said, you must know id before insert. Sou, in fact, plain insert sql script has to be adjusted before any execution.
Anyway, thx to all. I thought I missed something, as someone asked to make it with sql script, and I didnt know how to do it in elegant way :)
Adomas








Adam wrote:

>--- In firebird-support@yahoogroups.com, Adomas Urbanavicius
><adomas@i...> wrote:
>
>
>>Hi,
>>Is there any posibility to find out inserted ID in the middle of sql
>>script ? It has to auto create relationships (One - Many) from :
>>Something like sql script:
>>insert values into Primary_table (PK,data ....); //// PK - auto
>>incremented by trigger.
>>insert values into Foreign_table (FK,...) /// ---------> HOW to
>>
>>
>find
>
>
>>out FK, from previous SQL line ?
>>insert values into Foreign_table (FK) /// --------->
>>
>>
>>
>>Sample :
>>TABLE1 ID*, NAME Primary key ID. (with auto inc triger with
>>generator. of ID)
>>TABLE2 ID,CITY. Id - foreign key from TABLE1.
>>
>>Now if we want to run sql script run :
>>insert into TABLE1(NAMES) values ('John');
>>///// HOW to auto build relationship with fetching last ID from table1
>>insert into TABLE2 (ID,CITY) values (/* ID ???? */,'City1');
>>insert into TABLE2 (ID,CITY) values (/* ID ???? */,'City2');
>>COMMIT WORK;
>>...
>>
>>As far, as I know:
>>1. Create SP to handle it with local variable.
>>2. Select gen_id(my_gen,0) from rdb$database
>>3. Select max(id)
>>But 1 is not reusable, as for other tables SP has to be rewritten. (And
>>it has to exist before script is executed.)
>>2 is transaction independant, so might be changed by other concurrent
>>users in the middle of action.
>>3 for me is dangerous, if transactions are read_commited, it might be
>>different after other user commits.
>>
>>
>>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>