CFAJAXPROXY / JavaScript: get @@IDENTITY ?

[WolfShade]

Hello, everyone.

(This is back on, now.)

Been giving this one a lot of thought, and just don't know enough about using CFAJAXPROXY to figure it out.

Another  developer has created a CFC that has many, many functions in it.  I'm  using CFAJAXPROXY and JavaScript to access one or two of the functions.

One  of the functions will add a row of data to a table with just a few of  the columns populated with data (the rest are 'allow null').

What  I'm trying to do is get the page to refresh with that new record  populating some of the form fields.  The data that populates is chosen  by a CFSELECT tag with an onChange event.

Can anyone think of a  way, via JavaScript, to get the @@IDENTITY of the data that was just  entered into the table?  If it were pure CF, I'd just put another query  immediately after the insert query, and surround both of them with a  CFTRANSACTION tag.  But I don't think JavaScript has an equivalent.

Thanks,

^_^

[recursivecodes]

cfajaxproxy is just a - proxy - that calls the server side component via Ajax so if you need to return the ID of the newly created record you can just return it from the component and the ajaxproxy should get the value.  You might also want to look at getting the generated key/id via cfquery's result attribute instead of using @@Identity.

[WolfShade]

cfsilence wrote:

cfajaxproxy is just a - proxy - that calls the server side component via Ajax so if you need to return the ID of the newly created record you can just return it from the component and the ajaxproxy should get the value.  You might also want to look at getting the generated key/id via cfquery's result attribute instead of using @@Identity.

Unfortunately, I don't think modifying the method is an option; it's currently returning a string that is the query generated to insert the data into the table.

Is there a way to access the generated key/id from the query that is being run by the method?

Thanks,

^_^

[ilssac]

WolfShade wrote:

Is there a way to access the generated key/id from the query that is being run by the method?

From the ColdFusion method on the server, YES.

From the JavaScript function on the client, NO!.

All it knows is that it has a string, it has no knowledge of or direct connection to the database the string happened to come from.

If you CAN NOT modify the existing function, can you create a new function, maybe in a new component, that wraps the existing function, adds the required functionally to retrieve the desired data, and have the JavaScript interact with THAT new, wrapper function?

[WolfShade]

Looks like I'm not being left with a lot of options. 

I'll copy the functionality of the method and place it into my own component.

Once done, instead of returning the sql statement, how do I acquire the generated key/id?  Would it be a part of the #queryName# object?

Thanks,

^_^

[recursivecodes]

http://tinyurl.com/3cfvs3a

[Owainnorth]

Harsh, but you know what to expect when you get TinyUrl'd having asked a silly question

[Adam Cameron.]

That is brilliant.

--

Adam

[WolfShade]

It doesn't matter, anyway.  Although the RESULT struct is being returned, it does not include the auto-generated id/key; and they won't set up CF Admin to do so (also, we are still using CF9, not CF9.0.1 - don't know why they won't upgrade, either.)

Spoke with the lead developer and he modded the CFC, himself, to return "SCOPE_IDENTITY()", so at least I can get it, that way.

Thanks, everyone.

^_^

[WolfShade]

Okay.. the mod works for INSERTING a new record.

But if the record already exists, then the CFC method automatically switches to an UPDATE by calling another method within the same component.

If the WHERE conditions are being dynamically created (ergo, I don't know what the ID will be, if it's even used in the conditions), how can I get the identity ID of the record just UPDATED?

Thanks,

^_^

[recursivecodes]

If you're updating a record and you don't know the ID, well, I'm afraid you've got bigger problems then we can help you with.

[WolfShade]

So, if a table has unique email addresses and information is updated based upon the email address, there is no way to get the value of the IDENTITY column?

^_^

[ilssac]

Other then SELECT identity FROM table WHERE email = uniqueEmail?

[ilssac]

WolfShade wrote:

If it were pure CF, I'd just put another query  immediately after the insert query, and surround both of them with a  CFTRANSACTION tag.

Well, there are probably better ways then the second query and <cftransaction...> combination.  But this is just what you need to do.  It is ColdFusion's job to get needed data from the database and return it to the client.  All <cfajaxproxy..> and other AJAX techniques allow is for that to happen behind the scenes of the browser user interface.

To try and simplify this, the COMPONENT FUNCTION you are calling is what needs to return the required data such a record ID.