Coldfusion Report Builder - How can you set different datasources externally between prod/staging/dev?

Question

Coldfusion Report Builder is great.

One small issue. We use ANT+CFANT to deploy.

When we create the report, say in a datasource called MyApp_dev on a dev box.

Our other server is the production server. It also contains a staging build to ensure everything is going smoothly before we publish to live. (thanks to Al Everett for bringing this clarification to my attention.)

Everything works great when the report is created.

We deploy the report to our staging server, which has a datasource of MyApp_Staging. That server also, may or may not, have the live app working under MyApp_Live. Ant pushes the update to Staging just great.

Run the report, crashes and burns. Why?

It seems the report is looking for the MyApp_Dev data_source, even though the application is using the MyApp_Staging datasource.

In digging around I found a few approaches, I would like to do this one, final, ideal way from the beginning instead of having to go back to do dozens of reports differently when I have a new Aha! moment.

1) Obvious: Pass in the datasource in to the cfreport tag. Doesn't work for ColdFusion Builder Reports as of v8, or v9 as tested on Linux.

2) Most realistic option (but painful) so far: Pass in the query as an object into the ColdFusion Builder report. Let's think about this:

1. Create the Report with the report builder to my heart's content using the RDS, etc on my local box.
2. When I'm done, copy the query into a snippet of code, or into a database column to be dynamically be injected at runtime with correct datasource.
3. Modify my "run report" event to find the query from the database column, insert it into another dynamic cfquery and potentially... evaluate (!?!) it? Fun side is I can set the cfquery datasource to what I would need for each environment.
4. When I modify the report's columns in CF Report Builder, I always have to update the query in the database. Is there a snippet of code that can extract this for me? Hmm.

3) Less than ideal. Suck it up and let all the reports in staging run off the live server. Maybe copy the live data into staging (sans structural changes) to let it seem similar.

Are there any eloquent ways to accomplish the above?

Thanks in Advance!

Answer 1

If you have different dev/staging/production boxes, why not just use the same datasource name on each? That'll save you from having the code figure out where it is.

Because security concerns at my current assignment preclude me from using RDS, I use option 2 as a matter of course. I also like it as it makes it easier to debug.