Inserting latest USERID into multiple tables

[Flashdakota]

Hello

I have a DB question:

Query 1 Inserts new member info into table1 (MEMBERID is my index)

I then have query 2 that inserts the members interests into table2. I need to keep MEMBERID from QUERY 1 and insert it into table2

Please can someone give me a safe way to do this? Would CFTRANSACTION do the trick?

If so... how would I use it?

Thanks

[existdissolve]

Take a look at the docs for the cfquery tag: http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_17.html#1102316

Under "usage", you'll see a section for using the "result" attribute to return the id of an inserted row.  Based on what you said in your post, this would probably do the trick.  Of course, there are SQL-based ways of doing this too, but I'd start with the cfquery docs.

Example:

<cfquery name="myquery" datasource="mydsn" result="myresult">
     insert into thetable (stuff, things, whatever)
     values (
          <cfqueryparamm value="#stuff#" cfsqltype="cf_sql_varchar">,
          <cfqueryparamm value="#things#" cfsqltype="cf_sql_varchar">,
          <cfqueryparamm value="#whatever#" cfsqltype="cf_sql_varchar">
     )
</cfquery>
<cfquery name="secondquery" datasource="mydsn">
     insert into othertable(id,interest,whatever)
     values (
          <cfqueryparamm value="#myresult.identitycol#" cfsqltype="cf_sql_integer">, <!---sql server--->
          <cfqueryparamm value="#interest#" cfsqltype="cf_sql_varchar">,
          <cfqueryparamm value="#whatever#" cfsqltype="cf_sql_varchar">
     )
</cfquery>

NOTE: The result_name.identitycol is database specific.  So whether you're using MySQL, SQL Server, Oracle, etc., the syntax will be a bit different.  Be sure to check the docs to use the one that will work for your environment.

Re: adding the transaction, that wouldn't be a bad idea, although in and of itself it won't accomplish the new id inserting into the second table.  All the transaction will do is allow you set isolation levels on the db transactions and rollback the transactions if the whole process doesn't complete properly.

[Flashdakota]

Thanks... makes total sense... BUT...

Would the result name in the query not need to be unique?

If there were 10 simultaneous inserts at the exact same time, wouldn’t the result need to be unique?

Would CFTRANSACTION do the trick?

Look forward to your reply.

Delon Cheketri | Mobile: +27 83 406 4890 | Tel: +27 (0) 11 447 1777

www.fusebox.co.za | delon@fusebox.co.za

[existdissolve]

I could be wrong, but I don't think that the result name from the query needs to be unique.  As far as I understand it, each hit of the code would be a separate page request, so access to the query result variable would be limited to that same request, not other requests to the same page (since the query result doesn't live beyond the request).

Morever, cftransaction is used for ensuring that multiple queries executed in succession complete as a group, or are rolled back within the one transaction if something goes wrong. If you were looking to lock access to a shared data structure, you'd want to look at cflock.  However, for this example, I don't think cflock is necessary or appropriate.

But again, I could be wrong.  Let's see if anyone else chimes in to correct me

[ilssac]

existdissolve wrote:

As far as I understand it, each hit of the code would be a separate page request, so access to the query result variable would be limited to that same request, not other requests to the same page (since the query result doesn't live beyond the request).

For the most part, the default behavior would be for the query variable to be part of the "variables" scope which is local to the request and there would be no cross over with other requests.  But there is nothing preventing queries from being in other scopes, such as session and|or application, where multiple requests WOULD be sharing the same results.  But the programmer would have to have done this on purpose and one would hope would understand the ramifications of such choices and handles them appropriately.

existdissolve wrote:

Morever, cftransaction is used for ensuring that multiple queries executed in succession complete as a group, or are rolled back within the one transaction if something goes wrong. If you were looking to lock access to a shared data structure, you'd want to look at cflock.  However, for this example, I don't think cflock is necessary or appropriate.

Well <cftransaction...> CAN do more then that.  It can also be used to provide serialization similar to <cflock...> at the DATABASE level.  I.E., depending on what parameters are provided, <cftransaction...> can make it so the database will not process any other updates and|or reads from other requests, until this request is finished.  This is seldom necessary or desirable but it is available when it is.

[Flashdakota]

Hi existdissolve

Thanks for the reply.

So in a nutshell, I can use the RESULT.

Do I need to make the result unique or am I being slightly over the top by doing that?

Thank you again

Delon Cheketri | Mobile: +27 83 406 4890 | Tel: +27 (0) 11 447 1777

www.fusebox.co.za | delon@fusebox.co.za

[existdissolve]

Check out Dave's response above.  He's got the definitive answer.

Good luck!

[Flashdakota]

Hi

Since my post, another developer advised that I surround all the queries inside a CFLOCK.

It seems to make sense.

Please can I have your opinion.

Thanks

Delon Cheketri | Mobile: +27 83 406 4890 | Tel: +27 (0) 11 447 1777

www.fusebox.co.za | delon@fusebox.co.za

[existdissolve]

Do the queries match up to the guidelines outlined in the docs for cflock? http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_j-l_05.html#1100787

What were the reasons the other developer gave for using cflock?

[Flashdakota]

He simply recommended it when I posed the question.

Here is what I did...

Does that make sense?

I spoke to him about cfquery result but felt that cflock would be a bit more on the safe side.

Any opinion is really helpful, please give me yours on his method.

Thanks again

Delon Cheketri | Mobile: +27 83 406 4890 | Tel: +27 (0) 11 447 1777

www.fusebox.co.za | delon@fusebox.co.za

[existdissolve]

Doesn't look like the code you posted came through.

Regarding locking, though, you should check out the purpose for using it in the docs.  Here's the developer's guide discussion: http://livedocs.adobe.com/coldfusion/8/htmldocs/sharedVars_01.html#115906

[Dave Watts]

No, CFLOCK is not appropriate here, although it may work. CFLOCK allows you to single-thread a block of CF code. You want to control concurrency at the database in this case, not within CF. Use the result as suggested earlier.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Read this before you post:

http://forums.adobe.com/thread/607238

[Dave Watts]

As long as you're using a page-specific scope for your query variables, they'll be thread-safe. You could use CFTRANSACTION, but the point of getting the result directly from the insert is so you don't have to use CFTRANSACTION.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Read this before you post:

http://forums.adobe.com/thread/607238