Friday, March 26, 2010

Test/Verify Datasource Connection

I am trying to figure out how to test that a connection to a datasource is available with out querying the database server...

For example, I currently do the following:

------------------------------------------------------------------

%26lt;!--- attempt to connect to datasource ---%26gt;
?%26lt;cftry%26gt;
?%26lt;cfquery datasource=''#this.getName()#'' name=''test''%26gt;
?SHOW TABLES;
?%26lt;/cfquery%26gt;
?%26lt;cfcatch type=''database''%26gt;
?%26lt;cfif displayError%26gt;
?%26lt;cfthrow message=''Datasource Connection Failed'' /%26gt;
?%26lt;cfelse%26gt;
?%26lt;cfreturn false /%26gt;
?%26lt;/cfif%26gt;
?%26lt;/cfcatch%26gt;
?%26lt;/cftry%26gt;

------------------------------------------------------------------

I would like to be able to do this without running a query... is there any way to just ask coldfusion if it can communicate with the datasource??I want to be able to do something similar to:

%26lt;cfif datasource.isAvailable() %26gt;

%26lt;!--- DO SOME DATABASE WORK ---%26gt;

%26lt;/cfif%26gt;

But I do not want to add an extra db query everywhere....?All opinions, suggestions, ideas are welcome... Thanks!!

Test/Verify Datasource Connection

I'm not sure of a way you can do this but you can try a query like this that really shouldn't take up much (if any) bandwidth.

SELECT?TOP (1) product_id
FROM tbl_products
WHERE?(1 = 1)

Change the product_id and tbl_products to your column and table name.

Test/Verify Datasource Connection

Josh,

You should be able to do this via the Administrator API, assuming you're on CF8 (I can't recall off-hand if it applies to CF7 but thought it did). Check out the live docs on the Admin API:

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=basiconfig_37. html

However, I think CFMXPrGrmR has a better option because to use the Admin API, you have to authenticate first, then run the various commands to verify your datasource, etc. In his query, it should be a very low hit to the DB (as he noted). Additionally, you can use the Application.cfc to your advantage and ensure that this query only runs once.

If you take CFMXPrGrmR's query (or the query from your post if that's preferable) and place it in your Application.cfc file (in the onApplicationStart method), it will fire only on the application start up. Set an application scoped variable of some sort to hold the results of your check (datasource.isgood or whatever) and check against that variable where needed in your application.

Just a thought ...

If found this on http://www.dreamincode.net/forums/showtopic72050.htm

The coldfusion.server.ServiceFactory is an undocumented and unsupported feature.?That means that with any patch, hotfix or update to ColdFusion, how the ServiceFactory works or is accessed could be changed.?Thus breaking any code dependant on it.

If you are comfortable with that condition many impressive functions can be accomplished by accessing the ServiceFactory, if fact it is so powerful that it is a prime reason many shared hosts will block the createObject() function.?Otherwise any customer on the machine could access this object and coop the entire computer for their own purposes.

But, if you are going to go the route of the ServiceFactory, using the Administrator API previously mentioned would give you the same functionality with the same performance in a supported and documented manner.

No comments:

Post a Comment