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 hostlanguage 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>CURSOR FOR <query> A statement EXEC SOL OPEN<cursor>:the cursor is ready to retrieve the first tuple of the relation over which the cursor ranges. EXEC SOL FETCH FROM<cursor INTO <list of variables> EXEC SOL CLOSE <cursor>: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> CURSOR FOR <query> A statement EXEC SQL OPEN<cursor> : the cursor is ready to retrieve the first tuple of the relation over which the cursor ranges. EXEC SQL FETCH FROM < cursor > INTO <list of variables> EXEC SQL CLOSE <cursor>: the cursor is no longer ranges over tuples of the relation