Solved

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

Posted on 2007-03-22
5
219 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 57
Oracle DB monitor SW 21 48
Tsql query 6 20
Moving files from folder to folder 8 23
Let’s list some of the technologies that enable smooth teleworking. 
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

785 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