Two Function Calls to a Database to Retrieve the UniqueID of Inserted Records

[rycbn]

Hi guys,

I got the error message on Option 2 query as show.
Actually, this code is from Inside ColdFusion MX boook.
I just would like to know is this code in Option 2 correct?

<!---
Error Executing Database Query.
Missing semicolon (;) at end of SQL statement.
--->

Option 1

<cfquery name="InsertNames" datasource="#request.dsn#">
INSERT INTO Authors (firstname, lastname, email)
VALUES ('#form.fname#', '#form.lname#', '#form.email#')
</cfquery>
<cfquery name="getNewRecord" datasource="#request.dsn#">
SELECT FROM Authors
MAX(AuthorID)
</cfquery>

Option 2

<cfquery name="InsertNames" datasource="#request.dsn#">
INSERT INTO Authors (firstname, lastname, email)
VALUES ('#form.fname#', '#form.lname#', '#form.email#')
SELECT AuthorID
</cfquery>

[mxstu]

The examples in the book are written for what database type?

Option 1 looks strange as well. Assuming the queries are wrapped in some type
of transaction, it should be more like ....



<cfquery name="InsertNames" datasource="#request.dsn#">
INSERT INTO Authors (firstname, lastname, email)
VALUES ('#form.fname#', '#form.lname#', '#form.email#')
</cfquery>
<cfquery name="getNewRecord" datasource="#request.dsn#">
SELECT MAX(AuthorID) AS NewID
FROM Authors
</cfquery>

[cf_menace]

As for the second query: Depending on the database, you can run multiple
queries in one <cfquery> tag. However, SQL statements have to be separated by a
semi-colon. The semi-colon is the standard SQL "end of statement" delimeter.



<cfquery name="InsertNames" datasource="#request.dsn#">
INSERT INTO Authors (firstname, lastname, email)
VALUES ('#form.fname#', '#form.lname#', '#form.email#')
;
SELECT MAX(AuthorID) AS NewID
FROM Authors
</cfquery>

[MikerRoo]

Also, selecting the MAX(ID) to get the recently returned record, WILL break and
WILL corrupt your results.
The only question is how long it takes and how long before your boss, or
customer, discover the problem.

Do not use that approach on anything you care about.

[dempster]

If your goal is to get the record ID created by the database, I'd suggest
adding a date stamp field to the record then querying for the ID by a
combination of name, email and date to make sure you get the right record.
Trying to select on the most recent ID will not always work.

-Paul

[MikerRoo]

There are better methods than datestamp -- depending on the RDBMS used.
The only universal approach is a properly designed DB with a unique index on the key values.

[rycbn]

Many thanks for those replied guys. :-)

I tried to put the semecolon as cf_menace's code above, but I am still getting
the same error.

I am using MS Access Database.
Is MS Access support multiple SQL statements in a single <cfquery>?

[MikerRoo]

Is MS Access support multiple SQL statements in a single <cfquery>?

No.
You can test this using the Queries->Design->SQL view, in Access itself.

You have to put these statements into seperate cfqueries. If you use the
Max(ID) approach, at least surround the Q's with a CFTransaction.

In this case, it's better to put a unique index on email and then use email in
your where clause when pulling the inserted record.

[jonnycattt]

wrapping in a transaction will only work if the isolation level is
serializable, which basically locks the table for all other queries until these
2 queries run. if you can, add a new ID column and populate it with a
createUUID() call, like so:

<cfset thisID = createUUID()>
<cfquery blah>
insert into blah, blah, myIdField
values(blah,blah,#thisID#)
</cfquery>
<cfquery blah>
select top 1 * from blah where ,myIDField = #thisID# order by
MyAutoNumberField DESC
</cfquery>

the createUUID isn't a 100% guarantee of a unique key, but it's close enough.
so that, combined with getting the top 1 sorted by your autonumber field in
descending order guarantees you'll get the last field inserted with that ID,
which is damn near fool proof unless you're inserting a million records a
second.

[MikerRoo]

Adding a UUID column is a waste of time if there is a unique index possible
(email in this case).

Also locking the table is not a bad thing. It is required and desirable in
many situations.
You can NEVER use max or top autonumber in access since autonumber fields are
often set to randomize. Even if they are not, you will not get the order you
expect if you do not use locking.

Often, all it takes is for the user to double-click the submit button to bust
these kinds of schemes.

[jonnycattt]

"Adding a UUID column is a waste of time if there is a unique index possible
(email in this case)."

If the application allows for using email as an index, then that's fine. But
I've never met an app where I'd consider using email.

"Also locking the table is not a bad thing. It is required and desirable in
many situations.
You can NEVER use max or top autonumber in access since autonumber fields are
often set to randomize. Even if they are not, you will not get the order you
expect if you do not use locking."

It's not a bad thing if you have low user loads. But if you do, it most
certainly is a bad thing. You can use max or top autonumber if you know the
field is not set to randomize. or, you can have a datetime field and sort by
date descending that way. the point of the "top 1 * order by XXX desc" is
merely to get around the small but real possibility that createUUID() will
return the same value for your app. of course, you could get around this
completely by making that unique field createUUID() + getTickCount() or + email
or something. That would ensure uniqueness.

"Often, all it takes is for the user to double-click the submit button to bust
these kinds of schemes."

I'd assume that the application prevents against this. fusebox takes care of
this naturally.

[rycbn]

Thanks guys.
I am tried not to use Max(ID) approach for my application.