GENERATED_KEY issue

I'm having an error with the code attached.

The error is:
Element GENERATED_KEY is undefined in NEWLEADPK.  

If you notice I'm pulling the result="newLeadPk" from the first query and trying to access it from within the next query. Basically I want to have access to the Generated_Key from the most updated file in the table. I hope this make sense.

Can someone help me with this code?

<!--- Add Lead --->
 <cfquery name="insertLead" datasource="#application.datasource#" result="newLeadPk">
  INSERT INTO 
  leads(
        FirstName,
        LastName,
        Secondary_first,
        Secondary_last,
        Company,
        HomePhone,
        WorkPhone,
        MobilePhone,
        OtherPhone,
        OtherPhoneLabel,
        AltPhone,
        AltPhoneLabel,
        EmailAddress,
        SecondEmail,
        Website,
        PropertyAddress,
        City,
        State,
        ZipCode)
   VALUES (
        '#Trim(FORM.FirstName)#',
        '#Trim(FORM.LastName)#',
        '#Trim(FORM.Secondary_first)#',
        '#Trim(FORM.Secondary_last)#',
        '#Trim(FORM.Company)#',
        '#Trim(FORM.HomePhone)#',
        '#Trim(FORM.WorkPhone)#',
        '#Trim(FORM.MobilePhone)#',
        '#Trim(FORM.OtherPhone)#',
        '#Trim(FORM.OtherPhoneLabel)#',
        '#Trim(FORM.AltPhone)#',
        '#Trim(FORM.AltPhoneLabel)#',
        '#Trim(FORM.EmailAddress)#',
        '#Trim(FORM.SecondEmail)#',
        '#Trim(FORM.Website)#',
        '#Trim(FORM.PropertyAddress)#',
        '#Trim(FORM.City)#',
        '#Trim(FORM.State)#',
        '#Trim(FORM.ZipCode)#')
 
 </cfquery>
  <cfset action="ADDED">
  
  <!--- Insert Lead Tracking--->
  <cfquery name ="addRecord" dataSource="#application.datasource#"> 
	   INSERT INTO lead_tracking (
       LeadID,
       Lead_SourceID,
       Lead_typeID,
       Lead_priorityID,
       CreationDate)
       VALUES (
       '#newLeadPk.GENERATED_KEY#',
       '#FORM.Lead_SourceID#',
       '#FORM.Lead_typeID#',
       '#FORM.Lead_priorityID#',
       #CreateODBCdate(FORM.CreationDate)#)
 </cfquery>

Open in new window

LVL 1
jasch2244Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
gdemariaConnect With a Mentor Commented:

You could put them in the same cfquery
<!--- Add Lead --->
 <cfquery name="insertLead" datasource="#application.datasource#" result="newLeadPk">
  INSERT INTO 
  leads(
        FirstName,
        LastName,
        Secondary_first,
        Secondary_last,
        Company,
        HomePhone,
        WorkPhone,
        MobilePhone,
        OtherPhone,
        OtherPhoneLabel,
        AltPhone,
        AltPhoneLabel,
        EmailAddress,
        SecondEmail,
        Website,
        PropertyAddress,
        City,
        State,
        ZipCode)
   VALUES (
        '#Trim(FORM.FirstName)#',
        '#Trim(FORM.LastName)#',
        '#Trim(FORM.Secondary_first)#',
        '#Trim(FORM.Secondary_last)#',
        '#Trim(FORM.Company)#',
        '#Trim(FORM.HomePhone)#',
        '#Trim(FORM.WorkPhone)#',
        '#Trim(FORM.MobilePhone)#',
        '#Trim(FORM.OtherPhone)#',
        '#Trim(FORM.OtherPhoneLabel)#',
        '#Trim(FORM.AltPhone)#',
        '#Trim(FORM.AltPhoneLabel)#',
        '#Trim(FORM.EmailAddress)#',
        '#Trim(FORM.SecondEmail)#',
        '#Trim(FORM.Website)#',
        '#Trim(FORM.PropertyAddress)#',
        '#Trim(FORM.City)#',
        '#Trim(FORM.State)#',
        '#Trim(FORM.ZipCode)#'
      );
 
    SELECT @@IDENTITY as NewID
  </cfquery>

  <cfset action="ADDED">
  
  <!--- Insert Lead Tracking--->
  <cfquery name ="addRecord" dataSource="#application.datasource#"> 
	   INSERT INTO lead_tracking (
       LeadID,
       Lead_SourceID,
       Lead_typeID,
       Lead_priorityID,
       CreationDate)
       VALUES (
       '#insertLead.NewID#',
       '#FORM.Lead_SourceID#',
       '#FORM.Lead_typeID#',
       '#FORM.Lead_priorityID#',
       #CreateODBCdate(FORM.CreationDate)#)
 </cfquery>

Open in new window

0
 
gdemariaCommented:

Is this mySQL 4 or 5 ?  This isn't supported in 3..

0
 
jasch2244Author Commented:
MySQL 5
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
_agx_Commented:
Also, cfdump #newLeadPk#. What values *does* it contain?
0
 
jasch2244Author Commented:
I'm getting:

CACHED false  
EXECUTIONTIME 16  
RECORDCOUNT 1  
SQL INSERT INTO leads( FirstName, LastName, Secondary_first, Secondary_last, Company, HomePhone, WorkPhone, MobilePhone, OtherPhone, OtherPhoneLabel, AltPhone, AltPhoneLabel, EmailAddress, SecondEmail, Website, PropertyAddress, City, State, ZipCode) VALUES ( 'Test', 'Tuesday4', '', '', '', '', '', '', '', '', '', '', 'asdf@fuse.net', '', '', '', '', '', '')  
0
 
_agx_Commented:
Stupid question ... but are you sure there's an auto increment column on that table?
0
 
jasch2244Author Commented:
Yes in both tables
0
 
HainKurtSr. System AnalystCommented:
do you have auto increment column in the leads table?
0
 
HainKurtSr. System AnalystCommented:
0
 
_agx_Commented:
1) Any triggers on the tables or special connection strings in your dsn settings?
2) Does GENERATED_KEYS work for *any* of your tables?
3) Just for kicks, run the generated sql from your dump in a cfquery. Is the key still missing from the result structure ?

