Dynamic SQL and Embedded SQL

What is an Embedded and Dynamic SQL?

TY-05

SQL queries can be of two types i.e. Embedded or Static SQL and Dynamic SQL. So, in this blog, we will learn about these types.

Embedded / Static SQL

Embedded or static SQL are those SQL statements that are modified and cannot be changed during application. These statements are compiled during compilation only. The advantage of using this statement is that you know how to make statements because you have SQL statements in you so that you can improve your SQL query and be able to make the query in the best and fastest way. The data access method is pre-defined and these standard SQL statements are commonly used on those websites that are evenly distributed.
These statements have a strong code in the application, so if you want to build a specific program when you need flexible or duration SQL statements, you should use the Dynamic SQL statement.

Dynamic SQL

Dynamic SQL statements are those SQL statements that are created or used during operation. Users can answer their questions in other applications. These statements are compiled during operation. These types of SQL statements are used where there are differences in the data stored on the website. It is very flexible compared to static SQL and can be used in other flexible applications.
Since integration is done during operation, the system will only be able to access the website during operation. Therefore, no proper planning and performance can be done in advance. This will reduce system performance. Also, if you take a website query from a user during operation, then there is a chance that users may enter the wrong queries and this is very dangerous because here you are dealing with a lot of data.

Structure of Embedded SQL

The structure of embedded SQL defines step by step process of establishing a connection with DB and executing the code in the DB within the high-level language.

Connection to DB

This is the first step in writing a query in high-quality languages. The first DB connection we have accessed needs to be established. we can do this using the CONNECT keyword. But it should precede it with ‘EXEC SQL’ to indicate the SQL statement.

EXEC SQL CONNECT db_name;
EXEC SQL CONNECT HR_USER; //connects to DB HR_USER

Declaration Section

Once the connection is established via DB, we can make DB transactions. As this DB purchase depends on the prices and flexibility of the host language. Depending on their values, queries will be written and performed. Similarly, the results of the DB query will be returned to the host language which will be captured separately from the host language. We, therefore, need a variable declaration to transfer the value to the question and get the values ​​in question. There are two types of variables used in host language.

• Host variable: These are changes in the host language used to transfer the value to the query and to quote the values ​​returned to the query. Since SQL is dependent on host language we should use host language variables and such flexibility is known as host flexibility. But these hosting conditions should be announced in the SQL area or within the SQL code. That means the producer must be able to differentiate between standard C variables. We should therefore announce the host variables between the BEGIN DECLARE and END DECLARE categories. Also, these announced blocks should be locked within EXEC SQL and ‘;’.

EXEC SQL BEGIN DECLARE SECTION;
int STD_ID;
char STD_NAME [15];
char ADDRESS[20];
EXEC SQL END DECLARE SECTION;

• We can note here that the variables are written within the SQL start and end blocks, but are declined using C-code. Does not use SQL code to declare variables. Why? This is because they are not flexible handling conditions — variable C language. So we cannot use SQL syntax to publish them. Host language supports almost all types of data from int, char, long, float, double, pointer, array, string, structures, etc.
• If the host variable is used in the SQL query, it should be preceded by a colon — ‘:’ to indicate that it is a dynamic host. So when the front-end compiler compiles the SQL code, it replaces the host value for the host and integration.

EXEC SQL SELECT * FROM STUDENT WHERE STUDENT_ID =:STD_ID;

• In the code above,: STD_ID will be replaced by its value when the previous producer adds it.
• Suppose we do not know what the data should be for the host variant or any type of data in the oracle for a few columns. In that case, we can allow the compiler to download the column type data and provide it with a different host. It is done using the ‘BASED ON’ clause. But the announcement format will be in the host language.

EXEC SQL BEGIN DECLARE SECTION;
BASED ON STUDENT.STD_ID sid;
BASED ON STUDENT.STD_NAME sname;
BASED ON STUDENT.ADDRESS saddress;
EXEC SQL END DECLARE SECTION;

• Indicator Variable: These variables are also manageable variables but are a short versions of 2 bytes. These variables are used to take NULL values ​​returned by question or INSTALL / UPDATE any NULL values ​​in the tables. When used in the SELECT question, it captures any NULL value returned in any column. When used in conjunction with INSERT or UPDATE, it sets the column value as NULL, although the host variables have a value. If we have to capture NULL values ​​for each host variable in the code, then we must declare the variable variables in each host variable. This indicator variable is set immediately after the host variance in question or separated INDICATOR between host and index variance.

EXEC SQL SELECT STD_NAME INTO :SNAME :IND_SNAME
      FROM STUDENT WHERE STUDENT_ID =:STD_ID;

Execution Section

EXEC SQL SELECT * FROM STUDENT WHERE STUDENT_ID =:STD_ID;
EXEC SQL SELECT STD_NAME INTO :SNAME :IND_SNAME
FROM STUDENT WHERE STUDENT_ID =:STD_ID;
INSERT INTO STUDENT (STD_ID, STD_NAME)
VALUES (:SID, :SNAME);
UPDATE STUDENT
SET ADDRESS = :STD_ADDR
WHERE STD_ID = :SID;

