Dynamic SQL and Embedded SQL

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as IBM DB2, MS SQL Server , MySQL , Oracle, and Microsoft Access. The data in a RDBMS is stored in the database objects known as tables. A table is the collection of the related data entries, and it contains columns and rows. SQL stands for structured query language which is used to manipulate database objects and data they contain. It is comprised of several different statements which are used in manipulation of the data. SQL being a nonprocedural is not a general-purpose language. SQL lets you access and manipulate the databases. SQL queries can be of two types that is embedded or static SQL and dynamic SQL. So, in this blog, we are going to learn about these two types of SQL statements in detail.

Example of SQL Statement :

UPDATE EMP SET NAME= ‘ABC’ WHERE ID=0007;

How is SQL Statement Processed?

Before proceeding next, it is necessary to understand how the Structured Query Language statements are processed. DBMS performs the following steps shown in the diagram:

1. Parse Statement -

The DBMS parses the query into individual words. These words are known as tokens. To make sure the statement is free of syntax error and spelling mistakes . This process happens very quickly because in this process there is no need to access database.

2. Validate Statement -

Then Database Management System will check whether the tables in query are present in our database or not ? Does the user have appropriate privileges to run this statement ? DBMS validates the Column names aren’t ambiguous and they are not existed.

3. Optimize Statement -

DBMS optimizes the statement by exploring different methods . Can primary key be used to speed up a search process ? Should the DBMS first apply a search condition to the first Table and then join it to the second table, or should it begin with the join first and then use the condition afterwards? After taking in picture all the possible conditions, Database management System will choose one way which is more suitable. It may explore numerous possible different ways for the same query, so it is totally a CPU-intensive process.

4. Access Plan or Binary Form

In this step, the binary form of SQL statements or access plan will be created.

5. Execution of The Access Plan

This is last step. Here access plan is executed

Embedded / Static SQL

Embedded or Static SQL is those SQL statements that are fixed and cannot be changed at runtime in the application. These statements are compiled at compile-time only. The benefit of using this statement is that you know the path of execution of the statements because you have the SQL statements with you, so you can optimize your SQL query and then you can execute the query in the best and fastest possible way. The way in which they are accessing the data is already predefined and all these static SQL statements are basically used on only those databases which are uniformly distributed.

These statements are always hardcoded in the application, so if you wanted to build the application in which you need some dynamic or run-time SQL statements, then you can use the Dynamic SQL statement.

For example, let us assume that we are coding static SQL in a COBOL program. The UPDATE statement can be used to update the salaries of any employee. When you will write your program, you know that salaries must be updated, but you do not know anything until run time about whose salaries should be updated, and by how much.

Some of the prominent examples of languages with which we can embed SQL are as following:

C++

Java

Python etc.

Embedding SQL in High-Level Languages

For using embedded SQL, we need specific tools in each high-level language. In some cases, we have some inbuilt libraries which can provide us with the basic building block.

While in some cases we need to import or use some packages to perform the required tasks.

For example, in a Java, we need a connection class. We first create connection by using the connection class and then we open the connection bypassing the required parameters to connect with the database.

Advantages of Embedded SQL

Some of the advantages of using embedded SQL in high-level languages are as follows:

· Helps to access the databases from anywhere.

· Allows to integrate authentication service for large scale applications.

· Provides an extra security to the database transactions.

· Avoids most of the logical errors while performing transactions on our database.

· It makes easy to integrate the backend and the frontend of our application.

Dynamic SQL

Dynamic SQL statements are those SQL statements which are created or executed at run-time. Using dynamic SQL users can execute their own query in some application. These statements are compiled at the run-time. These type of SQL statements are used where there is a non-uniformity in the data stored in our database. It is more flexible as compared to the embedded SQL and can be used in some dynamic applications.

Since the compilation is done at the run-time, the system will come to know how to access the database at run-time only. So, no proper planning for execution and optimization can be made previously. This may reduce the performance of our system. Also, if you receive a website query from a user during operation, then there is a chance that users may have entered the wrong query, and it is very dangerous, because here you are dealing with very large amount of data.

You should use dynamic SQL in cases where static SQL does not support the process you wanted to do, or in cases where you don’t know the exact SQL statements that should be executed by a PL/SQL procedure. Dynamic SQL statements can be dependent on user input, or they can also depend on processing work done by the program.

Benefits of Dynamic SQL

Predicate Optimization — In dynamic SQL executing plans are created for every invocation of the query but optimization is done for only those invocations which are currently in use.

Single Query Plan Caching — For each and every stored procedure there will be one cached query plan and also an additional ad hoc plan caches for every invocation of the stored procedure. The dynamic query is not being parameterized, and therefore it is producing duplicate query plans for different arguments.

Drawbacks of Dynamic SQL

Speed — Dynamic SQL is actually slower than static SQL, as SQL Server must generate an execution plan every time at the runtime.

Permissions — Dynamic SQL requires the users must have direct access permissions on all accessed objects like tables and views .

Syntax: This is one more very important advantage of writing stored T-SQL procedures is that you directly get a syntax check. With dynamic SQL, some serious syntax errors may not show up until run time.

Conclusion

Static or Embedded SQL are those SQL statements in the application that never change at the runtime and, therefore, they can be hard coded into the application. Dynamic SQL statements are created during the execution so users can also give their own queries while executing the statements. The main conclusion is that if you want to make a flexible application then it is feasible to use dynamic SQL, but make sure that your users must be professionals and trained. Otherwise, you can go for static or embedded SQL , because this is more efficient as compared to the dynamic SQL.

That’s it for this blog.

Keep Learning 😊

Authors:

Abhishek Dake

Saurabh Dhote

Pranav Galande

Swaraj Garud.