9/26/2014

Database Programming

• Objective: to access a database from an

application program (as opposed to
Database Programming interactive interfaces)
• Why? An interactive interface is convenient
but not sufficient; a majority of database
operations are made through application
programs (nowadays through web
applications)

University of Botswana - CSI481 Database

9/26/2014 2
Systems

Database Programming
Impedance Mismatch
Approaches
• Embedded commands: database commands are embedded • Incompatibilities between a host
in a general-purpose programming language
• Library of database functions: available to the host programming language and the database
language for database calls; known as an API – for model, e.g.,
accessing a database from application programs e.g.,
functions to – type mismatch and incompatibilities;
– Connect to a database requires a new binding for each language
– execute a query
– Execute an update, etc. – set vs. record-at-a-time processing; need
• A brand new, full-fledged language (minimizes impedance special iterators to loop over query results and
mismatch)
manipulate individual values

University of Botswana - CSI481 Database University of Botswana - CSI481 Database

9/26/2014 3 9/26/2014 4
Systems Systems

Steps in Database Programming Embedded SQL

1. Client program opens a connection to the • Most SQL statements can be embedded in a
database server general-purpose host programming language
2. Client program submits queries to and/or such as C, Java, etc
updates the database • An embedded SQL statement is distinguished
3. When database access is no longer from the host language statements by EXEC
needed, client program terminates the SQL and a matching END-EXEC (or semicolon)
connection – shared variables (used in both languages)
usually prefixed with a colon (:) in SQL
University of Botswana - CSI481 Database University of Botswana - CSI481 Database
9/26/2014 5 9/26/2014 6
Systems Systems

1
 9/26/2014

Example: Variable Declaration SQL Commands for

in Language C Connecting to a Database
• Variables inside DECLARE are shared and can appear • Connection (multiple connections are
(while prefixed by a colon) in SQL statements possible but only one is active)
• SQLCODE is used to communicate errors/exceptions CONNECT TO server-name AS connection-name
between the database and the program AUTHORIZATION user-account-info;
int loop;
• Change from an active connection to
EXEC SQL BEGIN DECLARE SECTION;
varchar dname[16], fname*16+, …; another one
char ssn[10], bdate*11+, …; SET CONNECTION connection-name;
int dno, dnumber, SQLCODE, …; • Disconnection
EXEC SQL END DECLARE SECTION DISCONNECT connection-name;

University of Botswana - CSI481 Database University of Botswana - CSI481 Database

9/26/2014 7 9/26/2014 8
Systems Systems

Embedded SQL in C Embedded SQL in C

Programming Examples Programming Examples
loop = 1;
while (loop) {
• A cursor (iterator) is needed to process
prompt (“Enter IDNO: “, idno); multiple tuples
EXEC SQL
select FNAME, LNAME, ADDRESS, SALARY • FETCH commands move the cursor to the next
into :fname, :lname, :address, :salary tuple
from EMPLOYEE where IDNO == :idno;
if (SQLCODE == 0) printf(fname, …); • CLOSE CURSOR indicates that the processing
else printf(“IDNO does not exist: “, idno); of query results has been completed
prompt(“More SSN? (1=yes, 0=no): “, loop);
END-EXEC
}

University of Botswana - CSI481 Database University of Botswana - CSI481 Database

9/26/2014 9 9/26/2014 10
Systems Systems

Dynamic SQL Dynamic SQL: An Example

• Objective: executing new (not previously EXEC SQL BEGIN DECLARE SECTION;
compiled) SQL statements at run-time varchar sqlupdatestring[256];
– a program accepts SQL statements from the keyboard EXEC SQL END DECLARE SECTION;
at run-time
– a point-and-click operation translates to certain SQL
…
query prompt (“Enter update command:“,
• Dynamic update is relatively simple; dynamic
sqlupdatestring);
query can be complex EXEC SQL PREPARE sqlcommand FROM
– because the type and number of retrieved attributes :sqlupdatestring;
are unknown at compile time EXEC SQL EXECUTE sqlcommand;
University of Botswana - CSI481 Database University of Botswana - CSI481 Database
9/26/2014 11 9/26/2014 12
Systems Systems

2
 9/26/2014

Embedded SQL in Java:

Embedded SQL in Java
An Example
• SQLJ: a standard for embedding SQL in Java idno = readEntry(“Enter an IDNO: “);
try {
• An SQLJ translator converts SQL statements #sql{select FNAME, LNAME, ADDRESS, SALARY
into Java (to be executed through the JDBC into :fname, :lname, :address, :salary
from EMPLOYEE where IDNO = :idno};
interface) }
• Certain classes, e.g., java.sql have to be catch (SQLException se) {
imported System.out.println(“IDNO does not exist: “,+idno);
return;
}
System.out.println(fname+“ “+lname+… );

University of Botswana - CSI481 Database University of Botswana - CSI481 Database

9/26/2014 13 9/26/2014 14
Systems Systems

Database Programming with

Multiple Tuples in SQLJ
Functional Calls
• SQLJ supports two types of iterators: • Embedded SQL provides static database
– named iterator: associated with a query programming
result • API: dynamic database programming
– positional iterator: lists only attribute types with a library of functions
in a query result – advantage: no preprocessor needed (thus
• A FETCH operation retrieves the next tuple in more flexible)
a query result: – drawback: SQL syntax checks to be done at
fetch iterator-variable into program-variable run-time

University of Botswana - CSI481 Database University of Botswana - CSI481 Database

9/26/2014 15 9/26/2014 16
Systems Systems

Java Database Connectivity Steps in JDBC Database Access

• SQL connection function calls for Java 1. Import JDBC library (java.sql.*)
programming 2. Load JDBC driver:
Class.forname(“oracle.jdbc.driver.OracleDriver”)
• A Java program with JDBC functions can 3. Define appropriate variables
access any relational DBMS that has a JDBC 4. Create a connect object (via getConnection)
driver 5. Create a statement object from the
• JDBC allows a program to connect to several Statement class:
databases (known as data sources) 1. PreparedStatement
2. CallableStatement

University of Botswana - CSI481 Database University of Botswana - CSI481 Database

9/26/2014 17 9/26/2014 18
Systems Systems

3
 9/26/2014

Steps in JDBC Database Access ctd. Database Stored Procedures

• Persistent procedures/functions (modules) are
6. Identify statement parameters (to be stored locally and executed by the database
server (as opposed to execution by clients)
designated by question marks)
• Advantages:
7. Bound parameters to program variables
– if the procedure is needed by many
8. Execute SQL statement (referenced by an applications, it can be invoked by any of them
object) via JDBC’s executeQuery (thus reduce duplications)
9. Process query results (returned in an object of – execution by the server reduces
type ResultSet) communication costs
– ResultSet is a 2-dimentional table – enhance the modeling power of views
University of Botswana - CSI481 Database University of Botswana - CSI481 Database
9/26/2014 19 9/26/2014 20
Systems Systems

Stored Procedure Constructs Summary

• A stored procedure • A database may be accessed via an
CREATE PROCEDURE procedure-name (params)
interactive database
local-declarations
procedure-body; • Most often, however, data in a database is
• A stored function manipulated via application programs
CREATE FUNCTION fun-name (params) RETURNS • Several methods of database programming:
return-type
local-declarations – embedded SQL
function-body; – dynamic SQL
• Calling a procedure or function – stored procedure and function
CALL procedure-name/fun-name (arguments
University of Botswana - CSI481 Database University of Botswana - CSI481 Database
9/26/2014 21 9/26/2014 22
Systems Systems