[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2007-03-22
5
Medium Priority
?
230 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Viewers will learn how to use the Hootsuite Dashboard.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

656 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