There are a lot of syntactic and systems details related to this topic. I do not
expect you to memorize them. I will focus on the interesting
high-level differences among the various approaches to combining SQL
with procedural programming and using SQL as a part of other software
systems. When you actually use these methods (e.g. in the project), you can get the details from documentation.
DMBS API Organization
(diagram)
We will be looking at three main ways that SQL is combined with procedural programming:
Embedded SQL: mix SQL with procedural language
CLI (Call level interface): use DB API, usually through a DB-independent library layer
Stored Procedures: procedural programs stored in the DB
Impedance Mismatch Problem - syncing up the data model of a DB with that of a programming language
data types
big data
data consistency (concurrent access & updates)
Embedded SQL
SQL statments are interleaved with procedural progam statements.
Data transfer is done through declared shared variables
A
pre-processor converts the embedded SQL program to a valid procedural
program by replacing SQL staments with DB API function calls.
(diagram)
Because SQL appears in source file, it's clumsy to customize queries programmatically.
Call Level Interface (Using a DB API)
This is by far the most common way to use SQL within other software.
Data transfer done via return values from API function calls (Java) or by giving target pointers as API parameters (C).
Big advantage: DB independence.
(diagram)
Stored Procedures
aka PSM, persistent stored modules
code is stored in the database
Can look like embedded SQL (e.g. PL/SQL) or like a normal program calling DB API functions (e.g. Java).
Example: Oracle PL/SQL (instance of SQL standard's SQL/PSM)
SQL/PSM has very rich SQL/procedural integration.
Integration features:
Where can we call stored procedures?
General SQL Programming Issues
Cursors
In all the methods described here, results of a query are handled with a cursor.
Like an iterator in C++ or python. Gives access to query result one row at a time
Are often buffered
Cursor Features:
Updateable vs. Read-only
Advantages of Updateable
Advantages of Read-Only
Scrollable
Advantages of Scrollable
Advantages of non-Scrollable
Server-side vs Client Side
Advantages of Server-side
Advantages of Client-side
Error Handling
DB programming is error handling programming.
Sources of errors (list)
Whatever
SQL programming method you use, bu sure you learn how to use the
language's error-handling features (usually some kind of exepctions).
For long-running queries, you often need to use some kind of call-back infrastructure.
DB support for cancelling long-running queries is spotty.