Setting datasources within the application

[mmonclair]

Hello,
I've been wrestling with a problem for a few days, and after trying to Google the answer, I need to pose the question.

I would like to create a new datasource for use by an application I'm working on. However, I do not have administrator access to my ColdFusion server, and working for a large institution, I have to go through various hoops to get a request to add a datasource at the server level through. The reason I need to do this is because I have one application on server A (the in-house one) and one on server B (a third-party host). The one on server A needs to pass some info to the one on server B. The way I am trying to go about it is by writing a simple query that runs when the page is called. However, simply pointing to the location of the db (ie. "dbname.db.0000000.thisdbhost.com") doesn't work. I've found info on setting up datasources in the Application.cfc file, but this doesn't seem to work either. I have a feeling I may be going about this wrong, but in digging around I haven't found any good leads.

The other options would be to rewrite the application in server A in PHP and move it over to server B (which I don't have time to do), or pay my web host the extra amount for ColdFusion hosting (which will take approval I'm not sure I have time to wait for).

Thanks much,
Max

[arrgh]

The one on server A needs to pass some info to the one on server B.

How much information? How often?

dbname.db.0000000.thisdbhost.com

What are you pointing to that location? Connecting to a remote database MAY be possible if both your firewalls and permissions allow. They often don't for obvious security reasons. What's your setup?

[dipakatcvrca]

Hi mmonclair,

We really don't need to go to ColdFusion admin to create datasource. We can create the same through our coding also.
We have been provided with a set of ColdFusion APIs which contribute towards this.
But for creating the data source you need to have some basic information with you like: the ColdFusion admin password, database username, database password, database name for which you want to create the datasource.
I am putting bellow a sample code to create MySQL 5 datasource.
I have tested the code in my system and found it to be working well. Let me know if it reacts in same way for you.
In similar way you can create datasource for other db types.
Just dump the myObj variable, you can see different methods to manage datasources.

<cfscript>
    // Login is always required. This example uses two lines of code.
    adminObj = createObject("component","cfide.adminapi.administrator");
    adminObj.login("ColdFusion admin password");

    // Instantiate the data source object.
    myObj = createObject("component","cfide.adminapi.datasource");

    // Create a DSN.
      myObj.SETMYSQL5(
        name="test datasource",
        host = "give host name or IP",
        database = "database name here",
        username = "username for database",
        password = "password for database",
        login_timeout = "29",
        timeout = "23",
        interval = 6,
        buffer = "64000",
        blob_buffer = "64000",
        setStringParameterAsUnicode = "false",
        description = "Put some description here",
        pooling = true,
        maxpooledstatements = 999,
        enableMaxConnections = "true",
        maxConnections = "299",
        enable_clob = true,
        enable_blob = true,
        disable = false,
        storedProc = true,
        alter = false,
        grant = true,
        select = true,
        update = true,
        create = true,
        delete = true,
        drop = false,
        revoke = false );
</cfscript>

Best Of Luck.
Thanks,
Dipak

[mmonclair]

Thanks for your reply.

How much information? How often?

Application A is a registration and payment app which needs to pass on a username and password generated to Application B, which is a Moodle site. This information is passed whenever someone registers and pays for a class on there.

What are you pointing to that location? Connecting to a remote database MAY be possible if both your firewalls and permissions allow. They often don't for obvious security reasons. What's your setup?

I'm pointing to a MySQL database on server B for the Moodle site. I went and ran a simple script in PHP to test out the firewall issue, and was able to successfully connect to it from server A. This leads me back to the syntax issue. We're running ColdFusion 8 on server A. The PHP script I used to test the connect did a direct lookup of the database location, without using a datasource name:

$hostname='that database location';
$username='my username';
$password='my password';
$dbname='the db name';
$usertable='a table on that db';
$yourfield = 'a field in that table';

mysql_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.');
mysql_select_db($dbname);

$query = 'SELECT * FROM ' . $usertable;
$result = mysql_query($query);
if($result) {
while($row = mysql_fetch_array($result)){
$name = $row[$yourfield];
echo 'Name: ' . $name;
}
}

Is there a similar way that I can get around using a datasource defined at the admin level, and just directly call the db, as the above script does? Examples I've found for defining datasources in the Application.cfm or .cfc file only refer to picking what seem to be server-defined datasources. When I try a direct reference, within a <cfquery> tag, like this:

<cfquery name='outside_db' datasource='that database location' username="my username" password="my password">

I get an error indicating that CF cannot find the db.

[mmonclair]

Hi mmonclair,

