Creating CFMX DSN programmatically
Requirements
You must be allowed to use the administrator API. Shared hosting will most likely have this disabled.
f_web_root/CFIDE/adminapi must be available. If you are using sandbox security, f_web_root/CFIDE/adminapi needs to allowed.
Introduction
This tutorial is going to attempt to show you how to create a DSN using only Coldfusion. CFMX 7 has provided a set of CFCs that allow you to manage virtually all aspects of the coldfusion server by code rather than the browser interface. This functionality is official, and supported, but not very well documented, and still little known to many developers.
The CFC we will be looking at is the datasouce.cfc. It provides the functionality to delete, set update and verify Coldfusion Data sources. It can do a few other things too.
Let's get Started
Of course, this is simply a tutorial, not a pre built application. You need to take care of error control, security, etc.
First we need to log into the administrator API. Call the administrator component and use the login method. login requires password. This is your Coldfusion Administrator password that you would use to log into the CF Admin via the browser.
You must log in before you can access the other components and methods in the Admin API. Yes, you could do this in two lines, I prefer a single line for simple methods like this.
<cfset isLoggedIn = createObject("component","cfide.adminapi.administrator").login("adminPassword")>
The login method returns true or false. True if login was successful, false if not. you should provide a check to make sure you are actually logged in before preceding.
<cfif NOT isLoggedIn>
You are NOT logged In. (provide error control)
</cfif>
The next step is to instantiate the datasource Component. Do a cfdump to see all of the methods and their properties if you are curious.
<cfset dsnObj = createObject("component","cfide.adminapi.datasource")>
<cfdump var="#dsnObj#">
Now we list all of the datasources. We need to do this to make sure that the datasource we want to add does not already exists. if we are trying to add a new datasource, but that DS name already exists, we will overwrite the existing datasource. Use the method getDataSources() to get all datasources on the server. Return is a structure.
<cfset DSNList = dsnObj.getDataSources()>
All we need this for is to make sure we are not over writing an existing DSN, so I might do something like <cfset DSNList = structKeyList(dsnObj.getDataSources())> to get just a list of DSN names, the listFindNoCase to check the list for my new DSN name. It's up to you. I'll leave it that way.
Creating the DSN
Ok! Now we can actually create the DSN. My Example will be a MySQL DSN. You can do any of the supported DB types. Each database type supported has it's own methods to create the DSN. For MySQL we use setOther().
SetOther() requires a few arguments. Access or other DBs will have different requirements. Again, the <cfdump var="#dsnObj#"> will show you all you need for each type.
driver:
In the web based admin, this is the optional form called Driver Name. It is required by this method though.
name:
This is the datasource name.
url:
The url to the database.
username:
The username to access the DB with.
password:
The password to access the DB with.
class:
Equivalent to Driver Class in the web admin.
For MySQL I set the following:
<cfset dsnURL = "jdbc:mysql://xxx.xxx.xxx.xxx:3306/dbName?zeroDateTimeBehavior=convertToNull">
<cfset dsnClass = "com.mysql.jdbc.Driver">
<cfset dsnDriver = "MySQL (Other)">
<cfset dsnName = "MyDSNName">
<cfset dsnUser = "PleaseDontUseRoot!">
<cfset dnsPass = "strongPassword">
<cfset dsnObj.setOther(driver=dsnDriver,name=dsnName,url=dsnURL,username=dsnUser,password=dsnPass,class=dsnClass)>
After that we need to verify the DSN with the verifyDSN method. It accepts a DSN name and returns true or false.
<cfif dsnObj.verifyDSN(dsnName)>
DSN Successfully Added!
</cfif>
Conclusion
This was the quick way to getting DSNs created programmatically. You will find other challenges, I'm sure, such as how can you create a new database, new mysql user account that can only access that new database, and then create the dsn? This is al possible using Coldfusion and SQL. For MS Access users, it is even simpler.
I recommend against putting your CF Admin password in the code as I did in this example. Now that you know how this works, you can build interfaces and forms to input the details you need to crate DSN's in the go with Coldfusion. I hope to have a project done soon that allows our outside developers to create their own DSN's and MySQL or Access Databases. This way developers can manage sites without harassing us full time developers that have access to this stuff. Well also be able to manipulate IIS and MS DNS. In the end we will be able to create new web sites and manage existing ones from a web interface.
____________
