Autogenerated CFCs
Current setup...
I'm on MSSQL Server and autogenerate my bean, DAO, and Gateway with the wizard in Adobe's CF extensions. A lot of the code it generates is crap, but still it saves me a LOT of time and keeps some level of consistency to my code.
On an insert, the generated code puts a cftransaction around the insert, then immediately queries the record to get the autogenerated ID. With MSSQL I was able to toss something like the below immediately after the insert statement, in the same cfquery block, to get the auto ID (as queryName.AutoID).
Problem is that we might be moving to Oracle, and this code is MSSQL-specific. Crap. But CF8 adds a bunch of data to a query's result structure, namely it'll return the auto generated ID of an inserted row. Problem is that his is result_name.IDENTITYCOL for MSSQL, result_name.ROWID for Oracle, etc. So I made a new application scope variable for the database type and another to tell me what the field in the result structure will be.
<cflock scope="application" type="exclusive" timeout="10">
<cfscript>
application.dsn = '$DSN';
application.dbid = '$DBID';
application.dbpw = '$DBPW';
application.dsnAttributes = {
datasource = application.dsn,
username = application.dbid,
password = application.dbpw
};
application.dbServer = "SQL"; /* SQL=IDENTITYCOL | ORACLE=ROWID */
if( application.dbServer eq "SQL" )
application.insertResultID = "IDENTITYCOL";
else if( application.dbServer eq "ORACLE" )
application.insertResultID = "ROWID";
</cfscript>
</cflock>
</cffunction>
So now after a cfquery insert, with result="rCreate", I can set the auto-gen ID in the bean that was passed in, before I pass it back out. If the whole app moves to Oracle, I'll change one variable in Application.cfc and boom, done.
arguments.bean.setAOID( Evaluate("rCreate.#application.insertResultID#") );
</cfscript>
<cfreturn arguments.bean />
http://cflib.org/udf/getGeneratedKey
SCOPE_IDENT() is the proper thing to use.
1) It doesn't generate crap code
2) the code it does generate is completely customizable via the XSL or CFML templates
3) It is free and open source and you can do whatever you will with the code
4) Its got a cooler name than ColdFusion 8 Exlipse Extensions (which fwiw shows a complete lack of creativity).
5) See points 1 through 4
Convinced? ;)
@Todd - Brilliant CFC! Guess I should look around online more first, eh? Even better to not have to set the result value anywhere. And no ugly Evaluate() syntax. I can see this being extra great if I have MSSQL and Oracle calls in the same app.
@Joe - I didn't know this! I found the @@identity code a long while back and never hit a problem with it. But then, I generally don't do much with the bean once I get it back. I'll stop using it, using Todd's CFC instead, and read up on why I've been bad.
@All - Like Digg, here's proof that all the good content is in the comments, not the post!