Outline
This presentation is the property of its rightful owner.
Sponsored Links
1 / 36

Outline PowerPoint PPT Presentation


  • 79 Views
  • Uploaded on
  • Presentation posted in: General

Outline. Introduction Basic SQL Setting Up and Using PostgreSQL Advanced SQL Embeded SQL. Embeded SQL. Introduction SQL Communications Area Host Language Varibles Cursors Static Embedded SQL Statements Dynamic SQL ODBC. Introduction.

Download Presentation

Outline

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Outline

Outline

  • Introduction

  • Basic SQL

  • Setting Up and Using PostgreSQL

  • Advanced SQL

  • Embeded SQL

Lu Wei


Embeded sql

Embeded SQL

  • Introduction

  • SQL Communications Area

  • Host Language Varibles

  • Cursors

  • Static Embedded SQL Statements

  • Dynamic SQL

  • ODBC

Lu Wei


Introduction

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


Introduction1

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


Sql communications area

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


Sql communications area1

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


Sql communications area2

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


Sql communications area3

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


Sql communications area4

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


Sql communications area5

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


Sql communications area6

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


Host language varibles

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


Host language varibles1

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


Host language varibles2

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


Host language varibles3

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


Host language varibles4

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


Host language varibles5

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


Cursors

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


Cursors1

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


Cursors2

Cursors

  • Single-row queries

    • example1

Lu Wei


Cursors3

Cursors

  • Multi-row queries

    • example2

Lu Wei


Static embedded sql statements

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


Static embedded sql statements1

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


Dynamic sql

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


Dynamic sql1

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


Dynamic sql2

Dynamic SQL

  • EXECUTE IMMEDIATE statement

EXEC SQL EXECUTE IMMEDIATE [hostVariable|stringLiteral];

Lu Wei


Dynamic sql3

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


Dynamic sql4

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


Dynamic sql5

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


Dynamic sql6

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


Dynamic sql7

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


Dynamic sql8

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


Dynamic sql9

Dynamic SQL

  • Example3

Lu Wei


Dynamic sql10

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


Outline

ODBC

Lu Wei


Summary

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


  • Login