Database Programming
Embedded SQL, SQLJ, Dynamic SQL
SCL/CLI, JDBC
SQL/PSM, PL/SQL
1
Database Programming
⚫ Objective: to access a database from an application
program (as opposed to 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)
2
Database Programming
Approaches
⚫ Three main approaches
⚫ Embedded commands: database commands are embedded in
a general-purpose programming language
⚫ Embedded statements are replaced by function calls to
DBMS-generated code
⚫ Library of database functions: available to the host language
for database calls; known as an API
⚫ Actual query, update commands, other information are passed as
parameters in function calls
⚫ A brand new, full-fledged language (minimizes impedance
mismatch)
3
Impedance Mismatch??
⚫ Incompatibilities between a host programming language
and the database model, e.g.,
⚫ type mismatch and incompatibilities; requires a new binding
for each language
⚫ set vs. record-at-a-time processing
⚫ need special iterators to loop over query results and manipulate
individual values
4
Steps in Database Programming
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 terminates the connection
5
Embedding SQL into HLLs
⚫ Embedded SQL
⚫ SQL is embedded into high level languages such as ADA,C,
PASCAL, COBOL
⚫ SQLJ
⚫ Standard that specify to embed SQL into JAVA
⚫ Dynamic SQL
⚫ To run SQL statements at run time
6
Embedded SQL
⚫ Most SQL statements can be embedded in a
general-purpose host programming language such as
COBOL, C, Java
⚫ Dual mode principle: any statement which can be executed
interactively can be used in an application program
⚫ An embedded SQL statement is distinguished from the
host language statements by EXEC SQL and a matching
END-EXEC (or semicolon)
7
Embedded SQL
⚫ All host language variables that are to be used in SQL
statements must be declared in a DECLARE section
⚫ shared host language variables (used in both
languages) usually prefixed with a colon (:) in SQL
⚫ Data types must be compatible
⚫ SQLCODE (and SQLSTATE) is used to communicate
errors/exceptions between the database and the
program
⚫ Sqlcode=0 statement executed successfully
⚫ Sqlcode>0 (100) no more records in result
⚫ Sqlcode<0 error condition
8
Embedded SQL
⚫ SQLSTATE is a string of five charecters
⚫ Sqlstate= 00000 No error
⚫ Sqlstate= 02000 no more data
⚫ SQLCODE is standardized but SQLSTATE is DBMS
dependent
⚫ WHENEVER – to simplify error checking
⚫ WHENEVER <condition> <action>
⚫ <condition> SQLERROR|NOT FOUND
⚫ <action> CONTINUE|GOTO
9
SQL Commands for
Connecting to a Database
⚫ Connection (multiple connections are possible but only
one is active)
CONNECT TO server-name AS
connection-name
AUTHORIZATION user-account-info;
⚫ Change from an active connection to another one
SET CONNECTION connection-name;
⚫ Disconnection
DISCONNECT connection-name;
10
Example: Variable Declaration
in Language C
int loop;
EXEC SQL BEGIN DECLARE SECTION;
char sn[2], sname[10], city[10];
int status, SQLCODE;
EXEC SQL END DECLARE SECTION;
11
Example Embedded SQL in C:
Retrieving single tuple
loop = 1;
while (loop) {
printf(“Enter sn: “, sn);
EXEC SQL
select sn, sname, status, city
into :sn, :sname, :status, :city
from S where sn == :sn;
END-EXEC
if (SQLCODE == 0) printf(sname,status,city);
else printf(“SN does not exist: “, sn);
printf(“More SN? (1=yes, 0=no): “, loop);
12
Example Embedded SQL in C:
Retrieving multiple tuples
⚫ A cursor (iterator) is needed to process multiple tuples
⚫ A cursor is pointer that points to a single tuple at a time
from the result of a query that retreives multiple tuples
⚫ Commands for a CURSOR
⚫ DECLARE CURSOR- specify the definition for cursor
⚫ OPEN CURSOR- activate the cursor (execute associated
query)
⚫ FETCH- commands move the cursor to the next tuple
⚫ CLOSE CURSOR- indicates that the processing of query
results has been completed
13
Examples: CURSOR
EXEC SQL DECLARE sup CURSOR FOR
Select sname, status
From S Where city=‘london’
FOR UPDATE OF status;
EXEC SQL OPEN sup;
EXEC SQL FETCH FROM sup INTO :sname, :status;
while(sqlcode==0)
{ printf(“supplier name %d”,sname);
printf(“enter raise”);scanf(“%d”,&raise);
EXEC SQL Update S Set status = status+:raise
Where CURRENT OF sup;
EXEC SQL FETCH FROM sup INTO :sname, :status;
}
EXEC SQL CLOSE sup;
14
Dynamic SQL
⚫ Objective: executing new (not previously compiled)
SQL statements at run-time
⚫ a program accepts SQL statements from the keyboard at
run-time
⚫ a point-and-click operation translates to certain SQL query
⚫ Dynamic update is relatively simple; dynamic query
can be complex
⚫ because the type and number of retrieved attributes are
unknown at compile time
15
Dynamic SQL: An Example
EXEC SQL BEGIN DECLARE SECTION;
char sqlupdatestring[256];
EXEC SQL END DECLARE SECTION;
…
prompt (“Enter update command:“,
sqlupdatestring);
EXEC SQL PREPARE sqlcommand FROM
:sqlupdatestring;
EXEC SQL EXECUTE sqlcommand;
16
Embedded SQL in Java
⚫ SQLJ: a standard for embedding SQL in Java
⚫ An SQLJ translator converts SQL statements into Java (to
be executed through the JDBC interface)- it is necessary to
have JDBC interface
⚫ Certain classes, e.g., [Link] have to be imported
17
Database programming
through function calls
⚫ Embedded SQL is static approach
⚫ Query is written within program and cannot be changed
without recompilation
⚫ Programming through API library functions is dynamic
⚫ Query can provided as parameter to function at runtime
⚫ Drawbacks
⚫ SQL syntax checking is done on runtime
⚫ Requires more complex programming to access query
results because types and number of attributes are not
known in advance
18
Database programming
through function calls
⚫ Advantages
⚫ Easy to access multiple databases within the same
application program, even if they are stored under different
DBMS
⚫ No pre-processor needed
⚫ SQL/CLI (call level interface) which is part of SQL
standard and a follow-up of ODBC (open database
connectivity)
⚫ JDBC- a call function interface for accessing databases
from JAVA
19
Database programming using a
procedural database language
⚫ Also supports procedural constructs like variable declaration
conditional and looping structures (FOR loop, WHILE loop,
DO WHILE loop, IF THEN, IF THEN)
⚫ SQL/PSM (Persistent Stored Modules) – a part of SQL
standard
⚫ PL/SQL (Procedural SQL) – Oracle corporation’s
procedural language extension to SQL, also available in
IBM’s DB2
⚫ TransactSQL supported by Microsoft SQLServer, Sybase
⚫ PL/pgSQL suported by PostgreSQL
⚫ IBM DB2 has SQL Procedural Language which conforms to
SQL/PSM
20
Difference between Embedded SQL and Dynamic SQL
Aspect Embedded SQL Dynamic SQL
SQL statements are written
directly inside a host SQL statements are constructed and
Definition
programming language and executed at runtime as strings.
compiled along with it.
Static , queries are fixed and Dynamic , queries can change during
Nature
known at compile time. program execution.
Less flexible; structure of query Highly flexible; can build queries
Flexibility
cannot be modified at runtime. based on user input or conditions.
Execution Parsed and compiled during Parsed, compiled, and executed at
Time program compilation. runtime.
Slightly slower due to runtime
Performance Faster, since SQL is precompiled.
parsing and compilation.
Aspect Embedded SQL Dynamic SQL
When queries are fixed and
When queries depend on user
Use Cases known in advance (e.g., payroll,
inputs or variable table names.
reports).
Uses database commands like
Uses special SQL precompiler EXECUTE IMMEDIATE,
Host Language Interaction
(e.g., EXEC SQL in C/COBOL). PREPARE, or stored
procedures.
EXEC SQL SELECT *
`EXECUTE IMMEDIATE 'SELECT
Example FROM Employee WHERE
* FROM '
ID = :emp_id;
Errors detected at compile Errors detected only at
Error Detection
time. runtime.
Standardized applications with Applications needing runtime
Best For
fixed operations. customization and flexibility.