CFC Best Practices

This is more of a "how would you do it" question. I have a simple set of queries that are currently inside an cfif statement, the "if "block inserts a record, grabs the id of the just inserted record, and there's an optional update that runs if some one else is entering there record. I know I can just dump the queries into a function and it'll work... But would I be better served by creating separate functions and call the last two from inside the insert function? -- Scott Stewart ColdFusion Developer Office of Research Information Systems Research & Economic Development University of North Carolina at Chapel Hill Phone:(919)843-2408 Fax: (919)962-3600 Email: sastew01@email.unc.edu

Separate methods might make sense if you need to do this from more than one method. If it is specific to that method it might not be worth breaking it up. Be aware though that inserting a record and then selecting the MAX id can result in an incorrect ID if more than one thread runs this code at the same time. You'd probably be better off getting the last inserted ID from the cfquery itself if you are on CF8, or looking at using a native database function like scope_identity() (the actual function varies across platforms). A bit more can be found here: http://www.forta.com/blog/index.cfm/2007/7/6/ColdFusion-8-Can-Return-Identity-Values On Tue, Aug 19, 2008 at 10:22 AM, Scott Stewart <sastew01@email.unc.edu>wrote: ----- Excess quoted text cut - see Original Post for more -----

" Be aware though that inserting a record and then selecting the MAX id can result in an incorrect ID if more than one thread runs this code at the same time. You'd probably be better off getting the last inserted ID from the cfquery itself if you are on CF8, or looking at using a native database function like scope_identity() (the actual function varies across platforms). " Good point to ponder....... Brian Kotek wrote: ----- Excess quoted text cut - see Original Post for more -----

You can generally avoid the problem with a cftransaction tag if you're doing the MAX(id) thing, but the other solutions are better (especially the new CF8 goodies which, for example, are a life saver for Oracle). ----- Excess quoted text cut - see Original Post for more ----- -- mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/

James Holmes wrote: > You can generally avoid the problem with a cftransaction tag if you're > doing the MAX(id) thing, but the other solutions are better > (especially the new CF8 goodies which, for example, are a life saver > for Oracle). I've tried using the result.identitycol trick with CF8 and MSSQL and for me, it doesn't work.  I just get an error. So what I've stuck to is this... Before the first insert, I do <cfset now = Now()> Then in the insert, for my date_added value, I use #now#. Then after that, when I do my select MAX(id), I also add where date_added = '#now#' AND I also wrap the whole thing in a cftransaction... Rob

While it probably won't happen often, depending on load, even the Now() logic can result in two records with the same value if they come in at the same time. And cftransaction will only work if you go all the way to "serializable" for isolation level, which essentially single-threads access and, again depending on how often the query is run, can be a performance issue. If this query isn't hit a lot by multiple threads none of this may be an issue, but it is definitely something that must be kept in mind. ----- Excess quoted text cut - see Original Post for more -----

What I'm thinking, from this thread and others I've read in the past, is that if keeping your code database-independent is priority..... inserting some unique identifier and then querying for that in a second query ...might be the best option. Or do you folks find that you can't really stay uncoupled from your DB? ----- Excess quoted text cut - see Original Post for more -----

If you decide to go in this direction, I would suggest not just using the 'datetime' added column.  create a truly unique identifier such as: "datetime (YYYYMMDDHHMMSS)+ user ip address (or username if logged in) plus a random sequence".  This way you will know you can search on the unique and not get a possible duplicate. However, you can also create a separate set of functions (CFCs) for each possible database so that you only hit the database once per call, if you want to blend the need for optimized code along with database type independence. William ----- Excess quoted text cut - see Original Post for more -----

Rob, if you are using MSSQL why don't you use @@IDENTITY? e.g. this crappy example... <cfquery name="insertLocation" datasource="#mydsn#"> INSERT INTO tblLocations ( address1, address2, address3, town ) VALUES ( <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.address1)#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.address2)#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.address3)#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.town)#">, ) SELECT @@IDENTITY AS locationID </cfquery> #insertLocation.locationID# is then your last inserted item. (assuming tblLocations has an identity column that is) Cheers, Craig. James Holmes wrote: > You can generally avoid the problem with a cftransaction tag if you're > doing the MAX(id) thing, but the other solutions are better > (especially the new CF8 goodies which, for example, are a life saver > for Oracle). I've tried using the result.identitycol trick with CF8 and MSSQL and for me, it doesn't work.  I just get an error. So what I've stuck to is this... Before the first insert, I do <cfset now = Now()> Then in the insert, for my date_added value, I use #now#. Then after that, when I do my select MAX(id), I also add where date_added = '#now#' AND I also wrap the whole thing in a cftransaction... Rob

> Rob, if you are using MSSQL why don't you use @@IDENTITY? Just to be clear, @@identity returns the most recent inserted id of all tables. So if you had a trigger that when a table gets an insert, another table also gets an insert, @@identity would return the triggered table ID. For SQL 2000 +, look at SELECT SCOPE_IDENTITY() or SELECT IDENT_CURRENT('tablename'). http://www.sqlteam.com/article/alternatives-to-identity-in-sql-server-2000 I've seen other good blog entries / articles about this, but couldn't locate them at the moment. -- Matt Williams "It's the question that drives us."

Interesting, never had a problem with @@IDENTITY though. It seems to me that unless you're using triggers, it's pretty much impossible for it to return the wrong value in a query like the example below. SCOPE_IDENTITY() certainly looks a little safer but I'm not worried about all my old code. Craig. > Rob, if you are using MSSQL why don't you use @@IDENTITY? Just to be clear, @@identity returns the most recent inserted id of all tables. So if you had a trigger that when a table gets an insert, another table also gets an insert, @@identity would return the triggered table ID. For SQL 2000 +, look at SELECT SCOPE_IDENTITY() or SELECT IDENT_CURRENT('tablename'). http://www.sqlteam.com/article/alternatives-to-identity-in-sql-server-2000 I've seen other good blog entries / articles about this, but couldn't locate them at the moment. -- Matt Williams "It's the question that drives us."

Actually if you are using  MSSQL 2000 or 2005 or up, you should be using VALUES();   SELECT  SCOPE_IDENTITY() AS id Rob, if you are using MSSQL why don't you use @@IDENTITY? e.g. this crappy example... <cfquery name="insertLocation" datasource="#mydsn#"> INSERT INTO tblLocations ( address1, address2, address3, town ) VALUES ( <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.address1)#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.address2)#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.address3)#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(form.town)#">, ) SELECT @@IDENTITY AS locationID </cfquery> #insertLocation.locationID# is then your last inserted item. (assuming tblLocations has an identity column that is) Cheers, Craig. James Holmes wrote: > You can generally avoid the problem with a cftransaction tag if you're > doing the MAX(id) thing, but the other solutions are better > (especially the new CF8 goodies which, for example, are a life saver > for Oracle). I've tried using the result.identitycol trick with CF8 and MSSQL and for me, it doesn't work.  I just get an error. So what I've stuck to is this... Before the first insert, I do <cfset now = Now()> Then in the insert, for my date_added value, I use #now#. Then after that, when I do my select MAX(id), I also add where date_added = '#now#' AND I also wrap the whole thing in a cftransaction... Rob

Sandra Clark wrote: > Actually if you are using  MSSQL 2000 or 2005 or up, you should be using > > VALUES(); >   SELECT  SCOPE_IDENTITY() AS id > Okay, I just added this to my insert function, and got this: [Macromedia][SQLServer JDBC Driver][SQLServer]'SCOPY_IDENTITY' is not a recognized built-in function name. Here's the exact line:       );       select SCOPY_IDENTITY() as newuserid       </cfquery> Rob

Try SCOPE_IDENTITY() instead of SCOPY_IDENTITY() ----- Excess quoted text cut - see Original Post for more -----

William Seiter wrote: > Try SCOPE_IDENTITY() instead of SCOPY_IDENTITY() D'OH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Man and I even went over it to be absolutely certain it wasn't something stupid. Rob

>I've tried using the result.identitycol trick with CF8 and MSSQL and for >me, it doesn't work.  I just get an error. > Not that it is the best approach, but the result.identitycol trick is a little 'tricky' eg.  <cfquery name="queryname" datasource="nameddatasource"> if you cfdump queryname, you will see the identity col, BUT if you try to get the identitycol with queryname.identitycol, it doesn't work. Here is the tricky part: add a 'result' parameter eg.  <cfquery name="queryname" datasource="nameddatasource" result="qnvars"> and then refer to the identitycol through that result. <cfdump var="#qnvars.identitycol#"> Hope this helps

For MSSQL there's a native function to give you the id.   In a CFQuery tag it works like this: <cfquery name="Insert" datasource="#variables.dsn#" >     SET NOCOUNT ON     INSERT into Tablename     ( email, firstname, lastname ) VALUES     (     <cfqueryparam value="#email#" cfsqltype="cf_sql_varchar"/>,     <cfqueryparam value="#firstname#" cfsqltype="cf_sql_varchar"/>,     <cfqueryparam value="#lastname#" cfsqltype="cf_sql_varchar"/>        )     SELECT Ident_Current('Tablename') as RecordID     SET NOCOUNT OFF   </cfquery> The NOCOUNT thing prevents MSSQL returning messages that might cause the query to abort.   The Ident_Current('tablename') gives you the ident of the latest insert in the current tablename.   Because it's all done in a single CFQUERY, I do believe the table  is automatically locked This is only a MSSQL solution but i've never had a problem with getting the ID this way, even on my highest volume sites. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month ----- Excess quoted text cut - see Original Post for more -----

To what CF8 goodies do you refer?  I do everything in ORACLE (mostly using stored procs) but I haven't had a chance to do a deep dive into CF8 yet... Thanks! Warren Koch

http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_17.html "result_name.ROWID; Oracle only. The ID of an inserted row. This is not the primary key of the row, although you can retrieve rows based on this ID." This means we can now insert a row and then query the DB on the ROWID to get the new PK (useful if the PK came from a trigger that incremented a sequence). Even works without a transaction. If you're using stored procs you have the "RETURNING INTO" clause at your disposal, so it may be less useful for you. > To what CF8 goodies do you refer?  I do everything in ORACLE (mostly using > stored procs) but I haven't had a chance to do a deep dive into CF8 yet... > -- mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/

;) William Seiter (mobile) Have you ever read a book that changed your life? go to:  http://www.winninginthemargins.com and use passcode: GoldenGrove William Seiter wrote: > Try SCOPE_IDENTITY() instead of SCOPY_IDENTITY() D'OH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Man and I even went over it to be absolutely certain it wasn't something stupid. Rob