The examples above illustrate simple SQL queries/statements. But we can also have perplexing questions.
In this embedded SQL, all queries are based on host variance values ​​and queries are static. That means, in the example above of the SELECT question, it always draws the student ID information entered. But suppose a user replaces a student ID with a student ID. Then these SQLs do not change to change the query to download data based on the name. Suppose the question is based on the student’s name and address. The code will then change the question to download details based on the student’s name and address. That means the queries are fixed and cannot be modified based on user input. This type of SQL is therefore known as static SQLs.

Structure of Dynamic SQL

Since the query needs to be prepared at run time, we have three clauses in dynamic SQL. These are mainly used to build queries and then execute them at run time.

PREPARE

Since dynamic SQL creates a question during operation, as a first step we need to capture all the input from the user. It will be stored in the character unit variable. Depending on the input received by the user, a dynamic character unit is added to the input and SQL keywords. These series SQL-like statements are then converted into SQL queries. This is done using the PREPARE statement.
For example, below are small captions from a dynamic SQL. Here sql_stmt is a variety of characters, capturing input from users and SQL commands. But it will not be considered a SQL query as it is still a unit number of characters. It needs to be converted into a valid SQL query created in the last line using the PREPARE statement. Here sql_query is also a character unit, but holds the series as a SQL query.

sql_stmt = "SELECT  STD_ID FROM STUDENT ";
    if (strcmp(STD_NAME, '') != 0){
       sql_stmt = sql_stmt || " WHERE STD_NAME = :STD_NAME";
    }
    else if (CLASS_ID > 0){
       sql_stmt = sql_stmt || " WHERE  CLASS_ID = :CLASS_ID";
    }
    if (strcmp(STD_NAME, '') !=0 && CLASS_ID >0)
       sql_stmt = sql_stmt || "  AND CLASS_ID = :CLASS_ID";
    EXEC SQL  PREPARE sql_query FROM :sql_stmt;

EXECUTE

Using this statement we can compile and execute the SQL statements prepared in DB.

EXEC SQL EXECUTE sql_query;

EXECUTE IMMEDIATE

This statement is used to prepare SQL statements as well as execute the SQL statements in DB. It performs PREPARE and EXECUTE tasks in a single line.

EXEC SQL EXECUTE IMMEDIATE :sql_stmt;

A simple program that illustrates dynamic SQL is given below

TY-05

Let’s Compare both Embedded and Dynamic SQL

Advantages of Embedded SQL

• Small footprint database: Since embedded SQL uses the UltraLite database engine integrated directly into each application, the footprint is usually smaller than when using the UltraLite component, especially on a smaller number of tables. With a large number of tables, this benefit is lost.
• High Performance: Integrating the high performance of C and C ++ applications and the development of generated code, which includes data access programs, makes embedded SQL a great choice in the development of a highly efficient application.
• Extensive SQL: Support With embedded SQL you can use different SQL for your applications.

Advantages of Dynamic SQL

• Prediction Optimization: The real advantage of flexible SQL is that the applications generated in each query request will be customized for the currently used descriptions. The big problem with standalone SQL solutions, without maintenance, is that additional predictions confuse the query editor, causing us to build inefficient programs. Dynamic SQL deals with this issue by not adding anything to the question.
• Single Query Plan Caching: In each database there is a single cached query system with additional ad-program time for each process request (this can be verified using the view sys.dm_exec_cached_plans). This means that every time a new conflict is transferred to a stored course, mergers occur, which will obviously kill performance. Flexible query is not a parameter and therefore produces duplicate query programs for different arguments.

Disadvantages of Embedded SQL

• C or C ++ information required: If you are unfamiliar with the C or C ++ system, you may wish to use one of the UltraLite sites. UltraLite components provide connectivity from a few popular programming languages ​​and tools.
• Complex development model: The use of a website to maintain the UltraLite web schema, as well as the need to process your source code files, makes the embedded SQL development process more complex. UltraLite components provide a very simple upgrade process.
• SQL must be specified during design: Only SQL statements defined during integration can be included in your application. UltraLite components allow for flexible use of SQL statements.

Disadvantages of Dynamic SQL

• Speed: Dynamic SQL tends to run slower than static SQL, as the SQL Server must produce a regular application during operation.
• Permissions: Dynamic SQL requires users to have direct access permissions for all accessed objects such as tables and views. Typically, users are given access to stored processes that refer to those tables, but not to the tables directly. In this case, the default SQL will not work.
• Syntax: One unique advantage of writing stored T-SQL processes is that you get a syntax check directly. With variable SQL, a small syntax error may not show up until runtime. Even if you check your code carefully, there may be some queries or other variations of the query, which are done only in rare cases and are not included in your test program.

Conclusion

If you want to create a flexible application you can use dynamic SQL, but make sure your users are professional and trained. If not, you should go to static or embedded SQL. This works very well compared to the powerful SQL.

That’s it for this blog.

Do share this blog with your friends to spread the knowledge.

Keep Learning :)

Writers :

• Aaditi Badgujar
• Sahil Adhav
• Aditya Sood
• Ayush Prasad