Dynamic Datasources in ColdFusion MX 7

I spent some time recently trying to track down how to create a database and datasource in ColdFusion without going into the ColdFusion Adminstrator interface and doing in manually. I found plenty of articles and documentation on how this was done in ColdFusion 5 and notes that it had changed but nothing on how. Now that I’ve got it working I’d like to share how I did it in case anyone else is looking for help with this.

The Setup

Our goal is to create a new MySQL database, setup a datasource in ColdFusion, and and create the tables in the database. We’ll start out by creating a new datasource in ColdFusion administrator. We’ll specify a connection to MySQL without adding specifying a database.

We’ll use the following settings:

CF Data Source Name: Generic_DSN
JDBC URL: jdbc:mysql://localhost:3306/
Driver Class: com.mysql.jdbc.Driver
Driver Name: (leave this field blank)
Username: root (or whatever account you are using to access MySQL)
Password: (enter your MySQL account password.

NOTE: I’m assuming that you have set up datasources that point to MySQL before, the key to this one is that we don’t add a database name to the end of the JDBC URL. If you need more help with this just ask in the comments or email me.

This leaves us a with connection to MySQL that does not specify a database. We’ll use this connection to create our blank database.

Creating the Database

Next we’ll use the “Generic_DSN” in a cfquery to create a database called “myDatabaseName” if it does not yet exist.

[code]

CREATE DATABASE IF NOT EXISTS myDatabaseName

[/code]

Creating the Datasource

At this point we should have a database with no tables named “myDatabaseName”. The next step will be to log in to the ColdFusion Adminsitrator API. The first line creates an object for connecting to the Admin API and the next line tries to log in and sets isLoggedIn to true if successful and false if it fails.

[code]


[/code]

Now we’ll check to see if we’re logged in. If so, we’ll create and object called “setDSN” using the datasource cfc in the Administrator API. We then use cfinvoke to to create the new datasource called “my_dsn”. If we are not logged in we will just show a quick error message.

[code]












Login failed


[/code]

Creating the Tables

Now we have created a blank database named “myDatabaseName” and a ColdFusion datasource named “my_dsn”. before we can use this though we’ll need to create some tables in our database where our information will be stored. First we’ll drop (delete) the table called “my_table” if it exists to give ourselves a clean slate. Next we will create a table called “my_table” with two columns named “row_ID” and “name”.

[code]

DROP TABLE IF EXISTS my_table


CREATE TABLE my_table (row_ID int(11) NOT NULL auto_increment, name varchar(100), PRIMARY KEY (row_ID) )

[/code]

Clearly this isn’t a very useful database but you can easily put in more complex CREATE TABLE commands with more columns to suit your needs. You can also create as many tables as you want with different table names in your new database.

Not everyone will need to create databases and datasources dynamically. Many projects don’t call for this and you can simply log into the ColdFusion Adminstrator and set things up manually. But for projects where you may need to let users create databases without your manual intervention (in my case it was when they set up an account) this may be useful.

Kevin Hall

I am one of the owners of Infinite Web Design. I'll be glad to talk with you about who we are, what we do, or any of the topics we write about here.
Kevin Hall

5 thoughts on “Dynamic Datasources in ColdFusion MX 7

  1. Kevin: Thanx for the help!!!!!!!

    BTW, I ran into a problem creating the Generic_DSN. I was getting a connection error
    “The root cause was that: java.sql.SQLException: No suitable driver available for…”

    Which I found a fix

    And if ppl are having a problem filling the table with a SQL export file (MyPHP, MySQL Front etc)after creating the database you can parse the SQL script to to have each query run separately. CF blocks the running of multiple queries delimited by a “;”

    --->

    #Replace(Replace(TMP_Queryline, "''", "'" , "ALL"), """", "'" , "ALL")#

  2. Ran across this error message, anyone has any idea why?

    Thanks!

    Current user is not authorized to invoke this method.

  3. @ Skeptical: The Admin API can have permission blocked. If you are running the script in a hosted environment where you don’t control the server you may not have access to the ColdFusion Admin API.

    Check your username and password values in the cfinvokeargument tags.

  4. A normal restricted MySQL user account will not be able to create databases until granted access to do so by the root user.

    Simply granting create won’t do it either, unless you use wild cards for database name GRANT CREATE on *.* to yourUser@’%’ identified by ‘yourPassword’;

    Then after that you can create databases with a non-root account.

    My set-up will be creating new blank DBs, copying over standard tables used by our custom tools chosen by the developer, crating a new MySQL user accouht that can only access that new database, and creting a DSN for that DB so our remote developers can take advantage of out pre-made tools, set up a new mysql db, etc, and not have to call us every time.

    This is all through a admin website so we can control who gets what.

    Great write up, just know that you will have issues with a non-root account, and most dba’s will never allow root access remotely.

  5. @Kevin,
    Good point about the restricted access to the database. This can be a real problem when trying to use this method to create databases on the fly. Unfortunately, it’s the only way I’ve found to do this. I guess my view is that DBA’s are there to support the application designers not to hold them back when they have a legitimate reason to do something. If you need a root account or any other unusually high level of access to get something done they should set that up.

Comments are closed.