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
