Having had to wrestle with this problem recently, I discovered that it's
actually possible (in CF 4.5x+) to perform more than one SQL procedure
inside a single CFQUERY. Here's how I got the ID (using SQL Server 7/2000):
<CFTRANSACTION>
<CFQUERY NAME="testquery" DATASOURCE="#dsn#">
SET NOCOUNT ON
INSERT INTO test
(
testtext
)
VALUES
(
'testval'
)
SELECT Rec_id = @@identity
</CFQUERY>
</CFTRANSACTION>
You can then reference #testquery.Rec_id# in your code. Note that 'SET
NOCOUNT ON' is *required*--if you don't put it in, the SELECT statement
won't fire.
There's an even more elegant variation of this (in a cf tag) described on
TeraTech's site: http://www.teratech.com/coldcuts/cutdetail.cfm?cutid=225.
Hope this helps!
All the best,
REM O-
-----Original Message-----
From: Matt Robertson [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 26, 2001 4:27 PM
To: CF-Talk
Subject: RE: SELECT MAX(ID), Access & SQL Server - confused!
Yeah, both the original poster and I are in the same boat. No stored procs
or triggers. Supposedly v4.1 of mySQL will finally get them. Interesting
post (as usual) from Dave Watts. I had no idea Access had what sounds like
a quasi-stored proc capability.
---------------------------------------
Matt Robertson [EMAIL PROTECTED]
MSB Designs, Inc., www.mysecretbase.com
---------------------------------------
---------- Original Message ----------------------------------
from: Costas Piliotis <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
date: Mon, 26 Nov 2001 16:09:42 -0800
I guess if I read the WHOLE thread :-)
-----Original Message-----
From: Costas Piliotis
Sent: Monday, November 26, 2001 4:04 PM
To: CF-Talk
Subject: RE: SELECT MAX(ID), Access & SQL Server - confused!
If you want to know the ID of the last insterted record, there is a variable
called @@Identity. Be careful that there aren't any triggers firing on that
table onto another with an identity field.
No need to get more complicated than that. Using a stored procedure to do
your inserts and an out parameter will get that value:
CREATE PROCEDURE sp_InsertOne
@MyInValue int,
@MyOutValue int = null output
AS
INSERT INTO MyTable (myInvalue) VALUES(@MyInValue)
SET @MyOutValue = @@Identity
GO
-----Original Message-----
From: Matt Robertson [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 26, 2001 3:48 PM
To: CF-Talk
Subject: Re: SELECT MAX(ID), Access & SQL Server - confused!
No, Access doesn't support stored procs or triggers.
In SQL Server the analog for 'autonumber' is 'identity', which imho makes a
perfectly good primary key.
There's no real certainty that MAX is going to return *your* last inserted
ID. It might be from another user's insert statement. I think you can
choke the process down with a name-based cflock, but now you have a cflock
and a cftransaction block to go along with a query needed to determine the
last-inserted value.
If you don't have triggers/stored procs available to you, here's another way
to do the job: First, when the db table is created include a text field
called UUID or something similar. Then, in your insert operation do a
CFSET variables.myvalue=CreateUUID()
and insert variables.myvalue into the UUID field of the db.
Then, when you need the record next, select with a WHERE filter that looks
for the UUID value set in variables.myvalue. *Then* you can grab the much
tidier ID field value for future use, if needed.
This gets the job done without a CFTRANSACTION, CFLOCK or an extra query to
determine the ID value. Its cost is a db field and an (optional, really)
table index.
---------------------------------------
Matt Robertson [EMAIL PROTECTED]
MSB Designs, Inc., www.mysecretbase.com
---------------------------------------
---------- Original Message ----------------------------------
from: "Gyrus" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
date: Mon, 26 Nov 2001 23:22:38 -0000
I'm majorly confused about Primary Keys and Access' 'Autonumber' data type.
I've always used 'Autonumber' as seemingly the best
and easiest way for unique ID's to be created for PK
fields in Access tables.
I was aware of some issue with the common SELECT
MAX(ID) SQL operation you end up doing to get the ID
of something you just inserted. I found this at
http://www.defusion.com/articles/index.cfm?ArticleID=72
The guy seems to be responding to someone posting the
Fusebox technique of using SELECT MAX(ID) and
<cftransaction> as a universal method of creating
ID's for PKs, so that apps are portable between RDMS's.
He seems to think the problem he mentions only happens
on SQL Server 6.5/7, though. There was a response to
this at
http://www.defusion.com/articles/index.cfm?ArticleID=77
Here it was advised to use stored procedures, "isolation
level" on <cftransaction>, or an insert trigger.
Then I grab the Max_ID.cfm custom tag by Steve Nelson
from fusebox.org, which has a nice suggestion in the documentation, to use
an endless loop and <cftry> around the <cftransaction>, to keep trying to
get the new ID *and* insert the new record until it gets it right.
Questions:
- Am I right in thinking SQL Server doesn't have the
'Autonumber' data type? Why not? Is there a grave
disadvantage to it unless you're doing kid's stuff in
Access?
- Is it cool to use the SELECT MAX(ID) with SQL Server
2000 (i.e. do the 6.5/7 problems above not happen now)?
- If I put <cftransaction isolation="serializable"> around
the SELECT MAX(ID) and INSERT queries (in whatever
order), are there any hidden dangers? Does this prevent
anything else from changing the table concerned until
the new ID is calculated and the nice new record is
INSERTed?
- You can't use stored procedures or triggers on Access,
right?
I'm basically trying to get this process *right*, and trying
to construct app templates that will be easily portable
between different DB systems - so even though I'm still
mostly working with Access, I might drop the whole
'Autonumber' thing.
Any advice/tips greatly appreciated on this!
- Gyrus
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
