Embedded SQL vs Dynamic SQL

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.

i too have the book SQL and relational theory by C.J. Date. It is the best book you can have to read on relational concepts that are DBMS neutral. e.g. Designing tables, and writing SQLs that are relational oriented and not product based.

However, i find that the book not too practical when it comes to maintaining production systems or in practical situations whereby schema changes are less favorable. Also, the behavior of production data and applications may also have an influence over the transformation of table normal forms, e.g. the table may have started off nicely with 3NF, but ends up in 1NF to performance reasons. i.e. lesser joins and look-up tables the better.

Nonetheless, this is due to a limitation of the table based DBMS concept, which is why there has been a lot of emphasis on NoSQL key/pair databases recently.

Back on your topic of embedded SQL vs parameterized SQL, are you comparing between SQL written in the application tier's source codes and SQLs that reside on the database machines (e.g. PL/SQL in Oracle)?

In that case, i am for embedded SQL, i cannot name enough reasons to believe that business logic should reside at the application tier and not the database tier.

I am part of a team that maintains a moderately huge system, and in this system, there is a mixture of using PL/SQL with embedded SQL, it becomes hard this way if say a Java developer is not necessarily versed with PL/SQL (which is the case), whether to performance optimize it or to maintain it. So if you keep all your business logic in one place (preferably application tier, you gain a point here).

About database lock-in, i believe you need not be too concerned with this. Usually when a database product is purchased for use, you will seldom change. The effort, cost and risk is usually too large for such consideration. Unless you are shifting a paradigm, i.e. from relational databases to key/value database.

Hope this helps.