Dynamic and Embedded SQL

February 05, 2022

What is SQL ?

Structured Query Language (SQL) is an acronym for Structured Query Language. SQL is a language for interacting with databases. It is the standard language for relational database management systems, according to ANSI (American National Standards Institute).

SQL statements are used to perform tasks like updating data in a database and retrieving data from one. Oracle, Sybase, Microsoft SQL Server, Access, Ingres are some common relational database management systems that uses SQL .

Although most database systems use SQL, they often have their own proprietary extensions that are only used on their own platform.

Standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create" and "Drop" can be used to do almost everything with a database.

SHORT HISTORY OF SQL

In the 1970s, computer scientists began working on a standardised way to operate databases, and SQL emerged as a result of their efforts.

A number of SQL-based products were released in the late 1970s and early 1980s. The first SQL standard was adopted by the American National Standards Institute (ANSI) in 1986, and it quickly gained popularity. SQL has improved as a result of continued work on relational databases, making it one of the most widely used database languages.

Some prominent software companies, such as Microsoft and Oracle, developed their own SQL versions, and one open-source version, MySQL, became immensely popular.

What Can SQL do ?

SQL is capable of running queries against a database.
SQL can be used to get information from a database.
SQL can be used to create new records in a database.
SQL can be used to update data in a database.
SQL has the ability to delete records from a database.
SQL has the ability to build new databases.
SQL has the ability to create new tables in a database.
SQL can also create stored procedures in database.
SQL can also be used to generate views in database.
SQL can set permissions on views , procedures , and tables.

What is RDBMS ?

Relational Database Management System (RDBMS) is an acronym for Relational Database Management System.

SQL and all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access are built on top of RDBMS.

In a relational database management system (RDBMS), data is stored in database objects known as tables. A table is made up of columns and rows and contains a collection of interrelated data elements.

Types of SQL Statements

These are the different types of SQL statements:
Data Definition Language (DDL) Statements
Transaction Control Statements
Data Manipulation Language (DML) Statements
Session Control Statements
Embedded SQL Statements
System Control Statement

WHAT IS EMBEDDED SQL ?

When we talk about embedded SQL, we're talking about short SQL queries that are embedded in high-level languages to produce meaningful results.

We must ensure that we have a working database connector on our system before embedding SQL queries into other high-level languages.

We may easily run SQL queries on existing databases in our RDBMS or even create new ones by using the connectors. Let us now delve deeper into the embedded SQL concepts and have a better understanding of them.

SQL stands for Structured Query Language, as we've seen in earlier. It's the language we use to work with databases and perform operations and transactions.

When it comes to industry-level applications, we need well-connected systems that can get data from a database and show it to the user. Embedded SQL comes to our help in these situations.

We incorporate SQL queries into high-level languages so that the logic element of our study can be completed quickly.

The following are some of the most common languages in which SQL is embedded:

C++ , Python , Java , etc.

Why do we need Embedded SQL?

Embedded SQL allows us to use databases whenever and anywhere we want. Several things must be taken care of once the application we design is put into production mode.

We must deal with many issues, one of which is the issue of authorisation, as well as the obtaining and feeding of data into and out of the database.

We can quickly use the database without having to write any complex code thanks to query embedding. We can use embedded SQL to construct APIs that can quickly fetch and feed data as needed.

Concepts for Embedding the SQL Statements

SQL statements can be combined directly into general-purpose programming languages such as C, Java, or Pascal. There are a few methods for incorporating SQL statements into computer languages.

The host language is the programming language in which the SQL statements are embedded. The source program is made up of SQL statements and host language statements, which are sent into a SQL precompiler for processing.
Variables in the host programming language can be referenced in embedded SQL statements, allowing SQL statements to utilize values calculated by the applications.
To assign null values to database columns, several special program variables are used. The retrieval of null values from the database is supported by these program variables.

Embedded SQL Program Development

Because embedded SQL is a blend of SQL and programming language, it can't be immediately fed into a compiler for a general-purpose programming language. The program's execution is actually a multi-step process, as shown below.

The SQL precompiler is fed the embedded SQL source code first. The precompiler analyses the programe and processes any SQL statements that are embedded in it. Different precompilers are available for various programming languages.

The precompiler provides two files as output after processing the source code. The first file contains the source code without any embedded SQL statements, whereas the second file has all of the program's embedded SQL statements.

The compiler for the host programming language receives the first file produced by the precompiler (which contains the source programe) (like C compiler). The compiler takes the source code and converts it into object code.

The linker now links the object modules created by the compiler with various library routines to create an executable application.

The precompiler's database requested modules are delivered to a customised BIND software (in steps). The BIND programe checks each SQL query, parses it, validates it, optimises it, and then generates an application plan for it.

The result is a DBMS-executable version of the embedded SQL statements, as well as a combined application plan for the complete programe.

How to Embed SQL in High-Level Languages?

We'll need some tools in each high-level language to use embedded SQL. In other circumstances, we have built-in libraries that give us with the foundational elements.

In some circumstances, we'll need to import or use some packages to get the job done.