<cfquery  .......>
INSERT INTO leads( FirstName, LastName, Secondary_first, Secondary_last, Company, HomePhone, WorkPhone, MobilePhone, OtherPhone, OtherPhoneLabel, AltPhone, AltPhoneLabel, EmailAddress, SecondEmail, Website, PropertyAddress, City, State, ZipCode) VALUES ( 'Test', 'Tuesday4', '', '', '', '', '', '', '', '', '', '', 'asdf@fuse.net', '', '', '', '', '', '')  
</cfquery>

0
 
HainKurtSr. System AnalystCommented:
as you see

CACHED false  
EXECUTIONTIME 16  
RECORDCOUNT 1  

query does not return any identity key!
0
 
_agx_Commented:
.. and of course obvious

4) If you leave CF out of the mix and run the insert directly in MySQL does it generate a new ID?
0
 
HainKurtSr. System AnalystCommented:
looks like you do not have auto increment column... post the table definition (or table script)
0
 
jasch2244Author Commented:
For Lead_Tracking table I have the TrackingID field as the primary key and to auto increment
For Leads I have LeadID as  the primary key and to auto increment

Do you mean in my tables or in the insert command? As I do have columns that are set up to auto increment :)
0
 
jasch2244Author Commented:
The problem seems to lie in the second query (see code) as the first is inserting data into first insert table. I'm confident it is somehow not pulling ithe Generated_Key from the other query, the second insert query is not making it to table.
<!--- Insert Lead Tracking--->
  <cfquery name ="addRecord" dataSource="#application.datasource#"> 
	   INSERT INTO lead_tracking (
       LeadID,
       Lead_sourceID,
       Lead_typeID,
       Lead_priorityID,
       CreationDate)
       VALUES (
       '#newLeadPk.GENERATED_KEY#',
       '#FORM.Lead_SourceID#',
       '#FORM.Lead_typeID#',
       '#FORM.Lead_priorityID#',
       #CreateODBCdate(FORM.CreationDate)#)
 </cfquery>
  <cflocation url="Lead_Modifier.cfm?LeadID=#newLeadPk.GENERATED_KEY#"> 
</cfif>

Open in new window

0
 
_agx_Commented:
as the first is inserting data into first insert table

Ok ... but the question is *why* is the GENERATED_KEY not being populated.  Did you try any of the steps I mentioned .. to rule out obvious problems first.
0
 
_agx_Commented:
the second insert query is not making it to table

Makes sense. Since you said an error occurs on the 2nd query, it never executes. So nothing inserted into lead_tracking
0
 
_agx_Commented:
Sorry but it's time for me to turn into a pumpkin folks. :) I'll check back tomorrow.
0
 
jasch2244Author Commented:
_agx:

you mentioned: "If you leave CF out of the mix and run the insert directly in MySQL does it generate a new ID? "

I ran the Insert command listed above and it is indeed auto incrementing into the table and generating a new ID, yes
0
 
_agx_Commented:
Ok. I really do have to head out. But did you see the other questions/suggestions:

ie

* Does GENERATED_KEYS work for *any* of your tables?  ie Have you ever used it successfully on this server?

* Just for kicks, run the generated sql from your dump in a cfquery. Is the key still missing from the result structure ?

* Any triggers on the tables or special connection strings in your dsn settings?

0
 
jasch2244Author Commented:
Ok it works locally but not on server side... that sucks. Must be server issue... I'm in a shared environment if that means anything. I know -agx is down for the night and hope to have some help soon. Thanks as always :)
0
 
gdemariaCommented:

How about switching to the "old" way... Isn't there an equivalent to SQL Servers

 Select @@SCOPE_IDENTITY()...

You can just add this select after the insert...
0
 
jasch2244Author Commented:
Not sure what you mean... by old way as I'm an amature :) how would that work with my code can you show me please.
0
 
gdemariaCommented:
This results=""  is a fairly new feature for CF, before it, developers would simply select the identity using the database's supplied mechanism.

I looked it up for mySQL, it is @@Identity

You simply follow the insert statement with a fetch of @@identity and it gives you the last ID created...


<!--- Add Lead --->
 <cfquery name="insertLead" datasource="#application.datasource#" result="newLeadPk">
  INSERT INTO 
  leads(
        FirstName,
        LastName,
        Secondary_first,
        Secondary_last,
        Company,
        HomePhone,
        WorkPhone,
        MobilePhone,
        OtherPhone,
        OtherPhoneLabel,
        AltPhone,
        AltPhoneLabel,
        EmailAddress,
        SecondEmail,
        Website,
        PropertyAddress,
        City,
        State,
        ZipCode)
   VALUES (
        '#Trim(FORM.FirstName)#',
        '#Trim(FORM.LastName)#',
        '#Trim(FORM.Secondary_first)#',
        '#Trim(FORM.Secondary_last)#',
        '#Trim(FORM.Company)#',
        '#Trim(FORM.HomePhone)#',
        '#Trim(FORM.WorkPhone)#',
        '#Trim(FORM.MobilePhone)#',
        '#Trim(FORM.OtherPhone)#',
        '#Trim(FORM.OtherPhoneLabel)#',
        '#Trim(FORM.AltPhone)#',
        '#Trim(FORM.AltPhoneLabel)#',
        '#Trim(FORM.EmailAddress)#',
        '#Trim(FORM.SecondEmail)#',
        '#Trim(FORM.Website)#',
        '#Trim(FORM.PropertyAddress)#',
        '#Trim(FORM.City)#',
        '#Trim(FORM.State)#',
        '#Trim(FORM.ZipCode)#')
 
 </cfquery>
 <cfquery name="getID"  datasource="#application.datasource#">
   SELECT @@IDENTITY as NewID
 </cfquery>

  <cfset action="ADDED">
  
  <!--- Insert Lead Tracking--->
  <cfquery name ="addRecord" dataSource="#application.datasource#"> 
	   INSERT INTO lead_tracking (
       LeadID,
       Lead_SourceID,
       Lead_typeID,
       Lead_priorityID,
       CreationDate)
       VALUES (
       '#getID.NewID#',
       '#FORM.Lead_SourceID#',
       '#FORM.Lead_typeID#',
       '#FORM.Lead_priorityID#',
       #CreateODBCdate(FORM.CreationDate)#)
 </cfquery>

Open in new window

0
 
jasch2244Author Commented:
Do you have to reference the table of the last inserted record? Or does it just know?
You had:
<cfquery name="getID"  datasource="#application.datasource#">
   SELECT @@IDENTITY as NewID
 </cfquery>

Open in new window

0
 
gdemariaCommented:
It just knows... (it returns the last identity value created for that session)
So, the way it's shown above is correct
0
 
HainKurtSr. System AnalystCommented:
is this working fine?

<cfquery name="getID"  datasource="#application.datasource#">
   SELECT @@IDENTITY as NewID
 </cfquery>

it looks like this is Ms SQL implementation...
0
 
_agx_Commented:
Do you have to reference the table of the last inserted record? Or does it just know?

Yeah, it just knows. But be very careful when using session variables like @@IDENTITY in a different cfquery than the original insert.  You're not guaranteed the same db session from one cfquery to another unless you use a cftransaction around both.  Since you've got multiple inserts, that's something you should do anyway.

Though I'm still curious about the original question - why isn't result working on your server.

<cftransaction>
       <cfquery ...> INSERT 1  ....</cfquery>
       <cfquery name="getID"....> SELECT @@IDENTITY as NewID</cfquery>
       <cfquery ...> INSERT 2.... </cfquery>
</cftransaction>

Open in new window



0
 
_agx_Commented:
You could put them in the same cfquery

Yeah, but I think cftransaction is a better idea here.  Multiple statements *aren't* enabled by default and some hosts leave it that way for better security.  Pluse they need it anyway.  If one of the queries fails, they probably want both to fail so they don't end up with corrupted data.
0
 
jasch2244Author Commented:
Yeah I use to use SELECT MAX(lastidnum) etc... but someone on this site turned me onto Generated_Key instead due to if multiple users are inserting data at the same time as it could be problematic. I used Generated_Key on my local machine and everything worked fine until I uploaded to server. I've got a support ticket out right now to Support to see what is up. I'll keep you posted. In the meantime I'll play around with what is above.
0
 
_agx_Connect With a Mentor Commented:
Yeah, SELECT MAX() is definitely out. So go with a <cftransaction> + @@IDENTITY for now. But the best method for CF8+ is what you had originally. So hopefully the host can get it working properly :)
0
 
jasch2244Author Commented:
It was the drivers for the databse they were using 3.0 and moved me over to newer drivers and the Generated_Key works fine now. Thanks for your help and teaching me new method @@IDENTITY
0
 
_agx_Commented:
It was the drivers for the databse they were using 3.0

Wow, that's really old. They're up to around 5.15+ now.  

Btw, if the adjusted your connection to allow multiple statements like
       INSERT INTO Table (....) VALUES(....);
       SELECT @@IDENTITY AS TheID;

... that means you're vulnerable to sql injection.  So be sure to use cfqueryparam everywhere
0
All Courses

From novice to tech pro — start learning today.