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.
What is Embedded SQL in DBMS with Example

What is Embedded SQL in DBMS with Example

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.

Have a look at Stored Procedures in SQL

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

i. Altibase

  • C/C++

An embedded SQL precompiler is given by Altibase Corp. for its DBMS server.

  • IBM DB2

The version 9 IBM DB2 for Linux, UNIX and Windows support it for C, C++, Java, COBOL, FORTRAN, and REXX although they don’t support FORTRAN and REXX.

ii. Microsoft SQL Server

  • C/C++

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

  • C/C++

Linux, OpenVMS, and Windows support this.

  • COBOL

OpenVMS support embedded SQL for COBOL

  • Fortran

OpenVMS support embedded SQL for Fortran.

iii. Oracle Database

  • Ada

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.

  • C/C++

Pro*C became Pro*C/C++ with Oracle8. Oracle Database 11g supports Pro*C/C++ .

  • COBOL

Oracle Database 11g supports Pro*COBOL version.

Join DataFlair on Telegram
  • Fortran

Pro*FORTRAN is no longer updated as of Oracle8 although Oracle will continue to issue patch releases as bugs are reported and corrected.

  • Pascal

Pro*Pascal was not released with Oracle8.

  • PL/I

Pro*PL/I has been removed from the Oracle Documentation Library.

Do you know about SQL Comment?

iv. PostgreSQL

  • C/C++

ECPG is part of PostgreSQL since the arrival of version 6.3.

  • COBOL

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.

5. Summary 

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.

Also check, 

Most Asked SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.