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.
Walter USAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Try using SCOPE_IDENTITY() instead of IDENT CURRENT
Walter USAAuthor Commented:
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?
LajuanTaylorCommented:
@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.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Walter USAAuthor Commented:
i really don't want to go that MAX route, it's not safe and caused more harm in past.
LajuanTaylorCommented:
@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_Commented:
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 USAAuthor Commented:
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_Commented:
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 USAAuthor Commented:
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.
_agx_Commented:
That's fine, though you really don't need to add  scope_identity() manually. Not unless you're using a really old version of CF.  In CF8+, the "result" attribute of cfquery does that for you already.  

Also, while it'll work either way, technically there's no need for the quotes or pound signs here:

      <cfset newInsertedVal="#socprocessDAO.create(pedna)#" />

Instead just use:

      <cfset newInsertedVal = socprocessDAO.create(pedna) />

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Walter USAAuthor Commented:
do appreciate  the inputs and comments.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.