Dynamic SQL and Embedded SQL

Basic Idea About SQL

It’s a Programming language that’s tailored to a precise domain. In associate application development language, it allows a technologist to figure with knowledge. To store the information , a on-line database is utilized. SQL may be a customary information language utilized by on-line database management systems like SQL Server, MySQL, MS Access, et al. to handle this knowledge.

Embedded / Static SQL

SQL statements that square measure changed and can’t be altered throughout associate application square measure spoken as embedded or static SQL. solely throughout compilation square measure these statements compiled. The advantage of using this statement is that you just shrewdness to create statements since you have already got SQL statements in your head. this enables you to reinforce your SQL question and manufacture it within the best and effective manner potential. the information access technique is pre-defined, and typical SQL statements square measure oft used on equally distributed websites.

Dynamic SQL

The Dynamic SQL statement includes a robust code within the application, thus you ought to use it if you would like to construct a special programme that needs versatile or period SQL statements. SQL statements that square measure created or utilized dynamically square measure called dynamic SQL statements. Users will notice answers to their inquiries in alternative apps. throughout operation, these statements square measure compiled. once there square measure variations within the knowledge saved on the web site, these SQL statements square measure utilized. as compared to static SQL, it’s extraordinarily pliable and will be utilized during a kind of applications.

Structure of Embedded SQL

The structure of embedded SQL outlines the in small stages procedure of connecting to a information and running code within the information employing a application-oriented language.

Connection to Database

the primary step in developing a question during a high-quality language is to put in writing a question . the primary information association we’ve tried should be established. The CONNECT keyword are often be use to try this. It should, however, be preceded with ‘EXEC SQL’ to specify 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 through the DB, you can execute the DB transaction. Price and guest language flexibility are factors in this database acquisition. Queries are developed and executed based on their values. Similarly, the results of database queries are delivered to the guest language, which is recorded separately. Therefore, you need a variable declaration to pass the value to the question and get the value. There are two types of variables in the host language.

• Host Variables: These are changes in the host language used to pass a value to the query and cite the value returned in the query. SQL depends on the host language, and this flexibility is called host flexibility, so you need to use host language variables. However, these hosting criteria must be specified in the SQL area or SQL code. This requires the manufacturer’s ability to distinguish traditional C variables. Therefore, the host variable must be announced between the BEGINDECLARE and ENDDECLARE categories. Also, if these announced blocks are blocked during EXECSQL and’;’.

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

• Variables are written inside the SQL start and end blocks, but are rejected using C code. Variables are declared without SQL code. This is because the processing conditions cannot be adjusted. C language cannot be changed.
• You must prefix the SQL query with a colon-’:’ to indicate that the host variable is a dynamic host. As a result, when the front-end compiler generates SQL, the host value is used instead of host and integration.

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

• In the code above : When the preceding producer adds it, STD ID will be replaced by its value in the code above.
• Suppose you don’t know what the host variant data or other data in Oracle should be for some columns. You can download the column type data and instruct the compiler to assign a new host in this case. This is achieved with the expression “BASED ON”. The format of the announcement, on the other hand, is 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, but they are just 2 bytes long. These variables are used to INSTALL / UPDATE any NULL values in the tables or to accept NULL values returned by questions. It catches any NULL value returned in any column when used in a SELECT query. It changes the column value to NULL when used with INSERT or UPDATE, even while the host variables contain a value.

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;

Simple SQL queries/statements are demonstrated in the examples above.

All queries in this embedded SQL are based on host distribution values ​​and are static. That is, in the SELECT question example above, the student ID information entered is always pulled. However, let’s say the user replaces the student ID with a student ID. These SQLs do not modify the query to download the data by name. This means that the query is fixed and cannot be modified based on user input. Therefore, this type of SQL is called static SQL.

Structure of Dynamic SQL

Dynamic SQL has three clauses because queries need to be prepared at run time. These are primarily used to create queries.

PREPARE

Dynamic SQL generates a question during execution, so you must first record all user input. Construction unit variables are used for storage. Dynamic character units are added to the input and SQL keywords based on the user’s input. SQL queries are built from a series of SQL-like statements. The PREPARE statement is used for this.

EXECUTE

we can use this statement to compile and execute a DB-prepared SQL statement.

EXEC SQL EXECUTE sql_query;

EXECUTE IMMEDIATE

This statement is used to prepare the SQL statement and execute the SQL statement in the DB. Run the PREPARE and EXECUTE tasks on one line.

EXEC SQL EXECUTE IMMEDIATE :sql_stmt;

Comparing both Embedded and Dynamic SQL

Advantages of Embedded SQL

• Simple to deliver higher creation.
• There are fewer costs for per bit of resultant.
• It has not many interconnections.
• It has Better steady and Higher speed.
• It has Higher dependable

Disadvantages of Embedded SQL

The installed framework has some restrictions, including:

• Complex development model: The integrated SQL development process is made more complicated by the usage of a website to manage the UltraLite web schema, as well as the necessity to process your source code files. UltraLite components have a relatively straightforward update procedure.
• 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.

Benefits of Dynamic SQL

Predicate Optimization: The true advantage of dynamic SQL is that the execution plans created for each query invocation are optimised for the predicates that are currently being utilised. Aside from maintainability, the primary 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: There is one cached query plan for each stored procedure and an extra ad hoc plan cache for each invocation of the stored procedure.
This implies that every time a new argument is supplied to the stored procedure, a compilation will place, which will obviously degrade speed. Because the dynamic query isn’t parameterized, it generates redundant query plans for distinct parameters.

Drawbacks of Dynamic SQL

• Speed: Because SQL Server must construct an execution plan every time at runtime, dynamic SQL is slower than static SQL.
• Permissions: Users must have direct access rights to all accessible objects, such as tables and views, in order to utilise Dynamic SQL. Users often have access to the stored procedures that reference the tables, but not to the tables themselves. Dynamic SQL will not function in this scenario.

Conclusion

You can use dynamic SQL to construct a versatile application, but if you don’t, you should use static or embedded SQL. When compared to powerful SQL, this works quite well.

That is it for this blog.

I hope u ♥ it .

Writer :

• Shreyas Zanzad