Embedded SQL in OO languages like Java

Question

One of the things that annoys me working with SQL in OO languages is having to define SQL statements in strings.

When I used to work on IBM mainframes, the languages used an SQL preprocessor to parse SQL statements out of the native code, so the statements could be written in cleartext SQL without the obfuscation of strings, for instance in Cobol there is a EXEC SQL .... END-EXEC syntax construct that allows pure SQL statements to be embedded in the Cobol code.

<pure cobol code, including assignment of value
 to local variable HOSTVARIABLE>    

EXEC SQL
       SELECT COL_A, COL_B, COL_C
       INTO :COLA, :COLB, :COLC
       FROM TAB_A
       WHERE COL_D = :HOSTVARIABLE
END_EXEC

<more cobol code, variables COLA, COLB, COLC have been set>

...this makes the SQL statement really easy to read & check for errors. Between the EXEC SQL .... END-EXEC tokens there are no constraints on indentation, linebreaking etc., so you can format the SQL statement according to taste.

Note that this example is for a single-row select, when a multiple-row resultset is expected, the coding is different (but still v. easy to read).

So, taking Java as an example

1. What made the "old COBOL" approach undesirable ? Not only SQL, but system calls could be made much more readable with that approach. Let's call it the embedded foreign language preprocessor approach.

2. Would an embedded foreign language preprocessor for SQL be useful to implement ? Would you see a benefit in being able to write native SQL statements inside java code ?

Edit

I'm really asking if you think SQL in OO languages is a throwback, and if not then what could be done to make it better.

Answer 1

There is already a standard for embedded SQL in Java, it's called SQLJ.

Having said that, I've never seen it used in the wild, and I've no idea if it's really an option any more, with modern tools. Oracle went for it in a big way when the standard appeared, but I think it died on the vine.