Working with the Datasource Service Factory

August 19, 2004
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.


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

Trackbacks

Comments

For more info on the service factory in general check out this article: http://www.sys-con.com/coldfusion/article.cfm?id=500
Pete, I blogged earlier today about a different way to create new datasources on the fly. Basically, rather than using the Service Factory, I'm using the CFCs that came with CFMX itself. And most importantly, they persist after a reboot. Andy http://www.creative-restraint.co.uk
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
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. http://www.geocities.com/empiricallyspeaking/
To save the datasource, create a structure that holds all of the elements, called newDSN or something (name, driver, urlmap, etc.). Then cfset dsService[dsn] = newDSN
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.
Does anyone know how to programmatically refresh a datasource? When I remove columns from a table, the schema is somehow not updated, which makes <cfqueryparam> throw very obscure errors.
Is it possible to re-use the password I get with getJdbcPassword? The idea behind is that I have written a template which inserts thousands of records using a "prepareStatement". To use that functionality, I have to create a connection to the database with the getConnection Statement. There I have to pass the connectionURL, username and password; all in clear text (and directly in the code). All that Information I get with the getNames() method. But the password is not in clear text. Anyway, it would be nice if I don't have to pass the password in clear text when connecting to a datasouce (which is already configured with username/password in the ColdFusion Administrator)
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
Does anyone know if the adminapi can be used to then deploy a new datasource to different server instances?
Hi Pablo, What editor do you use to open datasource.cfc. I used dreamweaver, but I saw the codes with unreadable characters. Thanks
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.

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?