Thanks Ayudh - most informative. Cheers, Phil.
----- Original Message ----- From: "Ayudh Nagara" <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, November 26, 2003 5:46 PM Subject: [cfaussie] RE: get primary key allocated during insert? > In client-server days of old, each client could hold a dedicated connection > to the database, so the database is often licensed in terms of the number of > users and controlled by limiting the connections it would open. > > In the web environment, there are no permanent connections between client > and server. The browser is not able to open a direct connection to the > database, so the role of the application server such as CFMX is to manage > the DB connections on behalf of clients. Basically, the app server > virtualizes database connections by managing a pool of pre-opened > connections and serving it up to applications each time a query is made. > This is very efficient because opening database connections on demand each > time an application makes a query is very costly in system resources. You > can see the connection pooling parameters in CF Admin > Data sources under > Advanced Settings. > > Now, the database no longer has any knowledge or control of how many clients > are connecting to it because a small pool of connections can be time-shared > between a large number of users. That's why you can buy a 5-user licence and > have the database serve thousands of concurrent users. Of course the > database vendors can't allow that happen, so most of them now require that > if you serve internet (or intranet) users from the database, you will need > to purchase an additional "Internet licence" which maybe triples the price. > > Preamble over, back to the original question. If you insert a new record and > retrieve its identity using @@IDENTITY within the same CFQUERY block, you > will be guaranteed of using the same database connection because a > connection is allocated per query. Because $$IDENTITY gives you the last > identity within the connection, that's what you want. If you put the insert > and select @@identity in different CFQUERY blocks, it is possible that each > may use a different database connection and you'll get wrong results. > > I think @@IDENTITY is a Microsoft-only feature and certainly works with SQL > Server. Have no idea if it works with Access. > > Regards: Ayudh > > +----------------------------------------------------------------+ > | SOAP is the glue! Hook up your server directly to your bank. | > | Connect to VeriPay xServ, the Australian Payments Web Service. | > | Reliable, Secure, FAST: http://www.xilo.com/xserv | > +----------------------------------------------------------------+ > > ----- Original Message ----- > From: "Phil Evans" <[EMAIL PROTECTED]> > To: "CFAussie Mailing List" <[EMAIL PROTECTED]> > Sent: Wednesday, November 26, 2003 3:50 PM > Subject: [cfaussie] RE: get primary key allocated during insert? > > > > Just out of curiosity, can you define connection specific? > > > > Is there one db connection for each user session, or just one db > connection > > for the CF service, that is shared by multiple user sessions? > > > > If the latter, what are the licensing implications for proprietary db's? > > > > Thanks, > > Phil > > > > > > ----- Original Message ----- > > From: "Ayudh Nagara" <[EMAIL PROTECTED]> > > To: "CFAussie Mailing List" <[EMAIL PROTECTED]> > > Sent: Wednesday, November 26, 2003 3:44 PM > > Subject: [cfaussie] RE: get primary key allocated during insert? > > > > > > > @@IDENTITY is connection-specific. It returns the last identity value > > > created on the same database connection. So if you retrieve the > @@IDENTITY > > > within the same CFQUERY as the INSERT statement, you can be assured it > > will > > > return the identity you're after. SQL Server only AFAIK. > > > > > > <CFQUERY NAME="NewCustomer" ....> > > > INSERT INTO customers (..., ..., ...) > > > VALUES (..., ..., ...); > > > SELECT @@IDENTITY AS NewCustomerID FROM customers > > > </CFQUERY> > > > > > > > > > Regards: Ayudh > > > > > > +----------------------------------------------------------------+ > > > | SOAP is the glue! Hook up your server directly to your bank. | > > > | Connect to VeriPay xServ, the Australian Payments Web Service. | > > > | Reliable, Secure, FAST: http://www.xilo.com/xserv | > > > +----------------------------------------------------------------+ > > > > > > ----- Original Message ----- > > > From: "Taco Fleur" <[EMAIL PROTECTED]> > > > To: "CFAussie Mailing List" <[EMAIL PROTECTED]> > > > Sent: Wednesday, November 26, 2003 10:20 AM > > > Subject: [cfaussie] RE: get primary key allocated during insert? > > > > > > > > > @@IDENTITY returns the last ID inserted globally, it does not guarantee > > that > > > it is actually the ID your after. > > > SCOPE_IDENTIY() returns the last ID within the scope. > > > > > > -----Original Message----- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, 26 November 2003 9:16 AM > > > To: CFAussie Mailing List > > > Subject: [cfaussie] RE: get primary key allocated during insert? > > > > > > > > > Brian, > > > > > > I use this query immediately following an insert query if I want the > > > primary key value: > > > > > > <CFQUERY NAME="getid" DATASOURCE="dsn"> > > > SELECT @@Identity as artid FROM tablename > > > </cfquery> > > > > > > I guess you could wrap the two queries in a CFTRANSACTION if it is high > > > traffic. > > > > > > Regards... Steve C > > > > > > > > > -----Original Message----- > > > From: Brian Gilbert [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, 26 November 2003 10:12 AM > > > To: CFAussie Mailing List > > > Subject: [cfaussie] get primary key allocated during insert? > > > > > > > > > Hi all, > > > > > > Is there a way to get the primary key (autonumber) that would be > > > allocated during an insert statement in a cfquery? > > > > > > TIA > > > > > > Brian > > > > > > --- > > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > > > To unsubscribe send a blank email to > > > [EMAIL PROTECTED] > > > > > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > > > http://www.mxdu.com/ + 24-25 February, 2004 > > > > > > --- > > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > > > To unsubscribe send a blank email to > > [EMAIL PROTECTED] > > > > > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > > > http://www.mxdu.com/ + 24-25 February, 2004 > > > > > > --- > > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > > > To unsubscribe send a blank email to > > [EMAIL PROTECTED] > > > > > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > > > http://www.mxdu.com/ + 24-25 February, 2004 > > > > > > > > > --- > > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > > > To unsubscribe send a blank email to > > [EMAIL PROTECTED] > > > > > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > > > http://www.mxdu.com/ + 24-25 February, 2004 > > > > > > > > > --- > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > > To unsubscribe send a blank email to > [EMAIL PROTECTED] > > > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > > http://www.mxdu.com/ + 24-25 February, 2004 > > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to [EMAIL PROTECTED] > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > http://www.mxdu.com/ + 24-25 February, 2004 > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
