Classic procedural languages

Classic procedural languages are the first languages used to program computers and their descendants. The very first languages were machine languages, in which both instructions and data were represented as ones and zeros. Digital computers are binary machines, and ones and zeros are the only things they understand. Unfortunately, long sequences of ones and zeros aren’t particularly easy for humans to understand, so it wasn’t long before machine language was superseded — first by assembly language and then by compiled high-level languages such as Fortran and COBOL. C and Basic are examples of more recent classic procedural languages.

Classic procedural languages such as C and Basic are complete programming languages. They can implement any procedure that can be represented in algorithmic form. They operate primarily by executing one command after another in sequence, although they also have flow of control structures that enable them to branch, either unconditionally or depending on the value of a condition. They also support loops, which enable a program to execute a section of code repeatedly. SQL, as defined by the SQL-92 international standard, didn’t have these capabilities. Additions to the standard that became part of SQL:1999 have added some of these capabilities, but not all implementations of SQL have been upgraded yet to support them.

Object-oriented procedural languages

Object-oriented programming, the first incarnation of which was Simula-67 in 1967, came into its own in the 1990s, when it became the predominant programming paradigm. Large, complex software projects that would have been very difficult to build with one of the classical procedural languages were easier to accomplish with one or another of the object-oriented languages, such as C++, Java, C#, Python, or Visual Basic.NET.

The fundamental unit of a program written in an object-oriented language is the object, whereas the instruction is the fundamental unit of a classic procedural language program. Each object in an object-oriented program can receive messages sent by other objects, process data, and send messages to other objects.

Object-oriented code is intrinsically modular, which makes object-oriented programs easier to develop, understand, and maintain than programs generated according to the earlier classic paradigm. Objects are instances of classes. A class has associated attributes and methods. Attributes are characteristics of a class, and methods are actions that members of the class can perform.

Nonprocedural languages

SQL is an example of a nonprocedural language. Rather than deal with the data in a table one row at a time, it deals with data a set at a time, which means that a query may return a result set containing multiple rows. By contrast, procedural languages (both classic and object-oriented) process tables one row at a time and return data to the application the same way: one row at a time.

In the early days of relational databases, other nonprocedural languages competed with SQL. Among these competitors were QUEL and RDML. QUEL was the data sublanguage of the Ingres database management system (DBMS) that was developed at the University of California at Berkeley and later commercialized. Now it’s sold as an open source product by Ingres Corp. Due to the overwhelming acceptance of SQL in the marketplace, SQL syntax has been added to QUEL. RDML is the data sublanguage for Digital Equipment Corp.’s Rdb relational database products. Alas, both Rdb and Digital Equipment itself have passed into history.

Although SQL was developed by IBM, it was adopted at a very early stage by the company that was to become Oracle Corp. Other DBMS vendors followed suit, and SQL became a de facto standard that was codified into a recognized official standard in 1986. SQL didn’t beat out QUEL and RDML because of its technical superiority, which was debatable. It won because of IBM’s market clout and because Oracle’s early adoption of it started a domino effect of DBMS vendors joining the club and supporting SQL.

Challenges of using SQL with a classical procedural language

It makes sense to try to combine SQL and procedural languages in such a way that you can benefit from their combined strengths and not be penalized by their combined weaknesses. As valuable as such a combination may be, some challenges must be overcome before it can be achieved practically.

Challenges of using SQL with an object-oriented procedural language

The challenges mentioned in the preceding section with regard to using SQL with classic procedural languages apply equally to using SQL with object-oriented procedural languages. Added to those challenges are incompatibilities often called the impedance mismatch between SQL and object-oriented languages.

The original context of the term impedance mismatch comes from electrical engineering. Different parts of an electrical circuit may have different impedance values, and connecting two such circuit elements can cause problems. As a simple example, suppose that an audio speaker with an intrinsic impedance of 8 ohms is connected to a line with a 50-ohm impedance value. The result will sound attenuated, distorted, and noisy. In the context of SQL and object-oriented procedural languages, similar problems occur. The row and column organization of relational tables doesn’t mesh well with the hierarchical class/object paradigm of object-oriented programming.

Database vendors have addressed the impedance-mismatch problem by adding object-oriented features to their relational database products, turning their hybrid products into object-relational DBMSs. The object-oriented features added to such products as Db2, Oracle, and SQL Server were codified in the SQL:1999 international standard.

SQL:1999 notwithstanding, the marriage of SQL with object-oriented languages such as C++, C#, and Visual Basic.NET isn’t a perfect one. Difficulties remain, but these difficulties are manageable.

Embedding SQL in an Oracle Pro*C application

As an example of embedded SQL, look at a program written in Oracle’s Pro*C version of the C language. The program, which accesses a company’s employee table, prompts the user for an employee name and then displays that employee’s salary and commission. Then it prompts the user for new salary and commission data, and updates the employee table with it, as follows:

EXEC SQL BEGIN DECLARE SECTION;

VARCHAR uid[20];

