I'm in total agreement about your distaste for what passes as "a programming language" inside most DBMs, they are usually very restrictive and clumbsy to use. Now, if MySQL wanted to really steal a march on the rest of the world, they would embed a Perl interpreter--or better still, a Perlish, domain-specific language using Parrot--inside their engines for use in stored procedures. That could really be something.

Notionally, an SP is just a named method that takes some arguments and (optionally) returns some results. One of the best things about DBI is it's effect of isolating application code from the variations in the specific DBM.

If DBI defined a standardised routine for calling an SP, binding this set of arguments and returning a statement handle. The statement handle is a cursor that would provide for all the existing ways of retrieving those results. In this way, it would be just as possible to provide a standardised method for calling SPs, as it is for other operations.

I still don't buy the idea that stored procs can provide protection from schema changes of any significance.

This is really the same argument as not providing (externally callable) accessors/mutators to attributes in objects. Just as when you write application code to manipulate attributes directly, you effectively undo a good part of the benefit of OO, so accessing tables and columns by name is similarly flawed.

Just as we shouldn't write code that queries the state of an object and then acts upon that state, instead we should be asking the object to do something for us, so we shouldn't be querying values from the DB, modifying them and then updating the DB to reflect the change. We should be asking the DB to 'add this item to this invoice', not 'querying the price of this item' and then 'adding that much to that invoice'. With the latter method, if the price of the item changes between us querying it, and our updating the invoice, we've made an error.

Perhaps not a good example as it can be legitimately argued that allowing prices to change on the fly that way could lead to problems of the customer being invoiced a different price to the one he agreed to--though they probably wouldn't complain if it was a reduction:).

A better example is the stock level changing. If at the point the customer commits to purchasing the items on their pick list, all those items should be added to the invoice, and any items no longer in stock (sold out between the time they added it to their list, and the time they commited to purchasing it), should be detected and ommited from the invoice raised.

There are all sorts of holes in that example, but the point is that by performing the business function inside thde DB rather than in the application code, you get far fewer race conditions than by embedding the same logic in application code.

I don't expect that to convince anyone, but coming up with short, accurate examples is hard.

It's most instructive to try and code objects in multi-tasking environments where the actual value of any state queried from an object can change even before the querying code actually receives the value. In these environments, the only way to achieve synchronisation, is to ask the object to change it's state. All those "OO" designs that are little more than C-style structs with setters and getters rapidly fall in a heap in an multi-tasking environment where objects can be shared.

This is exactly the same problem, as with DB code that queries and manipulates values, rather than requesting the state-changes to high-level business objects. The state of any value queried can change before an application-modifed value is updated to the DB. Record locking can help, but in the end you set yourself up for a mess of race conditions that are a bugger to sort out.

IMO the only thing harder than good OO design, is good DB design. You generally have many more restrictions within which to work with the latter.