Thanks. As I already posted, it's a bug in CF8. For some reason, CF8 does
not properly handle an SELECT INTO insert, and does not return the new ID.
So the workaround is to use a standard VALUES insert instead.
Now then, if you do not need to know the newly inserted ID, then CF8 handles
the SELECT INTO just fine.
<!--- this works just fine in CF8 --->
<cfquery>
INSERT INTO
myTable
SELECT
'foo',
myOtherTable.IDcol
FROM
myOtherTable
WHERE
myOtherTable.bar = 'wtf?'
;
</cfquery>
However, this fails when you try and reference the query/new ID.
<cfquery name="tmp">
INSERT INTO
myTable
SELECT
'foo',
myOtherTable.IDcol
FROM
myOtherTable
WHERE
myOtherTable.bar = 'wtf?'
;
SELECT
scope_identity() AS NewId
FROM
myTable
;
</cfquery>
<cfset theNewId = tmp.NewId[1] /> <!--- this is where it fails --->
Even if you use the 'result' attribute instead of the 'name' attribute, the
query will do the insert, but the 'identitycol' key is undefined in the
'result' struct.
<cfquery result="tmp">
INSERT INTO
myTable
SELECT
'foo',
myOtherTable.IDcol
FROM
myOtherTable
WHERE
myOtherTable.bar = 'wtf?'
;
</cfquery>
<cfset theNewId = tmp.identitycol /> <!--- this is where it fails --->
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338581
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
