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.
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.
CREATE DATABASE IF NOT EXISTS myDatabaseName
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.
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.
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”.
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) )
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.