Create Datasource Name Dynamically?

[Guest]

I am writing an application where the user will be given an empty database to begin with. The application will require a connection to the database, which will be named however the users wishes.

I have created a form, which when submitted will write a plain text file with one single word and no formatting. That one single word will be used as the datasource name.

So these are the files I have.

<!---datasource.txt--->

FreshDb

<!---read_settings.cfm--->

    <!----Get the full path to the file datasource.txt--->

    <cfparam name="isname" default="datasource.txt">
    <cfset thisPath = ExpandPath("*.*")>
    <cfset thisDirectory = GetDirectoryFromPath(thisPath)>
    <cfparam name="islocation" default="#thisDirectory##isname#">
    <cfset isettingsfile = islocation>

  <!----Does the file datasource.txt exist?--->

    <cfif FileExists(isettingsfile)>
 

  <!---The file datasource.txt has been found, read it and place the contents into a variable called dsn--->

     <cffile action="read" file="#isettingsfile#" variable="dsn">

  <!---Set a variable called datasource and fill it with the contents of datasource.text--->

          <cfparam name="datasource" default="#dsn#">

<!---Application.cfm--->

  <!---Call the file read_settings.cfm---->

  <cfinclude template="includes/read_settings.cfm">

<!---DNS_Test.cfm--->

   <!---Query a table with one record to see if the connection is established--->

    <cfquery name="qry_DSNTest" datasource="#dsn#">
       SELECT *
       FROM tbl_Admin
    </cfquery>
  

  <!---Has the query returned any results? If yes, set the variable connection to connected, otherwise, set it to not_connected--->

     <cfif qry_DSNTest.RecordCount>
     <cfset tconnection="Connnected">
     <cfelse>
     <cfset tconnection="Not_connnected">

Now, when I attempt to run a simple test this is the error message I receive:

Error Occurred While Processing Request

Datasource FreshDb could not be found. The error occurred in Application.cfm: line 43 41 : <cfparam name="url.action" default=""> 42 : <cfif run EQ ""> 43 : <cfquery name="qry_DSNTest" datasource="#dsn#"> 44 :     SELECT * 45 :     FROM tbl_Admin DATASOURCE   FreshDb Resources: Check the ColdFusion documentation to verify that you are using the correct syntax.

If I remove the cfinclude template line in my Application.cfm file and replace it with

<cfparam name="datasource" default="FreshDb">

Then I can perform the query without any problem.

If I leave the cfinclude template in place, but do not attempt to perform a query using the datasource, I can do a variable dump and see these results:

VARIABLES

struct DATASOURCE FreshDb DOCREATESETTINGS No DSN FreshDb ISETTINGSFILE \includes\datasource.txt ISLOCATION \includes\datasource.txt ISNAME datasource.txt ROOT demos RUN [empty string] THISDIRECTORY \includes\ THISPATH \includes\*.*

So it appears to me, that my files are being found, the contents are being read, and the variables are beiong populated. So I cant figure out why I cant call the files using cfinclude template. and have the variables available.

Any ideas would be greatly appreciated. I am going into my second full day trying to figure out what I am doing wrong.

P.S. What I am trying to acheive with this code is a method to allow the user to define a custom datasource name. This should be done without ever connecting to the database, as there will be no connection to the database until the datasource name is defined somewhere. Also I do not want the user to have to manually edit any files. The goal is to submit a form with the datasource name and then test the connection. If there is an easier way to go about this, than the way I am trying, please offer your suggestions.

[Dan_Bracuk]

Where's the part where the datasource gets created in ColdFusion?

[Guest]

Sorry, I should have said in the original post. The connection in Cold Fusion was done manually. Although I know it is possible to create a ColdFusion connection programatically, it's a bit over my head to tackle. So for the moment, I am just trying to create a usable variable with that previously created ColdFusion connection name from a text file. The text file is that which was created from a web form. My application will be available to multiple users, where each user will have a different name for their ColdFusion connection. At this point I am assuming the end user will have a method of manually creating the Coldfusion Connection, and will then need only enter that connection name into my "Run Once" page. The Run Once page being the very first page containing the form that asks for the conenction name. After that form is submit, it writes the text file, which will then be used for the datasource name in the rest of the pages.

I think I need to make these application variables, session varibles, new structures, or something. But because the user will need to call the variable, regardless if they are logged in or not, I dont know which one to use. I'm not sure its relevent to the underlying problem, but in theory, I can then run a check on certain pages to determine if either the text file has been aletered or deleted or if the ColdFusion connection details changed. Basically if the datsource connnection in the application has been broken. If so, take the user back to the previously used form, to recreate it.

And to top it all off I havent had enough sleep in the last few days and I'm just really confusing myself!

So forgive me me if I am missing something really simple here. But I cant see the forest for the trees right now  

[ilssac]

You can easily use a variable to set the datasource value in a <cfquery ...> tag.

<cfset aVar = "myDSN">
<cfquery datasource="#aVar#" ...>

Now how you set the value of the variable used in the <cfquery...> tag is up to how you want to construct your system.

[Guest]

Look at my code again, thats what I am doing.   But the variable isnt available.

[ilssac]

Dump out the value of your #DSN# variable.  With some characters around it.

I.E.  <cfoutput>-=:#DSN#:=-</cfoutput>

I would guess, without playing on your system, that there is whitespace characters in your DSN variable, probably line feed|carriage return.

Your technique is fine, so the problem must be in the data.

[Guest]

In my application.cfm I have <cfoutput>.:#dsn#:.</cfoutput> On my page I have

.:FreshDb :.

There is a space between Db and :.

Its not in the text file.

I am calling this exactly

<cffile action="read" file="#isettingsfile#" variable="dsn">
<cfoutput>.:#dsn#:.</cfoutput>

[ilssac · Accepted solution]

RudyZim wrote:

.:FreshDb :.

The quick and easy way to fix this is to trim the data.

#trim(DSN)#

The white space is probably coming from a linefeed|carriage return at the end of the line in the text file.  Unless you use some tool that explicitly makes white space characters visable, they are very easy to overlook.  Until a situation like this where they make their presence known, usually in an unpleasant manner.

[Guest]

Do you have any idea hour many hours I struggled with this? Changing all sorts of things that had noth

ing to do with the problem!  Good greif!!!

Thank you!

using #trim(DSN)# solved the problem.

I never would have thought to put the characters around the variable to look for white spaces.  Again, thank you very much!

[ilssac]

RudyZim wrote:

I never would have thought to put the characters around the variable to look for white spaces.  Again, thank you very much!

Your welecome, and now you know a little more about how to fish rather then just being given one fish to eat.

[Guest]

Very true.

[tclaremont]

I dare say I use trim on just about EVERYTHING that I retrieve from the database, or that is dependent on end user data entry.

[Guest]

It's now become the a rule of thumb for me!

[Dan_Bracuk]

For user entry, our approach is to do the trimming with js in the form field's onChange event.   Mind you, I program for the intranet and anyone who disables javascript disabled is going to have bigger problems than my form fields.

[ilssac]

Dan Bracuk wrote:

anyone who disables javascript disabled is going to have bigger problems than my form fields.

Yeah, I suspect if anybody "disables JavaScript disabled" have really monkeyed with their system!

Sorry, Dan, I know I make many of these kinds of typos, but that one really tickled my funny bone.