An Introduction to Embedding SQL

This is a short discussion of interacting with a Relational
Database Management System server, by passing in strings of SQL
and retrieving sets of table rows. An huge series of
combinations is possible, but here we will consider MySQL,
PostgreSQL, and Oracle as RDBMS Servers; and C++, Perl, Python
and Ruby as client languages.

The principal reasons for using embedding languages:

It is difficult to make an interface friendly to the end user
with SQL alone.

Many business rules cannot be expressed in simple
declarative SQL. Procedural hacks like triggers are
available as SQL extensions, but do not make maintenance easy
for the programmer.

The steps of a single client server exchange is always about
the same. Some client languages combine some of the steps to
help the programmer. These steps are for SQL statements that
return a result set of rows. Other SQL statements are slightly
simpler.

Connect to the database server.

Convert the query logic and client variables to an ASCII-text
string in proper SQL syntax.

Pass the SQL to the server for parsing and compiling before
running.

Request the query be run or executed.

Retrieve the result set

For every row of the result set, assign each column to a client
variable.

Do whatever is needed with each client variable.

Release the result set.

Release the database connection.

Each of these steps needs to be checked for errors or exceptions.

There are several features of the client language which should be
considered essential by the database applications programmer.

List assignment, to allow fetching a row at a time with the
assignment operator, like: