Hello,
I'm going to use run-time generated queries to insert new records (using
application specific bussiness objects). How can I get the auto-generated
primary key value efficiently, even in multi-user environment? I thought
about stored procedures and using fetch, but how I return a single value and
not a cursor?

Felix.

Felix,

<< I'm going to use run-time generated queries to insert new records (using
application specific bussiness objects). How can I get the auto-generated
primary key value efficiently, even in multi-user environment? I thought
about stored procedures and using fetch, but how I return a single value and
not a cursor? >>

I will see if I can work in output parameters for DML statements into the
next build.  They make this sort of thing a breeze since they return the
values as a result of the statement execution.

--
Tim Young
Elevate Software
www.elevatesoft.com

And what approach do you recommend for now?

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:9C17E020-344C-4773-B0C9-33E82871D089@news.elevatesoft.com...
> Felix,
>
> << I'm going to use run-time generated queries to insert new records
> (using application specific bussiness objects). How can I get the
> auto-generated primary key value efficiently, even in multi-user
> environment? I thought about stored procedures and using fetch, but how I
> return a single value and not a cursor? >>
>
> I will see if I can work in output parameters for DML statements into the
> next build.  They make this sort of thing a breeze since they return the
> values as a result of the statement execution.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Felix,

<< And what approach do you recommend for now? >>

Are you using an IDENTITY column for the primary key, or some other type of
generated column ?

--
Tim Young
Elevate Software
www.elevatesoft.com

I use IDENTITY, like this:
CREATE TABLE "Drivers"
(
"DriverId" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY
1) NOT NULL,
"DriverName" VARCHAR(50) COLLATE "ANSI" NOT NULL,
CONSTRAINT "PK_DriverId" PRIMARY KEY ("DriverId"),
CONSTRAINT "U_DriverName" UNIQUE ("DriverName")
)

Felix.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:3717944D-586C-4205-A546-4FA715FCA41B@news.elevatesoft.com...
> Felix,
>
> << And what approach do you recommend for now? >>
>
> Are you using an IDENTITY column for the primary key, or some other type
> of generated column ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Felix,

<< I use IDENTITY, like this: >>

In that case you can use a transaction on the Drivers table to perform an
insert and then a select on the identity column to get the next value used.
The transaction will prevent the identity column from changing between the
insert and select:

Start Trans
Insert
Select
Commit


--
Tim Young
Elevate Software
www.elevatesoft.com

What exactly I select? Put a where clause with all fields equal to the
inserted ones? Or sort by primary key descending and take the first? If so,
how I limit the result to 1 row to reduce overhead? I haven't seen a TOP
clause or any other paging support.
Speaking of paging, please write it as a feature request.

Thanks,
Felix.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:CCA0131C-F398-4868-A4F7-EEB39EBAC235@news.elevatesoft.com...
> Felix,
>
> << I use IDENTITY, like this: >>
>
> In that case you can use a transaction on the Drivers table to perform an
> insert and then a select on the identity column to get the next value
> used. The transaction will prevent the identity column from changing
> between the insert and select:
>
> Start Trans
> Insert
> Select
> Commit
>
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Felix,

<< What exactly I select? Put a where clause with all fields equal to the
inserted ones? Or sort by primary key descending and take the first? >>

Sorry, just use this:

SELECT MAX(DriverID) AS LastDriverID
FROM Drivers

<< Speaking of paging, please write it as a feature request. >>

We've already got it on the list, but it will be more comprehensive than
just a simple TOP or LIMIT keyword.

--
Tim Young
Elevate Software
www.elevatesoft.com