We really don't need to go to ColdFusion admin to create datasource. We can create the same through our coding also.
We have been provided with a set of ColdFusion APIs which contribute towards this.
But for creating the data source you need to have some basic information with you like: the ColdFusion admin password, database username, database password, database name for which you want to create the datasource.
I am putting bellow a sample code to create MySQL 5 datasource.
I have tested the code in my system and found it to be working well. Let me know if it reacts in same way for you.
In similar way you can create datasource for other db types.
Just dump the myObj variable, you can see different methods to manage datasources.

Best Of Luck.
Thanks,
Dipak

Thanks for the reply. My problem is that I do not have administrator level access, and therefore no CF admin password, which is required for the expression on line 4. Is there any way around this?

[arrgh]

This information is passed whenever someone registers and pays for a class on there.

Don't they provide other transfer methods, like a web service? Seems strange an outside company would even want you connecting directly to their servers.

Is there a similar way that I can get around using a datasource defined at the admin level, and just directly call the db, as the above script does?

It's possible to create direct jdbc connections, instead of using cfquery. The link below should work .. but it's not a good example. It uses the slow jdbc:odbc driver. Do a search on dsn-less connections for better examples
http://www.hosting.com/support/mysql/coldfusionstring

But I'd be concerned about security. I doubt that method's secure and sending payment info insecurely is a recipe for disaster.

[mmonclair]

Don't they provide other transfer methods, like a web service? Seems strange an outside company would even want you connecting directly to their servers.

It's possible to create direct jdbc connections, instead of using cfquery. The link below should work .. but it's not a good example. It uses the slow jdbc:odbc driver. Do a search on dsn-less connections for better examples
http://www.hosting.com/support/mysql/coldfusionstring

But I'd be concerned about security. I doubt that method's secure and sending payment info insecurely is a recipe for disaster.

Actually, server B is a third party web host we contracted to host the Moodle site we're creating. This step was necessitated due to resource issues related to the institution I work for (long story: I work for a university). No payment information is being sent to server B from server A, only to automate profile setup in Moodle (and to save me time in entering this info myself). All information collected in server A will only stay in server A, which is secured. The info being passed is being generated after the payment info is saved. So I'm not too worried about that.

I'll take a look at the link you provided. Thanks for the tip.

[arrgh]

The info being passed is being generated after the payment info is saved. So I'm not too worried about that.

Just be sure it's not info that can be used to access the payment info either. Because that's just as insecure ;-)

Let me know if you have problems w/the link. If use CF's pure jdbc driver instead of the one in the link. I didn't have time to hunt down a better example. But if you search a little, I know they're some better ones out there.

[mmonclair]

