I have never had any luck with doing this. Under load this fails miserably as each request ties up a thread. Sucks. I've thought about making use of say TNSping, we use Oracle, to see if the DB is up or not, but have not had luck or time yet for that.

One other thing I have seen is if connection pooling is on for a DSN and your DB is failed over to a backup one or your DB is restarted, CF chokes. Seems to keep some of the connections pooled longer than speciifed in the settings.

How did it hang under load? If done in onAppStart, it should be single threaded. If done in a simple cfif, it could be done with locks. Also, the cferror approach should be fine as well.

ah, we are not using CF7 yet. onAppStart could be the ticket for sure. Another reason to upgrade!

Any approach that actually connects to the DB to determine if it is alive is an issue I have found. Its a third party call afterall.

Hey Ray,

It can be done w/o reliance on any existing tables etc. by trying to get a JDBC connection via ServiceFactory or the AdminAPI:

<!--- Using ServiceFactory --->
<cfset DatasourceName = "taskBlaster" />
<cftry>
<cfset conn = CreateObject("java","coldfusion.server.ServiceFactory").getDataSourceService().getDatasource(DatasourceName).getConnection() />
ServiceFactory: You've got a connection.
<cfcatch>
ServiceFactory: No Connection.
</cfcatch>
</cftry>

<!--- Using Admin API --->
<cfset DatasourceName = "taskBlaster" />
<cfset ColdFusionAdminPassword = "password" />
<cfset DatasourceDbUserPassword = "password" />

<cfset cfadmin = createObject("component","cfide.adminapi.administrator") />
<cfset cfadmin.login(ColdFusionAdminPassword) />
<cfset datasource = createObject("component","cfide.adminapi.datasource").getDatasources(DatasourceName) />

<cfset driver = CreateObject("java", "java.lang.Class") />
<cfset driver.forName(datasource.class) />
<cfset driverManager = CreateObject("java", "java.sql.DriverManager") />

<cftry>
<cfset conn = driverManager.getConnection(datasource.url, datasource.username, DatasourceDbUserPassword) />
AdminAPI: You've got a connection.
<cfcatch>
AdminAPI: No Connection.
</cfcatch>
</cftry>

-Joe

If you are using MS SQL Server, do a query like this:

select TABLE_CATALOG from information_schema.tables where table_name = '(your table name)' AND TABLE_TYPE = 'BASE TABLE'

This will very quickly tell you if that table exists or not. If recordcount eq 0, then no table exists!

Have fun,

Justice

Here's another approach as well: define an Application variable called "databaseVerified" or something. Have a scheduled task run every 5 or 10 minutes that runs a test query on the database and updates the value of this variable. Then, on each page request simply check this value. If its false then show your error page.

The nice thing about this approach is that it is "mostly real time" but puts a much lower load on the database server than testing the connection on each request. The drawback is that if a user hits the site while the database is down and BEFORE the app var gets swapped then they will get an error message. This type of DB error should be rare in a production environment so the tradeoff might be worth it.

Joe, nice use of the SF. interesting. Has this been tested against Oracle at all. We have a CF server that runs about 20 apps, so its difficult to say have one point to test if the DB server is up, rather we try to do this in a small subset of the 20 apps that are 'mission critical'. Typically I usually see that a test like this takes forever to return back to CF if there is a DB issue. Thus threads tend to pile up (we are on CF6, can't use onAppStart). Add to this, what happens if its a network issue? If the DB server is accessible, but the instance is down? Everything is smoking but the DB server is overloaded?

Hmm...use of onAppStart for this test. What if the DB becomes unavailable after onAppStart kicks off?

I basically gave up on this whole thing awhile back.

DK

Well, its not necessary to do the scheduled task, just let CF do it with a cached query. Like this:

<cftry>
<cfquery name="CheckOrdbill" datasource="Portal" cachedwithin="#CreateTimeSpan(0,0,30,0)#">
select * from information_schema.tables where table_name = 'ORDBILL'
</cfquery>

<cfcatch type="database">
There was an error, help!
<cfabort>
</cfcatch>
</cftry>

Douglass, as for why to only check on startup - you don't have to. You could simply use cferror/onerror. The more I think about it, the more I'd say that is probably best.

You should use cferror/onerror _anyway_.

Hey Douglas,

I've used it against Oracle, MySQL, and MSSQL - the database platform doesn't matter too much, because it's just JDBC.

-Joe

In my view the best way to handle this is with a try/catch around every query. This is basic error handling.

> Well, its not necessary to do the scheduled task, just let
> CF do it with a cached query

I guess that would work as well, I didn't even think about it.

Still, I personally like the scheduled task approach because it decouples the application logic from the "is the system working OK?" logic. I can add and remove different kinds of tests, error conditions and notification logic to the scheduled task without ever touching the main app's code.

I guess the right approach depends heavily on the specific situation at hand.

Gus, I disagree with that. I'd just use application error handling instead. _Unless_ you have very specific actions to take based on the sql. IE, do X if query A fails, do Y if query B fails, etc.

As a minor tangent, if you use CFMX on JRun (multiserver,j2ee configs) you can create and use J2EE datasources from the JRun console. Once J2EE datasources are added, you can manually edit the server's jrun-resources.xml file for fine control over the datasource properties, much more control than normal CFMX JDBC datasources offer.

Specifically, you can set a few helpful parameters to verify that you have a connection before the connection is used in your application. Add the validation-query parameter to the datasource properties in jrun-resources.xml and that simple query will get run before using the connection [validation-query]{your simple sql statement here}[/validation-query]

Documentation on this and other datasource properties are found here:
http://livedocs.macromedia....

Ray,

Granularity is precisely why in my view try/catch is the better way to go. It gives me the flexibility to handle specific situations as desired.

Of course an argument can be made to use application-wide handling and add try/catch as needed but I like the consistancy of using try/catch everywhere.

I also find wrapping code blocks in try/catch to be a tremendous help in debugging apps as I am building them.

Robert Blackburn blogs about DB unit testing.
http://www.rbdev.net/devblo...
He's talking about AFTER you get past the "Is the database up?" part of testing.
Can someone in plain english talk about xUnit testing?
Here's my point of view:
You have a table called customer, so there's a test that says
SELECT * FROM Customer WHERE CustID=1 which should always return your company name (for instance).
At the top of every form is a green submit button. When you press it, all the tests are run and if any fail, the button turns red.

Create a procedure like this
create procedure dbo.IsDataBaseUp
as
set nocount on
SELECT @@SPID as Spid
set nocount off

If the Spid is not greater than 50 ( a value below 51 is reserved for SQL server and can not be returned to CF anyway) your DB is down

Gus, the use of cftry/cacth around all the cfqueries seems logical, but for dead DBs this is useless, IME. Under any load all requests to any of these queries will tie up a thread forever eventually bringing your server to its knees.

Steven, I have been wondering about using JRun DSNs. I'll have to try this out. How about a future CF version that just uses these in the first place, eh?

DK