CS145 Lecture Notes (4) -- SQL + Procedural Programming

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


We will be looking at three main ways that SQL is combined with procedural programming:

Impedance Mismatch Problem - syncing up the data model of a DB with that of a programming language

Embedded SQL


Call Level Interface (Using a DB API)


Stored Procedures

Integration features:

Where can we call stored procedures?

General SQL Programming Issues


Cursor Features:
Updateable vs. Read-only
Advantages of UpdateableAdvantages of Read-Only

Advantages of ScrollableAdvantages of non-Scrollable

Server-side vs Client Side
Advantages of Server-sideAdvantages 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.