Embedded SQL vs Dynamic SQL

Question

I am currently doing the topic * cough * Oracle * cough *. The lecturer presents embedded SQL as a way to use other languages ​​(for example, C, C ++) to interact with the database (Oracle).

I myself did some work with a database (in mysql), where I use dynamic sql.

How does embedded SQL seem to be limited to a few Oracle and several others, is it more of an attempt to lock or is there real value in embedded SQL?

Edit: I only realized that this lesson was right when a PL / SQL lesson could be important.

The original question asked about parameterized SQL (now it is replaced by "dynamic sql" to improve the question).

Also: I think the book I bought at $ 30, SQL and Relational Theory, teaches me more than this database class.

Answer 1

Embedded SQL is parsed at compile time. One advantage is that you also detect syntax errors at compile time, which can prevent some types of awkward runtime errors. It also means that SQL attacks cannot change your intended SQL syntax at runtime.

Almost all SQL programmers these days put SQL in rows, and these rows are parsed at runtime. This is the original definition of dynamic SQL. This is also called the call level interface (CLI).

Since SQL is so common in this way, a new definition for “dynamic SQL” has become common, that is, people use this term for SQL queries that they create based on the logic and variables of the application, as opposed to a fixed string in their application, which sets the entire request.

Parameterized queries are a completely independent difference. You can put parameter placeholder in embedded or dynamic SQL.

For what it's worth, I don’t know anyone who uses embedded SQL these days (with the exception of preserving the outdated application architecture). I would even argue with your lecturer that they are teaching irrelevant, outdated technologies.

• Oracle 11g still supports many SQL precompilers .
• IBM DB2 UDB 9.7 supports an SQL preprocessor called PREP .
• Microsoft SQL Server deprecated embedded SQL support after MS SQL Server 2000.
• It is reported that Sybase also stopped embedding embedded SQL (but I cannot find a citation link).
• PostgreSQL still supports a preprocessor called ECPG for embedded SQL.
• MySQL has never supported embedded SQL.
• SQLite does not support the SQL preprocessor as far as I know.

This accounts for the vast majority of the RDBMS market share.

Answer 2

I also have a SQL book and CJ Date relational theory. This is the best book you can read on relational concepts that are DBMS neutral. for example, Designing tables and recording SQL queries oriented to relational orientation rather than product-based.

However, I believe that the book is not very practical when it comes to maintaining production systems or in practical situations where circuit changes are less favorable. In addition, the behavior of production data and applications can also influence the transformation of normal table forms, for example. the table may have started fine with 3NF, but ends at 1NF for performance reasons. those. the fewer joins and lookup tables the better.

However, this is due to the limitation of the concept of database-based DBMS, therefore, recently, much attention has been paid to NoSQL databases / pair databases.

Returning to the topic of embedded SQL with parameterized SQL, do you compare the SQL written in the application-level source code and the SQL that are on database machines (e.g. PL / SQL in Oracle)?

In this case, for embedded SQL, I can’t give sufficient reasons to believe that the business logic should be at the application level, and not at the database level.

I am part of a team that supports a moderately large system, and there is a mixture of using PL / SQL with embedded SQL in this system, it becomes difficult if you say that a Java developer does not necessarily understand PL / SQL (in this case), whether performance optimization or support. Therefore, if you save all your business logic in one place (preferably the application level, you get here).

About locking the database, I believe that you do not need to worry too much about it. Usually, when a database product is purchased for use, you rarely change. Efforts, costs and risks are usually too great for such a consideration. If you do not shift the paradigm, i.e. From relational databases to a key / value database.

Hope this helps.