Page 88 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 5: Integrity Constraints

Embedded SQL expressions for database modification (update, insert, and delete) don’t return a Notes
result. Thus, they are somewhat simpler to express. A database-modification request takes the
form
EXEC SQL < any valid update, insert, or delete> END-EXEC
Host-language variables, preceded by a colon, may appear in the SQL database modification
expression. If an error condition arises in the execution of the statement, a diagnostic is set in the
SQLCA.

Task Discuss the purpose of GRANT command in SQL

5.5 Dynamic SQL

The dynamic SQL component of SQL-92 allows programs to construct and submit SQL queries at
run time. In contrast, embedded SQL statements must be completely present at compile time,
and are compiled by the embedded SQL preprocessor. Using dynamic SQL, programs can create
SQL queries as strings at run time (perhaps based on input from the user), and can either have
them executed immediately, or have them prepared for subsequent use. Preparing a dynamic
SQL statement compiles it, and subsequent uses of the prepared statement use the compiled
version. The following is an example of the use of dynamic SQL from within a C program.

char * sqlprog = “update account set balance = balance * 1.05
where account-number = ?”
EXEC SQL prepare dynprog from : sqlprog;
char account [10] = “A-101”;
EXEC SQL execute dynprog using : account;
The dynamic SQL program contains a ?, which is a place holder for a value that is provided when
the SQL program is executed.
These are two main commands, PREPARE and EXECUTE, which we illustrate through a simple
example:
char c_sqlstring[] = {“DELETE FROM Sailors WHERE rating>5”};
EXEC SQL PREPARE readytogo FROM : c_sqlstring;
EXEC SQL EXECUTE readytogo;
The first statement declares the C variable c_sqlstring and initializes its value to the string
representation of an SQL command. These second statement results in this string being parsed
and compiled as an SQL command, with the resulting executable bound to the SQL variable
readytogo. (Since readytogo is an SQL variable, just like a cursor name, it is not prefixed by a
colon.) The third statement executes the command.

5.6 Summary

 Database objects allow fields to be defined that are calculated by any specified method
(and not stored in the database).
 They allow referential integrity (the relationships between objects that must be maintained
(e.g. invoice master/detail) to be defined in a database-independent way, they allow a set

LOVELY PROFESSIONAL UNIVERSITY 81