Pete Freitag Pete Freitag

Working with the Datasource Service Factory

coldfusion

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", 
 "macromedia.jdbc.MacromediaDriver",
 "description",
 "username",
 "password",
 "jdbc:macromedia:sqlserver://localhost:1433;databaseName=tester;SelectMethod=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=1000",
 true, 
 1200,
 420,
 map)>

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...
<cfelse>
	it does not exist
</cfif>

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">
 <tr>
 	<td>Name</td>
 	<td>Description</td>
 	<td>JDBC Class</td>
 	<td>JDBC URL</td>
 	<td>JDBC User</td>
 	<td>JDBC Password</td>
 	<td>Interval</td>
 	<td>Pooled</td>
 	<td>Timeout</td>
 </tr>
<cfset nameArray = dsManager.getNames()>
<cfloop from="1" to="#ArrayLen(nameArray)#" index="i">
	<cfset name =nameArray[i]>
	<cfoutput>
	<tr>
		<td>#name#</td>
		<td>#dsManager.getDescription(name)#</td>
		<td>#dsManager.getJdbcClass(name)#</td>
		<td>#dsManager.getJdbcUrl(name)#</td>
		<td>#dsManager.getJdbcUsername(name)#</td>
		<td>#dsManager.getJdbcPassword(name)#</td>
		<td>#dsManager.getInterval(name)#</td>
		<td>#YesNoFormat(dsManager.isPooled(name))#</td>
		<td>#dsManager.getTimeout(name)#</td>
	</tr>
	</cfoutput>
</cfloop>

To purge query cache:

<cfset dsService.purgeQueryCache()>

To verify a datasource (test connection):

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

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


Like this? Follow me ↯

Working with the Datasource Service Factory was first published on August 19, 2004.

FuseGuard Web App Firewall for ColdFusion

The FuseGuard Web Application Firewall for ColdFusion & CFML is a high performance, customizable engine that blocks various attacks against your ColdFusion applications.

Comments

For more info on the service factory in general check out this article: http://www.sys-con.com/coldfusion/article.cfm?id=500
by Pete Freitag on 08/19/2004 at 3:06:30 PM UTC
Very cool solution Andy thanks for sharing. Here's the permalink: http://www.creative-restraint.co.uk/blog/index.cfm?mode=entry&entry=7B83C13D-EEF8-5BAE-8195007923F93910
by Pete Freitag on 08/20/2004 at 10:49:22 AM UTC
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
by ekkis on 08/25/2004 at 8:07:15 PM UTC
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. http://www.geocities.com/empiricallyspeaking/
by Dan on 10/04/2004 at 2:25:17 AM UTC
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="http://192.168.0.3"> <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.
by Pierre Larde on 12/23/2004 at 5:45:36 AM UTC
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.
by Tom on 12/19/2005 at 2:10:13 PM UTC
dsService.getDman() is not available in CFMX 7 from what I can tell. anyone know the equive for CFMX7? Thanks!
by Rich on 01/10/2006 at 11:56:16 AM UTC
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
by Pablo Varando on 01/17/2006 at 11:07:44 PM UTC
Maybe it's just me, but I never use the StructNew() syntax to make srcttus.var myStruct = {};myStruct["key"] = value ;myStruct.foo= 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.
by Irina on 03/12/2013 at 5:41:33 PM UTC