Solved

GENERATED_KEY issue

Posted on 2011-02-15
34
469 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

914 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

17 Experts available now in Live!

Get 1:1 Help Now