Skip to main content
L
Libby_H
Inspiring
February 7, 2017
Answered

this.datasource - how to pass dsn, username and password to cfc?

  • February 7, 2017
  • 1 reply
  • 1817 views

I am currently converting/upgrading a CF9.0.2 application (Windows 2008R2/IIS 7, SQL Server 2008R2) environment to an internal company Share Hosting Environment that is CF11.  My CF9 environment is very basic; it began in 2000 in CF 4.5 and new development was done in the newest version that was available - so nothing has been rewritten to the latest version.

Our SHE sets up the DATASOURCE for us - 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.  I cannot put this information an application.cfm or application.cfc.

I now have to bring my CF9 code to the SHE requirements, and one of the first I need to tackle is updating my CFGRIDS to HTML.  I know the arguments of CFGRID - I don't need to be lectured about them please.  All of the grids have sorts and 90% are selectmode=row (I have spent to much time trying to convert them to regular tables). 

I have embedded the CFGRID into a CFFORM:

<cfform>

    <cfgrid format="html" name="paymentcodegrid" pagesize=10 sort=true

            bind="cfc:payment.getData({cfgridpage},{cfgridpagesize},

            {cfgridsortcolumn},{cfgridsortdirection})">

            <cfgridcolumn name="Payment_Type" header="Payment Code" width="95" />

            <cfgridcolumn name="Payment_Desc" header="Payment Description" width="200" />

    </cfgrid>

</cfform>

I have created a payment.cfc:

<cfcomponent>

    <cffunction name="getData" access="remote" output="false">

        <cfargument name="page">

        <cfargument name="pageSize">

        <cfargument name="gridsortcolumn">

        <cfargument name="gridsortdirection">

        <cfquery name="payment_all" datasource ="#this.name#" username ="#this.username#" password ="#this.password#">

   SELECT payment_type, Payment_Desc FROM paytype

            <cfif gridsortcolumn neq "" or gridsortdirection neq "">

             order by #gridsortcolumn# #gridsortdirection#

            </cfif>

        </cfquery>

        <cfreturn QueryConvertForGrid(payment_all, page, pageSize)>

    </cffunction>

</cfcomponent>

I have create a CachedQuery.cfc to pull the DataSources information:

<cfcomponent

    <cffunction name="init" access="public" returntype="CachedQuery" output="no">

     <cfargument name="datasources" type="string" required="yes">

     <cfset variables.datasources = arguments.datasources>

     <cfreturn this>

</cffunction>

</cfcomponent>

In my login.cfm, I have the following:

  <cfset Application.CachedQuery = CreateObject("component","CachedQuery").init(datasource=request.name)>

  <cfset Application.CachedQuery = CreateObject("component","CachedQuery").init(datasource=request.username)>

  <cfset Application.CachedQuery = CreateObject("component","CachedQuery").init(datasource=request.password)>

When I execute the page to open up the grid, I receive an error that states 'Element NAME is undefined in THIS'.  I took a class on CFC years ago - but I didn't need to pass the dsn name at that time - so this is just killing me! I have been trying different combinations of things for the last 5 days, that I am besides myself!  I am sure it is something quite simple - can anyone assist?

Libby H

    This topic has been closed for replies.
    Correct answer BKBK

    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.

    ...

    ...

    <cfcomponent>

    <cfquery name="payment_all" datasource ="#this.name#" username ="#this.username#" password ="#this.password#">

    </cfquery>

    </cfcomponent>

    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

    <cfform>

        <cfgrid format="html" name="paymentcodegrid" pagesize=10 sort=true

                bind="cfc:payment.getData({cfgridpage},{cfgridpagesize},

                {cfgridsortcolumn},{cfgridsortdirection},'#request.datasource#','#request.dbuser#','#request.dbpw#')">

                <cfgridcolumn name="Payment_Type" header="Payment Code" width="95" />

                <cfgridcolumn name="Payment_Desc" header="Payment Description" width="200" />

        </cfgrid>

    </cfform>

    <cfcomponent>

        <cffunction name="getData" access="remote" output="false">

            <cfargument name="page">

            <cfargument name="pageSize">

            <cfargument name="gridsortcolumn">

            <cfargument name="gridsortdirection">

            <cfargument name="dsn">

            <cfargument name="user">

            <cfargument name="pw">

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

                </cfif>

            </cfquery>

            <cfreturn QueryConvertForGrid(payment_all, page, pageSize)>

        </cffunction>

    </cfcomponent>

    1 reply

    WolfShade
    WolfShade
    Legend
    February 7, 2017

    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.

    HTH,

    ^_^

    L
    Libby_HAuthor
    Inspiring
    February 7, 2017

    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.)

    <cfscript>

        switch(Variables.thisServerType)

        {

          case "dev":

            {
    DataSource="treasury";     
    dbuser = "MYYYYY";
    dbpw = "xxxxxx";
            break;

            }

            case "stage":

            {
    DataSource="treasury";
    dbuser = "MYYYYY";
    dbpw = "xxxxxx"; 
    break;

            }

        } //end switch

    </cfscript>

    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.

    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    February 8, 2017

    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.

    ...

    ...

    <cfcomponent>

    <cfquery name="payment_all" datasource ="#this.name#" username ="#this.username#" password ="#this.password#">

    </cfquery>

    </cfcomponent>

    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

    <cfform>

        <cfgrid format="html" name="paymentcodegrid" pagesize=10 sort=true

                bind="cfc:payment.getData({cfgridpage},{cfgridpagesize},

                {cfgridsortcolumn},{cfgridsortdirection},'#request.datasource#','#request.dbuser#','#request.dbpw#')">

                <cfgridcolumn name="Payment_Type" header="Payment Code" width="95" />

                <cfgridcolumn name="Payment_Desc" header="Payment Description" width="200" />

        </cfgrid>

    </cfform>

    <cfcomponent>

        <cffunction name="getData" access="remote" output="false">

            <cfargument name="page">

            <cfargument name="pageSize">

            <cfargument name="gridsortcolumn">

            <cfargument name="gridsortdirection">

            <cfargument name="dsn">

            <cfargument name="user">

            <cfargument name="pw">

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

                </cfif>

            </cfquery>

            <cfreturn QueryConvertForGrid(payment_all, page, pageSize)>

        </cffunction>

    </cfcomponent>

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices