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.


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

 Download FuseGuard WAF for ColdFusion

Trackbacks

Trackback Address: 152/CD1D23B709F63BC56D8A1EBE2C333568

Comments

On 08/19/2004 at 5:06:30 PM EDT Pete Freitag wrote:
1
For more info on the service factory in general check out this article: http://www.sys-con.com/coldfusion/article.cfm?id=500

On 08/20/2004 at 10:04:29 AM EDT Andy Allan wrote:
2
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

On 08/20/2004 at 12:49:22 PM EDT Pete Freitag wrote:
3
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

On 08/25/2004 at 10:07:15 PM EDT ekkis wrote:
4
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

On 10/04/2004 at 4:25:17 AM EDT Dan wrote:
5
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/

On 10/28/2004 at 3:25:46 PM EDT ryanmc wrote:
6
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

On 12/23/2004 at 7:45:36 AM EST Pierre Larde wrote:
7
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.

On 01/13/2005 at 9:33:58 AM EST Mic wrote:
8
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.

On 01/13/2005 at 7:18:41 PM EST Juerg Anderegg wrote:
9
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)

On 12/19/2005 at 4:10:13 PM EST Tom wrote:
10
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.

On 01/10/2006 at 1:56:16 PM EST Rich wrote:
11
dsService.getDman() is not available in CFMX 7 from what I can tell. anyone know the equive for CFMX7?

Thanks!

On 01/18/2006 at 1:07:44 AM EST Pablo Varando wrote:
12
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

On 02/22/2006 at 12:10:36 AM EST Carl wrote:
13
Does anyone know if the adminapi can be used to then deploy a new datasource to different server instances?

On 03/08/2006 at 6:53:28 AM EST Peter wrote:
14
Hi Pablo, What editor do you use to open datasource.cfc. I used dreamweaver, but I saw the codes with unreadable characters. Thanks

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?