If Database A doesn't work, Use Database B

[Kevlar]

How would I go about doing this...

If "database a" fails, use "database b" if "database b" fails... show them the "so sorry" message. If I do the cftry/cfcatch setup, I would have to copy the same SQL over twice for two different cfquery blocks. Honestly, it's alot of SQL and having to remember to update both queries each time could be a problem.

Is there another way?

[tclaremont]

It kinda depends on your definition of "fails". You could use a CFTRANSACTION, and if the transaction fails, attempt the transaction on the second datasource.

[Kevlar]

Well the first thought that came to mind was to use this...

<cftry>

<cfquery datasource1>
** insert lots and lots of sql code here
</cfquery>

<cfcatch>

<cfquery datasource2>
** insert lots and lots of sql code here the same code as above
</cfquery>
</cfcatch>

</cftry>

If I do it this way, I have the same super chunk of sql code in two places. I would like just to have the sql in one place. This way I only have to worry about updating the sql in one place and well, I have double the size of my files to load.

[Guest]

Put your query into an include file

[Kevlar]

Could it be that I overlooked something that simple... I'm embarassed.

[Guest]

We've all done it before - and I bet we'll all do it again!

[Vurcease]

I'm a little late to the thread, but...

Most of my DB calls end up in components, which have a "datasource" property. If this approach were taken, you could use the try/catch to alter the datasource property...


<cfset DAO = CreateObject("component", "some.DAO.thing").init() />
<cftry>
<cfscript>
DAO.datasource = "ds1";
DAO.doQuery();
</cfscript>
<cfcatch type="database">
<cfscript>
DAO.datasource = "ds2";
DAO.doQuery();
</cfscript>
</cfcatch>
</cftry>


Same concept as the include approach, as far as modularity goes. I hope this alternative helps.


Adam Bellas | Full Sail University | Winter Park, FL