Why there may be a place for Direct SQL

Over at Service Oriented Shenanigans Graham’s written a good, provocative piece titled Why Direct SQL is a very stupid thing to do. I’m happy to be baited… 😉 I started to respond there, but it turned into something worth a full post. Hopefully trackback pulls the whole conversation together like it’s supposed to…

Graham argues that because Siebel implements referential integrity through the repository rather than through database rules, it’s idiotic bypass all these rules and run SQL directly against the database. I’d make a few points:

• If you’re using Siebel Remote, then direct SQL could be additionally stupid. Any changes made directly to the database are not be propagated to the transaction log, so won’t be routed to the local databases, so your server will be out of sync with your remote clients… Nasty!

• If, however, you’re NOT using Siebel Remote, or you’re re-extracting all your local databases anyway, then I’d argue that there is a place for direct SQL. A limited place, sure, requiring utmost care, diligence etc etc, but at the end of the day it’s just another tool to only be used when appropriate. Siebel Expert Services will happily run direct SQL when the alternatives are ridiculously cumbersome. The alternatives (e.g. EIM, EAI) are also quite capable of significantly damaging your database and also require an appropriate level diligence: developers shouldn’t lapse into thinking that just because things are being done with the ‘proper’ methods they can’t do harm (EIM Delete in the wrong mode, anyone?).

• If you use direct SQL to insert a record, then you won’t get a Siebel Row Id. The procedures to generate a Row Id are in the database and could be called directly, but this really is idiotic. (I actually worked on an implementation where Siebel Expert Services had done this – hmmm…)

So don’t use direct SQL to insert records, and don’t break your referential integrity, but do remember that SQL is just another tool, with pros and cons to be weighed against the alternatives. And don’t come crying to me if you break your database and your architect justifiably calls you an idiot.