I did some digging around and after some trial and error, I found a script that seemed to work on the CF side (ie. it didn't throw any errors)

<cffunction name="mySQL" description="Connects directly to a mySQL server and processes SQL">
        <cfargument name="sql" required="yes">
        <cfargument name="query_name" default="mySQL">
        <cfargument name="JDBC_URL" default="jdbc:mysql://[I]db path[/I]/[I]db name[/I]">
        <cfargument name="mySQL_username" default="[I]username[/I]">
        <cfargument name="mySQL_password" default="[I]password[/I]">
        <cfargument name="driver" default="org.gjt.mm.mysql.Driver">
        <cfscript>
            class = createObject("java", "java.lang.Class"); class.forName(driver);
            dm = createObject("java","java.sql.DriverManager");
            con = dm.getConnection("#JDBC_URL#","#mySQL_username#","#mySQL_password#");
            st = con.createStatement();
            if ( left(trim("[I]query statements[/I]"), 6 ) is 'select'){//select statements
                resultSet = st.executeQuery("[I]query statements[/I]");
                "#query_name#" = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
                return "#query_name#";
                resultSet.close();
            }
            else st.executeUpdate("[I]query statements[/I]");//insert, update, & delete statements
            st.close();
            con.close();
        </cfscript>
    </cffunction>

However, it did not seem to update my database, which is a different matter for me to investigate. I ran into trouble with the code snippet on the link you posted, as it threw an error that it could not find the db driver specified. I tinkered with that a bit, with no luck before trying a few others I found from searching for DSN-less connections.

[arrgh]

I tinkered with that a bit, with no luck before trying a few others I found from searching for DSN-less connections.

Sorry I didn't test it. Just assumed it worked. So much for assumptions.. lol Still it was a bad example anyway. So no loss there.

if ( left(trim("query statements"), 6 ) is 'select'){//select statements

That function's better, but not very robust. If I get a chance, I'll try and find one and post it.

However, it did not seem to update my database, which is a different matter for me to investigate.

Run the same statement, credentials,etc... in your php example. If it works there it's a problem with the cffunction.

[arrgh]

What's your CF version?

[mmonclair]

What's your CF version?

I'm on CF 8.

[arrgh]

Ok. Sorry it's been so hectic I haven't had a chance yet.

[mmonclair]

I did some digging around and after some trial and error, I found a script that seemed to work on the CF side (ie. it didn't throw any errors)

<cffunction name="mySQL" description="Connects directly to a mySQL server and processes SQL">
        <cfargument name="sql" required="yes">
        <cfargument name="query_name" default="mySQL">
        <cfargument name="JDBC_URL" default="jdbc:mysql://[I]db path[/I]/[I]db name[/I]">
        <cfargument name="mySQL_username" default="[I]username[/I]">
        <cfargument name="mySQL_password" default="[I]password[/I]">
        <cfargument name="driver" default="org.gjt.mm.mysql.Driver">
        <cfscript>
            class = createObject("java", "java.lang.Class"); class.forName(driver);
            dm = createObject("java","java.sql.DriverManager");
            con = dm.getConnection("#JDBC_URL#","#mySQL_username#","#mySQL_password#");
            st = con.createStatement();
            if ( left(trim("[I]query statements[/I]"), 6 ) is 'select'){//select statements
                resultSet = st.executeQuery("[I]query statements[/I]");
                "#query_name#" = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
                return "#query_name#";
                resultSet.close();
            }
            else st.executeUpdate("[I]query statements[/I]");//insert, update, & delete statements
            st.close();
            con.close();
        </cfscript>
    </cffunction>

However, it did not seem to update my database, which is a different matter for me to investigate. I ran into trouble with the code snippet on the link you posted, as it threw an error that it could not find the db driver specified. I tinkered with that a bit, with no luck before trying a few others I found from searching for DSN-less connections.

I had to set this aside for a while, but now have to get on it. When I invoke this function in my script, I need a value for this <cfinvokeargument name="sql" value="">.

I can't find the tutorial that had this originally. What value would I use for this?

[mmonclair]

I came back to this yesterday, and ran it successfully. Unfortunately, one of the fields I'm updating uses an encrypted string, so when I pass over a regular string, it is unreadable by the application. But that's another issue I have to chase down. But at least the query works. :-D

[cfwebdeveloper]

Just curious, what was the solution?

[arrgh]

But at least the query works

Be sure to scrub the sql string/params. Because you're vulnerable to sql injection without cfqueryparam.

[mmonclair]

Just curious, what was the solution?

This code:

<cffunction name="mySQL" description="Connects directly to a mySQL server and processes SQL">
        <cfargument name="sql" required="yes">
        <cfargument name="query_name" default="mySQL">
        <cfargument name="JDBC_URL" default="jdbc:mysql://db path/db name">
        <cfargument name="mySQL_username" default="username">
        <cfargument name="mySQL_password" default="password">
        <cfargument name="driver" default="org.gjt.mm.mysql.Driver">
        <cfscript>
            class = createObject("java", "java.lang.Class"); class.forName(driver);
            dm = createObject("java","java.sql.DriverManager");
            con = dm.getConnection("#JDBC_URL#","#mySQL_username#","#mySQL_password#");
            st = con.createStatement();
            if ( left(trim("query statements"), 6 ) is 'select'){//select statements
                resultSet = st.executeQuery("query statements");
                "#query_name#" = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
                return "#query_name#";
                resultSet.close();
            }
            else st.executeUpdate("query statements");//insert, update, & delete statements
            st.close();
            con.close();
        </cfscript>
    </cffunction>

[mmonclair]

Be sure to scrub the sql string/params. Because you're vulnerable to sql injection without cfqueryparam.

Thanks, I'll add that.

[mmonclair]

Be sure to scrub the sql string/params. Because you're vulnerable to sql injection without cfqueryparam.

OK, I marked this as solved prematurely. I can easily get data to go one way, but now I'm have trouble retrieving it. Mostly, it's how to get the result of a SELECT query out of the function and into my CF page.

Here's what I need to do: I need to check the Moodle database to see if a username and email address have already been used. In my <cfinvokeargument> tag for the argument "sql" in the script I posted above, I have this statement:

SELECT username AS this_user FROM mdl_user WHERE username = '#username#'

Just to review, this is what it looks like in the <CFScript> section of this function:

class = createObject("java", "java.lang.Class"); class.forName(driver);
dm = createObject("java","java.sql.DriverManager");
con = dm.getConnection("#JDBC_URL#","#mySQL_username#","#mySQL_password#");
st = con.createStatement();
            
resultSet = st.executeQuery("SELECT username AS this_user FROM mdl_user WHERE username = '#username#'");
"#query_name#" = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
return "#query_name#";
resultSet.close();
st.close();
con.close();

In my CF page, I get an "Attribute validation error for tag cfoutput." error when I invoke it as such:

<cfinvoke component="check_user" method="get_username" returnvariable="found_user">
    [lines with argument definitions]
    </cfinvoke>

<cfoutput query="found_user">#this_user#</cfoutput>

The problem I am finding is that the default value of #query_name#, set in the function's <cfargument> tags is what's being passed over, and not the result of the query. I've played around with the code in both the CFC and the CF page, looked up quite a few examples, but I'm a bit lost on this. I'm sure it's got to be something simple, but being new to CFScript, I'm sure I'm missing it.

[mmonclair]

I replaced the code I was using with this one:

<cfscript>

classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
dm = createObject("java","java.sql.DriverManager");

con = dm.getConnection(connection string);

st = con.createStatement();
rs = st.ExecuteQuery("Select * FROM table");
q = createObject("java", "coldfusion.sql.QueryTable").init(rs); >

</cfscript>

And it works now.

[arrgh]

"#query_name#" = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
return "#query_name#";
resultSet.close();
st.close();
con.close();

Couple problems.
1) Your return is in the wrong place. So the function exits before the connections are closed which'll cause big problems.

2) The function shouldn't know, or even care, what variable name the calling page uses. It's only job is to create a query and return it. Period. The correct order is:

resultSet.close();
st.close();
con.close();
return CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);

