Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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.
  • 2
1 Solution
not sure I fully understand what you are trying to do but I handle different DSNs like so

in application.cfm

<cfif CGI.REMOTE_ADDR => <!--- my dev machine --->
<cfset request.dsn = "devDSN">
<cfset request.dsn = "liveDSN">

in queries

<cfquery name="blah" datasource="#request.dsn#>

In your set up you could just eval the path and use a cfswitch to set your request.dsn in application.cfm

lennagyAuthor Commented:
Unfortunately, it's another layer more complicated than that.

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 was hoping the java classes would support a datastream or such...
lennagyAuthor Commented:
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.

Thanks for the forum.
PAQed with points refunded (500)

EE Admin

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now