From the title you can probably tell where this post is going. I know a few people who swear by things such as SQLObject or RoR's ActiveRecord, or whatever else there is out there. Let me sum up my point in one nicely bulleted list:
- There is already Standard syntax for this sort of thing. It is called SQL.
Ok, so standard's compliance isn't reason enough for you people to not reinvent the wheel. What about the fact that your wheel isn't round? Not one of the common object-data models out there supports everything that SQL can do (Note, there are a few decent ones for Java I've seen in my day). You need to understand that SQL is not an optional part of a RDBMS, it's required by the standard, it will be there - yes, even in Microsoft SQL Server (which has slightly better standard support than some other DBs, as far as SQL:2003 is concerned).
Now, you may question "But if my [insert name of some abstraction layer] doesn't support everything, and SQL is already a standard, why did someone invent this to begin with?" And, frankly, that's my question as well. One thing these developers tend to forget is that developers in these sort of roles already know how to write SQL. It's much the same as people developing XML parsers - you need to know most of the rules of XML in order to do it right. You will not find a single developer who has written or even consumed an XML parser who doesn't know the basic XML rules. Likewise, you will be hardpressed to find a developer for some data-driven application who doesn't know how to write an inner join, or a 'create table' statement.
This sort of thing should not be abstracted. Syntactic sugar is fine if you need it, but abstracting away a 'create table' statement to creating an instance of a class is asinine. You can corollary this to the actual output of HTML for some of these apps - most of them will give you 'button' objects and 'label' objects, and in general things that map to HTML tags, yet the basic rules and properties still follow those defined by HTML.
For another interesting example, I'd like to point out something people don't always think about. Schemas change. Database layouts evolve. Take your massive SQLObject project and now, add a column to TableA that maps to the LookupB table for the value. And add this without skipping a beat. Oh, and while you're adding it, if TableA has a value of 1 in ColumnX, the new column needs to have a value of 3. I should probably note that the described example is a trivial SQL 'alter' statement. Yes, it requires changes to your front-end code, it always does, there's no way around it (portably), but both the SQLObject version and the non-abstraction version requires changes to the front-end. For an example of what I mean, take a look here. SQLAlchemy is the only promising data access layer I've seen recently. The fact of the matter is that it does not hide SQL from you. In fact, it is really just a layer that helps generate the SQL in python syntax.
I know SQL, I'm fine with it. In fact, I prefer it to some odd abstraction layer. I would rather not learn your random API for data access that you created for your special needs.