上海交通大学：《数据库系统原理 The principle of Database System》课程教学资源（课件讲稿）chapter9 SQL in a server environment

Chapter 9 SQL in a server environment SQL in a Programming Environment embedded SQL persistent stored modules Database-Connection Libraries Call-level interface (CLI) JDBC PHP

Chapter 9 SQL in a server environment „ SQL in a Programming Environment embedded SQL persistent stored modules „ Database-Connection Libraries Call-level interface (CLI) JDBC PHP

SQL in Real Programs We have seen only how SQL is used at the generic query interface --an environment where we sit at a terminal and ask queries of a database. Reality is almost always different: conventional programs interacting with SQL

SQL in Real Programs „ We have seen only how SQL is used at the generic query interface --- an environment where we sit at a terminal and ask queries of a database. „ Reality is almost always different: conventional programs interacting with SQL

Options 1.SQL statements are embedded in a host language (e.g.,C). 2. Code in a specialized language is stored in the database itself (e.g., PSM,PL/SQL). 3. Connection tools are used to allow a conventional language to access a database (e.g.,CLI,JDBC,PHP/DB)

Options 1. SQL statements are embedded in a host language (e.g., C). 2. Code in a specialized language is stored in the database itself (e.g., PSM, PL/SQL). 3. Connection tools are used to allow a conventional language to access a database (e.g., CLI, JDBC, PHP/DB)

SQL in a Programming Environment Embedded SOL:add to a conventional programming language(C for example, we called host language )certain statements that represent SQL operation Host language+embedded SQL> code?

SQL in a Programming Environment „ Embedded SQL: add to a conventional programming language (C for example, we called host language ), certain statements that represent SQL operation. „ Host language+embedded SQL Æ code?

System Implementation Host Language Embedded SQL Preprocessing Host Language Function calls Host-language compiler SQL library Object-code program How to identify SQL statements? How to move data between SQL and a conventional programming language? Mismatch problem exists?

System Implementation „ How to identify SQL statements? „ How to move data between SQL and a conventional programming language? „ Mismatch problem exists? Host Language + Embedded SQL Preprocessing Host Language + Function calls Host-language compiler SQL library Object-code program

How to recognize SOL statements (the Interface between SOL statements and programming language) Each embedded SOL statement introduced with EXEC SQL ■ Shared variables exchange data between SQL and a host language.When they are referred by a SQL statement,these shared variables are prefixed by a colon,but they appear without colon in host- language statements. EXEC SOL BEGIN /END DECLARE SECTION to declare shared variables

How to recognize SQL statements (the Interface between SQL statements and programming language) „ Each embedded SQL statement introduced with EXEC SQL „ Shared variables : exchange data between SQL and a host language. When they are referred by a SQL statement, these shared variables are prefixed by a colon, but they appear without colon in host￾language statements. „ EXEC SQL BEGIN / END DECLARE SECTION to declare shared variables

the Interface between SOL statements and programming language SOL define an array of characters SQLSTATE that is set every time the system is called. ■ SOLSTATE connects the host-language program with the SQL execution system. 00000:no error √02000：could not be found

the Interface between SQL statements and programming language „ SQL define an array of characters SQLSTATE that is set every time the system is called. „ SQLSTATE connects the host-language program with the SQL execution system. 9 00000: no error 9 02000: could not be found

Implementations of SQLSTATE SQL defines an array of characters SQLSTATE that is set every time the system is called. Errors are signaled there Different systems use different way Oracle provides us with a header file sqlca.h that declares a communication area and defines macros to access it,such as NOT FOUND. Sybase provides SQLCA with sqlcode 0:success,<0:fail,100:not found

Implementations of SQLSTATE SQL defines an array of characters SQLSTATE that is set every time the system is called. „ Errors are signaled there „ Different systems use different way „ Oracle provides us with a header file sqlca.h that declares a communication area and defines macros to access it, such as NOT FOUND. „ Sybase provides SQLCA with sqlcode 0:success, <0: fail, 100: not found

Example:Find the price for a given beer at a given bar Sells (bar,beer,price) EXEC SOL BEGIN DECLARATION SECTION CHAR theBar[21],theBeer[21]; Float thePrice; EXEC SOL END DECLARAE SECTION EXEC SQL SELECT price INTO thePrice FROM sells WHERE beer =theBeer AND bar =theBar;

Example: Find the price for a given beer at a given bar Sells (bar, beer, price) EXEC SQL BEGIN DECLARATION SECTION CHAR theBar[21], theBeer[21]; Float thePrice; EXEC SQL END DECLARAE SECTION EXEC SQL SELECT price INTO :thePrice FROM sells WHERE beer = :theBeer AND bar =:theBar ;

Queries produce sets of tuples as a result,while none of the major host languages supports a set data type directly.So,cursors are used. A cursor declaration:EXEC SOL DECLARE CURSOR FOR A statement EXEC SOL OPEN:the cursor is ready to retrieve the first tuple of the relation over which the cursor ranges. EXEC SOL FETCH FROM EXEC SOL CLOSE :the cursor is no longer ranges over tuples of the relation

Queries produce sets of tuples as a result, while none of the major host languages supports a set data type directly. So, cursors are used. „ A cursor declaration: EXEC SQL DECLARE CURSOR FOR „ A statement EXEC SQL OPEN : the cursor is ready to retrieve the first tuple of the relation over which the cursor ranges. „ EXEC SQL FETCH FROM INTO „ EXEC SQL CLOSE : the cursor is no longer ranges over tuples of the relation