Embedded SQL in OO languages like Java
- by Steve De Caux
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
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.
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.
