Dynamic SQL and Embedded SQL

SQL (Structured Query Language)

Source:https://www.tutorialrepublic.com/sql-tutorial/

SQL (Structured Query Language) is a programming language used to manage relational databases and perform various operations on the data they contain. SQL is a programming language that is used to conduct operations on database records, such as updating, inserting, removing, and creating and altering database tables and views. SQL is a query language rather than a database system. 

This database language is primarily intended for use in relational database management systems to maintain data. It's a specialized tool that data professionals use to work with structured data (data which is stored in the form of tables).A person can quickly construct and operate the database, as well as access and modify table rows and columns, among other things. This query language was adopted as an ANSI standard in 1986 and an ISO standard in 1987.

Important points about SQL:

1. Almost all relational databases utilize SQL to query, manipulate, and define data, as well as provide access control and a variety of other functions.
2. Around 1970, IBM created SQL with Oracle as a major contribution, resulting in the SQL ANSI standard's implementation.
3. SQL is a widely used computer language for managing relational databases and performing various operations on the data contained within them.

What  SQL can do?

• SQL can be used to conduct queries against a database.
• SQL is a language that can be used to retrieve data from a database.
• SQL can be used to create new records in a database.
• In a database, SQL can be used to generate views.
• 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.
• In a database, SQL can create stored procedures.

Types of SQL

The types of SQL languages which are going to be discussed in this blog are:

1. Dynamic SQL.
2. Static SQL.

Static or Embedded SQL

Source:https://ic9.in/3463073 

Embedded SQL is a language that mixes a high-level programming language with a database language like SQL. It enables application languages to interact with databases and obtain the desired results. The host language is a high-level language that allows SQLs to be embedded within it. There are a variety of host languages that enable SQL embedding, such as C, C++, and others. SQL statements that are fixed and can be hard coded into an application are referred to as static SQL.. As static SQL's are fixed queries, these statements can be analyzed and optimized and do not require any specific handling for security purpose.

The compiler executes the full compilation operation in two steps when SQL is integrated within the C language. It pulls all SQL codes from the program first, and then the pre-compiler checks all SQL code for syntax, accuracy, and execution path. These executable codes are then integrated into the C code after the pre-compilation is completed. The C compiler will then compile and run the complete program.

As a result, the compilation is split into two stages: one for SQL and another for application language. As a result, in order to generate executable code, all of the query parameters, data values, and so on must be known at the time of compilation. Otherwise, the executable code cannot be compiled in C or any other high-level language. As a result, the SQL codes created are static, thus embedded SQL is also known as static SQL.

Dynamic or Interactive  SQL

  Source:https://data-flair.training/blogs/dynamic-sql/

SQL statements that are created dynamically based on user input and run within the program are referred to as dynamic SQL. Dynamic SQL is a programming approach that allows you to build SQL statements dynamically in real time. Because the entire textual context of the SQL statement may be unknown during compilation, a person can basically construct a lot more general purpose, adaptable applications by employing dynamic SQL. Dynamic SQL, for example, allows you to write procedures that act on tables whose names you don't know until they're called at runtime.

The ones that are constructed or executed at run-time are known as dynamic SQL statements. In a few applications, users can run their own queries .These statements are compiled at the run-time. These kinds of SQL statements are used in which there is a non-uniformity in the data stored in the database. It is way more more flexible as compared to static SQL and may be utilized in many  applications which are flexible.

Unlike static SQL statements, which are hard-coded in the software, dynamic SQL statements can be written during runtime and stored in a string host. After that, they're delivered to a database management system to be processed. Because the whole text of a SQL query may be unknown at compilation, Dynamic SQL allows you to design more general-purpose, adaptable applications.

Because the database management system must construct a dynamic SQL statements access strategy during the runtime, dynamic SQL is usually slower than static SQL. When a program containing dynamic SQL statements is compiled, the dynamic SQL statements are not removed from the program as they are in Static SQL, but instead substituted by a function call that delivers the statement to the database management system.

Basic differences between Static or Embedded and Dynamic or Interactive SQL

                                                            Fig. Basic differences

1.Database Access

The database procedure's access is already determined in the statement in Static SQL. In Dynamic SQL, however, how the database is accessed can only be determined at run time.

2.Efficiency

Static SQL statements are very fast and efficient  while as dynamic SQL statements are less efficient.

3.Compilation

Static SQL statements are compiled only the at compile time, whereas dynamic SQL statements are compiled only at the run time.

4.Use Cases

Static SQL is used in case of uniformly distributed data while as dynamic SQL is used in case of non-uniformly distributed data.

5.Dynamic Statements

