By curiosity, is there any way to intercept a cfquery to modify it before it passes on to the database? I'm already expecting a "no". Lol

Modify it in what way? You could do a QoQ on it and then pass that result along.

in standardvcfml I don’t think there is. In coldbox there is, if you use qb. Qb can be modified by interceptors. And it is quite simple to write your own <cf_myOwnQuery> which is just doing a qb.raw(myquery). We do it all the time. Not exactly modifying the sql here, just the datasource

What are you wanting to modify that you can't just modify when generating the sql for the query?

Thanks, I thought about QB and Coldbox as well, but before moving to these possibilities, I was thinking that maybe there might be a native approach. If there is a way to intercept any cf tag, possibly. I was also thinking about rewriting all of the cfquery tags to be "cf_query" and then write up an interception in this manner.

you can write your own JDBC driver (probably not really a fun time though)

You could also do custom tags. So use your own cf_myquery and inspect the thisTag.GeneratedContent before you submit the real cfquery in the tag processing. In that case you don’t need box or qb

@Scott Bennett I'm thinking of solutions for a colleague that works for a different client. Apparently the client has two databases for two different clients and they want to write CRUD exactly the same to both databases. Triggers are not a good solution in this case. So, I was thinking that if there is a way they could tap into the cfquery tag by intercepting it, then we could write code to write to both databases and this would become the least evasive approach so that cfqueries would not need to be modified throughout the application.

so you just need to change the datasource based on the client that is logged in?

So actually you are just duplicating your queries and send it to two different datasources?

ah, you want to write every query to both dbs?

@wil-shiftinsert right, custom tags is what I was mentioning by renaming it from cfquery to cf_query, but didn't actually say it, sorry about that.

That would be quite trivial to do if you are using the custom tag approach

yeah, in that case I would probably write a custom tag

from what I'm hearing, yes, that's what they are wanting to do

or perhaps a cfc function

Funny, I haven’t touched custom tags in years, but just today I was looking at it again. I was quite an expert in custom tags 15 years ago haha

I haven't touched them in quite a few years, either. However, this case seems to still keep custom tags valid when needed. 🙂

Using it for some html generation now, but this could also be an interesting use case.

Good info. Thank you, all!

I am not sure that

<cfquery>

(or: yikes inventing a

<cf_query>

custom tag!) is the best place to deal with this. You ought to have some manner of DB abstraction layer between your business logic tier and the storage tier (for brevity's sake, let's call your storage tier classes DAOs). Your storage tier just has methods that pass params to

<cfquery>

calls. Your business tier talks in domain objects to a repo; your repo deals with the mapping between domain objects and values for the storage tier (so the methods with the

<cfquery>

calls are pretty dumb. If you have that, it would be trivial to implement a decorated DAO which is itself initialised with a collection of actual DAOs that are configured with each different DB connection you need. This decorated DAO iterates over its DAO collection, calling the same method/values on each. Job done. The business tier doesn't need to know anything about this. Even the repo doesn't need to know: it still thinks it's got a DAO. The DAOs themselves don't need to know. All you need to change is initialising the repo with the "multi-DB-DAO", and... it just works. It also keeps your concerns well separated and well defined.

Yeah, I agree, Adam. I am thinking about how much time any of these ideas take to implement and I think the quickest turn-around would be the custom tag idea to get them running correctly. Long term, your idea with the use of QB and/or CB would be good to implement.

The thing is that at some point one needs to start designing one's app properly. Just deferring it and deferring it and deferring it just leads to a ball of shite.

I guess it would be OK if clients are willing to forego the short term for the long term. If the client is willing to do both by meeting their own client demand and to then immediately follow up with development on the long term solution, that would work well.

Adam, you are too romantic 🤣. Customers wanting a well designed solution instead of a quick solution.

Customers wanting a well designed solution instead of a quick solution.

This is - often - a red herring. The customers don't know how long things take, and they can seldom understand the lack of correlation between how quick something is to say, compared to how long it takes to do. However they are almost always prepared to take our guidance when saying "that's more work that it might sound, but def doable". They rely on us to tell them how long something will take, and the cost it will be to them. If you say something with take x time, they will go "yeah OK", or "actually screw it, it's not that important". They won't second-guess the nature of the work involved to deliver the solution. That's your job. It's almost always the developer screwing themselves out of the opportunity to do their jobs well. It's up to us as developers to provide professional level services to our clients; and this includes thoughtful design, testing, etc.

I agree with that, Adam. I look at the issue from a management/client point of view. "What would it take to make everyone satisfied?" If we compromise as devs, then chances are likely that the client will, too. This sometimes can allow us to move forward, without the client deciding to cancel the project because the dev team only gives the long-term solution, and can allow the client to quickly move forward as well. It even allows for more work for devs, actually, and the payoff is that the client can have something immediately rather than waiting for the long term solution when they asked for it ASAP.

I think part of keeping the client happy here is iterative delivery, rather than big bang. Give them a release every week or something that improves something. That way they will feel more invested, and way less "I have NFI what the devs are up to cos I can't see anything and I'm getting itchy". Obvs not so easy with a low-level project like "distribute database writes to multiple DBs in one hit", but I'm sure it's doable somehow.

That is sensible, Adam.

The weekly iteration with a Friday 0,5-1,5 hour Teams meeting is something I use with a client in a major relaunch of their site atm. Generally works fine, but sometimes I need to stick in a few "hardcoded sample data" to get the process moving and figure out how the heck the many exceptions and details need to come together for a "_simple table list of client-side configured items (which might exclude other selections sometimes) neatly presented "like Apple" (or "like AUDI"? "Volkwagen?)") in a PDF_". 🙂