3) If your function's in a shared scope, even VARIABLES, you're going to hit all sorts of wonky problems if you don't VAR scope all those variables.

[mmonclair]

Couple problems.
1) Your return is in the wrong place. So the function exits before the connections are closed which'll cause big problems.

2) The function shouldn't know, or even care, what variable name the calling page uses. It's only job is to create a query and return it. Period. The correct order is:

resultSet.close();
st.close();
con.close();
return CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);

3) If your function's in a shared scope, even VARIABLES, you're going to hit all sorts of wonky problems if you don't VAR scope all those variables.

Thanks for the advice. I had found, by the way, that using the argument #query_name# instead of just explicitly declaring a variable in the line

#query_name# = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
return #query_name#;

wasn't helping me. Rather than creating an object using resultSet, it was just passing the default name from the argument declaration at the beginning of the function. When I changed it to

q = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
return q;

It works OK. After adapting the code snippet I indicated above for the function I'm using, I now have:

classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
dm = createObject("java","java.sql.DriverManager");
con = dm.getConnection("#JDBC_URL#","#mySQL_username#","#mySQL_password#");
st = con.createStatement();
            
resultSet = st.executeQuery("SELECT username AS this_user FROM mdl_user WHERE username = '#username#'");
q = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
return q;
resultSet.close();
st.close();
con.close();

It looks like you are simply cutting out the middleman ('q'), so to speak, and just returning CreateObject(). This seems to work the same, but your code is cleaner.

Now, when you say I should VAR scope all of the variables, do you mean in the CFScript declarations, like so?

classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
var dm = createObject("java","java.sql.DriverManager");
var con = dm.getConnection("#JDBC_URL#","#mySQL_username#","#mySQL_password#");
var st = con.createStatement();
            
var resultSet = st.executeQuery("SELECT username AS this_user FROM mdl_user WHERE username = '#username#'");
var q = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
return q;
resultSet.close();
st.close();
con.close();

Or do you mean in the function, after the <cfscript>? Or do you mean when I invoke them in my calling page?

[JerryFang]

Hi mmonclair,

We really don't need to go to ColdFusion admin to create datasource. We can create the same through our coding also.
We have been provided with a set of ColdFusion APIs which contribute towards this.
But for creating the data source you need to have some basic information with you like: the ColdFusion admin password, database username, database password, database name for which you want to create the datasource.
I am putting bellow a sample code to create MySQL 5 datasource.
I have tested the code in my system and found it to be working well. Let me know if it reacts in same way for you.
In similar way you can create datasource for other db types.
Just dump the myObj variable, you can see different methods to manage datasources.

