Solved

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

Posted on 2007-03-22
5
215 Views
Last Modified: 2013-12-12
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.
0
Comment
Question by:lennagy
  • 2
5 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 18771852
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 = 10.10.10.100> <!--- my dev machine --->
<cfset request.dsn = "devDSN">
<cfelse>
<cfset request.dsn = "liveDSN">
</cfif>



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



0
 

Author Comment

by:lennagy
ID: 18773533
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...
0
 

Author Comment

by:lennagy
ID: 18801561
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.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 21890332
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

How to run any project with ease

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

Join & Write a Comment

Let’s list some of the technologies that enable smooth teleworking. 
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…
Viewers will learn how the fundamental information of how to create a table.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now