Chapter 7 System Aspects of SQL
SQL in a Programming Environment Transactions Authorization
2020/12/10
1
7.1 SQL in a Programming Environment
Host Languages:
2020/12/10
7
7.1.2 Dynamic SQL
Most applications use specific queries and modification statements in their interaction with the database.
Thus, we can compile the EXEC SQL … statements into specific procedure calls and produce an ordinary host-language program that uses a library.
Any conventional language can be a host language, that is, a language in which SQL calls are embedded.
The use of a host/SQL combination allows us to do anything computable, yet still get the very-high-level SQL interface to the database.
EXEC SQL INSERT INTO Studio(name,address)
VALUES(:studioName, :studioAddr);
}2020/12/10
6
Cursor Statements
Declare a cursor c with:
EXEC SQL DECLARE c CURSOR FOR <query>;
Open and close cursor c with:
EXEC SQL OPEN CURSOR c;
EXEC SQL CLOSE CURSOR c;
Fetch from c by:
EXEC SQL FETCH c INTO <variable(s)>;
Macro NOT FOUND is true if and only if the FETCH fails to find a tuple.
1) Preparing a query:
1. EXEC SQL PREPARE <query-name> FROM <text of the query>;
2) Executing a query:
EXEC SQL EXECUTE <query-name>; “Prepare” = optimize query. Prepare once, execute many.1 Embedded SQL
Key idea: Use a preprocessor to turn SQL statements into procedure calls that fit with the host-language code surrounding. All embedded SQL statements begin with EXEC SQL, so the preprocessor can find them easily.
They may be used as constants provided by the host-language program. They may get values from SQL statements and pass those values to the host-language program.
EXEC SQL BEGIN DECLARE SECTION;
Always needed
<host-language declarations>
EXEC SQL END DECLARE SECTION;
2020/12/10
4
Use of Shared Variables
In SQL, the shared variables must be preceded by a colon.
What if the program is something like a
generic query interface, that doesn’t know what
it needs to do until it runs?
2020/12/10
8
Two steps for Dynamic SQL
2020/12/10
3
Shared Variables
To connect SQL and the host-language program, the two parts must share some variables.
Declarations of shared variables are bracketed by:
In the host language, shared variables behave like any other variable.
2020/12/10
5
Example: C Plus SQL ——Insert a new studio
void printNetWorth() {
EXEC SQL BEGIN DECLARE SECTION;
char char studioName[15];
int presNetWorth;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
/* print request that studio name and address be entered and read response into variables studioName and studioAddr */