Many people are using databases but they actually don’t have an understanding of the difference between static and dynamic SQL. Their purpose is to get the output of the query either it is static or dynamic. In this article, you will learn about static vs dynamic SQL queries with a detailed discussion, how SQL statement is processed, Dynamic DBMS (Database Management System), Static vs Dynamic reports and embedded SQL example of an application.
What is Embedded SQL?
Embedded or Static SQL queries are those instructions that do not change at the execution or runtime and can be hard-coded (where data or parameters cannot be altered) into different applications.
Embedded SQL Application
Consider the example of an embedded application where the statements are mostly embedded in C++ source files where a preprocessor translates the statements into calls to a runtime library. It is portable to other environments and delivers the same functionality to each operating environment.
Embedded SQL application consists of SQL statements and these statements are converted to C or C++ code before compiling. As you can see in the diagram, application at the runtime uses SAP IQ interface library which is known as DBLIB and used to communicate with database servers. DBLIB is a dynamic link library (DLL) which can be shared on all major platforms.
What is Dynamic SQL?
As the name indicates, it is a technique that allows professionals to build SQL statements that can be changed dynamically at the runtime. A dynamic query is a statement that can be constructed at execution or runtime; for example, the application may allow users to run their own queries at execution.
What is Dynamic Database Management System?
Dynamic DBMS is a database having “value-based” relationships of objects, which is specified at retrieval time and locations of related records are discovered during retrieval.
Which Of The Following Statements About Static And Dynamic Reports Is Not Accurate?
A printed sales analysis is an example of a dynamic report is the wrong statement about the static and dynamic reports. Following statements are true about the reports:
- Static reports are published as a PDF document or Business Intelligent (BI) documents that are fixed at the time of creation, so these reports not modified.
- Publishing the dynamic report requires Business Intelligent (BI) applications with the access of data source when it delivered to the user.
- Always remember, Printed sales analysis is an example of static reports.
- Dynamic reports are Business Intelligent (BI) documents and updated at the time whenever a user makes a request for this file.
How SQL Statement is 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
For example, the user wants to run the following query:
SELECT P, Q, R
FROM A, B
WHERE P> 800
AND R = ‘XYZ’
The DBMS will parse the query into individual words, known as tokens, to make sure the statement is free of spelling mistakes and syntax errors. It will be done very quickly because there is no need to access the database while parsing the statement.
2. Validate Statement
Database Management System will check that the tables (in case of our example table A and B) are existed in the database (system catalog) or not? Does the user have privileges to run these statements? DBMS validates the Column names are not ambiguous and existed.
3. Optimize Statement
DBMS optimizes the statement by exploring different ways. Can an index (primary key) be used to speed a search? Should the Database Management System first apply a search condition to the first Table and then join it to the second table, or should it begin with the join and use the condition afterwards? After exploring all the possible conditions, DBMS will choose one which is more suitable. It may explore thousands of possible different ways for the same query, so it is a CPU-intensive process.
4. Access Plan or Binary Form
In this step, the access plan or binary form of SQL statements will be generated.
5. Execution of The Access Plan
At the final step, the Access plan will be executed.
Basic Differences Between Embedded and Dynamic SQL
| In Static or Embedded SQL, how the database will be accessed is predetermined in the embedded SQL statement and performed by the preprocessor, where user cannot run queries on runtime. ||How database will be accessed is determined at runtime and user is also able to run Structured Query Language instructions on runtime. |
| It is swifter and more efficient. || It is less swift and efficient. |
| All database queries are compiled at compile-time. || All database queries are compiled at application runtime. |
| All processes including Parsing, Validation, Optimization and Generation of application access plan are done at compile time. || All processes including Parsing, Validation, Optimization and Generation of application access plan (Binary form of SQL queries) are done at runtime. |
| It is mostly used for situations where data is distributed consistently. || It is used for situations where data is distributed non uniformly. |
| PREPARE, EXECUTE and EXECUTE IMMEDIATE statements are not used. || PREPARE, EXECUTE and EXECUTE IMMEDIATE statements are used. |
| It is less flexible because of hard-coded into the application. || We can run our SQL statements at the runtime, so it is more flexible. |