asked on

oo coldfusion get inserted id

i am not oo cf guy and wondering how i can get the insert id of the current record.

DAO.CFC FILE
*************

<cffunction name="create" access="public" output="false" returntype="boolean">
<cfargument name="socprocess" type="socprocess" required="true" />
<cfset var qCreate = "" />
<cfquery name="qCreate" datasource="#variables.dsn#">
INSERT INTO pediatricAssessments (patientsref)
VALUES ( <cfqueryparam value="#arguments.socprocess.getpatientsref()#" CFSQLType="cf_sql_integer" null="#not len(arguments.socprocess.getpatientsref())#" /> )
SELECT IDENT_CURRENT ('pediatricAssessments') AS insertid
</cfquery>
<cfset createExtend(arguments.socprocess,#qCreate.insertid#)>
<cfreturn true />
</cffunction>

Open in new window

I NEED TO GET THAT insertid VALUE, SO I CAN INSERT INTO THE INCLUDE FILE insertTMCompletedPEDASSESS.cfm

PROCESS.CFM FILE
*****************

<cfif isDefined("FORM.processType") AND FORM.processType EQ "addRecord">
<cfset pedna=CreateObject("component","socprocess").init('',FORM.PatientsRef) />
<cfset socprocessDAO = createObject("component", "socprocessDAO").init(dsn=this.dsn) />
<cfset socprocessDAO.create(pedna)>
<!---adding values in taskMgrCompleted table--->
<cfif isDefined("FORM.povRef") AND FORM.povRef NEQ "">
<cfinclude template="../insertTMCompletedPEDASSESS.cfm" />
</cfif>
</cfif>

Open in new window

any help, comment or feedback would be deeply appreciated.

Brian Crowe

Try using SCOPE_IDENTITY() instead of IDENT CURRENT

Walter USA

ASKER

have changed the sql to: select SCOPE_IDENTITY('pediatricAssessments') as insertid

but still wondering how i can get that current inserted id, so i can pass it to my page: insertTMCompletedPEDASSESS.cfm?

LajuanTaylor

@TYNET USA - After adding a new row you can find an ID of the last inserted record using syntax like the following in your query
SELECT MAX(insertid) AS LastID FROM [YourTable]

The SQL MAX function can be used with numeric, character, uniqueidentifier, and datetime columns, but not with bit columns. Aggregate functions and subqueries are not permitted.

Walter USA

ASKER

i really don't want to go that MAX route, it's not safe and caused more harm in past.

LajuanTaylor

@TYNET USA - What about using the ROW_NUMBER Function As An Alternate To The MAX Function. The ROW_NUMBER Function will return a row number for each record in your result set.
https://msdn.microsoft.com/en-us/library/ms186734(v=sql.110).aspx

I was thinking that you could use the last insertid where you have the highest row number...

_agx_

Yeah, the problem with SELECT MAX(ID) is that it isn't thread safe.

Without knowing anything about the app, usually the object you're passing into create() would have methods for setting/getting the id. Something like setID() /getID(). Inside create() you use cfquery's "result" attribute to get the new ID (basically the same thing as using SCOPE_IDENTITY(), etc...). Then apply it to the object by invoking it's setID() method:

Edit: Add missing VAR scope

       
        ....
       <!--- For CF9+, LOCAL is preferred over VAR --->
        <cfset var qCreate= "">
        <cfset var qResult = "">
        ...
      <cfquery name="qCreate" datasource="#variables.dsn#" result="qResult">
            INSERT INTO pediatricAssessments (patientsref)
           VALUES 
           ( 
               <cfqueryparam value="#arguments.socprocess.getpatientsref()#" 
                       CFSQLType="cf_sql_integer" 
                       null="#not len(arguments.socprocess.getpatientsref())#" /> 
          )
      </cfquery>

      <!--- Save new ID to the object --->
      <!--- CF9 or earlier use IDENTITYCOL --->
      <cfset arguments.socprocess.setID( qResult.GeneratedKey )>

Open in new window

Now once you exit the function, you can retrieve the ID whenever you need by invoking the object's getID() method. That's the approach I use.

<cfset theNewIDValue = pedna.getID()>

Another (less typical) option is to modify the function to return an ID, rather than true/false.

<cffunction name="create" access="public" output="false" returntype="numeric">
       ....
       <cfset var qResult = "">
      ...
      <cfquery name="qCreate" datasource="#variables.dsn#" result="qResult"> .....</cfquery>

      <!--- return new ID --->
     <cfreturn qResult.generatedKey>
</cffunction>

Open in new window

Walter USA

ASKER

thanks for everyone's input, i guess i got the solution and overall it's working for me.

http://www.aliaspooryorik.com/blog/index.cfm/e/posts.details/post/my-dao-gateway-hybrid-99

_agx_

Interesting. That basically combines approach #2 above (return new record ID instead of true/false)

<cfreturn Val(qryInsert.IdentityInsert) />
</cffunction>

, with some DIY caching via QoQ's. I'm not sure the latter (ie caching) is suitable for all apps. Its effectiveness would depend on the number of records in the table (small to moderate size best) and the type of query filters applied (most effective with a few filters).

Side note, if you do decide to use that code, I'd recommend changing it to use cfquery "result" NOT @@IDENTITY. Granted, "result" may not have existed back when the article was written in 2008.

Walter USA

ASKER

correct, returning value has solved the problem instead of boolean. i'm using scope_identity(), just an fyi. and on .cfm page using: <cfset newInsertedVal="#socprocessDAO.create(pedna)#" /> to pass the value of insertedid into my include file.

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Walter USA

ASKER

do appreciate the inputs and comments.