Re: [KCFusion] system migration

[Adaryl Wakefield]

Title: Message

That worked. I'll have another question a little later but for now im kind of confused about cftransaction. I thought it was used to make sure every statement inside the transaction executed properly or none of them would execute. I put that in there so im not needlessly messing up tables if there is an error but it does not seem to be working that way. One of the tables in the tables table was misspelled so the whole thing stopped but every statement before that got executed. Am I using transaction wrong?     Adaryl "Did you reboot?" Wakefield Aviator by passion Programmer by sheer force of will ----- Original Message ----- From: Ellis, Randy To: [EMAIL PROTECTED] Sent: Thursday, April 08, 2004 10:09 AM Subject: RE: [KCFusion] system migration Here is another CFLOOP example.  See if this works.   <cfquery name="getTables" datasource="portfolio"> SELECT thisTable FROM tables </cfquery>   <cfquery name="getIDs" datasource="portfolio"> SELECT newPK, oldPK FROM oldAndNewIDs </cfquery>    <cftransaction action="">   <cfloop from=1 to="#getTables.RecordCount#" index="Index1">   <cfloop from=1 to="#getIDs.RecordCount#" index="Index2">   <cfquery name="update" datasource="portfolio">   UPDATE #getTables.thisTable[Index1]#   SET studentID = #getIDs.newPK[Index2]#   WHERE studentID = #getIDs.oldPK[Index2]#   <cfquery> </cfloop>   </cfloop> </cftransaction>   Randy Ellis City of Overland Park Senior Programmer Analyst   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Adaryl Wakefield Sent: Thursday, April 08, 2004 9:49 AM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] system migration There are 20 tables and 76 sets of IDs that need to change. 20 * 76 = 1520 the extra 1 a result of initiating the couting var to 1. So I think its running over the tables properly so it may be an output issue for #getTables.thisTable#   Adaryl "Did you reboot?" Wakefield Aviator by passion Programmer by sheer force of will ----- Original Message ----- From: Adaryl Wakefield To: [EMAIL PROTECTED] Sent: Thursday, April 08, 2004 9:40 AM Subject: Re: [KCFusion] system migration Tried loop yesterday but I think i had it in the inner loop. What you have runs, the problem is it does not break out of the inner loop. In other words it just get 1521 iterations of the same statement. It won't move from the first table.   Adaryl "Did you reboot?" Wakefield Aviator by passion Programmer by sheer force of will ----- Original Message ----- From: Ellis, Randy To: [EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 3:35 PM Subject: RE: [KCFusion] system migration It doesn't look like you are using the output query "group" attribute correctly.  Try using cfloop as Ryan mentioned.  Something like this:   <cfquery name="getTables" datasource="portfolio"> SELECT thisTable FROM tables </cfquery>   <cfquery name="getIDs" datasource="portfolio"> SELECT newPK, oldPK FROM oldAndNewIDs </cfquery>    <cftransaction action=""> <cfloop query="getTables">    <cfoutput query="getIDs">   <cfquery name="update" datasource="portfolio">   UPDATE #getTables.thisTable#   SET studentID = #getIDs.newPK#   WHERE studentID = #getIDs.oldPK#   <cfquery>  </cfoutput> </cfloop> </cftransaction>   Randy Ellis City of Overland Park Senior Programmer Analyst -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Adaryl Wakefield Sent: Wednesday, April 07, 2004 2:54 PM To: [EMAIL PROTECTED] Subject: [KCFusion] system migration I have to update the primary keys of every tuple in 20 different tables. I was thinking I could get this done by having a table of all the tables that need up dating and having a table of the old primary keys with the new values. I'd loop over the tables table then nested in that loop id loop over the table with the new values. So far no luck.   <cfquery name="getIDs" datasource="portfolio"> SELECT newPK, oldPK FROM oldAndNewIDs </cfquery>    <cfquery name="getTables" datasource="portfolio"> SELECT * FROM tables </cfquery>   <cftransaction action=""> <cfoutput query="getTables" group="id">  <cfoutput query="getIDs">   <cfquery name="update" datasource="portfolio">   UPDATE #getTables.thisTable#   SET studentID = #getIDs.newPK   WHERE studentID = #getIDs.oldPK <cfquery>  </cfoutput> </cfoutput> </cftransaction>   Im kind of at a loss on how to group this statement right.   Adaryl "Did you reboot?" Wakefield Aviator by passion Programmer by sheer force of will