Using Dynamic SQL with Mimer SQL

Introduction

This article is a short introduction to dynamic SQL and how to use it when developing applications that connects to Mimer SQL.

For more information on using dynamic SQL against a Mimer SQL database, please refer to the Programmer’s Manual found in the Mimer SQL Documentation Set.

Description

In the past, the majority of applications communicated with an SQL database by using embedded SQL. Embedded SQL enables a developer to code SQL statements in an application written in a common programming language, for example, C. These kinds of applications are considered to be ‘static’ because the SQL statements are hard-coded. Dynamic SQL provides an extension to the embedded interface that allows SQL statements to be constructed and executed at run-time, this includes SQL statements submitted by an end-user.

Mimer SQL supports the full set of dynamic SQL statements enabling you, the developer, to write general-purpose applications which can generate and execute SQL statements at run-time.

Dynamic SQL contains a number of statements but the most important are PREPARE, DESCRIBE and EXECUTE. Normally, a dynamic SQL statement is constructed in a host string variable, which is then prepared; this can be considered to be the equivalent of a compilation phase. The SQL statement may contain parameter markers to indicate where actual values are to be substituted when the prepared statement is executed.

Information regarding the parameters and the data returned by the statement, if any, is obtained by executing a DESCRIBE statement. The value of any input parameters can be set in a descriptor area and the prepared statement executed. Any returned data values can be determined from a second descriptor area.

Preparing Statements

The PREPARE statement can be considered to be the equivalent of a compilation phase. The SQL statement is ‘prepared’ for execution. The prepared form of the statement is named so that it can be referenced elsewhere, for example, in the EXECUTE statement.

For example:

 sql_str = "UPDATE ROOMS SET STATUS = 'FREE' WHERE ROOMNO = 'LAP301'";
 exec sql PREPARE CMD_1 FROM :sql_str;

The statement name may be an extended name, which is a host variable that contains a character string that has the format of an identifier.

In dynamic SQL, parameter markers identify positions in the statement where the value of a host variable will be inserted when the prepared statement is executed. Parameter markers in dynamic SQL are identified by ‘?’ rather than the ‘:name’ format used in static SQL.

For example:

 sql_str = "UPDATE ROOMS SET STATUS = ? WHERE ROOMNO = ?";
 stmt_name = "CMD_1";
 exec sql PREPARE :stmt_name FROM :sql_str;

If the SQL statement does not contain parameter markers and there isn’t any resulting data, you can use the SQL statement EXECUTE IMMEDIATE; this is a shorthand form of the PREPARE and EXECUTE operations.

For example:

 sql_str = "GRANT ALL ON TABLE HOTEL TO CHARLIE";
 exec sql EXECUTE IMMEDIATE :sql_str;

SQL Descriptor Areas

Because of the very nature of a dynamic query, it is not possible to predict the number and type of any input parameters or result columns. Consider a dynamic SELECT statement, it is not possible to predefine host variables because the data types of the result columns are not known in advance, even the number of result columns is unknown.

SQL descriptor areas are used for managing input and output data in SQL statements where the number and/or data type of the host variables required is not known at the time the program is written.

You allocate an SQL descriptor area using the statement ALLOCATE DESCRIPTOR. The DESCRIBE statement populates an SQL descriptor area with information about the input parameters or the output values of a prepared statement, for example:

 exec sql ALLOCATE DESCRIPTOR :in_desc;
 exec sql ALLOCATE DESCRIPTOR :out_desc;

 sql_str = "SELECT * FROM ROOMS WHERE ROOMNO = ?";
 exec sql PREPARE :stmt_1 FROM :sql_str;

 exec sql DESCRIBE INPUT :stmt_1 USING SQL DESCRIPTOR :in_desc;
 exec sql DESCRIBE OUTPUT :stmt_1 USING SQL DESCRIPTOR :out_desc;

Each descriptor area has a field named COUNT that defines how many items are in use, for example, how many input parameters are defined or how many columns there are in the result set.

Each item in the descriptor area describes various details of the input parameters or the output values (for example, NAME, TYPE, LENGTH, PARAMETER_MODE).

