Title: Message
Oh nm. Even though it shows up in the debugging output as having ran apprently the tables aren't updated.
 
Adaryl "Did you reboot?" Wakefield
Aviator by passion
Programmer by sheer force of will
----- Original Message -----
Sent: Thursday, April 08, 2004 10:33 AM
Subject: Re: [KCFusion] system migration

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 -----
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 -----
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 -----
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

Reply via email to