Link to home
Start Free TrialLog in
Avatar of jasch2244
jasch2244

asked on

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

Avatar of gdemaria
gdemaria
Flag of United States of America image


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

Avatar of jasch2244
jasch2244

ASKER

MySQL 5
Also, cfdump #newLeadPk#. What values *does* it contain?
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', '', '', '', '', '', '')  
Stupid question ... but are you sure there's an auto increment column on that table?
Yes in both tables
do you have auto increment column in the leads table?
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>

as you see

CACHED false  
EXECUTIONTIME 16  
RECORDCOUNT 1  

query does not return any identity key!
.. 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?
looks like you do not have auto increment column... post the table definition (or table script)
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 :)
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

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.
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
Sorry but it's time for me to turn into a pumpkin folks. :) I'll check back tomorrow.
_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
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?

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 :)

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...
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.
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

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

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

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

it looks like this is Ms SQL implementation...
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



ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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