Return Primary Key of newly inserted row

[Steve Naidamast]

Hello...

I believe I have done this before with Firebird but I cannot remember when or how.

I am inserting a row successfully and want to return the new primary key back from the stored procedure that has accomplished the insert.

I am using Firebird 2.5.9.

Does anyone know how to do this?

Thank you...

Steve Naidamast

Sr. Software Engineer

[Dimitry Sibiryakov]

Steve Naidamast wrote 28.09.2023 17:53:
>
> I believe I have done this before with Firebird but I cannot remember when or how.
>
> I am inserting a row successfully and want to return the new primary key back
> from the stored procedure that has accomplished the insert.
>
> I am using Firebird 2.5.9.
>
> Does anyone know how to do this?

doc/sql.extension/README.returning.txt

--
WBR, SD.

[Steve Naidamast]

Thank you sd...

However, what is this link... doc/sql.extension/README.returning.txt ?

Steve Naidamast

Sr. Software Engineer

[Dimitry Sibiryakov]

Steve Naidamast wrote 28.09.2023 18:11:
> However, what is this link... doc/sql.extension/README.returning.txt ?

It is not link. It is a file in your Firebird installation.
If you want link, here is one:
https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref25/fblangref25-dml-insert.html#fblangref25-dml-insert-returning

--
WBR, SD.

[Steve Naidamast]

Thank you, sd...

I found it.

I believe I got my stored procedure working now.

Steve Naidamast

Sr. Software Engineer

[Mário Reis]

I dont beleave it's possible this way.

We are on a Net work mutiple users inserting... 

The only way as o can see, os creatina a trigger before insert...that do something like this:

Select max(PK) from tableX INTO :myPk

If (myPk os null) then

  New.Pk=1

Eles

  new.pk=MyPk + 1

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/e326c746-84f4-4de1-96b1-f7dd43cb9516n%40googlegroups.com.

[Karol Bieniaszewski]

Hi

 

This is totally against client/server .

Mário, never use aproaches with select max+1…

 

For PK use generator https://www.firebirdfaq.org/faq29/

Or simply:

create table t1 (

   id bigint generated by default as identity primary key

)

 

Regards,

Karol Bieniaszewski

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAF_dzrn-Z9qH8CtjQnUj0KYOedc8%2BiL2sQUVSMEyT0Hd_UpETA%40mail.gmail.com.

 

[Mark Rotteveel]

On 29-09-2023 00:56, Mário Reis wrote:
> I dont beleave it's possible this way.
> We are on a Net work mutiple users inserting...
> The only way as o can see, os creatina a trigger before insert...that do
> something like this:
>
> Select max(PK) from tableX INTO :myPk
> If (myPk os null) then
>   New.Pk=1
> Eles

> new.pk <http://new.pk>=MyPk + 1

Do not do this, this doesn't work correctly under concurrent access.

You can use INSERT ... RETURNING <column-list> to obtain values from an
inserted record, including the value of an identity column, or a column
populated from a sequence.

Mark
--
Mark Rotteveel

[Steve Naidamast]

Hello...

As Mark suggested, the "RETURNING" clause does in fact work and that is what I had used before but couldn't remember that.  and since I am developing with Firebird Embedded, I do not have to be concerned with multi-user issues.

However, as to the issue with multi-user platforms, if it is being found for whatever reason that the "RETURNING" clause is returning incorrect data due to multi-user operations then in this case, the developer will have to implement a write-lock thereby queuing up user inserts allowing each one to occur on its own and in isolation.

This should be able to be accomplished using a transactional task instead, since transactions are suppose to be isolated from each other.  However, if this method is not working than the write-lock technique, I just mentioned, will have to be used.

I had to develop such a write-lock for an application a number of years ago and it worked quite nicely without any issues.  However, with more modern systems today, the transactional process should suffice.

Steve Naidamast

Sr. Software Engineer

[Mark Rotteveel]

On 29-09-2023 18:18, Steve Naidamast wrote:
> As Mark suggested, the "RETURNING" clause does in fact work and that is
> what I had used before but couldn't remember that.  and since I am
> developing with Firebird Embedded, I do not have to be concerned with
> multi-user issues.

Even with embedded you can have multiple concurrent connections and
multiple concurrent transactions. The fact you use embedded in itself
does not somehow prevent concurrency within that single application. And
if you use a firebird.conf with an appropriate value for `ServerMode`,
multiple applications using embedded can access one and the same
database, so then you can have concurrency from multiple applications,
and concurrency from multiple connections (or multiple transactions) in
each application.

> However, as to the issue with multi-user platforms, if it is being found
> for whatever reason that the "RETURNING" clause is returning incorrect
> data due to multi-user operations then in this case, the developer will
> have to implement a write-lock thereby queuing up user inserts allowing
> each one to occur on its own and in isolation.

RETURNING, if used in combination with a primary key generated using an
identity column or a sequence and trigger will not have a concurrency issue.

The concurrency issue I was referring to has nothing to do with
RETURNING, it was in response to the example produced by Mario to use
`Select max(PK) from tableX` and then incrementing by one to produce the
next primary key.

> This should be able to be accomplished using a transactional task
> instead, since transactions are suppose to be isolated from each other.
> However, if this method is not working than the write-lock technique, I
> just mentioned, will have to be used.

If you use an identity column or a sequence + trigger, then that is
already covered.

Mark
--
Mark Rotteveel