Skip to main content
J
josheby
Inspiring
July 20, 2009
Question

Test/Verify Datasource Connection

  • July 20, 2009
  • 3 replies
  • 4699 views

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:

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

<!--- attempt to connect to datasource --->
        <cftry>
            <cfquery datasource="#this.getName()#" name="test">
                SHOW TABLES;
            </cfquery>
            <cfcatch type="database">
                <cfif displayError>
                    <cfthrow message="Datasource Connection Failed" />
                <cfelse>
                    <cfreturn false />
                </cfif>
            </cfcatch>
        </cftry>

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

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:

<cfif datasource.isAvailable() >

     <!--- DO SOME DATABASE WORK --->

</cfif>

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

This topic has been closed for replies.

3 replies

J
joshebyAuthor
Inspiring
July 20, 2009
If found this on http://www.dreamincode.net/forums/showtopic72050.htm

Any thoughts on this?

<cfset yourDSN = "redhead">
<cfset factory = createObject("java", "coldfusion.server.ServiceFactory")>
<cfset datasources = factory.getDataSourceService().getDatasources()>
<cfif structKeyExists(datasources, yourDSN)>
    <!--- show the datasources that do exist --->
    <b style="color: #008000;">Datasource Found!</b><br>
<cfelse>
    <b style="color: #ff0000;">Error - Datasource not found</b><br>
    <!--- show which datasources do exist --->
    <cfoutput>
    Existing datasources:<br><br>
    <cfloop collection="#datasources#" item="dsnName">
        #dsnName#<br>
    </cfloop>
    </cfoutput>
</cfif>
ilssac
ilssac
Inspiring
July 20, 2009

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.

C
craigkaminsky
Inspiring
July 20, 2009

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 ...

A
Anonymous
July 20, 2009

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices