Dynamic SQL, unlike embedded SQL statements, are built at the run time and placed in a string in a host variable. The created SQL statements are then sent to the DBMS for processing. Dynamic SQL is generally slower than statically embedded SQL as they require complete processing including access plan generation during the run time.However, they are more powerful than embedded SQL as they allow run time application logic. The basic advantage of using dynamic embedded SQL is that we need not compile and test a new program for a new query.
Let us explain the use of dynamic SQL with the help of an example:
Example: Write a dynamic SQL interface that allows a student to get and modify permissible details about him/her. The student may ask for subset of information also. Assume that the student database has the following relations.
STUDENT (enrolno, name , dob)
RESULT (enrolno, coursecode, marks)
In the table above, a student has access rights for accessing information on his/her enrolment number, but s/he can’t update the data. Assume that user names are enrolment number.
/* declarations in SQL */
EXEC SQL BEGIN DECLARAE SECTION;
Char inputfields (50);
Char tablename (10);
Char sqlquery ystring (20);
EXEC SQL END DECLARE SECTION;
Printf(“Enter the fields you want to see \n”);
Scanf(“SELECT %s”, inputfields);
Printf(“Enter the name of table STUDENT or RESULT”);
Scanf(“FROM %s”, tablename);
Sqlqueryystring= “SELECT” +inputfields+””+”FROM” +tablename+ “WHERE enrolno + “USER”
/*Plus is used as a symbol for concatenation operator; in some DBMS it may be ||*/
/* Assumption: the user name is available in the host language variable USER */
EXEC SQL PREPARE sqlcommand FROM: sqlqueryystring;
EXEC SQL EXECUTE sqlcommand;
Please note the following points in the example above.
- The query can be entered completely as a string by the user or s/he can be suitably prompted.
- The query can be fabricated using a concatenation of strings. This is language dependent in the example and is not a portable feature in the present query.
- The query modification of the query is being done keeping security in mind.
- The query is prepared and executed using a suitable SQL EXEC commands.
