INeed for Dynamic SQL Most applications use specific queries and modification statements to interact with the database. The DBMS compiles EXEC SQL...statements into specific procedure calls and produces an ordinary host-language program that uses a library. Sometimes we don't know what it needs to do until it runs?
Need for Dynamic SQL Most applications use specific queries and modification statements to interact with the database. The DBMS compiles EXEC SQL … statements into specific procedure calls and produces an ordinary host-language program that uses a library. Sometimes we don’t know what it needs to do until it runs?
Dynamic SQL Preparing a query: EXEC SQL PREPARE <query-name> FROM <text of the query>; Executing a query: EXEC SQL EXECUTE <query-name>; "Prepare"=optimize query. Prepare once,execute many times
Dynamic SQL Preparing a query: EXEC SQL PREPARE <query-name> FROM <text of the query>; Executing a query: EXEC SQL EXECUTE <query-name>; “Prepare” = optimize query. Prepare once, execute many times
Example:A Generic Interface EXEC SQL BEGIN DECLARE SECTION; char query[MAX_LENGTH]; EXEC SOL END DECLARE SECTION; while(1){ /issue SQL>prompt * /read user's query into array query * EXEC SQL PREPAREq FROM query; EXEC SQL EXECUTE q is an SQL variable ) representing the optimized form of whatever statement is typed into query
Example: A Generic Interface EXEC SQL BEGIN DECLARE SECTION; char query[MAX_LENGTH]; EXEC SQL END DECLARE SECTION; while(1) { /* issue SQL> prompt */ /* read user’s query into array query */ EXEC SQL PREPARE q FROM :query; EXEC SQL EXECUTE q; } q is an SQL variable representing the optimized form of whatever statement is typed into :query
Execute-Immediate If we are only going to execute the query once,we can combine the PREPARE and EXECUTE steps into one. ■Use: EXEC SQL EXECUTE IMMEDIATE <text>;
Execute-Immediate If we are only going to execute the query once, we can combine the PREPARE and EXECUTE steps into one. Use: EXEC SQL EXECUTE IMMEDIATE <text>;
Example:Generic Interface Again EXEC SOL BEGIN DECLARE SECTION char query [MAX LENGTH]; EXEC SOL END DECLARE SECTION while(1){ /issue e SQL>prompt /read user's query into array query EXEC SOL EXECUTE IMMEDIATE query;
Example: Generic Interface Again EXEC SQL BEGIN DECLARE SECTION; char query[MAX_LENGTH]; EXEC SQL END DECLARE SECTION; while(1) { /* issue SQL> prompt */ /* read user’s query into array query */ EXEC SQL EXECUTE IMMEDIATE :query; }