Outline

1. Outline • Introduction • Basic SQL • Setting Up and Using PostgreSQL • Advanced SQL • Embeded SQL Lu Wei

2. Embeded SQL • Introduction • SQL Communications Area • Host Language Varibles • Cursors • Static Embedded SQL Statements • Dynamic SQL • ODBC Lu Wei

3. Introduction • We mentioned in chapter 3 that SQL can be used in two ways: • Use SQL interactively by entering the statements at a terminal • Embed SQL statements in a procedural language (programmatic SQL) Lu Wei

4. Introduction • In fact, we can distinguish between two types of programmic SQL • Embeded SQL statements • SQL statements are embeded directly into the program source code and mixed with the host language statements. • Need to be precompiled. • Application Programming Interface (API) • Provide the programmer with a standard set of functions that can be invoked from the software. • Need not to be precompiled. • Open Database Connectivity (ODBC) standard. Lu Wei

5. SQL Communications Area • The DBMS uses an SQL Communications Area (SQLCA) to report runtime errors to the application program. • SQLCA is a data structure that contains error variables and status indicators. • An application program can examine the SQLCA to determine the success or failure of each SQL statement. Lu Wei

6. SQL Communications Area Definition of the SQLCA for Oracle. /** NAME SQLCA: SQL Communication Area. FUNCTION Contains no code. Oracle fills in the SQLCA with status info during the execution of an SQL statement. **/ Lu Wei

