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.