psql datasource

[wannab0133]

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]

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">

[wannab0133]

BKBK,

I will try that, but that only overrides the password set up in the datasource.  I am trying to run an sql statement before every query that includes an owner name....SET OWNER='myPassword'  The owner name is not the same as the datasource password.  Also, I don't want the owner name all over the code, or even in one place unless I can hash it.  I appreciate the reply and I will try this and see what happens.

[BKBK]

What brand of SQL/database are you using? How does the query look like in full? If it is a composite query, then the "Validation Query" setting might be a possible solution.

[wannab0133]

Pervasive SQL (PSQL) utilizing an odbc datasource.

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

SET OWNER='myOwnerName';

SELECT * FROM "TRIPS";

</cfquery>

The SET OWNER statement includes the owner name which is essentially a password in PSQL.  I don't want to have to have that statement everywhere in the code.  It needs to run for every sql session.  I would prrefer to have that statement set up in the datasource settings within the coldfusion administrator. 

[BKBK]

Thanks for the explanation. I am curious to know whether my earlier suggestion would work.

Go to the datasource page in the Coldfusion Administrator. Open the settings for the datasource concerned, and navigate to 'Advanced Settings'. Enter the value of Validation Query as SET OWNER='myOwnerName'

[wannab0133]

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:

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

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

     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?

[BKBK · Accepted solution]

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:

<cfset application.setOwnerQuery = "SET OWNER='myOwner';">

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

Later on,

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

     #application.setOwnerQuery#

     SELECT * from "TRIPS"

</cfquery>

I consider this simpler in concept.