psql datasource

Question

I have set up a psql datasource(odbc) in cf10. Everything works fine, but some tables require a password. In order to provide the password, for each databse connection, I need to make the following SQL statement: SET OWNER='myPassword';

I do not want that statement before every query. I could use cfinclude and have the password in one file. But, I would rather have it as part of the datasource settings. Is there anyway to do this? Can I set up the above command to run in the datasource settings?

BKBK · Accepted Answer

I tried that, and it did not work unfortunately. The datasource did test ok after adding the sql string in the validation query box, but when I ran a query, it gave me an owner name not specified error. I also tried the specifying username and password in the cfquery tag. That didnt work either. I can do the following:

SELECT * from "TRIPS"

</cfquery>

The above works fine. The contents of setOwner.cfm is: "SET OWNER='myOwner';"

That way, I only have one file with the ownername. Is this an acceptable workaround?

wannab0133 wrote:

<cfquery name="tripList" DATASOURCE="myDatasource">

<cfinclude template="shared/setOwner.cfm">

SELECT * from "TRIPS"

</cfquery>

That is, naturally, a correct and good example of reuse. However, my preferred solution would be to treat the query string as an application constant. That is, I would place the following in the onApplicationStart method in Application.cfc:

(If the variable depends on the current user, then store it in the session scope instead, in the method onSessionStart.)

Later on,

#application.setOwnerQuery#

SELECT * from "TRIPS"

</cfquery>

I consider this simpler in concept.

BKBK · Answer

wannab0133 wrote:some tables require a password... Can I set up the above command to run in the datasource settings?Yes, using the cfquery tag that sends a query to the table, like this<cfquery username="dbUser" password="dbPassword">

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded