Embedded SQLWhy Isn’t Interactive SQL Enough?Idea of Embedded SQLOracle APIA Sample Pro*C/C++ ProgramPL & SQL VariablesPL & SQL Variables (cont.)Handle SQL ErrorsEmbedded SQL StatementsTransaction Control StatementsSample Program Using A CursorDynamic SQLWhy Dynamic SQLMethods of Dynamic SQLExecute-Immediate StatementPrepare-Execute StatementPro*C/C++ and PL/SQLOracle JDBCJDBC: Program TasksOracle SQLJEmbedded SQL SummaryLook AheadEmbedded SQLJohn OrtizLecture 15 Embedded SQL 2Why Isn’t Interactive SQL Enough? How to do this using interactive SQL?Print a well-formatted transcript of a student with either a name or an idWhat does it take for someone to use SQL? To know schemas, syntax, semantics, mathematics, logic, …Solution?Write application programs to help naïve users to manipulate the data.How to access database from within a program?Lecture 15 Embedded SQL 3Idea of Embedded SQLCombine the power of both SQL & a general purpose programming language.Use (embedded) SQL to perform data retrieval and updates.Use the general purpose programming language (host PL) to perform more complex data processing and to provide a friendly user interface.Lecture 15 Embedded SQL 4Oracle APISupport embedded SQL through five host PLs (pro*languages)C/C++, Cobol, PL/I, Ada, PascalOracle8i supports Java/JDBC and SQLJSQL stmts are placed in host PL programsData flow from database to program variables and vice versaTwo step compilation:Precompilation: prog.pc  prog.ccCompilation: prog.cc  prog.oLecture 15 Embedded SQL 5A Sample Pro*C/C++ ProgramThe program is sample1.pc Common tasks:Declare variables interfacing SQL & host PLPrepare for any SQL errorInclude sqlca (communication area)Use whenever sqlerror, …Provide for error processingConnect to databaseIssue SQL statementsDisconnect the databaseLecture 15 Embedded SQL 6PL & SQL VariablesEvery variable used to get data from & pass data to database must be declared in a declare section exec sql begin declare section; varchar user_name[20], passwd[10]; … exec sql end declare section;Must use SQL data typesCan appear in both SQL (preceded by a colon :user_name) & PL statements (no colon)Lecture 15 Embedded SQL 7PL & SQL Variables (cont.)Oracle Pro*C pre-processor will convert varchar user_name[20]; to: struct { unsigned short len; unsigned char arr[20]; } user_name;SQL string is not ‘\0’-ended, but C string isVariables must have comparable types to get data from table columnsLecture 15 Embedded SQL 8Handle SQL ErrorsSQLCA (p.121): SQL Communication area is a predefined data structure used to pass control information from Oracle to application programError code: sqlca.sqlcode=0 successful; > 0 value not found; < 0 errorCan also use predefined error types in whenever: sqlerror, not found, sqlwarning, …Common error handling actions: goto Label, continue, stop, do func, do break, do return, …Lecture 15 Embedded SQL 9Embedded SQL StatementsEvery SQL statement is preceded by exec sqlCan use all SQL statements plus special ones.Connect, disconnectWhenever Select … into … from …RollbackCommitStatements declare and use cursorsStatements define and execute dynamic queriesLecture 15 Embedded SQL 10Transaction Control StatementsA transaction is modeled by a sequence of database update operationsOperations of a transaction should either all be successfully executed or none is executedWhen a failure occurs, all updates done so far need to be undoneExplicit control:Start: set transaction [read only, read write]Undo: rollbackEnd: commitLecture 15 Embedded SQL 11Sample Program Using A Cursor How does a program handle query result containing more than one tuple?Use a cursor. See sample2.pc A cursor is a “window” through which one tuple can be accessed.A cursor must be declared with a queryOpen cursor executes the queryFetch cursor moves to the next tupleA cursor can be closed and re-openedLecture 15 Embedded SQL 12Dynamic SQL Create SQL statements at run time and then execute the newly created statements.General framework:Declare a host string variable.Place an SQL statement in the variable at run-time.Let the DBMS parse & execute the SQL statement in the host variable.Lecture 15 Embedded SQL 13Why Dynamic SQLConsider relation: Projects(Pno, Name, Budget, Duration)How to write a program to delete projects that satisfy some yet unknown conditions? delete Projects where <unknown>Some possible types of conditions: (1) Budget > 2000000(2) Budget > 2000000 and Duration > 24(3)Name = 'Manned Spacecraft to Mars‘Why isn’t static SQL a viable solution?Lecture 15 Embedded SQL 14Methods of Dynamic SQLMethod 1: Non-query statement w/o host var. delete from emp where deptno = 20Method 2: Non-query w/ known # of input var. delete from emp where empno = :eidMethod 3: Query w/ known select-list & input host variables. select ename from emp where deptno = :dnoMethod 4: Query w/ unknown select-list & var. select <unknown> from empLecture 15 Embedded SQL 15Execute-Immediate Statement A sample program execute immediate causes the dynamic SQL statement to be compiled and executed immediately. General execute immediate statement: exec sql execute immediate :host_variable;Implement Method 1.Lecture 15 Embedded SQL 16Prepare-Execute Statement A sample program prepare stmt_name from :host_variable compiles the SQL query in host_variable and then saves the prepared result in stmt_name. stmt_name is an SQL identifier and needs not be explicitly declared.Implement Method 2: The host_variable can contain any SQL statement other than a query and can have a known number of placeholders. Prepare once, run many times (with different values in input host variables).Lecture 15 Embedded SQL 17Pro*C/C++ and PL/SQLOn Oracle, Pro*C/C++ programs may contain any SQL statement and PL/SQL blocks.See this sample programPrecompiled and stored PL/SQL procedures and functions can be used directly in embedded SQL statement exec sql execute p(…)Lecture 15 Embedded SQL 18Oracle JDBCJDBC (Java Database Connectivity): API that allows Java applications and applets to connect to Oracle databases, send SQL statements, and receive data from databases.Need to set up CLASSPATH environment variable.