Working with the Datasource Service Factory

August 19, 2004

I was playing around with the DataSource Service Factory today, and I found out how to do a few things. I created datasources, and used them, but I couldn't figure out how to get it to save the datasource. Anyways here's how you create a datasource, it took me a few tries to figure out the ordering of the setDatasource method (since reflection only tells you the datatypes, I had to figure out what each argument was for):

<cfset dsService = CreateObject("java", "coldfusion.server.ServiceFactory").DataSourceService>
<cfset dsManager = dsService.getDman()>
<cfset map = dsService.getDefaults()>
<!--- name, driver class, description, username, password, url, isPooled, timeout, interval, map --->
<cfset dsManager.setDatasource("datasource_name", 

The map variable has some more values you can set (mostly the permissions), but check out the default values by doing a cfdump of the map variable.

The above code will create a datasource that you can use, but it does not persist after the server is rebooted. If you can find a way to make that work please post a comment.

The next code example will list all the installed JDBC drivers, note that we are using the dsService variable from above.

<cfdump var="#dsService.getDrivers()#" label="Installed JDBC Drivers">

To test if a datasource exists:

<cfif dsManager.exists("datasource_name")>
	it exists...
	it does not exist

To remove a datasource (not sure if this will persist after the server is restarted):

<cfset dsManager.removeDatasource("datasource_name")>

To list all installed datasources:

<table border="1">
 	<td>JDBC Class</td>
 	<td>JDBC URL</td>
 	<td>JDBC User</td>
 	<td>JDBC Password</td>
<cfset nameArray = dsManager.getNames()>
<cfloop from="1" to="#ArrayLen(nameArray)#" index="i">
	<cfset name =nameArray[i]>

To purge query cache:

<cfset dsService.purgeQueryCache()>

To verify a datasource (test connection):

<cfif dsService.verifyDatasource("datasource_name")>
	not verified

As always remember that this undocumented stuff is subject to change from version to version.

Like this? Follow me ↯

7 people found this page useful, what do you think?


For more info on the service factory in general check out this article:
Very cool solution Andy thanks for sharing. Here's the permalink:
can anyone suggest how I could find out the type of a datasource e.g. Sybase, Oracle, etc.? I've been looking around but can't seem to find a way
I guess there is three to four ways to add, save, and backup DSN's and there is three to four ways to avoid using them too.
I used this code to create a dynamic datasource for an access database (mdb). I do not see where to tell him the MDB file adress : <cfset dsService = CreateObject("java", "coldfusion.server.ServiceFactory").DataSourceService> <cfset dsManager = dsService.getDman()> <cfset map = dsService.getDefaults()> <cfset http_start=""> <cfset port="8500"> <cfset dsManager.setDatasource("cinema_dyn_datasource", "macromedia.jdbc.MacromediaDriver", "test dynamique datasource", "", "", "jdbc:sequelink:msaccess://#http_start#:#port#;serverDatasource=c:\inetpub\wwwroot\cinema97.mdb", true, 1200, 420, map)> It does create a datasource, when I run a query, it says invalid URL. The datasource created is not seen in the CF administrator (datasource management) Thanks to help. Pierre.
Starting with CF7, the preferred way to do this is with the CF Administrator API. Check out: http://localhost/CFIDE/adminapi/datasource.cfc for the datasource calls available.
dsService.getDman() is not available in CFMX 7 from what I can tell. anyone know the equive for CFMX7? Thanks!
Rich, Look into: CFIDE.adminapi.datasource There you have a few methods such as : setMSSQL() and setOracle(), etc. open the object in the Component Utility Tool and you will see what to pass to it, etc. http://localhost/CFIDE/adminapi/datasource.cfc
Maybe it's just me, but I never use the StructNew() syntax to make srcttus.var myStruct = {};myStruct["key"] = value ; bar ;I feel a table is an invalid way to describe a struct. It's a key-value store and nothing more. It's equivalent to an Object in Javascript and a HashMap in Java. I bring those two up specifically, because you can JsonSerialize your struct into a json object and the HashMap becuase a coldfusion Struct IS (by inheritance) a Java Hashmap that takes a simple value as a Key and anything as a Value. You can actually call the Java Methods of a HashMap on your struct. Furthermore, this means you get all the nice utility classes from java that take HashMaps as a parameter.NOTE: cfscript is where it's at.

Foundeo Inc.