dont know about mysql, but this is how i do it in mssql2k :
cfquery name=getMaxObject datasource=#dsn#
SET NOCOUNT ON
INSERT INTO Objects (EmployeeID, patientID, ObjectTypeID)
VALUES (cfqueryparam cfsqltype=CF_SQL_INTEGER value=#session.whoami#,
cfqueryparam cfsqltype=CF_SQL_INTEGER
If you are only inserting into the address table in this bit of code, you could
also serialize the whole thing by using cflock around the transaction, but I
don't like using application-level locking where I should be using
database-level locking.
Is this the best way to do this:
Assuming there's only one CF server (not a cluster) and no other
method of accessing the DB is used (no other server platforms,
developers etc).
On 1/13/06, Robert Munn [EMAIL PROTECTED] wrote:
If you are only inserting into the address table in this bit of code, you
could also serialize the
ORDERS... It's much easier to tell your colleague to look into
OrderID: 155000 rather than OrderID: hagfhdvklchjcvadj...
-Original Message-
From: Bobby Hartsfield [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 08, 2006 10:32 PM
To: CF-Talk
Subject: RE: Get last inserted ID
This should
What version of MySQL was this?
..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: Baz [mailto:[EMAIL PROTECTED]
Sent: Monday, January 09, 2006 1:00 AM
To: CF-Talk
Subject: RE: Get last inserted ID
Thanks for the info guys
inserted ID
What version of MySQL was this?
...:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: Baz [mailto:[EMAIL PROTECTED]
Sent: Monday, January 09, 2006 1:00 AM
To: CF-Talk
Subject: RE: Get last inserted ID
Thanks for the info
for last_insert_id() but who knows.
..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: Baz [mailto:[EMAIL PROTECTED]
Sent: Monday, January 09, 2006 9:32 AM
To: CF-Talk
Subject: RE: Get last inserted ID
MySQL5... someone mentioned to append
]
Sent: Sunday, January 08, 2006 6:24 PM
To: CF-Talk
Subject: Get last inserted ID
Is this the best way to do this:
cftransaction
cfquery name=InsertAddress datasource=#DSN#
INSERT INTO Address (City)
VALUES (cfqueryparam value=#City# cfsqltype=cf_sql_integer /)
/cfquery
cfquery name
-Original Message-
From: Andy Matthews [mailto:[EMAIL PROTECTED]
Sent: Monday, January 09, 2006 10:42 AM
To: CF-Talk
Subject: RE: Get last inserted ID
mySQL has the LAST_INSERT_ID() function.
I do this:
SELECT LAST_INSERT_ID() as id
FROM yourTableName
!//--
andy
Is this the best way to do this:
cftransaction
cfquery name=InsertAddress datasource=#DSN#
INSERT INTO Address (City)
VALUES (cfqueryparam value=#City# cfsqltype=cf_sql_integer /)
/cfquery
cfquery name=getInsertedID datasource=#DSN#
SELECT MAX(AddressID) as MaxID
It's only 100% reliable if you use a serializable transaction, as far
as I remember. This is certainly the case with Oracle.
There is an extensive thread on this in the archives somewhere.
On 1/9/06, Baz [EMAIL PROTECTED] wrote:
Is this the best way to do this:
cftransaction
cfquery
What I usually do is have a column that just holds a specific
timestamp or uuid of some sorts. I create a variable to hold the
timestamp or uuid right before the insert query, then I select against
the table looking for that timestamp or uuid. If you are using a db
that stores a pretty detailed
, January 08, 2006 8:08 PM
To: CF-Talk
Subject: Re: Get last inserted ID
What I usually do is have a column that just holds a specific
timestamp or uuid of some sorts. I create a variable to hold the
timestamp or uuid right before the insert query, then I select against
the table looking
What if someone were to insert another city just before the SELECT
statement executed?
Mike
Baz wrote:
James, in which cases is it not 100% reliable? Are the CF-Talk archives
searchable?
On 1/9/06, Baz [EMAIL PROTECTED] wrote:
Is this the best way to do this:
cftransaction
cfquery
(This is based on Oracle defaults; ymmv with MySql). It's essentially
what Mike said, but only if the other insert is committed first. It's
like this:
1) My transaction is started
2) My INSERT is done
3) Someone else's transaction starts
4) Their INSERT is done
5) Their SELECT is done
6) Their
I'm going to be using SQL Server and I wanted something like Oracle's
sequences. I found this:
http://jamesthornton.com/software/coldfusion/nextval.html
Mike
James Holmes wrote:
(This is based on Oracle defaults; ymmv with MySql). It's essentially
what Mike said, but only if the other
yes, this topic has been beaten to death. All posts here have links
at the bottom, one of which is to the archives
http://www.houseoffusion.com/cf_lists/threads.cfm/4
With Oracle all you need is a sequence defined and use it. No trans
is needed, the sequence always gives a unique value.
other
-Original Message-
From: Ryan Guill [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 08, 2006 8:08 PM
To: CF-Talk
Subject: Re: Get last inserted ID
What I usually do is have a column that just holds a specific
timestamp or uuid of some sorts. I create a variable to hold the
timestamp or uuid
own defined IDs over autoincrementing ones.
..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: Baz [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 08, 2006 7:24 PM
To: CF-Talk
Subject: Get last inserted ID
Is this the best way
James,
CFTransaction doesn't take care of that?
Baz
-Original Message-
From: James Holmes [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 08, 2006 8:39 PM
To: CF-Talk
Subject: Re: Get last inserted ID
(This is based on Oracle defaults; ymmv with MySql). It's essentially
what Mike
Not without the serializable isolation level, no.
On 1/9/06, Baz [EMAIL PROTECTED] wrote:
James,
CFTransaction doesn't take care of that?
--
CFAJAX docs and other useful articles:
http://jr-holmes.coldfusionjournal.com/
~|
:19 PM
To: CF-Talk
Subject: Re: Get last inserted ID
Not without the serializable isolation level, no.
On 1/9/06, Baz [EMAIL PROTECTED] wrote:
James,
CFTransaction doesn't take care of that?
--
CFAJAX docs and other useful articles:
http://jr-holmes.coldfusionjournal.com
: Sunday, January 08, 2006 10:32 PM
To: CF-Talk
Subject: RE: Get last inserted ID
This should insert your record and return the new primary key as 'newid'
cfquery NAME='myqry'
Insert into address (city) Values (cfqueryparam value=#City#
cfsqltype=cf_sql_integer /); Select LAST_INSERT_ID() as newid
What does cftransaction do then?
The livedocs say that it can be used to group multiple
queries that use CFQUERY into one business event. How do
other events occur in between one event?
Transactions are a bit complicated, and can be used to achieve different
sorts of results.
One thing
It happens because Oracle and most other DBs do not default to
serializable transactions; Oracle, for example, defaults to read
committed, which means that your transaction can read other committed
data even if it happened during your transaction (although in Oracle
this level does provide
PROTECTED]
Sent: Monday, January 09, 2006 12:45 AM
To: CF-Talk
Subject: Re: Get last inserted ID
It happens because Oracle and most other DBs do not default to
serializable transactions; Oracle, for example, defaults to read
committed, which means that your transaction can read other committed
data even
You can just modify your cftransaction tags to use the serializable
isolation level if you want, but do some load testing first because
there will be locks-a-plenty if you do...
On 1/9/06, Baz [EMAIL PROTECTED] wrote:
Thanks for the info guys - all this makes me really sad... I remember
: Monday, January 09, 2006 1:14 AM
To: CF-Talk
Subject: Re: Get last inserted ID
You can just modify your cftransaction tags to use the serializable
isolation level if you want, but do some load testing first because
there will be locks-a-plenty if you do...
On 1/9/06, Baz [EMAIL PROTECTED] wrote
28 matches
Mail list logo
