Skip navigation
Currently Being Moderated

Inserting latest USERID into multiple tables

Nov 4, 2010 3:49 AM

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

 
Replies
  • Currently Being Moderated
    Nov 4, 2010 5:22 AM   in reply to Flashdakota

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

     

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 4, 2010 7:24 AM   in reply to Flashdakota

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 4, 2010 8:01 AM   in reply to existdissolve

    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.

     
    |
    Mark as:
  • Dave Watts
    747 posts
    Mar 11, 2003
    Currently Being Moderated
    Nov 4, 2010 8:12 AM   in reply to Flashdakota

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 4, 2010 8:43 AM   in reply to Flashdakota

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

     

    Good luck!

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 8, 2010 5:48 AM   in reply to Flashdakota

    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#11007 87

     

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

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 8, 2010 6:21 AM   in reply to Flashdakota

    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#115 906

     
    |
    Mark as:
  • Dave Watts
    747 posts
    Mar 11, 2003
    Currently Being Moderated
    Nov 8, 2010 7:21 AM   in reply to Flashdakota

    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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points