We are slowly wrapping lunch here at CFUnited Express, and the guy next to me brought up the issue of handling a database that is down. He was considering running a query wrapped in try/catch on every request, but that would be a bit slow. I did a quick check and was pleasantly surprised to see that DSN verification (the same thing you get in the ColdFusion Administrator) is available via the Admin API. Specifically, datasource.cfc, method verifyDSN. You can have it optionally return a message with error details if things go wrong. Here is a quick sample:
<cfscript>
adminObj = createObject("component","cfide.adminapi.administrator");
adminObj.login("admin");
datasource = createObject("component", "cfide.adminapi.datasource");
res = datasource.verifyDSN("peanutbutterjellytime", true);
</cfscript>
<cfdump var="#res#">
Obviously the admin password would be different in production, but you get the idea. The result was:
coldfusion.sql.DataSourceFactory$DataSourceException: Datasource peanutbutterjellytime could not be found.
Setting the second argument to false makes the method just return a simple false.
Archived Comments
This is really cool / helpful. Thanks!
I tried it on my dev box and when invoking the admin login ...
"adminObj.login("admin");"
I'm getting
ErrorCode: CFACCESSDENIED
Message: The current user is not authorized to invoke this method.
Is 'admin' your pssword?
Uhhh ...
Yeah ...
Sure ...
Hey ... It Works Now!
;)
Here's a challenge for you (which you may have already done; haven't done a search yet).
I am creating a new server with CF8 and moving content from CFMX. About 20 databases defined in CFMX.
Assuming the same folder/file structure on both servers, is there a quick way to get the databases defined in CF8 -- an 'export' of some type of the data sources from the CFMX server and then an import into CF8? That would save a lot of time in setting up the new server.
Probably related to that is a way to document data sources into a text file.
CARS. Feature built into CF since CFMX. Allows you to backup/restore settings. Not sure if it will work from 6 to 8, but you can try.
CARS sounds interesting, but down at the bottom of the CARS screen when you create a new one: "ODBC Datasources cannot be included in J2EE archives". And since the 'developers' (the quotes are intentional....) around here use Access databases, CARS won't work.
Ouch. Guess you are out of luck. Sorry. I've heard you can copy the XML file that contains the dns info, but... that kinda concerns me.
Verification just runs a query, so not sure that there's any difference between using that method (which requires the admin password) and try..catch with a SELECT 1 query in it (which anyone can do).
I believe that some of the driver config screens actually let you specify the SQL used for the verification query, but I might be remembering incorrectly.
Interesting. Yep, I see verification sql in advanced settings, although it is blank. I wonder what SQL it uses then. Perhaps some default 'get tables' type thing that is guaranteed to work in the db being used.
Well, still, good tip I think. You know it will always work no matter what tables you end up in there. :)
@Ray
Thanks for this one - a cool and quick trick!
I'm sure it's making some sort of meta request that is specific to the DB running rather than if the table space is actually available. Our DBA takes our table space offline to do cold backups in our dev environment, so even though the db was alive, the app would bomb when trying to write data to the table.
In our java app, we run a select * from tablename where 1=0 which actually hits the table, but returns nothing without any cost. You can throw some error handling in there, but we felt this was more graceful code wise.
A long story short, I would recommend using a query that hits a table intended for the app instead.
I'm trying to think of where this would be useful at the same time that you'd actually have an admin password.
I could totally see using it with a nice friendly installer script of some kind, testing to see if the DSN is in place before attempting to use it... but that probably wouldn't have the admin password.
Maybe if you were creating DSNs on the fly? I know I was working on a script the other day to basically automate a fresh CF install to meet our security standards... turning off stuff we don't use, requiring username & password, setting complex password for admin, creating individual users for all the developers, creating DSNs and mappings and so on... I suppose I could use something like that after the case to test & see if the DSN was successfully added.
...and then I reread the first line and realize you're not really testing to see if it exists, but how to handle it if it's currently *down* for some reason... and things make more sense.
Ignore me. No coffee today. :)
Though it probably uses the same underlying methods, you can use the java class to get the same result:
dsService = CreateObject("java", "coldfusion.server.ServiceFactory").DataSourceService;
res = dsService.verifyDatasource("vegemiteisgoodalso");
Been using this for awhile...surprised you don't need the admin password for it.
<cfset dsService = CreateObject("java", "coldfusion.server.ServiceFactory").DataSourceService />
<cfset dataSources = dsService.getDataSources() />
<cfloop list="#structKeyList(dataSources)#" index="ds">
<cfset datasource = dataSources[ds] />
<cfoutput>
#datasource.name# : #datasource.username# (#datasource.password#)<br />
</cfoutput>
</cfloop>
I was wondering if there is equivalent code for Railo. I looked around and all that may be possible is to instatiate "com.microsoft.jdbc.sqlserver.SQLServerDriver". I may be totally wrong, but has anybody ported this code to Railo?
I'm curious if there's any way to pass in an encrypted password into the adminObj.login() method instead of it being simply plain text.
When I do a cfdump var="#adminObj.login#" it returns the list of arguments that the method takes, and one of them is salt.
Does anyone have any ideas?
well, there's always #decrypt(MyEncryptedPassword,crypto.key,crypto.method,"hex",crypto.ivector)#
At least then you don't have a password sitting in your code.
I'm looking forward to seeing how I can wrangle this verifyDSN() into a monitoring solution of sorts. Putting this code somewhere that gets hit by the CF scheduler every few minutes might be a halfway-decent way of setting a flag in the server scope, which in turn tells all the applications running on the server "don't bother trying that DSN!"
Thanks again for your help on this, it really got my wheels turning!
Cheers,
"The Guy Who Was Next To You"
Can anyone tell me what my problem is?
I can call/use createObject(), but I'm not able to call VerifyDSN() or setMSAccessUnicode(). I get the error "The current user is not authorized to invoke this method." I'm using ColdFusion 8. It works on my local machine, but not on my server
<cfset datasourceService = createObject("component", "CFIDE.adminapi.datasource")>
<cfif datasourceService.VerifyDSN(datasource_name) eq false>
I assume you did the login portion first, right?
Previously working code has stopped working when using the cfide "datasource" feature with the same error about "user not authorized". I suspect one of the recent security hotfixes has broken something. I tried several different variations including creating a users specifically for API calls, also adding the "admin" username but nothing helped. I think this is just a bug that was introduced in the hotfix.
I'd encourage you to file a bug report if you can.
I was experiencing the "The current user is not authorized to invoke this method" error this morning and have spent an hour or so to finally find out what caused it to not work in my case.
I use a common code set which includes the Application.cfc from a CF Mapping - So, in the application.cfc in the actual website I do this:-
<cfinclude template="/UberDirectory/Application.cfc">
Then, in the /UberDirectory/Application.cfc, I was initialising a CFC which checks if the datasource was created for the website. The datasource checking code attempts to log into the Admin API and check & create if necessary the datasource.
This has previously worked without fail for me - But in this instance it failed!! I was doing two things wrong - Firstly, the CFC should only be called in the Application.cfc in the onRequestStart section as the Application had to be initialised first - This is maybe because I've invoked the application.cfc in a "non-standard" manner.
Secondly, once I'd moved the CFC invocation into oNRequestStart I saw the following error:-
The string COOKIE.CFAUTHORIZATION_uber-directory is not a valid ColdFusion variable name.
I had this as the app name .... <cfset this.name = 'uber-directory'>
Changedthe dash to an underscore and I was away and could once again check the datasources
Hope it helps
Martin
Thanks for sharing that, Martin.
No probs