Hello, Libby H,
First of all, my condolences on your inheritance. I can only imagine the quagmire you are dealing with.
You should use trusted connections, so the username and password are built in to the queries, without needing to code them into any cfm or cfc files. However, if that is not possible, then the next thing would be to put them in the application scope (which it appears from your last lines of code that is your intention.) I should point out that your last lines of code, each is over-writing the previous one, not appending a struct or object or array. So, first Application.CachedQuery is being assigned the name variable in the request scope, then it's being changed to the username value of the request scope, then the password value of the request scope. Which, BTW, seems like a lot of processing for nothing, since you can set the application variables in your passwords.cfm module, and those variables will be made available to the whole application.
Application variables are like session variables, except they never expire.
Looking at your code, I'm going to assume that the original coder is the type who likes complexity for the sake of being complex. Our old lead developer is like that, and I am SOOOOOOOOO glad he isn't here, anymore. I've been slowly correcting his code so that we get the same results in one or two steps that he created 9 or 10 steps to achieve. Jerk.
In any case.. there is a chance that I misunderstand your question. But it seems to me that you can set the application.name, application.username and application.password (you shouldn't use reserved words for variable names) inside your passwords.cfm file, and it will be set for as long as the server is running. Then you can use those for the credentials in the query.
Or, you can just use trusted connections and never worry about username and password.
WolfShade - thanks for your response.
After posting my question - I continued to scour the internet for examples and came up with some revelations to my 'opportunity'. As I mentioned above, I do not have an application.cfc, (but I misspoke) I do have an application.cfm. In my application.cfm is the cfapplication and cferror statements, code for our general login scripts, and of course, the cfinclude for the above mentioned password.cfm. The password.cfm module is a basic cfscript with a case statement i.e.)
dbuser = "MYYYYY";
dbpw = "xxxxxx";
dbuser = "MYYYYY";
dbpw = "xxxxxx";
} //end switch
I was told not to make these client, session, variable or application variables per the Shared Hosting Environments rules.
So - as I look though additional documentation - trying to use the this as in this.datasource won't work because although the datasource information is included in the application.cfm, it isn't set there. The whole idea with including the password.cfm is the division of developer (me) and support (me??). Because of SOX, I'm not supposed to have access to production passwords (or test either I guess), so someone else has the passwords. Everything needs to be coded as such.
Thanks for pointing out the issues with the Application.CachedQuery - it made perfect sense as soon as I read your response.
Libby H wrote:
Libby H wrote:
but we need to set the datasource, username and password into a passwords.cfm module in a restricted directory and therefore pass those parameters to each and every query.
<cfquery name="payment_all" datasource ="#this.name#" username ="#this.username#" password ="#this.password#">
First off, the reason why you are getting the error message. The this scope stands for public variables defined within the component. None of the three is defined there, hence the error.
As WolfShade suggests, you could define the variables in passwords.cfm as application.datasource=..., application.dbuser=... and application.dbpw=.... Then modify your cfquery tag to
<cfquery name="payment_all" datasource ="#application.datasource#" username ="#application.dbuser#" password ="#application.dbpw#">
Alternatively, if, as you say, you are unable to use the application scope, then you could use the request scope and pass arguments as follows.
Define the variables in passwords.cfm as request.datasource=..., request.dbuser=... and request.dbpw=.... Then modify your cfgrid and cfcomponent to
<cfgrid format="html" name="paymentcodegrid" pagesize=10 sort=true
<cfgridcolumn name="Payment_Type" header="Payment Code" width="95" />
<cfgridcolumn name="Payment_Desc" header="Payment Description" width="200" />
<cffunction name="getData" access="remote" output="false">
<cfquery name="payment_all" datasource ="#arguments.dsn#" username ="#arguments.user#" password ="#arguments.pw#">
SELECT payment_type, Payment_Desc FROM paytype
<cfif gridsortcolumn neq "" or gridsortdirection neq "">
order by #gridsortcolumn# #gridsortdirection#
<cfreturn QueryConvertForGrid(payment_all, page, pageSize)>
Thanks - I will see if that will be do-able with our share hosting group.
And thanks for showing that you can pass additional parameters to the cfgrid - something like this you just don't find trolling the internet.
I need to continue searching for my course book from years ago that I took on cfc's . . .
I appreciate everyone's attention and help.