In Java, for example, we require a connection class. We first use the connection class to build a connection, and then we open it without using the required parameters to connect to 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();

Here the database's name is DataFlair, the username is user, and the password is root.

Advantages of Embedded SQL

The following are some of the benefits of using SQL embedded in high-level languages:

Allows users to access databases from any location.
Allows large-scale applications to integrate an authentication service.
Gives database transactions an extra layer of protection.
When running transactions on our database, it avoids logical mistakes.
It's simple to integrate our application's frontend and backend.

Summary

Because SQL has widespread developer support, it can be incorporated in practically any high-level language. SQL integration is supported by languages such as C, C++, Java, and others.

Some programming languages, such as Python, include built-in libraries for integrating database queries into the code. The SQLite package for Python makes it simple to connect to a database via the embedding process.

When utilised in a large-scale application which is in production, sometimes embedding queries might cause issues because they are clumpy and can cause processing failures.

Embedded SQL should be used with caution, as even a minor error could result in the vital data being disclosed.

Now lets discuss about Dynamic SQL :

We'll look at how Dynamic SQL is used. Let's move on to learning more about dynamic SQL from the ground up.

The process of programming SQL queries in such a way that the queries are constructed dynamically with the application actions is known as dynamic SQL.

It enables us to manage large industrial applications and transactions without going over budget.

We can design flexible SQL queries with dynamic SQL, and the names of the variables or any other arguments are supplied to the application as it runs.

Stored procedures can be used to generate dynamic queries that can be performed whenever we want.

The exec keyword is used in Dynamic SQL.

When using static SQL, the query is not changed from one execution to the next, but with dynamic SQL, the query can be changed in each run.

Static SQL should always be preferred over dynamic SQL because of the following advantages:

When a query compiles correctly, it means the syntax is valid.

When a query succeeds in compiling, it ensures that all permissions and validations are proper.

Because all of the data in static SQL is known ahead of time, the overhead costs are significantly reduced.

Why do we need Dynamic SQL?

The following usage cases need the use of Dynamic SQL:

When we need to conduct dynamic queries, primarily DML queries, on our database.
When we need to access an object that did not exist at the time of build.
Whenever we need to reduce the time it takes for our queries to run.
When we need to instantiate the logic blocks we've created.
When we need to use invoker permissions to conduct operations on application-fed data.

How to use Dynamic SQL?

When generating and running a dynamic SQL cycle, we can use the following syntax.

-- Start by declaring the Query variable and other required variables

DECLARE @SQL nvarchar(1000)

DECLARE @variable1 varchar(50)

DECLARE @variable2 varchar(50)

-- Set the values of the declared variables if required

SET @variable1 = 'A'

-- Define the query variable

SET @SQL = 'SELECT columnName1, columnName2, columnName3...

FROM tableName where columnName1 = @variable1

-- Prepare the statement to be run on the database

PREPARE Query FROM @SQL;

-- Execute the prepared Dynamic SQL statement

Execute Query;

Let's look at an example of Dynamic SQL in work by defining it on our DataFlair database.

Use DataFlair;

-- Set the value of user-defined variables

SET @id = 'A01';

-- set the query you want to execute on the database

SET @query = 'SELECT * FROM DataFlair where emp_id = @id';

-- Prepare the statement to be run on the database

PREPARE stmt FROM @query;

-- Execute the prepared statement

Execute stmt;

Benefits of Dynamic SQL

Predicate Optimization: The real advantage of dynamic SQL is that the execution plans generated for each query invocation are optimised for the predicates that are currently being used. Aside from maintainability, the main difficulty with static SQL solutions was that the additional predicates confused the query optimizer, resulting in inefficient plans. By not providing anything more in the query, Dynamic SQL avoids this problem.

Single Query Plan Caching: For each stored procedure invocation, there is one cached query plan and an additional ad hoc plan cache (this can be checked using the view sys.dm exec cached plans). This means that every time a new argument is supplied to the stored procedure, a compilation takes place, which will obviously degrade speed. Because the dynamic query isn't parameterized, it generates redundant query plans for various arguments.

Drawbacks of Dynamic SQL

Because SQL Server must construct an execution plan every time at runtime, dynamic SQL is slower than static SQL.

Users must have direct access permissions on all accessed objects, such as tables and views, in order to use Dynamic SQL. Users typically have access to the stored procedures that reference the tables, but not to the tables themselves. Dynamic SQL will not work in this scenario.

Syntax: Writing stored T-SQL procedures has the advantage of providing a syntax check right away. A simple syntax issue in dynamic SQL might not show up until run time.

Summary

We've learned what it has to know about Dynamic SQL in this Blog. Dynamic SQL is a programming technique that enables us to make real-time queries for our application.

We've looked at some of the scenarios in which dynamic SQL is required. Following that, we looked at how to use dynamic SQL as well as the benefits and drawbacks of doing so.

We've learned that we should avoid utilising dynamic SQL unless absolutely essential, as it can result in unintended data changes that are difficult to track.

Tanmay Kadam || Shubham Waghule || Bhakti Sarag || Sahil Shendurkar

Dynamic SQL & Embedded SQL

Dynamic and Embedded SQL