Solved

Overriding the datasource username in a CFQUERY tag

Posted on 2011-03-01
1
587 Views
Last Modified: 2013-12-24
I have a circumstance where a customer has a single Oracle database containing multiple schemas.  Each schema has the same table set and is differentiated only by the schema name.  Different usernames are associated with each schema, but the password is the same for every schema.  They have literally hundreds of these schemas.

They want to query these schemas using ColdFusion 8.  Thus far they have simply defined multiple datasources in the CF server console.  Each datasource is IDENTICAL to all the others with the exception of the datasource name and the username: same Oracle SID, same server, same port, same password.  

Rather than create hundreds of datasources, I thought we could simply create one datasource in the CF server console and then override the username in the CFQUERY tag.  For example:

<CFQUERY name="GetREQ" datasource="BigSource" username="user1">

I was hoping this would access the datasource and use all the server-level attributes except username, which in this case, would be user1 instead of whatever is stored with the datasource definition itself.  Unfortunately, testing seems to indicate that no matter what username I put in the username attribute of the CFQUERY tag, ColdFusion ALWAYS uses the username defined in the datasource itself.  This is evident due to the data returned by the query, which readily shows which schema is actually being accessed.

So, will this not work?  MUST the password also be specified in the CFQUERY tag to do what I am attempting?
0
Comment
Question by:ChickenDinner
[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
1 Comment
 
LVL 52

Accepted Solution

by:
_agx_ earned 250 total points
ID: 35010443
So, will this not work?  MUST the password also be specified in the CFQUERY tag to do what I am attempting?

AFAIK, yes.  Both are required to override the dsn settings. That's how it works w/MS SQL datasources.
0

Featured Post

Optimum High-Definition Video Viewing and Control

The ATEN VM0404HA 4x4 4K HDMI Matrix Switch supports 4K resolutions of UHD (3840 x 2160) and DCI (4096 x 2160) with refresh rates of 30 Hz (4:4:4) and 60 Hz (4:2:0). It is ideal for applications where the routing of 4K digital signals is required.

Question has a verified solution.

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

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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