Database - Discussions

Introduction to SQL Programming Techniques

Dr. Buleje

Chapter 10 Outline

◼ Database Programming: Techniques and Issues

◼ Embedded SQL, Dynamic SQL, and SQLJ

◼ Database Programming with Function Calls:

SQL/CLI and JDBC

◼ Database Stored Procedures

and SQL/PSM

◼ Comparing the Three Approaches

Slide 10- 2

Introduction to SQL

Programming Techniques

◼ Database applications

◼ Host language

◼ Data sublanguage

◼ SQL standards

Slide 10- 3

Database Programming: Techniques

and Issues

◼ Interactive interface

◼ Application programs or database

applications

Slide 10- 4

Approaches to Database

Programming

◼ Embedding

◼ Database statements

◼ Pre-compiler or pre-processor scans the source

program code

◼ Called embedded SQL

◼ Using a library of database functions

◼ Designing a brand-new language

Slide 10- 5

Typical Sequence of Interaction in

Database Programming

◼ Open a connection

◼ Interact with database

◼ Terminate or close connection

Slide 10- 8

Embedded SQL, Dynamic SQL, and

SQLJ

◼ Embedded SQL

◼ SQLJ

◼ Programming language called host language

Slide 10- 9

Retrieving Single Tuples with

Embedded SQL

◼ EXEC SQL

◼ Shared variables

◼ Used in both the C & Embedded SQL

◼ Prefixed by a colon (:) in SQL statement

Slide 10- 10

C program variables used in the embedded SQL Example

Slide 10- 11

Retrieving Single Tuples with

Embedded SQL (cont’d.)

◼ Connecting to the database CONNECT TO <server name>AS <connection name>

AUTHORIZATION <user account name and password> ;

◼ Change connection SET CONNECTION <connection name> ;

◼ Terminate connection DISCONNECT <connection name> ;

Slide 10- 12

Retrieving Single Tuples with

Embedded SQL (cont’d.)

◼ SQLCODE and SQLSTATE communication

variables

◼ SQLCODE variable

Slide 10- 13

Retrieving Multiple Tuples with

Embedded SQL Using Cursors

◼ Cursor

◼ OPEN CURSOR command

◼ FETCH commands

◼ FOR UPDATE OF

Slide 10- 16

Specifying Queries at Runtime Using

Dynamic SQL

◼ Dynamic SQL

◼ Execute different SQL queries or updates

dynamically at runtime

◼ Dynamic update

◼ Dynamic query

Slide 10- 19

SQLJ: Embedding SQL Commands

in Java

◼ Standard adopted

◼ Import several class libraries

◼ Uses exceptions for error handling

Slide 10- 21

Retrieving Multiple Tuples in SQLJ

Using Iterators

◼ Iterator

◼ Named iterator

◼ Positional iterator

Slide 10- 25

Database Programming with Function

Calls: SQL/CLI & JDBC

◼ Use of function calls

◼ Library of functions

◼ SQL Call Level Interface (SQL/CLI)

Slide 10- 28

SQL/CLI: Using C

as the Host Language

◼ Environment record

◼ Connection record

◼ Statement record

◼ Description record

◼ Handle to the record

Slide 10- 29

JDBC: SQL Function Calls for Java

Programming

◼ JDBC

◼ These are Java function libraries

◼ Single Java program can connect to several

different databases

◼ Called data sources accessed by the Java

program

Slide 10- 33

Database Stored Procedures

and SQL/PSM

◼ Stored procedures

◼ SQL/PSM (SQL/Persistent Stored Modules)

Slide 10- 37

Comparing the Three Approaches

◼ Embedded SQL Approach

◼ Library of Function Calls Approach

◼ Database Programming Language Approach

Slide 10- 44

Summary

◼ Techniques for database programming

◼ Embedded SQL

◼ SQLJ

◼ Function call libraries

◼ SQL/CLI standard

◼ JDBC class library

◼ Stored procedures

◼ SQL/PSM

Slide 10- 46