Embedded SQL in DBMS
In this article, we are going to discuss about embedded SQL in DBMS, first we are going to see about what is embedded SQL , why we need embedded SQL , how to Embed SQL in High-Level Languages? and its advantages and disadvantages.
What is embedded SQL?
- Embedded SQL is a method of inserting inline SQL statements or queries into the code of a programming language, which is known as a host language. Because the host language cannot parse SQL, the inserted SQL is parsed by an embedded SQL pre-processor. Embedded SQL is a robust and convenient method of combining the computing power of a programming language with SQL’s specialized data management and manipulation capabilities.
- Embedded SQL is not supported by all relational database management systems. Oracle DB and PostgreSQL provide embedded SQL support. MySQL, sybase and SQL Server 2008 do not, although support was provided by earlier versions of SQL Server (2000 and 2005).The C programming language is commonly used for embedded SQL implementation. For example, a commercial bank’s information system (IS) has a front-end user interface created in the C language, and the IS interfaces with a back-end Oracle DB database. One of the front-end interface modules allows quick viewing and commission calculation for sales agents during specified periods. An inefficient approach to handling this process would be to store each commission value in a database table. However, a more effective solution is to calculate and return commission values based on unique user requests on specified dates. The application accomplishes this by embedding a SQL query within the C code, as follows:
SELECT 0.2*SALE_AMOUNT FROM TOTAL_SALES WHERE SALE_DATE=’MM/DD’YYYY’ AND AGENT_NO=xx
- In this example, the SQL statement calculates and returns 20 percent of the sale amount from a TOTAL_SALES table, while the user is expected to input the SALE_DATE and AGENT_NO values. This SQL query is then inserted inline into the C code of the front-end module. The C code and SQL query work together to deliver seamless user results.
why we need embedded SQL?
Embedded SQL gives us the freedom to use databases as and when required. Once the application we develop goes into the production mode several things need to be taken care of. We need to take care of a thousand things out of which one major aspect is the problem of authorization and fetching and feeding of data into/from the database. With the help of the embedding of queries, we can easily use the database without creating any bulky code. With the embedded SQL, we can create API’s which can easily fetch and feed data as and when required.
How to Embed SQL in High-Level Languages?
For using embedded SQL, we need some tools in each high-level language. In some cases, we have inbuilt libraries which provide us with the basic building block.While in some cases we need to import or use some packages to perform the desired tasks.
For example, in Java, we need a connection class. We first create a connection by using the connection class and further we open the connection bypassing the required parameters to connect with the database.
Example: How to connect to a database (using JAVA).
Code[with embedded SQL]:
Class.forName(“com.mysql.jdbc.Driver”);
Connection connection = DriverManager.getConnection( “jdbc:mysql://localhost:3306/DataFlair”,”user”,”root”);
Statement statement = connection.createStatement();
Advantages of embedded SQL:-
- Small footprint database:- As embedded SQL uses an Ultra Lite database engine compiled specifically for each application, the footprint is generally smaller than when using an Ultra Lite component, especially for a small number of tables. For a large number of tables, this benefit is lost.
- High performance :- Combining the high performance of C and C++ applications with the optimization of the generated code, including data access plans, makes embedded SQL a good choice for high-performance application development.
- Extensive SQL support :- With embedded SQL you can use a wide range of SQL in your applications.
Disadvantages of embedded SQL:-
- Knowledge of C or C++ required:- If you are not familiar with C or C++ programming, you may wish to use one of the other Ultra Lite interfaces. Ultra Lite components provide interfaces from several popular programming languages and tools.
- Complex development model:- The use of a reference database to hold the Ultra Lite database schema, together with the need to pre-process your source code files, makes the embedded SQL development process complex .The Ultra Lite components provide a much simpler development process.
- SQL must be specified at design time:- Only SQL statements defined at compile time can be included in your application. The Ultra Lite components allow dynamic use of SQL statements.
SQL can be embedded in almost all high-level languages due to the vast support it has from almost all developers. Languages like C, C++, Java etc, support SQL integration.Some languages like python have inbuilt libraries to integrate the database queries in the code. For python, we have the SQLite library which makes it easy to connect to the database using the embedding process. I hope you learnt something about embedded SQL from this article.
Website:-
