Skip to main content
Participant
December 17, 2011
Question

dsn in user defined functions

  • December 17, 2011
  • 3 replies
  • 1568 views

hey, I'm a newbie in coldfusion. Have been stuck with this problem for a week now... Hope somebody could help...

I implemented a related select on a form:

<table>

        <tr>

       <td>Select Level:</td>

       <td><cfselect name="level_id" id="level_id"

           bind="cfc:myfuncs.getLevel()"

           bindonload="true" value="level_id" display="level_name"  /></td>

       </tr>

       

       <tr>

       <td>Select Class:</td>

       <td><cfselect name="class_id"

              bind="cfc:myfuncs.getClasses({level_id})" value="class_id" display="class_id" /></td>

       </tr>

</table>

Meanwhile myfuncs.cfc looks like this:

<cfcomponent output="false">

   <cfset THIS.dsn="#Request.DSN#">

   <cffunction name="getLevel" access="remote" returnType="array">
      <!--- Define variables --->
      <cfset var data="">
      <cfset var result=ArrayNew(2)>
      <cfset var i=0>

      <!--- Get data --->
      <cfquery name="data" datasource="#THIS.dsn#">
      <cfquery name="data" datasource="#THIS.dsn#">
              SELECT level_id, level_name
               FROM tbLevel
                ORDER BY level_id
      </cfquery>

      <!--- Convert results to array --->
      <cfloop index="i" from="1" to="#data.RecordCount#">
         <cfset result[1]=data.level_id>
         <cfset result[2]=data.level_name>
      </cfloop>

      <!--- And return it --->
      <cfreturn result>
   </cffunction>

 

   

<cffunction name="getClasses" access="remote" returnType="array">
      <cfargument name="level_id" type="numeric" required="true">

      <!--- Define variables --->
      <cfset var data="">
      <cfset var result=ArrayNew(2)>
      <cfset var i=0>

      <!--- Get data --->
      <cfquery name="data" datasource="#THIS.dsn#">
      SELECT class_id, class_day, class_time, class_size, start_dt, end_dt
      FROM tbClass
      WHERE level_id = #ARGUMENTS.level_id#
      ORDER BY class_day
      </cfquery>
 
      <!--- Convert results to array --->
      <cfloop index="i" from="1" to="#data.RecordCount#">
   
         <cfset result[1]=data.class_id>
         <cfset result[2]=data.class_day&" "&data.class_time>
         <cfset result[3]=data.class_size>
         <cfset result[4]=data.start_dt>
         <cfset result[5]=data.end_dt>  
      </cfloop>

      <!--- And return it --->
      <cfreturn result>
   </cffunction>

</cfcomponent>

My Application.cfm file contains:

<cfparam name="Request.DSN" default="mydsn">

<cfparam name="Request.username" default="myuser">

<cfparam name="Request.password" default="mypass">

But my drop-list is just empty on the form. No errors. Coldfusion version is 8 and the server is a multi-home server.

Could any expert shed some light on this?

Thanks!

-madison

This topic has been closed for replies.

3 replies

Participant
December 18, 2011

When I tried to implement the exactly same thing on Coldfusion 9 (the free download developer's edition), my code works fine.

It looks like the myfuncs.cfc can't read #Request.DSN# from Application.cfm on Coldfusion 8 server, while it can on Coldfusion 9.

I need it to be working on Coldfusion 8. I'll keep investigating on this...

BKBK
Community Expert
Community Expert
December 18, 2011

madison2013 wrote:

It looks like the myfuncs.cfc can't read #Request.DSN# from Application.cfm on Coldfusion 8 server, while it can on Coldfusion 9.

I wonder. I would have expected you to then get an error.

How did you arrive at that conclusion anyway? Did you do the test I suggested earlier? What happens when you replace the line

<cfparam name="Request.DSN" default="mydsn">

with

<cfset Request.DSN = "mydsn">

In any case, remember you at least have a temporary solution, namely datasource="mydsn".

Participant
December 18, 2011

I tried that.

<cfset Request.DSN ="mydsn">

<cfset Request.username="myusername">

<cfset Requset.password="mypassword">

...

<cffunction name="getLevel" access="remote" returnType="array">
      <cfset var data="">
      <cfset var result=ArrayNew(2)>
      <cfset var i=0>

      <cfquery name="data" datasource="#Request.DSN#">
      SELECT level_id, level_name
      FROM tbLevel
      ORDER BY level_id
      </cfquery>

...

Still no luck. The curious thing is, it doesn't complain at all. Even when I tried <cfset Request.DSN="dsn_not_existing"> I've got the same thing. Empty dropdown list, no errors.

I wonder why it doesn't complain when the specified DSN doesn't exist.

BKBK
Community Expert
Community Expert
December 17, 2011

I can see no mistake in your code, except the repeated cfquery tag, which is an obvious typographic error. I would therefore expect the first select-list to be populated. There are 2 tests to do.

Firstly, verify that the result set is non-empty. To do this, create a test page and run the following

<cfquery name="data" datasource="mydsn">

         SELECT level_id, level_name

         FROM tbLevel

        ORDER BY level_id

</cfquery>

<cfdump var="#data#">

Secondly, verify that the (form) page you keep opening isn't a cached copy. You can do this by adding an arbitrary query-string to the URL before opening it in the browser, something like

http://127.0.0.1:8500/selecttestdir/formPage.cfm?var=xyz123

Inspiring
December 17, 2011

First things first, what's the result being returned by the call to getClasses()?  You need to work out at what stage of things the problem is occurring before you start wondering what needs to be done to fix it.

I've only really used <CFSELECT> with BIND as a proof of concept, so cannot claim any expertise with it, but in reading the docs (http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_r-s_14.html) you might be mishmashing two different calling methods.  One can return a query from the method being called by the bind, in which event one specifies the value/display attributes, so as to tell CF which columns of the returned query to use.  However if one just uses an array, then those attributes are pointless because there's no column references any more.  I do wonder why if you can return a query, then why you're making a DB call then converting a perfectly good query into an array. Anyway, I'd check if that syntax mishmash is valid.  How are you thinking CF is going to be able to correlate your value/display settings to something meaningful when you are using an array as the data structure?  But like I said; I've no real experience with using this feature of CF, so I am purely basing that on what the docs say, and what seems logical given what the docs say.

Also (but not immediately related to your problem): I really don't think the THIS scope is the correct place for your DSN.  The THIS scope is for things the CFC instance exposes to the calling code, not for "global" variables. The VARIABLES scope is a better fit for this sort of thing.

--

Adam