Here is a little snippet that may help, of course since you are using Oracle
the syntax would differ.
Using Identity Keys in SQL Server
Most applications rely on primary keys in database tables. Many of those
same applications use the auto-increment capabilities of some DBMSes like
MSSQL Server. When inserting a record in a table that uses auto-increment
primary keys, you do not have to insert the value of the primary key
yourself - the DB does it for you. But what if you want to know the value of
the row's primary key that you just inserted? Commonly, developers will run
two queries in a row, wrapped in <cftransaction>. The first one inserts the
record and the second one selects the newest record. But using this syntax,
SQL server can insert the record as well as return the value of the new
primary key all in one query.
<cfquery name="insert" datasource="DSN">
set nocount on
insert into customer (firstName, lastName)
values
('#attributes.firstName#', '#attributes.LastName#')
select @@identity as newID
set nocount off
</cfquery>
I am the new primary key value:
<cfoutput>#insert.newID#</cfoutput>
----- Original Message -----
From: "Daniel Kessler" <[EMAIL PROTECTED]>
To: "CF-Talk" <cf-talk@houseoffusion.com>
Sent: Thursday, October 05, 2006 12:01 PM
Subject: locking, I think
> I have an oracle database and I set the ID of a database entry using:
> unique_repair_ticket_s.NEXTVAL - which is basically a counter
>
> I then need to get that ID and send it to the user. Is the process
> for this to then query the database for the last entry? If so, do I
> surround it with a cftransaction or a cflock to inhibit others
> writing to it until that query is complete so that the last number is
> actually the number that I need?
>
> thank you.
>
> --
>
> Daniel Kessler
>
> College of Health and Human Performance
> University of Maryland
> Suite 2387 Valley Drive
> College Park, MD 20742-2611
> Phone: 301-405-2545
> http://hhp.umd.edu
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255711
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