<cfscript>
    // Login is always required. This example uses two lines of code.
    adminObj = createObject("component","cfide.adminapi.administrator");
    adminObj.login("ColdFusion admin password");

    // Instantiate the data source object.
    myObj = createObject("component","cfide.adminapi.datasource");

    // Create a DSN.
      myObj.SETMYSQL5(
        name="test datasource",
        host = "give host name or IP",
        database = "database name here",
        username = "username for database",
        password = "password for database",
        login_timeout = "29",
        timeout = "23",
        interval = 6,
        buffer = "64000",
        blob_buffer = "64000",
        setStringParameterAsUnicode = "false",
        description = "Put some description here",
        pooling = true,
        maxpooledstatements = 999,
        enableMaxConnections = "true",
        maxConnections = "299",
        enable_clob = true,
        enable_blob = true,
        disable = false,
        storedProc = true,
        alter = false,
        grant = true,
        select = true,
        update = true,
        create = true,
        delete = true,
        drop = false,
        revoke = false );
</cfscript>

Best Of Luck.
Thanks,
Dipak

Dipak,

I was searching on how to modify all of my data sources in CF when i came across your post. I was wondering if you can help me with my situation. We currently have 19 webhost that is mirroring each other for load balance and active/passive. I need to go in to each webhost and modify the timeout and interval setting on all of my data source's on each webhost. The post you have basically show how to create a new data source and i was wondering if you know a way that i can modify/change multiple data sources at one time.

I do apologize for the lack of information as my admins do not wish to work on this and it is just tedious to waste time to go in to each data source and manually change them. I can provide further information if need it but i definately would like to find out if there is a way to do so just for next time this issue comes around.

Thank you

[JerryFang]

Hi all,

I need some help on modifying data multiple data source timeout and interval setting on each of my data source. Is there an automated way to do so? we have multiple data sources on 19 webhost.

TIA

[arrgh]

Rather than creating an object using resultSet, it was just passing the default name from the argument declaration at the beginning of the function.

That doesn't sound right. I'd have to test it to see what actually, happens. But either way, you shouldn't use that code. Functions aren't like custom tags. They're like a black box. They shouldn't know or care what happens to the results once they've done their job.

I now have:

You're still exiting too soon. "return" means abort the function and don't run any more code. So all that stuff you have after the "return" line never executes .. so you never close the objects.

It looks like you are simply cutting out the middleman ('q'), so to speak, and just returning CreateObject(). This seems to work the same, but your code is cleaner.

Yeah, that's a personal preference thing. They both do the same thing.

Now, when you say I should VAR scope all of the variables, do you mean in the CFScript declarations, like so?

More like the 1st example. But if you don't know what var scoping is, do a search on it. There's lots of examples, and it's an important concept to understand.
http://www.iknowkungfoo.com/blog/index.cfm/2007/8/22/Object-Oriented-Coldfusion--4--The-var-scope

[arrgh]

The post you have basically show how to create a new data source

No, it doesn't create a datasource, only a connection. It's not the way queries are normally handled in CF either. Normally dsn's are created through the CF Admin and queries are simpler. This way is much more complex and error prone. So don't use it unless you have to.

If you already have CF dsn's, you can use the Admin API to change settings programatically
http://www.mindfiresolutions.com/AccessModify-ColdFusion-Administrator-settings-programmatically-by-ColdFusion-Administrator-API-calls-172.php

[mmonclair]

OK, now I have an issue that has nothing to do with the variables. I've been using this function, along with some of the changes you indicate above, and it's been working pretty well so far. Then I ran it a few minutes ago and I get this error:

Communication failure during handshake. Is there a server running on rwj2003a.db.8200001.hostedresource.com:3306?

I checked and the database in question IS running on port 3306. Even when I add this explicitly in my connection string I get this. I also checked my username and password settings and nothing had changed since the last time it worked. Additionally, the site running off of that database is still working fine.

What could be causing this (other than an issue with the web host), and is it something I can find a work around?

[arrgh]

Hard to say. There's many causes for that error (do a search on that error without the server name).

-What do the log files say?
-Did you fix the code to always close the connections, because failing to do would cause problems?
-Does the error happen very time, or only sometimes or with certain statements?

[mmonclair]

Hard to say. There's many causes for that error (do a search on that error without the server name).

-What do the log files say?
-Did you fix the code to always close the connections, because failing to do would cause problems?
-Does the error happen very time, or only sometimes or with certain statements?

I found the problem. Some time between yesterday morning and yesterday afternoon, the needed driver changed from org.gjt.mm.mysql.Driver to com.mysql.jdbc.Driver. The web host customer service had no clue about this, but I figured that this had to be the culprit when I loaded this script on another server and got the same error. A PHP query I ran had no trouble with connecting to the database. When I did a search for more alternate drivers, I used the latter one I mentioned and it works fine now.

The fact that this is something not under my control, and puts me at the mercy of my web host, is leading me to put together a case to take to my CF administrator to set up a datasource for me to use. Either that, or redo my registration form in PHP. I'll have to see which one will take less time...