Solved

GENERATED_KEY issue

Posted on 2011-02-15
34
466 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:jasch2244
  • 12
  • 11
  • 6
  • +1
34 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 34903278

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

0
 
LVL 1

Author Comment

by:jasch2244
ID: 34903292
MySQL 5
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34903303
Also, cfdump #newLeadPk#. What values *does* it contain?
0
 
LVL 1

Author Comment

by:jasch2244
ID: 34903363
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34903379
Stupid question ... but are you sure there's an auto increment column on that table?
0
 
LVL 1

Author Comment

by:jasch2244
ID: 34903415
Yes in both tables
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34903426
do you have auto increment column in the leads table?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34903431
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34903441
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34903443
as you see

CACHED false  
EXECUTIONTIME 16  
RECORDCOUNT 1  

query does not return any identity key!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34903455
.. 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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34903467
looks like you do not have auto increment column... post the table definition (or table script)
0
 
LVL 1

Author Comment

by:jasch2244
ID: 34903562
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
 
LVL 1

Author Comment

by:jasch2244
ID: 34903595
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34903636
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34903645
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34903842
Sorry but it's time for me to turn into a pumpkin folks. :) I'll check back tomorrow.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:jasch2244
ID: 34903860
_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
 
LVL 52

Expert Comment

by:_agx_
ID: 34903903
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
 
LVL 1

Author Comment

by:jasch2244
ID: 34903941
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34907250

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
 
LVL 1

Author Comment

by:jasch2244
ID: 34907277
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34907336
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
 
LVL 1

Author Comment

by:jasch2244
ID: 34907546
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34907821
It just knows... (it returns the last identity value created for that session)
So, the way it's shown above is correct
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34908233
is this working fine?

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

it looks like this is Ms SQL implementation...
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34908321
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34908332
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 250 total points
ID: 34908421

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
 
LVL 52

Expert Comment

by:_agx_
ID: 34908489
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
 
LVL 1

Author Comment

by:jasch2244
ID: 34908495
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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
ID: 34908524
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
 
LVL 1

Author Closing Comment

by:jasch2244
ID: 34928891
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34928966
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now