Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

capture id of index (identity) column after insert in cold fusion insert query

Posted on 2009-07-09
6
Medium Priority
?
619 Views
Last Modified: 2013-12-24
I have a query that I am executing:

<cftransaction isolation="serializable">

<cfquery name="enter_ms_values" datasource="my_DSN" result="music_insert">
INSERT INTO Music (church_ID,Music_Date,Music_Time,Performance_Type,Music_Location,Music_Desc,calendar_ID,user_id,created_by,created_date,created_time)
VALUES ('#church_id#', '#new_music_date#', '#new_music_time#','#new_performance_type#','#new_music_location#','#new_music_desc#','#calendar_id#','#posted_by#','#posted_by#','#dateformat(now(), 'mm/dd/yyyy')#','#TimeFormat(Now(),"hh:mm tt")#')
</cfquery>



      <cfoutput>
      <!--- this is where we capture the number of the identity/key column for music --->
      <CFPARAM NAME="new_music_id" DEFAULT="">
      <cfset new_music_id = "#??????????????#">      
       
       
       the number of my music even is "#new_music_id#"<br />
   
               
          </cfoutput>



I need the value that goes where I have put "????????"  - or - a  better way of getting it done, besides this way.




I need to capture the id of the key/identity column (music_id) for the table "music". When the information is added to the database, it represents the addition of a new music event - but I have to have the music event's Id  (music_id)  because I have to add other values to another table (see my second question) and I have to add the music_id to that table so that those values are associated with this music event by that number.

I am also a but fuzzy on 'table locking' because this table may be getting hit with multiple addition queries - and it is important to make sure that the right music_id is captured.
0
Comment
Question by:btintermedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 160 total points
ID: 24818373
immediately after the insert statement run this , this will return the last inserted value
SELECT SCOPE_IDENTITY()
0
 
LVL 19

Assisted Solution

by:erikTsomik
erikTsomik earned 360 total points
ID: 24818408
what you need to do is this . After the insert query add this query and store the identity column

<cfquery name="MusicID" datasource="XXXXXXX">
                                    SELECT @@Identity AS MusicID
                              </cfquery>
            
            <cfset newMusicID= MusicID.MusicID>
The you can set the value

  <cfset new_music_id = "#newMusicID#">      
0
 
LVL 19

Accepted Solution

by:
Jones911 earned 480 total points
ID: 24818508
Both of those are not good the best way to handle this the best way is to use the inbuilt functionality of Coldfusion:

http://www.forta.com/blog/index.cfm/2007/7/6/ColdFusion-8-Can-Return-Identity-Values
<cfquery name="enter_ms_values" datasource="my_DSN" result="music_insert">
INSERT INTO Music (church_ID,Music_Date,Music_Time,Performance_Type,Music_Location,Music_Desc,calendar_ID,user_id,created_by,created_date,created_time)
VALUES ('#church_id#', '#new_music_date#', '#new_music_time#','#new_performance_type#','#new_music_location#','#new_music_desc#','#calendar_id#','#posted_by#','#posted_by#','#dateformat(now(), 'mm/dd/yyyy')#','#TimeFormat(Now(),"hh:mm tt")#')
</cfquery>
 
<cfoutput> Total records: #music_insert.IDENTITYCOL#</cfoutput>

Open in new window

0
Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

 
LVL 19

Assisted Solution

by:Jones911
Jones911 earned 480 total points
ID: 24818513
CF8 only however.
0
 

Author Closing Comment

by:btintermedia
ID: 31601837
thanks - your help was greatly appreciated
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24818737
Jones911's suggestion is the most correct for CF8.

> SELECT @@Identity

In terms of sql only, aneeshattingal is correct.   SCOPE_IDENTITY() is generally preferred over @@Identity.  
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

721 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