Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Alternative to Firebird "Returning" clause in Insert Into



Permlink Replies: 12 - Last Post: Jul 6, 2017 8:53 AM Last Post By: quinn wildman Threads: [ Previous | Next ]
John Birch

Posts: 135
Registered: 8/23/02
Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 22, 2014 4:47 AM
Hi,
I need to convert an SQL Insert Into from Firebird to InterBase. The Insert Into include at the end the "Returning" clause so I can get the new inserted primary key ID and use it with another insert statement to insert records in that that particular master key ID. what I should use ?

example:
  Insert Into table1 (field1, field2) values (3,4) returning pk_id into :var_key_id
 
  Insert into detail_table (field3, field4) values (:var_key_id, 5);
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 22, 2014 9:28 AM   in response to: John Birch in response to: John Birch
John Birch wrote:
Hi,
I need to convert an SQL Insert Into from Firebird to InterBase. The Insert Into include at the end the "Returning" clause so I can get the new inserted primary key ID and use it with another insert statement to insert records in that that particular master key ID. what I should use ?

example:
  Insert Into table1 (field1, field2) values (3,4) returning pk_id into :var_key_id
 
  Insert into detail_table (field3, field4) values (:var_key_id, 5);

Doesn't exist.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
John Birch

Posts: 135
Registered: 8/23/02
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 22, 2014 12:18 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Doesn't exist.

Is there any way to return the new inserted key value ?
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 22, 2014 3:11 PM   in response to: John Birch in response to: John Birch
John Birch wrote:
Doesn't exist.

Is there any way to return the new inserted key value ?

No, you have to either get it before and use it (like with IBX's GeneratorField
property) or re-read it from a secondary key.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
John Birch

Posts: 135
Registered: 8/23/02
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 22, 2014 5:22 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thanks,
I think I will use select max(pk_id) to return the last inserted ID.
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 22, 2014 7:50 PM   in response to: John Birch in response to: John Birch
John Birch wrote:
Thanks,
I think I will use select max(pk_id) to return the last inserted ID.

Not safe in a multi user environment. That may or may not be your newly
inserted record. Even then max would be much slower than just asking for the
current gen_id of your generator and subtracting 1 (which would be the same as
the max as long as you don't skip.

Instead of max after the fact call select gen_id(<generator>, 1) from
rdb$database and fill in your key before sending it. That always works in a
multiuser environment.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
John Birch

Posts: 135
Registered: 8/23/02
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 23, 2014 3:34 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:
Instead of max after the fact call select gen_id(<generator>, 1) from
rdb$database and fill in your key before sending it. That always works in a
multiuser environment.

you mean like that:
select gen_id(<generator>, 1) from rdb$database into :new_key
 
Insert into Master_table (field1, field2) values (:new_key, 2);
 
Insert into detail_table (field1, field3) values (:new_key, 3);


The "Master_table" has after insert trigger, it uses also gen_id(<generator>, 1) to get the next available key ID, is it going to overwrite the value I got from "select gen_id(<generator>, 1) from rdb$database" above ?
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 23, 2014 11:50 AM   in response to: John Birch in response to: John Birch
John Birch wrote:
Jeff Overcash (TeamB) wrote:
Instead of max after the fact call select gen_id(<generator>, 1) from
rdb$database and fill in your key before sending it. That always works in a
multiuser environment.

you mean like that:
select gen_id(<generator>, 1) from rdb$database into :new_key
 
Insert into Master_table (field1, field2) values (:new_key, 2);
 
Insert into detail_table (field1, field3) values (:new_key, 3);


The "Master_table" has after insert trigger, it uses also gen_id(<generator>, 1) to get the next available key ID, is it going to overwrite the value I got from "select gen_id(<generator>, 1) from rdb$database" above ?

always protect your triggers with getting a new generator value only if the
value sent in is NULL. If non null accept that value and assume the client app
got it by hand first because it needed it for other purposes (like filling out
detail records).

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
John Birch

Posts: 135
Registered: 8/23/02
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 23, 2014 4:37 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
I tried now to call select gen_id(<generator>, 1) from rdb$database and it work great,

Thanks.
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 23, 2014 11:53 AM   in response to: John Birch in response to: John Birch
John Birch wrote:
I tried now to call select gen_id(<generator>, 1) from rdb$database and it work great,

Thanks.

Good, if you are using IBX in your app I built in a GeneratorField property. It
allows you to associate a generator with a field and then 3 event types -

OnInsert - Gets the value when you call insert on the dataset
OnPost - Gets the value when you post the record
OnServer - Never gets the value (assumes it is done in a trigger), but the
TField's required is ignored

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
John Birch

Posts: 135
Registered: 8/23/02
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 23, 2014 2:59 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Yes but I use it all inside single stored procedure.
shivanka de silva

Posts: 1
Registered: 6/5/17
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 10, 2017 8:26 PM   in response to: John Birch in response to: John Birch
John Birch wrote:
Yes but I use it all inside single stored procedure.

Hi, I'm working on implementing Entity Framework provider for interbase. im also stucked at the point of getting hold of auto generated ID. in EF. it expects the ID to be returned automatically. anyway I could handle this. otherwise entity objects are not getting the newly generated ID after adding.
quinn wildman

Posts: 856
Registered: 12/2/99
Re: Alternative to Firebird "Returning" clause in Insert Into
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 6, 2017 8:53 AM   in response to: shivanka de silva in response to: shivanka de silva
To get the next generated value:

myvar = gen_id(mygenerator,1)

Use myvar in your insert statement and return it in your stored procedure.

shivanka de silva wrote:
John Birch wrote:
Yes but I use it all inside single stored procedure.

Hi, I'm working on implementing Entity Framework provider for interbase. im also stucked at the point of getting hold of auto generated ID. in EF. it expects the ID to be returned automatically. anyway I could handle this. otherwise entity objects are not getting the newly generated ID after adding.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02