Dynamic SQL/Oracle Tutorial

http://www.cs.ubc.ca/~ramesh/cpsc304/tutorial/DynamicSQL/dynamicSQL1.html

PREPARE:


VARCHAR host_string[64];
char *examCount = "SELECT driver_sin, count(exam_score) FROM exam WHERE exam_type = :typ GROUP BY driver_sin";
strcpy((char*)host_string.arr, "INSERT INTO branch VALUES (:bid, :bname, :baddr, :bcity, :bphone)");
host_string.len = strlen((char*)host_string.arr);

EXEC SQL PREPARE stmt FROM :host_string;
EXEC SQL PREPARE stmt2 FROM "INSERT INTO branch VALUES (:bid, :bname, :baddr, :bcity, :bphone)";
EXEC SQL PREPARE stmt3 FROM :examCount;



EXECUTE:


int branchID;
char branchName[20];
char branchAddr[50];
char branchCity[20];
int branchPhone;
short int branchAddr_ind;
short int branchPhone_ind;
int branchID2;
char branchName2[20];
char branchAddr2[50];
char branchCity2[20];
int branchPhone2;
short int branchAddr2_ind;
short int branchPhone2_ind;

/* get values for your host variables */

EXEC SQL EXECUTE stmt USING :branchID, :branchName, :branchAddr:branchAddr_ind, :branchCity, :branchPhone:branchPhone_ind;

EXEC SQL EXECUTE stmt USING :branchID2, :branchName2, :branchAddr2:branchAddr2_ind, :branchCity2, :branchPhone2:branchPhone2_ind;

A statement can be re-executed using different host variables without needing a re-PREPARE. A re-PREPARE is only necessary when you want to associate a statement name with another statement.