I agree about the "little language". However, I have not seen OOP do that better than procedural...
I have. When requirements change, they apply force on design asymetrically. OO allows the design to slip differentially with a minimum of effort; procedural grinds gears and breaks teeth.
...and second SQL is generally a pretty compact language. It is hard to beat it on code-size (even though SQL stinks in many ways). Jones' study hilighted [sic] that pretty well.
SQL is compact because you need further code to process its results, translating those results into usable structures--this is the "do_stuff_with_row" which you conveniently left out. By mixing SQL directly into your application language (whether "procedural" style or "object-oriented"), you force the developer to manage two languages at once, manually handling the translation step between the two every time they make a choice about development. A good ORM hides that, freeing the developer to focus on the problem domain.
Sometimes one can use a combination of SQL and "custom language" routines. Example:
func displayEmployees(criteria) {
sql = "SELECT * FROM emp,other_tables WHERE "
sql .= " [join stuff] and (" . criteria . ") ORDER BY foo"
rs = query(sql)
while (row = getNextRow(rs)) {
do_stuff_with_row(row)
}
}
Now we can pass it a boolean criteria expression to filter our rows.
OOP: Great! You've just introduced a security hole by manually interpolating strings.
TOP: Oh, but we'll never pass it unsafe criteria.
OOP: And that's documented/enforced where, exactly?
TOP: Fine, we'll add a comment in the procedure, documenting exactly what is considered "safe" criteria.
OOP: Good. But you've got a staff of 10 developers. Who's got time to read each others' embedded comments in a library call?
TOP: Fine (adding a line or two of code), we'll enforce it within the procedure.
OOP: Wonderful! You missed a spot.
TOP: Fine (another 10 lines of code).
OOP: OK. Now do that in *every* procedure with embedded SQL.
TOP: Fine, we'll abstract that into an "SQL checker" tool (adding 1000 lines of code).
OOP: Nice! You've just written an SQL parser/validator. But you slowed down your DB I/O by a factor of ten (or more).
TOP: Fine, we can speed that up greatly by validating each component *before* we construct the SQL string. We just need a little extra structure to say "this atom is the tablename" and "this atom is a boolean criteria string" and "this atom is the ORDER BY clause".
OOP: Hmmm. It works, but you've got this huge bottleneck procedure with 38 arguments.
TOP: No, I reworked it last night. Now I have 74 different procedures with slightly different argument lists for every occasion.
OOP: Oog. That's really confusing. Can't we build our list of SQL atoms incrementally? Maybe there's some way to collect all those components into a single structure and then just pass that.
TOP: Fine. I worked 20 hours a day for 2 weeks, but it's done: a module which builds safe SQL strings out of potentially unsafe components, and it's developer-friendly. Happy now?
OOP: Congratulations. You've just written your first ORM.
It is tough to do these kinds of things with "flat" API's.
It's tough regardless. Why not let a team of experts do the tough parts once and let everyone benefit?
Plus, one can develop and test it as SQL first, and then paste it into the routine.
I'd rather have a complete test suite. Did you test the corner cases? Did you test malformed data? Did you test code coverage? Unless you've found (or written) a complete testing framework/harness within your database as well... in which case, my hat's off to you. But I haven't seen one yet.
TOP may be fine for developers who have never worked on anything other than a small project, who care nothing for portability, maintainability, or software engineering (as opposed to software construction). Which is fine--lots of code is of the home construction type, where you build a small one-story edifice, live in it for life, and patch as you go. It's an asset on the books, and depreciates. Just don't expect it to give your corp a competitive advantage--that requires engineering, design, and risk management. Embedding SQL in procedural code is two-by-fours and drywall, and lots of paint. A good ORM and good OO design is pre-stressed steel, trusses, sunken piles, shear walls, and an interior designer.