VARCHAR pwd[20];

VARCHAR ename[10];

FLOAT salary, comm;

SHORT salary_ind, comm_ind;

EXEC SQL END DECLARE SECTION;

main()

{

int sret; /* scanf return code */

/* Log in */

strcpy(uid.arr,"FRED"); /* copy the user name */

uid.len=strlen(uid.arr);

strcpy(pwd.arr,"TOWER"); /* copy the password */

pwd.len=strlen(pwd.arr);

EXEC SQL WHENEVER SQLERROR STOP;

EXEC SQL WHENEVER NOT FOUND STOP;

EXEC SQL CONNECT :uid;

printf("Connected to user: percents ",uid.arr);

printf("Enter employee name to update: ");

scanf("percents",ename.arr);

ename.len=strlen(ename.arr);

EXEC SQL SELECT SALARY,COMM INTO :salary,:comm

FROM EMPLOY

WHERE ENAME=:ename;

printf("Employee: percents salary: percent6.2f comm: percent6.2f ",

ename.arr, salary, comm);

printf("Enter new salary: ");

sret=scanf("percentf",&salary);

salary_ind = 0;

if (sret == EOF !! sret == 0) /* set indicator */

salary_ind =-1; /* Set indicator for NULL */

printf("Enter new commission: ");

sret=scanf("percentf",&comm);

comm_ind = 0; /* set indicator */

if (sret == EOF !! sret == 0)

comm_ind=-1; /* Set indicator for NULL */

EXEC SQL UPDATE EMPLOY

SET SALARY=:salary:salary_ind

SET COMM=:comm:comm_ind

WHERE ENAME=:ename;

printf("Employee percents updated. ",ename.arr);

EXEC SQL COMMIT WORK;

exit(0);

}

You don’t have to be an expert in C to understand the essence of what this program is doing and how the program does it. Here’s a rundown of the order in which the statements execute:

1. SQL declares host variables.
2. C code controls the user login procedure.
3. SQL sets up error handling and connects to the database.
4. C code solicits an employee name from the user and places it in a variable.
5. An SQL SELECT statement retrieves the named employee’s salary and commission data, and stores it in the host variables :salary and :comm.
6. C takes over again, displaying the employee’s name, salary, and commission data, and then soliciting new values for salary and commission. It also checks whether an entry has been made, and if an entry hasn’t been made, it sets an indicator.
7. SQL updates the database with the new values.
8. C displays an “operation complete” message.
9. SQL commits the transaction, and C exits the program.

You can mix the commands of two languages this way because of the preprocessor. The preprocessor separates the SQL statements from the host language commands, placing the SQL statements in a separate external routine. Each SQL statement is replaced by a host-language call of the corresponding external routine, and then the language compiler can do its job. The way that the SQL part is passed to the database is implementation-dependent. You, as the application developer, don’t have to worry about any of this process; the preprocessor takes care of it. You should be concerned, however, about a few things that don’t appear in interactive SQL — things such as host variables and incompatible data types.

Interactive SQL is a conversation that takes place between a user entering SQL statements by hand at a terminal and a DBMS responding to those statements. SQL statements embedded in a procedural language program have some complications that go beyond what you encounter with interactive SQL.

Embedding SQL in a Java application

SQLJ is the tool to use to embed SQL in a Java program. The process is similar to the way that SQL statements are embedded in an Oracle Pro*C application but with a slight syntactical difference. Here’s an example:

#sql (INSERT INTO ENGINES

(EngineName, Displacement, Horsepower, Cylinders, Valves)

VALUES

(:engname, :cid, :hp, :cyl, :valves)) ;

#sql, rather than EXEC SQL, is the signal to the preprocessor that what follows is an SQL statement.

Using SQL in a Perl application

In a Perl application, the SQL statement is passed to the DBMS as a string rather than as an embedded executable statement in the Perl code, as follows:

my $sql = "INSERT INTO ENGINES

(EngineName,Displacement,Horsepower,Cylinders, Valves) " . "values('$engname','$cid','$hp','$cyl','valves')";

 print "SQL => $sql " if $DEBUG;

 my $sth = $dbh->prepare($sql);

 $sth->execute();

This code uses the Perl database interface (DBI), which I mention in Book 5, Chapter 2. If an error is encountered, the offending SQL statement is printed out. If no error is detected, the SQL statement is prepared and then the last line actually executes it.

Embedding SQL in a PHP application

Once again, with PHP and a MySQL database, the operation is basically the same as with Perl, except that the syntax has been changed to protect the innocent, as in this example:

$query = "INSERT INTO ENGINES

(EngineName, Displacement, Horsepower, Cylinders, Valves)

VALUES

('engname', 'cid', 'hp', 'cyl', 'valves')" ;

mysql_query($query) or die('Error, insert query failed');

The last line checks whether the insert was performed successfully. If not, an error message is displayed.

Using SQL with a Visual Basic .NET application

