Database for education CHAPTER 3, Summaries of Database Programming

Download Database for education CHAPTER 3 and more Summaries Database Programming in PDF only on Docsity!

Chapter 3

Database Programing

Chapter Outline

I. Introduction Database Programming

II. Database Programming Approaches

III. Typical steps in Database Programming

IV. Embedded SQL

V. Database programming language

VI. Database Programming with Function

Calls

VII.Summary

I. Database Programming (contd.)

• Most database access in practical applications is

accomplished through software programs that

implement database applications.

• This software is usually developed in a general-

purpose programming language such as Java, C/C+

+/C#, COBOL, or some other programming

language.

• many scripting languages, such as PHP and

JavaScript, are also being used for programming of

database access within Web applications.

• It is important to note that database programming

is a very broad topic.

II. Database Programming Approaches

(contd.)

Several techniques exist for including database interactions in

application programs. The main approaches for database

programming are the following:

1. Embedded commands :

• Database commands are embedded in a general-purpose

programming language (host language). For example, the prefix for

embedded SQL is the string EXEC SQL, which precedes all SQL

commands in a host language program.

• A precompiler or preproccessor scans the source program code to

identify database statements and extract them for processing by the

DBMS. They are replaced in the program by function calls to the

DBMS-generated code. This technique is generally referred to as

embedded SQL.

3. A brand new, full-fledged language :

A database programming language is designed to

be compatible with the database model and query

language. Additional programming structures such

as loops and conditional statements are added to

the database language to convert it into a full

fledged programming language. An example of this

approach is Oracle’s PL/SQL.

II. Database Programming Approaches

(contd.)

• In practice, the first two approaches are more

common, since many applications are already

written in general-purpose programming languages

but require some database access. The third

approach is more appropriate for applications that

have intensive database interaction.

• One of the main problems with the first two

approaches is impedance mismatch , which does not

occur in the third approach.

II. Database Programming Approaches

(contd.)

III. Typical steps in Database Programming

When a programmer or software engineer writes a program

that requires access to a database, it is quite common for the

program to be running on one computer system while the

database is installed on another. A common architecture for

database access is the client/server model , where a client

program handles the logic of a software application, but

includes some calls to one or more database servers to access

or update the data. When writing such a program, a common

sequence of interaction is the following:

1.Client program opens a connection to the database server

2.Client program submits queries to and/or updates the

database

3.When database access is no longer needed, client program

closes (terminates) the connection

IV. Embedded SQL

We give an overview of the technique for how SQL

statements can be embedded in a general-purpose

programming language. The examples used with the C

language, known as embedded SQL

Most SQL statements can be embedded in a general-

purpose host programming language such as COBOL, C,

Java

Declaring Variables:

SQL statements can refer to Shared variables defined in

the host program. Such host-language variables must be

prefixed by a colon (:) in SQL statements and must be

declared between the commands EXEC SQL BEGIN

DECLARE SECTION and EXEC SQL END DECLARE SECTION.

IV. Embedded SQL (contd.)

Embedded SQL Statements

An embedded SQL statement is distinguished

from the host language statements by

enclosing it between

EXEC SQL … END-EXEC ( or semicolon)

EXEC SQL BEGIN … EXEC SQL END (or

semicolon)

Syntax may vary with language

IV. Embedded SQL (contd.)

Retrieving Single Tuples with Embedded SQL

loop = 1;

while (loop) {

prompt (“Enter SSN: “, ssn);

EXEC SQL

select FNAME, LNAME, ADDRESS, SALARY

into :fname, :lname, :address, :salary

from EMPLOYEE where SSN == :ssn;

if (SQLCODE == 0) printf(fname, …);

else printf(“SSN does not exist: “, ssn);

prompt(“More SSN? (1=yes, 0=no): “, loop);

END-EXEC

}

Note: The INTO clause can be used in this way only

when the query result is a single record;if multiple

records are retrieved, an error will be generated

16

IV. Embedded SQL (contd.)

A few of the common bindings of C types to SQL types

The SQL types INTEGER, SMALLINT, REAL, and

DOUBLE are mapped to the C types long, short, float, and

double, respectively.

Fixed-length and varying-length strings (CHAR[i],

VARCHAR[i]) in SQL can be mapped to arrays of

characters (char [i+1], varchar [i+1]) in C that are one

character longer than the SQL type because strings in C

are terminated by a NULL character (\0),which is not part of

the character string itself.

Although varchar is not a standard C data type, it is

permitted when C is used for SQL database programming.

IV. Embedded SQL (contd.)

Note: communication variables:

SQLCODE == 0 : the statement was executed successfully

SQLCODE > 0 : no more data (records) are available in a

query result

SQLCODE < 0 : some error has occurred

SQLSTATE: a string of five characters

SQLSTATE ==‘00000’: no error or exception

other values indicate various errors or exceptions. For

Example, SQLSTATE ==‘02000’indicates ‘no more data’

it is generally better to use SQLSTATE because this

makes error handling in the application programs

independent of a particular DBMS.

IV. Embedded SQL (contd.)

Retrieving Multiple Tuples with Embedded SQL Using Cursors

In general, an SQL query can retrieve many tuples. In that

case, the C program will typically go through the retrieved

tuples and process them one at a time. The concept of a

cursor is used.

A cursor (iterator) is needed to process multiple tuples

OPEN command executes the query and

fetches its result.

FETCH command move the cursor to the next tuple

CLOSE CURSOR indicates that the processing of query

results has been completed

IV. Embedded SQL (contd.)