Testing Database Connection within Application
I've done my searching online, throughout several groups, and now I turn to you guys. I've been having trouble figuring out how to detect, from within my ColdFusion application, whether or not my database connection is valid. The DB setup is valid, but my goal is to automatically detect when the database server is temporarily down/unavailable in order to display an automatic "maintenance" message in the case of a crash or true maintenance when the database is offline. I've tried to put a simple query into my Application file and use CFQUERY's Timeout to force a quick (5 second) error within a CFTRY, but I'm inclined to think that it only has an affect on SQL Server when it's up and running, but taking too long to return a result. With my current attempt, it's taking up to 18 seconds to fail, which is just too long. Even using CFSETTING to set RequestTimeout to a lower value doesn't seem to have an affect. This was my attempt: SELECT TOP 1 THEME_ID FROM TBL_THEME If it matters, we're using MSSQL now, though we're planning to switch to MySQL eventually. I know there are timeout differences with CFQUERY's Timeout in conjunction with different DB drivers and I'd like to come up with a single, generic solution. I appreciate any feedback. Rob ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275850 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Testing Database Connection within Application
You could wrap your database call in a cftry block and then specify code to run when an error happens. code to run in event of database error (cfmail?) -Original Message- From: Rob O'Brien [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 10:38 AM To: CF-Talk Subject: Testing Database Connection within Application I've done my searching online, throughout several groups, and now I turn to you guys. I've been having trouble figuring out how to detect, from within my ColdFusion application, whether or not my database connection is valid. The DB setup is valid, but my goal is to automatically detect when the database server is temporarily down/unavailable in order to display an automatic "maintenance" message in the case of a crash or true maintenance when the database is offline. I've tried to put a simple query into my Application file and use CFQUERY's Timeout to force a quick (5 second) error within a CFTRY, but I'm inclined to think that it only has an affect on SQL Server when it's up and running, but taking too long to return a result. With my current attempt, it's taking up to 18 seconds to fail, which is just too long. Even using CFSETTING to set RequestTimeout to a lower value doesn't seem to have an affect. This was my attempt: SELECT TOP 1 THEME_ID FROM TBL_THEME If it matters, we're using MSSQL now, though we're planning to switch to MySQL eventually. I know there are timeout differences with CFQUERY's Timeout in conjunction with different DB drivers and I'd like to come up with a single, generic solution. I appreciate any feedback. Rob ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275853 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Testing Database Connection within Application
That's what I'm doing now. Unfortunately, the call to the database is taking too long to timeout (16-18 seconds). I was hoping for a way to speed that up to 3-5 seconds. -Original Message- From: Billy Cox [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 10:50 AM To: CF-Talk Subject: RE: Testing Database Connection within Application You could wrap your database call in a cftry block and then specify code to run when an error happens. code to run in event of database error (cfmail?) -Original Message- From: Rob O'Brien [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 10:38 AM To: CF-Talk Subject: Testing Database Connection within Application I've done my searching online, throughout several groups, and now I turn to you guys. I've been having trouble figuring out how to detect, from within my ColdFusion application, whether or not my database connection is valid. The DB setup is valid, but my goal is to automatically detect when the database server is temporarily down/unavailable in order to display an automatic "maintenance" message in the case of a crash or true maintenance when the database is offline. I've tried to put a simple query into my Application file and use CFQUERY's Timeout to force a quick (5 second) error within a CFTRY, but I'm inclined to think that it only has an affect on SQL Server when it's up and running, but taking too long to return a result. With my current attempt, it's taking up to 18 seconds to fail, which is just too long. Even using CFSETTING to set RequestTimeout to a lower value doesn't seem to have an affect. This was my attempt: SELECT TOP 1 THEME_ID FROM TBL_THEME If it matters, we're using MSSQL now, though we're planning to switch to MySQL eventually. I know there are timeout differences with CFQUERY's Timeout in conjunction with different DB drivers and I'd like to come up with a single, generic solution. I appreciate any feedback. Rob ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275862 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Testing Database Connection within Application
There is a decent chance that the admin api functionality gives you access to the same thing that happens when you click the "verify data source" icon in CF Admin. I've never looked, but I am curious now. Of course, if that worked, it would probably just tell you that a database engine was listening on that server and port, but it wouldn't give you any application aware information like are the database objects there, or is replication up-to-date etc. ~Brad -Original Message- From: Rob O'Brien [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 10:38 AM To: CF-Talk Subject: Testing Database Connection within Application I've done my searching online, throughout several groups, and now I turn to you guys. I've been having trouble figuring out how to detect, from within my ColdFusion application, whether or not my database connection is valid. ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275863 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Testing Database Connection within Application
Out of curiosity, how are you testing it? Are you turning off your database and then refreshing the page to see what happens? ~Brad -Original Message- From: Rob O'Brien [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 11:30 AM To: CF-Talk Subject: RE: Testing Database Connection within Application That's what I'm doing now. Unfortunately, the call to the database is taking too long to timeout (16-18 seconds). I was hoping for a way to speed that up to 3-5 seconds. ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275865 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Testing Database Connection within Application
Doesn't look promising anymore. http://livedocs.adobe.com/coldfusion/7/htmldocs/1738.htm That link tells you how to add data sources to CF with the admin API. The last step says: (Optional) To verify this data source later, click the verify icon in the Actions column. Note: To check the status of all data sources available to ColdFusion MX, click Verify All Connections. If there was a way to do it via the API they probably wouldn't tell you to go to the CF Administrator interface. ~Brad -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 11:30 AM To: CF-Talk Subject: RE: Testing Database Connection within Application There is a decent chance that the admin api functionality gives you access to the same thing that happens when you click the "verify data source" icon in CF Admin. ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275867 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Testing Database Connection within Application
Here is a stack trace of what CF is doing when it verifies a data source from CF ADMIN. Unfortunately these files are encrypted, so there is no (Legal) way to find out how the code looks. "jrpp-88" in Object.wait() - locked <138> (a coldfusion.server.j2ee.sql.pool.JDBCPool) at java.lang.Object.wait(Native Method) at java.lang.Thread.join(Thread.java:1009) at coldfusion.server.j2ee.sql.pool.ConnectionRunner.fetchConnection(Connect ionRunner.java:43) at coldfusion.server.j2ee.sql.pool.JDBCPool.create(JDBCPool.java:519) at coldfusion.server.j2ee.sql.pool.JDBCPool._checkOut(JDBCPool.java:444) at coldfusion.server.j2ee.sql.pool.JDBCPool.checkOut(JDBCPool.java:350) at coldfusion.server.j2ee.sql.pool.JDBCPool.requestConnection(JDBCPool.java :720) at coldfusion.server.j2ee.sql.pool.JDBCManager.requestConnection(JDBCManage r.java:123) at coldfusion.server.j2ee.sql.JRunDataSource.getConnection(JRunDataSource.j ava:138) at coldfusion.server.j2ee.sql.JRunDataSource.getConnection(JRunDataSource.j ava:125) at coldfusion.sql.CFDataSource.getConnection(CFDataSource.java:37) at coldfusion.sql.Executive.verifyDatasource(Executive.java:260) at sun.reflect.GeneratedMethodAccessor71.invoke(Unknown source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor Impl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at coldfusion.runtime.StructBean.invoke(StructBean.java:391) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:1662) at cfudflibrary2ecfm24721086$funcVERIFYDSN.runFunction(C:\blackstone_update s\cfusion\wwwroot\CFIDE\administrator\datasources\udflibrary.cfm:14) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:344) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.j ava:254) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java: 56) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:207) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:366) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:1772) at cfindex2ecfm1536982671._factor11(C:\blackstone_updates\cfusion\wwwroot\C FIDE\administrator\datasources\index.cfm:432) ~Brad -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 11:43 AM To: CF-Talk Subject: RE: Testing Database Connection within Application Doesn't look promising anymore. http://livedocs.adobe.com/coldfusion/7/htmldocs/1738.htm That link tells you how to add data sources to CF with the admin API. The last step says: (Optional) To verify this data source later, click the verify icon in the Actions column. Note: To check the status of all data sources available to ColdFusion MX, click Verify All Connections. If there was a way to do it via the API they probably wouldn't tell you to go to the CF Administrator interface. ~Brad -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 11:30 AM To: CF-Talk Subject: RE: Testing Database Connection within Application There is a decent chance that the admin api functionality gives you access to the same thing that happens when you click the "verify data source" icon in CF Admin. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275870 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Testing Database Connection within Application
Ok, you really got me curious on this one. I googled, and found an old CF dev journal article (http://pdf.sys-con.com/ColdFusion/beyond.pdf) with this function. It doesn't use the admin API, but the service factory. Now keep in mind anything in the service factory is undocumented and therefore technically unsupported by Adobe if you use it in your code. It also kicks butt in my opinion. Here is a good resource on it: http://spike.oli.tudelft.nl/jochemd/index.cfm?PageID=10 This function works for me to verify datasources: ~Brad -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 11:52 AM To: CF-Talk Subject: RE: Testing Database Connection within Application Here is a stack trace of what CF is doing when it verifies a data source from CF ADMIN. Unfortunately these files are encrypted, so there is no (Legal) way to find out how the code looks. ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275874 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Testing Database Connection within Application
> There is a decent chance that the admin api functionality gives you
> access to the same thing that happens when you click the "verify data
> source" icon in CF Admin.
Yes, you can get this from the adminapi:
// you must log in before accessing other adminapi components
adminObj = createObject('component',
'cfide.adminapi.administrator');
adminObj.login('yourCFAdminPassword');
// create the data object and test the DSN
dataObj = createObject('component',
'cfide.adminapi.datasource');
writeOutput(dataObj.verifyDSN('dsnName')); //returns boolean
I tested shutting down my datasource, and it retured false in about 1-2
seconds in my local environment.
HTH,
Rich Kroll
~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 &
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275875
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Testing Database Connection within Application
Well, what do you know! I'm quite curious why the live doc I found told you to use the CF Admin interface and didn't mention this. I assume this uses the same back-end functionality the service factory offers. ~Brad -Original Message- From: Richard Kroll [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 12:07 PM To: CF-Talk Subject: RE: Testing Database Connection within Application > There is a decent chance that the admin api functionality gives you > access to the same thing that happens when you click the "verify data > source" icon in CF Admin. Yes, you can get this from the adminapi: ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275876 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Testing Database Connection within Application
>I tested shutting down my datasource, and it retured false in about 1-2 >seconds in my local environment. This looks like exactly what I'm looking for and I really appreciate the effort. I tested the same way. I stop the SQL Server service and then refresh the script. Unfortunately, this still takes 16-18 seconds to return False (tested locally). If you're reporting 1-2 seconds, I'm thinking there's another factor involved. Any ideas? Rob ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275885 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Testing Database Connection within Application
I think Select null >From table Is less taxing, and produces the same results -- Scott Stewart ColdFusion Developer SSTWebworks 7241 Jillspring Ct. Springfield, Va. 22152 (703) 220-2835 http://www.sstwebworks.com -Original Message- From: Brian Polackoff [mailto:[EMAIL PROTECTED] Sent: Friday, April 20, 2007 1:02 PM To: CF-Talk Subject: RE: Testing Database Connection within Application Rob, This may sound simple, maybe too simple, but how long does your application take to error on a cfquery if the table isn't available? I useed a different approach, rather then testing to see if the DSN was valid, I simply tried to query from it. I have just ran into this problem myself and here's how if configured the application. Hope it helps. In application.cfm in the login code as I create all the session variables do a.. Select * from testdsConn Again, hope it helps and sorry if this answer in some form has already been given, Brian -Original Message- From: Rob O'Brien [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 5:17 PM To: CF-Talk Subject: RE: Testing Database Connection within Application >I tested shutting down my datasource, and it retured false in about 1-2 >seconds in my local environment. This looks like exactly what I'm looking for and I really appreciate the effort. I tested the same way. I stop the SQL Server service and then refresh the script. Unfortunately, this still takes 16-18 seconds to return False (tested locally). If you're reporting 1-2 seconds, I'm thinking there's another factor involved. Any ideas? Rob ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275944 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Testing Database Connection within Application
Rob, This may sound simple, maybe too simple, but how long does your application take to error on a cfquery if the table isn't available? I useed a different approach, rather then testing to see if the DSN was valid, I simply tried to query from it. I have just ran into this problem myself and here's how if configured the application. Hope it helps. In application.cfm in the login code as I create all the session variables do a.. Select * from testdsConn Again, hope it helps and sorry if this answer in some form has already been given, Brian -Original Message- From: Rob O'Brien [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 5:17 PM To: CF-Talk Subject: RE: Testing Database Connection within Application >I tested shutting down my datasource, and it retured false in about 1-2 >seconds in my local environment. This looks like exactly what I'm looking for and I really appreciate the effort. I tested the same way. I stop the SQL Server service and then refresh the script. Unfortunately, this still takes 16-18 seconds to return False (tested locally). If you're reporting 1-2 seconds, I'm thinking there's another factor involved. Any ideas? Rob ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275943 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Testing Database Connection within Application
Rob O'Brien wrote: >> I tested shutting down my datasource, and it retured false in about 1-2 >> seconds in my local environment. > > This looks like exactly what I'm looking for and I really appreciate the > effort. > > I tested the same way. I stop the SQL Server service and then refresh the > script. > > Unfortunately, this still takes 16-18 seconds to return False (tested > locally). If you're reporting 1-2 seconds, I'm thinking there's another > factor involved. Any ideas? Maybe a 'stealth mode' software firewall. Attach a packetsniffer and see what really happens. Jochem ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275973 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
