Database Programming
CS2008 Database Systems
CS2011 Database Systems Practice
Prof. Sivaselvan B
Professor, Dept. of CSE
IIITDM Kancheepuram
January, 2024
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 1 / 29
Contents
1 Database Programming
2 Function Calls
3 Database Programming with Function Calls
4 Database Stored Procedures and SQL/PSM
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 2 / 29
Database Programming
Database Programming
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 3 / 29
Database Programming
Approaches to database programming
Embedding database commands in a general purpose programming
language where Database statements identified by a special prefix
Precompiler or preprocessor scans the source program code, Identify
database statements and extract them for processing by the DBMS
called embedded SQL.
Using a library of database functions available to the host
programming language - Application programming interface (API).
Designing a brand-new language - Database programming language
designed from scratch.
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 4 / 29
Database Programming
Impedance Mismatch
Differences between database model and programming language model
Binding for each host programming language
Specifies for each attribute type the compatible programming language
types
Cursor or iterator variable
Loop over the tuples in a query result
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 5 / 29
Database Programming
Interaction in Database Programming
Open a connection to database server.
Interact with database by submitting queries, updates, and other
database commands.
Terminate or close connection to database
Some database programming languages are: Embedded SQL (C
Language), SQLJ (Java). Programming Languages are called host
languages.
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 6 / 29
Function Calls
Function Calls
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 7 / 29
Function Calls
Retrieving Single Tuples with Embedded SQL
EXEC SQL
Prefix
Preprocessor separates embedded SQL statements from host language
code
Terminated by a matching END-EXEC or by a semicolon (;)
Shared Variables
Used in both the C program and the embedded SQL statements
Prefixed by a colon (:) in SQL statement
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 8 / 29
Function Calls
Retrieving Single Tuples with Embedded SQL
SQLCODE and SQLSTATE communication variables
Used by DBMS to communicate exception or error conditions
SQLCODE 0 = statement executed successfully
SQLCODE 100 = no more data available in query result
SQLCODE < 0 = indicates some error has occurred
SQLSTATE is a string of five characters, ‘00000’ = no error or
exception. Other values indicate various errors or exceptions.
For example, ‘02000’ indicates ‘no more data’ when using SQLSTATE.
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 9 / 29
Function Calls
Variables used in Embedded SQL
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 10 / 29
Function Calls
Retrieving Single Tuples with Embedded SQL
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> ;
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 11 / 29
Function Calls
C Program segment E1 with Embedded SQL
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 12 / 29
Function Calls
Retrieving Multiple Tuples with Embedded SQL Using
Cursors
Cursor - Points to a single tuple (row) from result of query.
OPEN CURSOR
Fetches query result and sets cursor to a position before first row in
result
Becomes current row for cursor
FETCH - Moves cursor to next row in result of query
FOR UPDATE OF - List the names of any attributes that will be
updated by the program
Fetch orientation - Added using value: NEXT, PRIOR, FIRST,
LAST, ABSOLUTE i, and RELATIVE i
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 13 / 29
Function Calls
C Program Segment E2 that uses cursors with Embedded
SQL
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 14 / 29
Function Calls
Specifying Queries at Runtime Using Dynamic SQL
Dynamic SQL - Execute different SQL queries or updates dynamically
at runtime.
Dynamic update
Dynamic query
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 15 / 29
Function Calls
C Program Segment E3 that uses dynamic SQL for
updating a table
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 16 / 29
Database Programming with Function Calls
Database Programming with Function Calls
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 17 / 29
Database Programming with Function Calls
Database Programming with Function Calls
Dynamic approach for database programming
Library of functions - Also known as application programming
interface (API)
Used to access database
SQL Call Level Interface (SQL/CLI) - Part of SQL standard
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 18 / 29
Database Programming with Function Calls
SQL/CLI: Using C as the Host Language
Environment record
Track one or more database connections
Set environment information
Connection record - Keeps track of information needed for a
particular database connection
Statement record - Keeps track of the information needed for one
SQL statement
Description record - Keeps track of information about tuples or
parameters
Handle to the record - C pointer variable makes record accessible to
program
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 19 / 29
Database Programming with Function Calls
Program segment CLI1, a C program segment with
SQL/CLI
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 20 / 29
Database Programming with Function Calls
Program segment CLI2, a C program segment that uses
SQL/CLI for a query with a collection of tuples
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 21 / 29
Database Stored Procedures and SQL/PSM
Database Stored Procedures and SQL/PSM
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 22 / 29
Database Stored Procedures and SQL/PSM
Database Stored Procedures and SQL/PSM
Stored procedures
Program modules stored by the DBMS at the database server
Can be functions or procedures
SQL/PSM (SQL/Persistent Stored Modules)
Extensions to SQL
Include general-purpose programming constructs in SQL
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 23 / 29
Database Stored Procedures and SQL/PSM
Database Stored Procedures and Functions
Persistent stored modules are stored persistently by the DBMS.
Uselful when database program is needed by several applications.
Reduces data transfer and communication cost between client and
server in certain situations.
Enhances modeling power provided by views.
Declaring stored procedures:
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 24 / 29
Database Stored Procedures and SQL/PSM
Database Stored Procedures and Functions
Each parameter has parameter type
Parameter type: one of the SQL data types
Parameter mode: IN, OUT, or INOUT
Calling a stored procedure:
Conditional branching statement:
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 25 / 29
Database Stored Procedures and SQL/PSM
SQL/PSM - Constructs for Looping
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 26 / 29
Database Stored Procedures and SQL/PSM
Declaring a function in SQL/PSM
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 27 / 29
Database Stored Procedures and SQL/PSM
Comparing the three approaches
Embedded SQL Approach
Query text checked for syntax errors and validated against database
schema at compile time.
For complex applications where queries have to be generated at
runtime.
Function call approach more suitable.
Library of Function Calls Approach
More flexibility
More complex programming
No checking of syntax done at compile time
Database Programming Language Approach
Does not suffer from the impedance mismatch problem
Programmers must learn a new language
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 28 / 29
Acknowledgement
Thank you!
sivaselvanb@iiitdm.ac.in IIITDM Kancheepuram January 2024 29 / 29
