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.

Rodie

Definitely +1 to "SQL and Relational Theory"! – Melton

Haha! Sitting in that UOW embedded SQL lecture right now… – Fleshly

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.

Melton

Answer 2

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.

Fraya

One reason to move logic out of the database tier is for scalability. I analyzed a high-volume website whose database server was running with CPU pegged, but I/O only lightly loaded -- the reverse of what you'd typically expect of a db server. Meanwhile, their PHP application servers were also very lightly loaded. As you might guess, they had a development policy that all data code was to be implemented in stored procedures. As they shifted more code out to PHP apps, the load on their RDBMS server eased up and their horizontal scalability improved. – Melton

Thanks Bill, this is insightful. – Fraya