Getting the Generated Key From A Query in ColdFusion (Including Script Based Queries)

Are you are still writing things like SELECT SCOPE_IDENTITY()  or LAST_INSERT_ID() in your ColdFusion Queries?  One of the more powerful and perhaps lesser known features of the cfquery tag is the ability to return the generated ID from an insert statement.

Prior to ColdFusion 9 the value returned in the query struct would change depending on the type of database the value was being inserted into (see cfquickdocs for more information).   For Instance Oracle would return a key called RowID  and SQL Server would return a key called identitycol in the result structure of a query (provided you supplied a result argument in the query itself).

<cfquery name="insertMe" datasource="mysql_db" result="queryResult">
 INSERT INTO
   Contact(firstname, lastname) 
 VALUES('ryan','anklam');
</cfquery>

In ColdFusion 9 Adobe simplified this by always returning a value called generatedkey in the result structure as shown below:

 






Things get a bit more interesting when leveraging ColdFusion's new script based queries.  Lets use the following code as an example:

<cfscript>
 insertQuery = new query();
 
 insertQuery.SetDatasource("facets_sql");
 
 insertQuery.SetSql("insert into contact(firstname, lastname) values('ryan','anklam')");
 
 result = insertQuery.Execute();
</cfscript>

One might expect that generated key would be a property that could be accessed through the result variable.  However, looking at the dump of the result variable we can see that ColdFusion actually wraps all the result goodness in a property called prefix:

So, in order to get the generated key from a script based query the following code is required:

<cfscript>
theKey = result.getPrefix().generatedkey;
</cfscript>

The Books / Classes I Wish to complete in 2011

Since I've purchased my nook color last year I've been addicted to reading tech-ebooks and with that has come the motivitation to learn everything that I can, including webinars and online classes. 

• Douglas Crockford's JavaScript Master Class is something I've wanted to watch for some time now.
• Programming Ruby 1.9 The Pragmatic Programmer books are excellent, and I really want to learn more Ruby this year.
• 97 Things Every Programmer Should Know I read and enjoyed 97 Things Every Software Architect should know so this seems like the next logical step.
• Head First Software Develpment I liked Head First Java and I want to read other things besides just programming language books this year.
• jQuery Cookbook I've heard great thigns about this book.
• HTML5 Up and Running I need to start to come around on the whole HTML5 thing.
• Agile Web Development With Rails Last and not least I want to learn more about Rails 3 and I can't think of a better resource than one with DHH himself listed as an author.

Book Review: Sitepoint's Simply JavaScript by Kevin Yank & Cameron Adams

My life has finally slowed down over the last few weeks which has allowed me to start to tackle my huge list of books to read.  The first book I decided to read was SitePoint's Simply JavaScript written by Kevin Yank and Cameron Adams.  This was also the first book I read on my new Nook Color (I previously had a e-paper Nook) and I have to say that I waas very impressed with the quality of SitePoint's ePub format.  The code was fairly easy to read in within the constraints of the ePub format and the illustrations generally looked really good.   The splashes of color were also a nice touch when reading, its amazing much a subtle detail like a blue line can make your reading experience a little more enjoyable.  

So why did I choose this book to read first from the list of about 15 books I want to read?  I've been a software developer for over 10 years but I never really learned the basics of JavaScript.  I've spent a good part of  my career behind a firewall where I only had to target my company's standard browser so I was always able to hack away a solution that worked.  Once I moved on to a company that had more public facing sites I picked up jQuery and pretty much learned how to write jQuery code and not worry too much about the JavaScript behind what I was writing.  This always somewhat bothered me and when Site Point (http://www.sitepoint.com) offered this book as one of their 25 days of Christmas deals this year I jumped on it.

I immediately felt comfortable with the way the authors spoke to me in this book.  Some technical books are so dry they could suck the mositure out of the desert air, however this book has a plesent delivery that made reading it that much more easy.  Another caviat I have about a lot of technical books is that they typically spend too much time delving into a topic and bore me into glazing over the pages and not really absorbing what the author is trying to convey.  Kevin and Cameron do a great job of pacing the concepts they cover in this book.

The first chapter is all about one of the fundamential building blocks of JavaScript in the browser: the DOM.  They start by explaining the concepts of the DOM and some of its idiosyncrasies. A good example of this is their explanation of how whitespace can actually create new text nodes in the DOM structure. The next chapter dives into the event model, and goes quite in-depth about all of the cross browser issues that developers used to complain about before frameworks became popular and took care of most of these issues automatically.  After events are covered the authors then delve into the world of animation, another thing that the popular libraries do a great job of abstracting out for modern JavaScript developers.  This book did a very good job of explaining a lot of the details qne techniques behind animation, again providing some very good examples along the way.  After animation is covered the authors build upon the lessons learned in previous chapters and introduce the conepts of form enhancement using JavaScript.   In this chapter the authors will walk the reader through creating some common form enhancements, which after you'll begin to really appreciate how much work goes into plugin development for your favorite JS library.  Next the authors walk you through the black art of debugging JavaScript code and finish up with a brief introduction to Ajax.

What I really liked about this book is that the sample code in this book is easy to follow and really helps strengthen the concepts that are discussed throughout. The code builds upon the concpepts that are introduced throughout the book and by the end you feel very comfortable using the techniques described througout.   Kevin and Cameron also do a great job of breaking down the code after it is presented and give an in-depth description important steps throughout the code.  Other important JavaScript concepts are sprinkled throughout text as well, for example closures and object literal syntax are both covered  in this book.  In addition basics like variable scoping, array creation, looping concepts and conditional statments are also introduced and explained in this book.  One of the biggest compliments I can give this book is that after reading it I actually understood the concept of closures, something I had been trying to grasp for sometime.

Since this book is aimed at a beginner to JavaScript I feel like it could have done a better job of explaining some of the built in functions of the JavaScript language and perhaps touched some more on a few of the shorthand methods that one will see in many scripts such as ternary operators.  

I'd definately reccomend this book to anyone that wants to learn a little bit more about JavaScript and whats behind the magic of some of today's popular JavaScript frameworks.  An advanced JavaScript developer will probabaly find this book a boring, but thats not the intended audience for this book anyway.