Unlike Oracle’s Pro*C, and Java, but like Perl and PHP, Visual Basic .NET doesn’t support embedded SQL. Instead, it passes a string containing the SQL statement to the ADO.NET data provider to accomplish the same effect as embedded SQL.

Here’s an example of an SQL operation as Oracle’s Pro*C would do it with embedded SQL, followed by the Visual Basic .NET equivalent using ADO.NET. First, here’s the Pro*C:

EXEC SQL UPDATE VENDOR

SET VendorName = :vendorname

WHERE VendorID = 'PENGUIN';

Here’s the ADO.NET equivalent:

Dim strSQL As String

strSQL = "UPDATE VENDOR SET VendorName = @vendorname "& _

"WHERE VendorID = 'PENGUIN'"

Dim cmd As New SqlCommand(strSQL, cn)

Dim par As SqlParameter

Par = cmd.Parameters.Add("@vendorname",SqlDbType.VarChar, 10)

Par.Value = "VendorName"

Dim InsertRecordsAffected As Integer = cmd.ExecuteNonQuery()

ADO.NET is a library of data access procedures in the .NET Framework.

Using SQL with other .NET languages

All .NET languages other than Visual Basic .NET — C#, C++.NET, COBOL.NET, Perl.NET, and so on — use ADO.NET in the same way that Visual Basic .NET does to provide data access to relational databases. ADO.NET eliminates the need to embed SQL code within a procedural application program.

Module declarations

The syntax for the declarations in a module is as follows:

MODULE [module-name]

[NAMES ARE character-set-name]

LANGUAGE {ADA|C|COBOL|FORTRAN|MUMPS|PASCAL|PLI|SQL|JAVA}

[SCHEMA schema-name]

[AUTHORIZATION authorization-id]

[temporary-table-declarations…]

[cursor-declarations…]

[dynamic-cursor-declarations…]

procedures…

As indicated by the square brackets, the module name is optional. Naming it anyway is a good idea, however, to help keep things from getting too confusing.

The optional NAMES ARE clause specifies a character set. If you don’t include a NAMES ARE clause, the default set of SQL characters for your implementation is used.

The LANGUAGE clause tells the module which language it will be called from. The compiler must know what the calling language is, because it will make the SQL statements appear to the calling program to be subprograms in that program’s language.

Although both the SCHEMA clause and the AUTHORIZATION clause are optional, you must specify at least one of them, or you can specify both. The SCHEMA clause specifies the default schema, and the AUTHORIZATION clause specifies the authorization identifier. The authorization identifier establishes the privileges you have. If you don’t specify an authorization ID, the DBMS uses the authorization ID associated with your session to determine the privileges your module is allowed. If you don’t have the privilege to perform the operation your procedure calls for, your procedure isn’t executed.

If your procedure requires temporary tables, declare them with the temporary table declaration clause. Declare cursors and dynamic cursors before any procedures that use them. Declaring a cursor after a procedure is permissible as long as that particular procedure doesn’t use the cursor. Doing this for cursors used by later procedures may make sense. You can find more in-depth information on cursors in Book 3, Chapter 5.

Module procedures

Finally, after all these declarations, the functional parts of the module are the procedures. An SQL module language procedure has a name, parameter declarations, and executable SQL statements. The procedural language program calls the procedure by its name and passes values to it through the declared parameters. Procedure syntax is as follows:

PROCEDURE procedure-name

(parameter-declaration [, parameter-declaration ]… )

SQL statement ;

[SQL statements] ;

The parameter declaration should take the following form:

parameter-name data-type

or

SQLSTATE

The parameters you declare may be input parameters, output parameters, or both. SQLSTATE is a status parameter through which errors are reported. SQLSTATE is covered extensively in Book 4, Chapter 4.

Modules in Oracle

Oracle’s implementation of module language, named SQL*Module, is specifically designed to overcome the impedance mismatch (refer to “Challenges of using SQL with an object-oriented procedural language,” earlier in this chapter) between SQL and application programs written in the Ada programming language. SQL*Module compiles SQL module language files. A module language file contains parameterized procedures that encapsulate SQL statements. The SQL*Module compiler translates these procedures into calls to the SQL runtime library on the Oracle server. All the SQL code resides in a separate module. SQL*Module defines the interface between the SQL module and the host program written in Ada.

A module is composed of three parts:

• A preamble, containing introductory material
• Cursor declarations that queries use to return multiple rows of data
• Definitions of procedures that will be called by the host application

The SQL code that you can put in a module is somewhat restricted. Statements that aren’t supported by SQL*Module include the following:

• Data Definition Language (DDL) statements
• Data Manipulation Language (DML) statements other than SELECT, UPDATE, DELETE, and INSERT
• Data Control Language (DCL) statements other than COMMIT, ROLLBACK, CONNECT, and DISCONNECT

The fact that SQL*Module doesn’t support DDL statements means that you can’t create database objects such as tables with SQL*Module. You can’t even alter the structure of an existing table. Thus, the database must exist and be in its final form before you try to operate on it with an application program that uses SQL*Module.