A reader asks:
What is the best way to determine if your CF server can access the datasource defined in app properties? Basically I want to have and if then clause that continues as usual if the database is available but redirects to a site maintenance page if the db is unavailable.
Well, I'm not sure if I can say what is the best way, since it will depend on your application, but I can think of a few solutions to this. The first solution is to run a simple query in your application start up. This can be in either onApplicationStart, or wrapped in a old-style Application init setup in Application.cfm. Consider the following:
<cftry>
<cfquery name="test" datasource="cflib">
select top 1 id
from tblUDFs
</cfquery>
<cfcatch>
<cfinclude template="dbdead.cfm">
<cfabort>
</cfcatch>
</cftry>
This simply returns a very small query and if anything goes wrong, a file is included and execution aborts. As I said above, you probably want to run this when the application starts up, not on every request.
There is another way to handle this that would work on every request. Simply use the cferror/onerror approach. You can check the error type, and if it is database, load a different message then you would for a normal error. One thing you want to check though - a bad SQL statement will also throw a database exception. If you check the detail, however, you will normally see a message that will tell you if it was a connection problem, or a SQL problem. The following message is what my box said when I tried to connect to SQL Server and it was shut down:
[Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
When I used a bad SQL statement, I got:
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'tblUDFsdd'.
Archived Comments
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