Isaac
asked on
id question
Is there a way to get a database primary id before inserting the data?
Also, What database platform are you on?
ASKER
I am using access for now. Some time in the future I may switch to ORACLE.
ASKER
I'm new to Coldfusion. Do you mind elaborating?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What would be the effect of your code if there were simultaneous hits to the database.
Thanks.
Thanks.
The code that I provided would handle simultaneous hits gracefully.
Using <cflock> essentially single-threads a section of code. Not the entire app, but the code within cflock. So if two people requested the template at the exact same time, one of them would be queued while the other insert took place. It happens so fast that it would most likely not even be noticible to the end user, But you are protected against simultaneous inserts, and ID's being swapped.
If you're on Access, then the likelyhood of two simultaneous requests is extremely low anyway (Access does not support the kind of traffic that would need to be hitting the app for this to be a concern).
Take Care,
Seth
Using <cflock> essentially single-threads a section of code. Not the entire app, but the code within cflock. So if two people requested the template at the exact same time, one of them would be queued while the other insert took place. It happens so fast that it would most likely not even be noticible to the end user, But you are protected against simultaneous inserts, and ID's being swapped.
If you're on Access, then the likelyhood of two simultaneous requests is extremely low anyway (Access does not support the kind of traffic that would need to be hitting the app for this to be a concern).
Take Care,
Seth
What is the reason you need the id before the data is sent to the database? Are you providing the record number to the user before they submit their form or on a confirmation? I may have a solution for ya, just need to see what ya got and what you're trying to do.
K'rgds,
~trail
K'rgds,
~trail
ASKER
I needed the id so I could pass it to another page.
I am new to coldfusion, so I'm using ASP experience to solve Coldfusion problem, which I'm sure is the wrong idea.
Anyway, the code in ASP is below. How can I do the below in Coldfusion?
The code below lets me get the id of the record added to the database.
Set rConn = Server.CreateObject("ADODB .RECORDSET ")
rConn.LockType = 3
rConn.Open "tblTest", con
rConn.addNew()
rConn("fname") = fname
rConn("lname") = lname
id = rConn("uid")
rConn.Update()
rConn.Close
I am new to coldfusion, so I'm using ASP experience to solve Coldfusion problem, which I'm sure is the wrong idea.
Anyway, the code in ASP is below. How can I do the below in Coldfusion?
The code below lets me get the id of the record added to the database.
Set rConn = Server.CreateObject("ADODB
rConn.LockType = 3
rConn.Open "tblTest", con
rConn.addNew()
rConn("fname") = fname
rConn("lname") = lname
id = rConn("uid")
rConn.Update()
rConn.Close
The equivalent would be a cfquery
<cfquery datasource="yourdsn">
INSERT INTO tbl_Test (fname, lname)
VALUES(#fname#, #lname#)
</cfquery>
and to get the ID back out you would enclose this in a transaction if the id is a autoincrementing field.
<cftransaction action="begin">
<cfquery datasource="yourdsn">
INSERT INTO tbl_Test (fname, lname)
VALUES(#fname#, #lname#)
</cfquery>
<cfquery datasource="yourdsn" name="GetID">
SELECT MAX(id) AS NewID FROM tbl_Test
</cfquery>
<cftransaction action="commit">
</cftransaction>
Then you would access the newID as
#GetID.NewID#
<cfquery datasource="yourdsn">
INSERT INTO tbl_Test (fname, lname)
VALUES(#fname#, #lname#)
</cfquery>
and to get the ID back out you would enclose this in a transaction if the id is a autoincrementing field.
<cftransaction action="begin">
<cfquery datasource="yourdsn">
INSERT INTO tbl_Test (fname, lname)
VALUES(#fname#, #lname#)
</cfquery>
<cfquery datasource="yourdsn" name="GetID">
SELECT MAX(id) AS NewID FROM tbl_Test
</cfquery>
<cftransaction action="commit">
</cftransaction>
Then you would access the newID as
#GetID.NewID#
Oh I just saw that Seth already gave you this answer higher up - so I am not sure why you were still asking for how to convert it into Cold Fusion code.
If you switch to Oracle or SQL then you may have options such as the @@Identity to grab the just inserted ID.
Hope these answers help.
If you switch to Oracle or SQL then you may have options such as the @@Identity to grab the just inserted ID.
Hope these answers help.
The short answer is: not reliably. Especially if multiple users are on the app, or multiple apps access the table.
Depending on your database platform, you can specify a column as an identity or sequence column, and have the insert statement return the value of the record just inserted.
If only your CF app is accessing the table, you can use a 'select max(mycolumn) from mytable' query along with your INSERT query inside a <cflock> and get consistent results.
Let me know if you'd like more elaboration on either of these methods.
Take Care,
Seth