7. SQL Communications Area struct sqlca{ char sqlcaid[8]; //contains fixed text “SQLCA” long sqlcabc; //length of SQLCA structure long sqlcode; //SQL return code struct{ short sqlerrml; //length of error message char sqlerrmc[70]; //text of error message } sqlerrm; char sqlerrp[8]; //reserved for future use long sqlerrd[6]; //sqlerrd[2]-number of rows processed char sqlwarn[8]; Lu Wei

8. SQL Communications Area //sqlwarn[0] set to “W” on warning //sqlwarn[1] set to “W” if character string truncated //sqlwarn[2] set to “W” if NULLs eliminated from aggregates //sqlwarn[3] set to “W” if mismatch in columns/host variables //sqlwarn[4] set to “W” when preparing an update/delete without a where-clause //sqlwarn[5] set to “W” due to PL/SQL compilation failure //sqlwarn[6] no longer used //sqlwarn[7] no longer used char sqlext[8]; //reserved for furture use } Lu Wei

9. SQL Communications Area • The SQLCODE is set by the DBMS as follows: • 0 indicates that the statement executed successfully (although there may be warning messages in sqlwarn) • A negative value indicates that an error occurred. The value in SQLCODE indicates the specific error that occurred. • A positive value indicates that the statement executed successfully, but an exceptional condition occurred, such as no more rows returned by a SELECT statement. Lu Wei

10. SQL Communications Area • The WHENEVER statement • Every embedded SQL statement can potentially generate an error. Clearly, checking for success after every SQL statement would be quite laborious. • Oracle precompiler provides an alternative method to simplify error handling. • The format of the WHENEVER statement is EXEC SQL WHENEVER <condition> <action> Lu Wei

11. SQL Communications Area • Condition • -SQLERROR(SQLCODE<0) • -SQLWARNING(SQLCODE>0) • -NOT FOUND • Action • -CONTINUE • -DO, DO BREAK, DO CONTINUE • -GO TO label • -STOP Lu Wei

12. Host Language Varibles • A host language variable is a program variable declared in the host language. It can be either a single variable or a structure. • Host language variables can be used in embedded SQL statements to transfer data from the database into the program, and vice versa. Lu Wei

13. Host Language Varibles • To use a host variable in an embedded SQL statement, the variable name is prefixed by a colon(;). • Example EXEC SQL UPDATE Staff SET salary = salary + :increment WHERE staffNo=‘SL21’; Lu Wei

14. Host Language Varibles • Host language variables must be declared to SQL as well as being declared in the syntax of the host language. • All host variables must be declared to SQL in a BEGIN DECLARE SECTION…END DECLARE SECTION block. EXEC SQL BEGIN DECLARE SECTION; float increment; EXEC SQL END DECLARE SECTION; Lu Wei

15. Host Language Varibles • Indicator variables • Most programming languages do not provide support for unknown or missing values, as represented in the relational model by nulls. • Embedded SQL provides indicator variables to resolve this problem. Lu Wei

16. Host Language Varibles • The meaning of the indicator variable is as follows: • A 0 value means that the associated host variable contains a valid value. • A -1 value means that the associated host variable should be assumed to contain a null(the actual content of the host variable is irrelevant). • A positive indicator value means that the associated host variable contains a valid value, which may have been rounded or truncated. Lu Wei

17. Host Language Varibles • Example EXEC SQL BEGIN DECLARE SECTION; char address[51]; short addressInd; EXEC SQL END DECLARE SECTION; addressInd = -1; EXEC SQL UPDATE privateOwner SET address =:address :addressInd WHERE ownerNo = ‘CO21’; Lu Wei

18. Cursors • We can also retrieve data using the SELECT statement, but the processing is more complicated if the query produces more than one row. • The complication results from the fact that most high-level programming languages can process only individual data items or individual rows of a structure whereas SQL process multiple rows of data. Lu Wei

19. Cursors • To overcome this impedance mismatch, SQL provides a mechanism for allowing the host language to access the rows of a query result one at a time. • Embedded SQL divides queries into two groups: • Single-row queries • Multi-row queries Lu Wei

20. Cursors • Single-row queries • example1 Lu Wei

21. Cursors • Multi-row queries • example2 Lu Wei

22. Static Embedded SQL Statements • In static embedded SQL statement, the pattern of database access is fixed and can be ‘hard-coded’ into the program. • Static SQL does not allow host variables to be used in place of table names or column names. • The embedded SQLs we discussed above are all static SQLs. Lu Wei

23. Static Embedded SQL Statements • In static Embedded SQL, the follow elements must be fixed: • Reserved words (SELECT,UPDATE,DELETE…) • The number of host variables • The data type of each host variable • The database object will be accessed in SQL(table,column,view,index,…) • Or dynamic SQL will be used. Lu Wei

24. Dynamic SQL • In many situations where the pattern of database access is not fixed and is known only at runtime. This requires more flexibility than static SQL. • Dynamic embedded SQL can be used to resolve this problem. Lu Wei

25. Dynamic SQL • The basic idea of dynamic SQL is to place the complete SQL statement to be executed in a host variable. The host variable is then passed to the DBMS to be executed. Lu Wei

26. Dynamic SQL • EXECUTE IMMEDIATE statement EXEC SQL EXECUTE IMMEDIATE [hostVariable|stringLiteral]; Lu Wei

27. Dynamic SQL EXEC SQL BEGIN DECLARE SECTION; float increment; EXEC SQL END DECLARE SECTION; EXEC SQL UPDATE Staff SET salary=salary+:increment WHERE staffNo=‘SL21’; Lu Wei

28. Dynamic SQL EXEC SQL BEGIN DECLARE SECTION; char buffer[100]; EXEC SQL END DECLARE SECTION; sprintf(buffer,”UPDATE Staff SET salary=salary+%f WHERE staffNo=‘SL21’”,increment;); EXEC SQL EXECUTE IMMEDIATE :buffer; Lu Wei

29. Dynamic SQL • The PREPARE and EXECUTE Statements Precompiler Bind utility Execute Compile time Run time （a）static SQL Run time EXECUTE IMMEDIATE statement EXECUTE PREPARE statement Parse statement Validate statement Optimize statement Create execute plan Execute statement Lu Wei （b）动态SQL

30. Dynamic SQL • The format of the PREPARE statement • The format of the EXECUTE statement EXEC SQL PREPARE statementName FROM [hostVariable|stringLiteral] EXEC SQL EXECUTE statementName [USING hostVariable[indicatorVariable][,…]| USING DESCRIPTOR descriptorName] Lu Wei

31. Dynamic SQL EXEC SQL BEGIN DECLARE SECTION; char buffer[100]; float newSalary; char staffNo[6]; EXEC SQL END DECLARE SECTION; SPRINTF(buffer,”update staff set salary=:sal where staffNo=:sn”); EXEC SQL PREPARE stmt FROM :buffer; Lu Wei

32. Dynamic SQL do{ printf(“Enter staff number:”); scanf(“%s”,staffNo); printf(“Enter new salary:”); scanf(“%f”,newSalary); EXEC SQL EXECUTE stmt USING :newSalary,:staffNo; printf(“Enter another(Y/N)?”); scanf(“%c”,nore); } until(more!=‘Y’); Lu Wei

33. Dynamic SQL • Example3 Lu Wei

34. Dynamic SQL • The SQL Descriptor Area • Placeholders are one way to pass parameters to the EXECUTE statement. • An alternative is through a dynamic data structure called the SQL Descriptor Area(SQLDA) • The SQLDA is used when the number of placeholders and their data types are not known when the statement is formulated. Lu Wei

35. ODBC Lu Wei

36. Summary • How SQL statements can be embedded in high-level programming languages. • The difference between static and dynamic embedded SQL • How to write programs that use static or dynamic embedded SQL statements • How to use ODBC to connect to database. Lu Wei