What is Embedded SQL in DBMS?
Later we discussed Pseudocolumn in SQL. Today, we will see Embedded SQL in DBMS.
SQL stands for Structured Query Language, it provides as a declarative query language. However, a general-purpose programming language requires to get access to the database because
- SQL is not as powerful as any of the general purpose language available today.
- There are many declarative actions such as interacting with the user sending the result to a GUI or printing a report which we cannot do using SQL.
- There are many queries that we can express in C, Pascal, Cobol and many more but we cannot express in SQL.
Recommended Reading – SQL Query Interview Questions
1. What is Embedded SQL?
This is a method for combining data manipulation capabilities of SQL and computing power of any programming language. Then embedded statements are in line with the program source code of the host language. The code of embedded SQL is parsed by a preprocessor which is also embedded and is replaced by the host language called for the code library it is then compiled via the compiler of the host.
Two steps which define by SQL standards community, they are –
Module language defining which is formalization and then an embedded SQL standard derived from the module language. Most popular hosting language is C, it is called for example Pro*C in Oracle and Sybase database management systems and ECPG in the PostgreSQL database management system.
2. Need for Embedded SQL in DBMS
When you embed SQL with another language. The language that is embedded is known as host language and the SQL standard which defines the embedding of SQL is known as embedded SQL.
- The result of a query is made available to the program which is embedded as one tuple or record at a time
- For identification of this, we request to the preprocessor via EXEC SQL statement: EXEC SQL embedded SQL statement END-EXEC
- Its statements are declare cursor, fetch and open statements.
- It can execute the update, insert a delete statement
3. Systems that Support Embedded SQL
An embedded SQL precompiler is given by Altibase Corp. for its DBMS server.
- IBM DB2
ii. Microsoft SQL Server
Embedded SQL for C has been discontinued by Microsoft SQL Server 2008 although earlier versions of the product support it.
- Mimer SQL
They support it, namely Linux, OpenVMS, and Windows
Linux, OpenVMS, and Windows support this.
OpenVMS support embedded SQL for COBOL
OpenVMS support embedded SQL for Fortran.
iii. Oracle Database
Pro*Ada was officially desupported by Oracle in version 7.3. Starting with Oracle8, Pro*Ada was replaced by SQL*Module but appears to have not been updated since. It supports the Ada83 language standard for Ada.
Pro*C became Pro*C/C++ with Oracle8. Oracle Database 11g supports Pro*C/C++ .
Oracle Database 11g supports Pro*COBOL version.
Pro*FORTRAN is no longer updated as of Oracle8 although Oracle will continue to issue patch releases as bugs are reported and corrected.
Pro*Pascal was not released with Oracle8.
Pro*PL/I has been removed from the Oracle Documentation Library.
ECPG is part of PostgreSQL since the arrival of version 6.3.
Cobol-IT is presently giving a COBOL precompiler for PostgreSQL.
v. Raima Database Manager (RDM)
The 14.0 version supports it for C/C++ and PL/SQL.
vi. SAP Sybase
The 15.7 version supports embedding of SQL for C and COBOL as making it a part of the Software Developer Kit Sybase.
SAP Sybase SQL Anywhere supports it for C and C++ too as it is a part of the SQL Anywhere database management system SQL Anywhere.
4. Embedding of SQL Through Domain-Specific Languages
LINQ-to-SQL- It embeds SQL-like language into .NET languages.
JPA- It embeds SQL-like language via Criteria API into Java.
jOOQ- It embeds SQL-like language to Java.
So, this was all in Embedded SQL. Hope you liked our explanation.
Hence, in this tutorial, we learned a lot about embedded SQL. Moreover, we discussed the system that supports this. Also, we saw it through domain-specific language.
Still, if you have any query, feel free to ask through the comment tab.