12 Replies Latest reply: Nov 8, 2010 7:21 AM by Dave Watts RSS

    Inserting latest USERID into multiple tables

    Flashdakota Community Member

      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

        • 1. Re: Inserting latest USERID into multiple tables
          existdissolve Community Member

          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.

          • 2. Re: Inserting latest USERID into multiple tables
            Flashdakota Community Member

            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

            • 3. Re: Inserting latest USERID into multiple tables
              existdissolve Community Member

              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

              • 4. Re: Inserting latest USERID into multiple tables
                ilssac Community Member

                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.

                • 5. Re: Inserting latest USERID into multiple tables
                  Dave Watts CommunityMVP

                  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

                  • 6. Re: Inserting latest USERID into multiple tables
                    Flashdakota Community Member

                    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

                    • 7. Re: Inserting latest USERID into multiple tables
                      existdissolve Community Member

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

                       

                      Good luck!

                      • 8. Re: Inserting latest USERID into multiple tables
                        Flashdakota Community Member

                        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

                        • 9. Re: Inserting latest USERID into multiple tables
                          existdissolve Community Member

                          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?

                          • 10. Re: Inserting latest USERID into multiple tables
                            Flashdakota Community Member

                            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

                            • 11. Re: Inserting latest USERID into multiple tables
                              existdissolve Community Member

                              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

                              • 12. Re: Inserting latest USERID into multiple tables
                                Dave Watts CommunityMVP

                                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