I once worked for a company that used to mix SQL and C. Then a new (sub)product was created, and the developers assigned to this decided to access database data through two layers: a set of C-libraries that called stored procedures for any access to data (both select and insert/update/delete). No application code talked directly to the database, and no C code ever touched a table. It took some additional effort to get things going, but when their product was rolled out, they were much quicker in making new feature requests (the companies business model was "we'll sell you our software for what ever amount we can get - even if we give it away; but we charge for new features"). Of course, them being good coders played an important part in the high turn out of new features as well. Loved working with them (as sysadmin/dba).

Another gig I did was for a large bank. There our group had no direct access to the (wholesale) customer database whatsoever. If we needed a new way to access data (select, insert, modify, whatever), we had to put in a request to the database development group, who'd supply us with (compiled) Java classes giving us the required access. Perhaps not so good for speed of development for one particular group of programmers, but if you have thousands of developers on hundreds of widely different projects scattered over dozens of locations all over the globe, controlling access to an extremely important and valuable database is important. And it makes auditors happier.

In my current gig, SQL and Perl code freely mixes. There are a lot of database classes, giving some abstraction, but that doesn't mean many programmers (including myself) don't mix SQL and Perl on a regular basis. For the current gig, being able to rapidly release new features is more important than writing beautiful abstraction layers. All code stays in house, and we have a small set of developers.

I cannot say what the best method is. Different business models and development environments lead to different solutions. I do think it's a mistake to think there's a "one solution fits all".