The GET DESCRIPTOR statement is used to retrieve information from an SQL descriptor area:

 exec sql GET DESCRIPTOR :out_desc :num_cols = COUNT;
 do index = 1 to num_cols
    exec sql GET DESCRIPTOR :out_desc VALUE :index
                            :data_type = TYPE,
                            :data_length = LENGTH;

SET DESCRIPTOR is for populating and updating the details relating to a particular item in the SQL descriptor area. The following example sets the data type associated with the first input parameter to be VARCHAR, that is, TYPE = 12.

 exec sql SET DESCRIPTOR :in_desc VALUE 1
                                  TYPE = 12;

Executing Statements

The EXECUTE statement performs the prepared statement. The syntax is:

EXECUTE <prepared statement name> [<into clause>] [<using clause>]

If the prepared statement contains input parameter markers, you must specify the USING clause. This can either be an SQL descriptor area or a list of host variables.

You must also specify the INTO clause if there are any output parameters. This can either be an SQL descriptor area or a list of host variables.

 sql_str = "SELECT STATUS FROM ROOMS WHERE ROOMNO = ?";
 exec sql PREPARE :stmt_1 FROM :sql_str;

 exec sql EXECUTE :stmt_1 INTO :status USING :roomno;

This implies that this mechanism cannot be used for multi-row retrievals. To perform multi-row retrieval in dynamic SQL you use a cursor using OPEN, FETCH and CLOSE operations (UPDATE and DELETE CURRENT can be performed against retrieved rows). Cursors defined by the dynamic form of DECLARE CURSOR, or generated by ALLOCATE CURSOR, are considered to be dynamic cursors.

Example Files

To help you understand how you can use dynamic SQL when developing an application, you can review the example files, dsqlsamp.c and dsql.ec included in your Mimer SQL distribution.

The dsqlsamp.c program demonstrates how you can construct a C program using dynamic SQL syntax that complies with international standards. By calling the routines defined in dsql.ec, the program enables a user to enter an SQL statement that will be executed directly, in a manner similar to the Mimer SQL BSQL program.

dsql.ec is the source code file for the dsqlsamp program. The code contains a collection of routines that define a simple but convenient API for DSQL which can be called from other C programs.

Environment

UNIX

The following example demonstrates how to compile the dsqlsamp program and build it using the distributed example makefile. We assume that Mimer SQL is installed using default options, ending up in the directory /opt/mimersql1100-11.0.0A (or corresponding):

 $ mkdir dsql # Do everything in a sub directory
 $ cd dsql
 $ cp /opt/mimersql1100-11.0.0A/examples/ex_makefile ./makefile
 $ cp /opt/mimersql1100-11.0.0A/examples/dsql*

Update the makefile so that the MYPROG symbol is set to ‘dsqlsamp’ and the MYFUNCS symbol is set to ‘dsql.o’ (avoid trailing spaces):

 MYPROG = dsqlsamp
 MYFUNCS = dsql.o

Then make the dsqlsamp program:

 $ make

OpenVMS

The following example illustrates how to compile and link the dsqlsamp program under OpenVMS:

 $ ESQL/C MIMEXAMPLES:DSQL ! Preprocess source code
 $ COPY MIMEXAMPLES:DSQL.H SYS$DISK:[] ! Get header file
 $ COPY MIMEXAMPLES:DSQLSAMP.C SYS$DISK:[] ! Get main program
 $ CC DSQL ! Compile preprocessed code
 $ CC DSQLSAMP ! Compile sample program
 $ LINK DSQLSAMP,DSQL,MIMLIB:MIMER/OPT ! Link executable program

Windows

On Windows, when you install Mimer SQL remember to select the ‘Development and Sample Files’ option. The examples files are installed in the \DEV\Samples directory located below the installation directory. The \DEV\Samples directory contains a makefile called makefile.mak, which will build all the example programs at the same time. The examples have been tested using Microsoft Visual C.

Assuming that you are using Microsoft Visual C and that you execute the make command from the Samples directory, enter the following:

$ nmake -f makefile.mak

Benefits

Dynamic SQL adds flexibility and functionality to your application. It is extremely useful in interactive environments.

Links

See the Programmer’s Manual of the Mimer SQL Documentation set.

If you don’t have a copy of Mimer SQL, you can download a free developer version from Downloads »

Graphic Element - Cube