Railo doesn't recognize cfquery's result ID variable.

{ Posted By : Eric Cobb on February 24, 2009 }
Comments (5)
1558 Views
Related Categories: Railo, ColdFusion

This may not come as a big surprise to some people, but Railo doesn't seem to recognize the new query result attribute added to CF 8 that gets the ID of an inserted record.

I wasn't sure if Railo supported this or not, I've never seen any documentation that specifically stated whether it did or didn't. So I thought I would try it and see.

For those that don't know, the cfquery result attribute returns a list of variables in a structure. In this particular case, I was specifically after the ID variable. LiveDocs defines this as "The result variable of INSERT queries contains a key-value pair that is the automatically generated ID of the inserted row".

For whatever reason, Railo seems to bomb whenever you try to reference the result ID. I didn't try any of the other variables, so I don't know for sure if they work or which ones may be supported.

This really isn't a big deal for me, as I usually just use SQL Server's Scope_Identity() for this. I just wanted to make note of it in case anyone else ran into it.

Send | del.icio.us | Digg It! | Linking Blogs
Comments
[ Add Comment ] [ Subscribe to Comments ]
Sean Corfield's Gravatar One thing that is a pain about the CF8 result ID machinery is that it is a different key for each database which makes it impossible to write DB-neutral code (unless you enumerate *all* of the possible ID key names!).
# Posted By Sean Corfield | 2/25/09 1:27 AM
Gary Gilbert's Gravatar Eric,

you are quite right Railo doesn't support it and I am quite glad that they don't for the reasons that Sean highlighted. You have to use a different ID for each type of database. It would have been more helpful if CF provided a single variable and based on the database return the appropriate value in the ID field.
# Posted By Gary Gilbert | 2/25/09 4:55 AM
Gert Franz's Gravatar Eric,

we will have this functionality in Railo 3.1. In addition Railo will have a key called ID in the result struct, which always will contain the last inserted key. Next to this one the ones CF produces will be available for the corresponding DB drivers...
Anyway since this really hurts performance, we will only include it in the result struct as soon as someone really uses the attribute result="" and if its of course an Insert statement.

Hope this makes sense...

Greetings from Switzerland
Gert Franz
Railo Technologies GmbH
gert.franz@railo.ch
www.railo.ch

Join our Mailing List
german:    http://de.groups.yahoo.com/group/railo/
english:    http://groups.yahoo.com/group/railo_talk/
linked in:    http://www.linkedin.com/e/gis/71368/0CF7D323BBC1
Bug tracker:    http://jira.jboss.org/jira/browse/RAILO
Railo Blog:    http://www.railo-technologies.com/blog
# Posted By Gert Franz | 2/25/09 8:28 AM
Eric Cobb's Gravatar @Sean & @Gary: Yeah, it's really a pain that you have to use a different key for each DB. I'll agree that CF needs a single generic variable here, regardless of the DB used. I've actually never even used this feature before, I just wrote a quick insert query for something I was working on last night and decided to try it as a shortcut to get the ID.

@Gert: That's good to know. Railo is an amazing product, and you guys have done a fantastic job with it! You guys are really on top of things. I love how whenever someone finds a bug, you are usually already aware of it and have a patch or update in the works for it. Keep up the great work!
# Posted By Eric Cobb | 2/25/09 10:17 AM
Anthony's Gravatar Hey Gert,

I am using 3.1 and it's not in there as you have said, what gives? Wanting to switch my app over from cf8, alas not having the ID in the result struct is a bit of a deal breaker. Any chance you can give us an update on this one?
# Posted By Anthony | 8/12/09 1:37 AM
[ Add Comment ]