Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Returning generated values after insert |
Tue, Jun 12 2007 2:13 PM | Permanent Link |
"Felix Gartsman" | 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. |
Tue, Jun 12 2007 7:06 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Wed, Jun 20 2007 12:45 PM | Permanent Link |
"Felix Gartsman" | 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 > |
Fri, Jun 22 2007 6:48 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Fri, Jun 22 2007 7:54 AM | Permanent Link |
"Felix Gartsman" | 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 > |
Fri, Jun 22 2007 8:03 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Fri, Jun 22 2007 11:09 AM | Permanent Link |
"Felix Gartsman" | 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 > |
Mon, Jun 25 2007 3:57 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |