1 / 22

Embedded SQL

Embedded SQL. John Ortiz. Why 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?

nat
Download Presentation

Embedded SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Embedded SQL John Ortiz

  2. Why 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? Embedded SQL

  3. Idea 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. Embedded SQL

  4. Oracle 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.o Embedded SQL

  5. A 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 database Embedded SQL

  6. PL & 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) Embedded SQL

  7. PL & 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 columns Embedded SQL

  8. Handle 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, … Embedded SQL

  9. Embedded 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 queries Embedded SQL

  10. Transaction 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: commit Embedded SQL

  11. Sample 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-opened Embedded SQL

  12. Dynamic 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. Embedded SQL

  13. Why 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 • Name = 'Manned Spacecraft to Mars‘ • Why isn’t static SQL a viable solution? Embedded SQL

  14. Methods 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 emp Embedded SQL

  15. Execute-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. Embedded SQL

  16. Prepare-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). Embedded SQL

  17. Pro*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(…) Embedded SQL

  18. Oracle 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. • See QueryUnivDB.java Embedded SQL

  19. JDBC: Program Tasks • Import Java SQL API library • Load JDBC driver: Class.forName(…) • Create a DB connection DriveManager.getConnection(<connect string>) • Create a query statement object • Create a result object by executing the query • Process the result • Close the query • Close the connection Embedded SQL

  20. Oracle SQLJ • Java with embedded SQL (prec. w/ #sql) • Part of the new SQL 1999 Standard. • Much easier to use: • Connect to database • Create iterators for query results • Oracle8i only supports JDK1.1 • Need two compilation steps: • sqlj file[.sqlj] • javac file.java • See QueryUnivDB.sqlj Embedded SQL

  21. Embedded SQL Summary • Many additional statements handling communication between database and PL system • Special handling of multiple tuple result (cursor) • Must handle errors and exceptions generated by DBMS • Must pay special attention to program structure (goto, whenever not found,… ) • JDBC & SQLJ have much better DB-PL interface Embedded SQL

  22. Look Ahead • Next topic: Storage System • Read textbook: • Chapter 5 Embedded SQL

More Related