In static SQL, statements such as EXECUTE IMMEDIATE, EXECUTE, and PREPARE are not utilized, however in dynamic SQL, statements such as EXECUTE IMMEDIATE, EXECUTE, and PREPARE are utilized.

6.Flexibility

Static SQL is less flexible than dynamic SQL, which is far more flexible.

Advantages of Embedded SQL

Small footprint database:  Because embedded SQL relies on an Ultra-lite database engine that is custom-compiled for each application, the footprint is typically less than when utilizing an Ultra-lite component, especially for applications with a small number of tables. For the large number of tables, this benefit is usually lost.

High performance: Embedded SQL is an excellent choice for developing high-performance applications since it combines the very high performance of C and C++ applications with essentially the optimization of created code, including data access plans.

Extensive SQL support:  Embedded SQL allows users to use a wide variety of SQL in their applications.

Some more advantages are:

• It helps to access the databases almost from anywhere.
• Allows to integrate the authentication service for very large scale applications.
• Provides an extra security to the database transactions.
• Avoids lot of the logical errors while performing the transactions on the database.
• Makes it very easy to integrate the frontend and backend of application.

Advantages of Dynamic SQL

Predicate Optimization: The most significant advantage of adopting dynamic SQL is that the execution plans generated for each and every query invocation are optimized for the predicates currently in use. Aside from the maintainability issue, the main difficulty with static SQL solutions is that the additional predicates confuse the query optimizer, forcing it to generate a large number of inefficient plans. By not providing anything more in the query, Dynamic SQL gets around this problem.

Single Query Plan Caching: There is only one cached query plan for each stored procedure in dynamic SQL, plus an additional ad hoc plan caches for each execution of the stored procedure. This means that every time a new argument is supplied to a stored process, a compilation will take place, thereby killing performance. Because the dynamic query is not parameterized, it generates a lot of duplicate query plans for different types of parameters.

Drawbacks of Embedded SQL

Knowledge of C or C++ required: If you don't know how to code in C or C++ programming, you may want to use one of the Ultra-lite interfaces. Ultra-lite components provide interfaces for a number of popular programming languages and tools.

Complex development model :  The usage of a reference database to house the Ultra-lite database schema, as well as the requirement to preprocess the source code files, makes the embedded SQL development process extremely complicated. The Ultra-lite components greatly simplify the development process.

SQL must be specified throughout the design phase : Only SQL statements defined at compile time can be included in your application. The Ultra-lite components allow dynamic use of SQL statements. They are hard-coded into apps because they do not alter at runtime.

Some other drawbacks of embedded SQL are:

• Static statements require regeneration of access plans if they were modified.
• They are less flexible

Drawbacks of Dynamic SQL

There are basically a lot of draw backs of using the dynamic SQL, however the stored procedures executes very fast than the dynamic SQL statements, because the application is basically only looking for the commands. The database program must generate code and then execute the code as asked when using dynamic SQL statements, which means it must handle a lot more data before looking for results. It then formats the findings which slows down the application even further.

Security concerns and the likelihood of errors are two further drawbacks of dynamic SQL. Because of the increased flexibility, it is much easier to detect and exploit code flaws. In addition, unlike stored procedures, dynamic SQL statements cannot be validated for every single instance. There is more freedom for very big databases because there are essentially no parameters, and the code is not written until the execution phase. It's far more difficult, if not impossible, to test for errors and conflicts.

Some other drawbacks of dynamic SQL are:

Speed: Because SQL Server must construct an execution plan every time throughout the runtime, dynamic SQL is much slower than static SQL.
Permissions: Dynamic SQL require the users to have the direct access permissions on basically all accessed objects like the tables, views etc. Users usually have access to the stored procedures that refer to the tables, but not to the tables themselves. In this case, dynamic SQL will never work. Following are some additional drawbacks of dynamic SQL:

• A dynamic query's execution strategy cannot be cached.
• Debugging is quite difficult.
• Error management become quite unreliable.
• Unless they are global, temporary tables from the main statement cannot normally be used.

Conclusion

You can utilize dynamic SQL to create a versatile application, but make sure your users are professionals who have been properly taught. If static SQL does not support the operation you want to execute, you should use dynamic SQL, or in cases where you do not know the exact SQL statements, that must be executed by a dynamic SQL. Otherwise, static or embedded SQL is the way to go because when compared to dynamic SQL, this is more efficient.

References

1. https://www.tutorialspoint.com/sql/index.htm
2. https://www.sqlshack.com/learn-sql-dynamic-sql/
3. https://www.geeksforgeeks.org/difference-static-dynamic-sql/
4. https://vivadifferences.com/difference-between-dynamic-and-static-sql-with-examples/
5. https://afteracademy.com/blog/what-is-an-embedded-and-dynamic-sql