moving data between Cfquery tags ( SELECT from first INSERT to next cfquery tag )

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.

There are two environments that will be involved in a given transaction, out of three ( dev, test, live ).

First env contains the repository. Therefore, if you open the app from the /dev/ path, then the application will consider itself in development and store archive data in the development copy of the database. Likewise, if the /live/ path is used, then the archive data will be stored in the live copy of the database.

Second env, regardless of which context the app is running, described above, it needs to run DML ( SELECT,INSERT,UPDATE,DELETE ) statements with one of the three environments. This becomes the user choice in the app. So if a user chooses to backup metadata in the live environment, then that user can SELECT data from there, regardless which 'first env' is being used.

So I would like to know how to move data between cfquery tags, each with a different datasource, and doing an insert or update on the second cfquery tag.

I've worked out a solution that works exactly as I need it to. The key to the solution is a combination of datasources and database links. While I can hide datasources inside the JRun panel, which is exclusive of the CFMX admin panel, I can't hide the database links. However, I found that by configuring them as pass-through authentication, they become unusable if the SQL user does not have permissions in the linked-to database.

The flow works as thus: Hidden datasource is using a SQL login that is defined in all environments ( database instances ) The CFMX code permits the OPs users to choose where to pull data, it only permits updates in the local environment, remote environments are read-only. The datasources have permission to use all the dblinks, and the tool works seamlessly for the user.

Featured Post

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…