Embedded SQL vs Dynamic SQL

Question

I am currently doing a *cough*Oracle*cough* database subject. The lecturer is introducing embedded SQL as the way you get other languages (e.g. C, C++) to interact with the (Oracle) database.

I have done some database work myself (on mysql) where I use dynamic sql.

As embedded SQL seems to be limited to a few Oracle and a few others, so is this more an attempt at lock-in, or is there real value in embedded SQL?

Edit: I just realized that the fact this lesson was right after a lesson on PL/SQL may be important.

Original question asked about parameterized SQL (now replaced by "dynamic sql" to improve question).

Aside: I am thinking that the ~$30 "SQL and relational theory" book I bought is teaching me more than this database class.

Answer 1

Embedded SQL is parsed at compile-time. One advantage is that you catch syntax errors at compile-time too, which can prevent some types of embarrassing runtime errors. It also means that there's no way SQL injection vulnerabilities can alter your intended SQL syntax at runtime.

Virtually all SQL programmers these days put SQL into strings, and have these strings parsed at runtime. That's the original definition of dynamic SQL. This is also called the Call-Level Interface (CLI).

Because it's so common to use SQL in this way, a new definition for the "dynamic SQL" has become common usage, i.e. people use this term for SQL queries that they build up conditionally based on application logic and variables, as opposed to being a fixed string in their application that specifies the whole query.

Parameterized queries is a completely independent distinction. You can put parameter placeholders into either embedded or dynamic SQL.

For what it's worth, I don't know anyone who uses embedded SQL these days (except to maintain legacy application architecture). I would even be willing to argue with your lecturer that they're teaching irrelevant, outdated technology.

• Oracle 11g still supports a variety of SQL precompilers.
• IBM DB2 UDB 9.7 support an SQL preprocessor called PREP.
• Microsoft SQL Server has deprecated support for embedded SQL after MS SQL Server 2000.
• Sybase reportedly also has discontinued embedded SQL (but I can't find a reference to cite).
• PostgreSQL still supports a preprocessor called ECPG for embedded SQL.
• MySQL has never supported embedded SQL.
• SQLite does not support an SQL preprocessor as far as I know.

That accounts for the overwhelming majority of RDBMS market share.