How to Use Module Language with SQL - dummies

How to Use Module Language with SQL

By Allen G. Taylor

Module language provides a relatively easy method for using SQL with a procedural programming language. With module language, you explicitly put all the SQL statements into a separate SQL module.

An SQL module is simply a list of SQL statements. Each SQL statement is included in an SQL procedure and is preceded by a specification of the procedure’s name and the number and types of parameters.

Each SQL procedure contains only one SQL statement. In the host program, you explicitly call an SQL procedure at whatever point in the host program you want to execute the SQL statement in that procedure. You call the SQL procedure as if it were a subprogram in the host language.

Thus you can use an SQL module and the associated host program to explicitly hand-code the result of the SQL preprocessor for embedded syntax.

Embedded SQL is much more common than module language. Most vendors offer some form of module language, but few emphasize it in their documentation. Module language does have several advantages:

  • SQL programmers don’t have to be experts in the procedural language. Because the SQL is completely separated from the procedural language, you can hire the best SQL programmers available to write your SQL modules, whether or not they have any experience with your procedural language. In fact, you can even defer deciding which procedural language to use until after your SQL modules are written and debugged.

  • You can hire the best programmers who work in your procedural language, even if they know nothing about SQL. It stands to reason that if your SQL experts don’t have to be procedural language experts, certainly the procedural language experts don’t have to worry themselves over learning SQL.

  • No SQL is mixed in with the procedural code, so your procedural language debugger works. This can save you considerable development time.

Once again, what can be looked at as an advantage from one perspective may be a disadvantage from another. Because the SQL modules are separated from the procedural code, following the flow of the logic isn’t as easy as it is in embedded SQL when you’re trying to understand how the program works.

Module declarations

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

MODULE [module-name]
 [NAMES ARE character-set-name]
 [SCHEMA schema-name]
 [AUTHORIZATION authorization-id]

The square brackets indicate that the module name is optional. Naming it anyway is a good idea if you want to 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 knows what the calling language is, because it will make the SQL statements appear to the calling program as if they are subprograms in that program’s language.

Although the SCHEMA clause and the AUTHORIZATION clause are both 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 that your module is allowed. If you don’t have the privileges needed 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 you declare any procedures that use them. Declaring a cursor after a procedure starts executing is permissible as long as that procedure doesn’t use the cursor. Declaring cursors to be used by later procedures may make sense.

Module procedures

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 looks like this:

PROCEDURE procedure-name
 (parameter-declaration [, parameter-declaration]...
 SQL statement ;
 [SQL statements] ;

The parameter declaration should take the following form:

parameter-name data-type



The parameters you declare may be input parameters, output parameters, or both. SQLSTATE is a status parameter through which errors are reported.