ChickenDinner
asked on
Overriding the datasource username in a CFQUERY tag
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.