moving data between Cfquery tags ( SELECT from first INSERT to next cfquery tag )
Posted on 2007-03-22
Architecture dilemma. I'm building an operations application using CFMX7. I want to permit Ops to manage data in three environments, dev, test and live in one application. Of course, I still have to develop, test and deploy these apps; therefore, am trying to put in place functionality that enables the app to work from it's own environment, while grabbing MetaData from other Environments ( or updating in the case of a restore ). As a secondary requirement, I want to limit what JDBC or database links I setup, because they pose a liability when developers see them and consider exploiting what they find.
To support the environment the app is running in, I've coded a path-based method to determine where the app is actually running. So a /dev/ path means that code is running in the context of development and so on for /test/ and /live/.
To support the environment the app will run against ( to update or backup ), I've created JDBC links systemDev, systemTest, and systemLive in JRun, hidden from the CFAdministrator panels.
However, I run into a problem when I need to move data between CFQUERY tags. For example, I want to select from the chosen environment and store in the local environment. Or select from the local and update the chosen.
I must be missing something patently obvious; however, am at a standstill. If I query the chosen environment via JDBC in a query, I can't INSERT into my local environment in the same query. I can't INSERT it via a dbtype="query" which can see the other query but can't insert. I have no intention of creating an INSERT statement for each record in each table, although that is looking like the only viable solution.
I've considering setting up a dblink ( sql server 2k5 ), in each environment, pointing to each of the environments; however, the development team/consultants can 'see' these links, again providing fodder for experimentation and liability.