The main disadvantage of embedded SQL is that it supports only static SQLs. If we need to build up queries at run time, then we can use dynamic sql. That means if query changes according to user input, then it always better to use dynamic SQL. Like we said above, the query when user enters student name alone and when user enters both student name and address, is different. If we use embedded SQL, one cannot implement this requirement in the code. In such case dynamic SQL helps the user to develop query depending on the values entered by him, without making him know which query is being executed. It can also be used when we do not know which SQL statements like Insert, Delete update or select needs to be used, when number of host variables is unknown, or when datatypes of host variables are unknown or when there is direct reference to DB objects like tables, views, indexes are required.
However this will make user requirement simple and easy but it may make query lengthier and complex. That means depending upon user inputs, the query may grow or shrink making the code flexible enough to handle all the possibilities. In embedded SQL, compiler knows the query in advance and pre-compiler compiles the SQL code much before C compiles the code for execution. Hence embedded SQLs will be faster in execution. But in the case of dynamic SQL, queries are created, compiled and executed only at the run time. This makes the dynamic SQL little complex, and time consuming.
Since query needs to be prepared at run time, in addition to the structures discussed in embedded SQL, we have three more clauses in dynamic SQL. These are mainly used to build the query and execute them at run time.
Table of Contents
PREPARE
Since dynamic SQL builds a query at run time, as a first step we need to capture all the inputs from the user. It will be stored in a string variable. Depending on the inputs received from the user, string variable is appended with inputs and SQL keywords. These SQL like string statements are then converted into SQL query. This is done by using PREPARE statement.
For example, below is the small snippet from dynamic SQL. Here sql_stmt is a character variable, which holds inputs from the users along with SQL commands. But is cannot be considered as SQL query as it is still a sting value. It needs to be converted into a proper SQL query which is done at the last line using PREPARE statement. Here sql_query is also a string variable, but it holds the string 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
This statement is used to compile and execute the SQL statements prepared in DB.
EXEC SQL EXECUTE sql_query;
EXECUTE IMMEDIATE
This statement is used to prepare SQL statement as well as execute the SQL statements in DB. It performs the task of PREPARE and EXECUTE in a single line.
EXEC SQL EXECUTE IMMEDIATE :sql_stmt;
A simple program that illustrates dynamic SQL is given below
#include
#include
int main(){
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
int STD_ID;
char *STD_NAME;
int CLASS_ID;
char *sql_stmt;
char *sql_query;
EXEC SQL END DECLARE SECTION;
EXEC WHENEVER NOT FOUND GOTO error_msg1;
EXEC WHENEVER SQLERROR GOTO error_msg2;
printf("Enter the Student name:");
scanf("%s", STD_Name);
printf("Enter the Class ID:");
scanf("%d", &CLASS_ID);
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;
EXEC SQL EXECUTE sql_query;
printf("STUDENT ID:%d", STD_ID);
exit(0);
error_msg1:
printf("Student Id %d is not found", STD_ID);
printf("ERROR:%ld", sqlca->sqlcode);
printf("ERROR State:%s", sqlca->sqlstate);
exit(0);
error_msg2:
printf("Error has occurred!");
printf("ERROR:%ld", sqlca->sqlcode);
printf("ERROR State:%s", sqlca->sqlstate);
exit(0);
}Methods of Accessing Dynamic SQL
There are four different methods of using Dynamic SQL. These methods of dynamic SQL are based on the number of host variables and known/unknown list of SELECT list.
Method 1
This type of dynamic SQL will not have any SELECT queries and host variables. But it can be any other SQL statements like insert, delete, update, grant etc. But when we use insert/ delete/ updates in this type, we cannot use host variables. All the input values will be hardcoded. Hence the SQL statements can be directly executed using EXECUTE IMMEDIATE rather than using PREPARE and then EXECUTE.
EXEC SQL EXECUTE IMMEDIATE ‘GRANT SELECT ON STUDENT TO Faculty’; EXEC SQL EXECUTE IMMEDIATE ‘DELETE FROM STUDENT WHERE STD_ID = 100’; EXEC SQL EXECUTE IMMEDIATE ‘UPDATE STUDENT SET ADDRESS = ‘Troy’ WHERE STD_ID =100’;
Here these SQL statements are parsed each time they are executed.
Method 2
This type of SQL will not have any SELECT queries but it can have host variables in it. Hence we can use PREPARE to create SQL statements and EXECUTE to execute the SQL.
sql_stmt = ‘DELETE FROM STUDENT WHERE STD_ID = :SID’; EXEC SQL PREPARE sql_query FROM :sql_stmt; EXEC SQL EXECUTE sql_query;
Here SQL statements are parsed only once by calling PREPAR statement and executed as many times as required by using EXECUTE for different set of values.
Method 3
This type of dynamic SQL allows user to run query – SELECT query dynamically. It accepts inputs from the user and depending upon the user inputs it builds its query at run time and executes it. This method uses PREPARE and EXECUTE to create query and execute it. Here number of host variables and number of columns selected using SELECT query are known at pre-compilation itself.
For example, consider below query :
SELECT STD_ID, STD_NAME FROM STUDENT; SELECT STD_ID, SUM(STD_MARKS) AS total_marks STUDENT GROUP BY STD_ID; SELECT STD_NAME, STD_ADDRESS FROM STUDENT WHERE STD_ID = :SID;
These are all examples of method 3 dynamic SQL. Please note that these queries have to be used with PREPARE and EXECUTE statements. First and second queries above do not have any host variables, but it is a query(SELECT query) and select list columns are known at pre-compilation time itself.
Method 4
This type of dynamic SQL allows user to enter as many conditions as they want and as many columns as they want in the SELECT list. The query gets on building up as the user enters the data and is created and executed at run time. Here host variables and select list columns are not known to the developer or at compilation time. In our example above to get student ID by accepting student name and class id or either of them is the best example of this method. In that example, the query is wholy dependent on user input.
Summary
Main advantage and disadvantages of Embedded and Dynamic SQL are :
Different host languages supported using embedded SQL are listed below. It also shows which version of Oracle supports embedding SQL.
