Lawrence Technological University
College of Arts and Science
Department of Mathematics and Computer Sciences

Handouts

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:

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.

  1. Connect to the database server.
  2. Convert the query logic and client variables to an ASCII-text string in proper SQL syntax.
  3. Pass the SQL to the server for parsing and compiling before running.
  4. Request the query be run or executed.
  5. Retrieve the result set
  6. For every row of the result set, assign each column to a client variable.
  7. Do whatever is needed with each client variable.
  8. Release the result set.
  9. 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.

C is the language most low level RDBMS clients are written in. C, however, has none of the programmer amenities mentioned above.

C++ can provide database programmer amenities, but the dialect is dependent on the compiler.

For some measure of platform independence combined with database programmer friendliness, consider Perl, Python and Ruby.

If you are new to combining Perl, Python and Ruby with MySQL, PostgreSQL and Oracle, consider notes on getting started in embedded SQL using Windows.

Perl, Python and Ruby are also Web programmer friendly. Why this is important.

Revised November 23, 2005