William Smith is the engineering manager at Montana Software, a software development company specializing in custom applications for MS-DOS and Windows. You may contact him by mail at P.O. Box 663, Bozeman, MT 59771-0663.
Extending C by embedding it with Structured Query Language (SQL) code takes advantage of both languages. For report generation and database querying, SQL works better than C. On the other hand, C works better than SQL for developing the overall application and user interface.
To avoid complicating maintenance, debugging and portability when embedding SQL, you should isolate the SQL code from the C code. You can accomplish this with an extension interface technique called stored procedures, which stores the SQL statements for a given task in a database record. Storing procedures in a database record gives the user the ability to extend the application by creating new custom procedures and saving them for future use, without changing or recompiling the C code. Storing procedures in a database keeps them all located and organized in one place for fast retrieval.
I will use examples from a project I completed for a customer to illustrate the technique of storing procedures in a database. The customer used Raima Data Manager in applications. While some DBMS applications support stored procedures, others, such as Data Manager, do not. So one of the first things I did was to add support for stored procedures.
Raima Data Manager is a relational and network model DBMS. Data Manager (formerly db_Vista) is the C API and db_Query is the SQL API. Although Raima corporation sells Data Manager by itself without db_Query, to support SQL you must purchase the entire package including db_Query. Since the concept of SQL and stored procedures requires both Data Manager and db_Query, any reference I make to Data Manager is a reference to both Data Manager and db_Query.
The version of SQL supported by Data Manager is a subset of standard SQL. It is only suitable for report generation. It does not support making modifications to the database. Even so, using SQL as the means to query the database and generate reports is much more efficient than using the C function interface.
The code presented here is a library of functions for providing stored SQL procedure support to Data Manager. The code will work with either the MS-DOS or MS-Windows versions of the Data Manager and db_Query libraries. There are some subtle differences between these two versions. A small amount of conditionally compiled code (based on the whether or not the constant _WINDOWS is defined) accounts for these differences. The code works with v3.1/3.2 of Data Manager and v2.1/2.2 of db_Query.
Extension Interface Functionality
Extending another language with stored procedures requires support of certain core functionality. In addition to the normal activities of storing, retrieving, modifying, and deleting data records in the database, the database functions must include the ability to execute the stored procedure. The DBMS API usually contains a function supporting the ability to parse and execute an SQL statement. In the case of Data Manager, these functions are d_query, q_sqlinit, and q_nextval.
Sample Code
Listing 1, SQL_PROC.C, contains the functions required to support SQL stored procedures with Data Manager.Besides the basic functions for store, retrieve, modify, delete, and execute there are three other functions in Listing 1. sql_fetch_str and sql_fetch_dev retrieve data after executing a SQL command. These two functions take a fairly simple approach and concatenate the column data for each row. Columns are comma and tab delimited. If you want to load individual columns into different entries in a 2-D list box in Windows, for instance, you will have to use the db_Query function q_nextval directly. You use sqlproc_fetch_str to fetch the results of query row by row. sqlproc_fetch_str returns the text for the row as a pointer to a dynamically allocated string. You must call sqlproc_fetch_str for each row. It will return NULL when there is no more data to fetch. sqlproc_fetch_dev will fetch rows to a device or file. You pass a pointer to the device (type FILE) as a parameter. A single call to sqlproc_fetch_dev will fetch all the rows to the device.
The third additional function in Listing 1 is sqlproc_find. You use this function to set the current record to the one specified by the procedure name (unique key). This function is a helper function for most of the other functions in Listing 1.
Before you can call any of the functions in Listing 1, you must have already opened the database that contains the sql_proc record and sql_name key field. Your program code will have to handle opening and closing the Data Manager database.
Listing 2, SQLPPROC.H, is one of the associated include files for Listing 1, SQL_PROC.C. This include file contains prototypes for the functions in Listing 1. It also contains some necessary manifest constants.
Listing 3, SQL_PROC.DDL, is the Data Definition Language (DDL) file that contains the definition of the sql_proc record and all its fields. It is set up to store the SQL procedures in their own database. If you choose, you can incorporate the sql_proc record into your own DDL file and database. To do this, just modify your database scheme to provide for a stored procedure record.
The sql_proc record contains three fields. They are sql_name, sql_description and sql_command. SQL_PROC.DLL specifies sql_name as a unique key. The code in Listing 1 uses this field as an access mechanism for each stored procedure. The field, sql_description, provides for an optional description of the procedure. The field, sql_command, is the actual SQL command string. All three fields are strings. The maximum lengths are set at 16, 64, and 424 characters (including zero terminator) respectfully. Feel free to change the length of these fields or even to add fields as required by your specific application. The sizes as they stand now, fit very efficiently into the Data Manager block file format. Changing the sizes will undoubtably lead to some wasted space.
Listing 4, SQL_PROC.H, is the Data Manager generated include file for SQL_PROC.DDL.SQL_PROC.H contains the structure definition for the SQL_PROC record and some manifest constants used to identify this record and its fields. The Data Manager utility DDLP.EXE creates this include file when processing the DDL file. Listing 4 is the other associated include file for Listing 1.
SQL commands can range from simple and short to very complex. The length limit on the field that holds the SQL command is arbitrarily set at 424 characters. Under Data Manager, creating a virtual table to view data with the create view command and then selecting data with the select command is a common scenario. SQL commands used to format the output data and to make mathematical calculations on the output data can get complicated. Generally the SQL statements I use with Data Manager fit within the 424 character field size. You may find that this is not an appropriate size for your situation. If you need a larger field, I recommend breaking the SQL command into separate lines and storing each in its own record. This is much more space efficient under a fixed field and record size DBMS such as Data Manager.
Sample Implementation
The code
sqlproc_store( "ListField", "Generate a list of " "all the stored " "procedures", "select %s;" );stores a new procedure in the database under the name ListField. Since the name of the procedure field, sql_name, is a unique key, Data Manager will only create a new record if there is not an existing procedure with the name ListField. Typically the text for the name, the description, and the command will be input by the user through a data entry screen or editor.The following command will execute this procedure.
sqlproc_exec( "ListField", "sql_name" );Again, you should supply the stored procedure and field name as variables in the code, not as constants.Once you have executed a stored procedure and produced output associated with this procedure, you can fetch the output with either sqlproc_fetch_str or sqlproc_fetch_dev. sqlproc_fetch_str dynamically allocates strings row by row. You must call sqlproc_fetch_str once for each row until sqlproc_fetch_str returns NULL. You can also fetch all the rows to a device (file) with a single call to the function sqlproc_fetch_dev. If you need to fetch into a Windows list box or some other particular user-interface object, you will most probably have to write your own fetch function.
Argument Passing
Notice, that sqlproc_exec supports a variable argument list. You need this to support passing arguments to the stored procedure. If you need a stored procedure with arguments, the command string for the SQL statements will have to contain markers to where the arguments go. I use the C syntax for printf. In effect, the SQL command string that is stored in the database is a format string. You build the actual SQL command with the requested arguments with the function vsprintf. You must make sure you match the number and type of arguments with a specific stored procedure. The simple example above passes a single string argument to the stored procedure ListField. The stored procedure expects the name of a field in the database. Passing arguments are important from the standpoint of code reusability. In this case you are reusing the stored-procedure code. If you want to query the database for fields that match a certain value, it is natural to have that value be set with an argument.
Hints
Providing a list of all the stored procedures for a user to choose from is one of the first user-interface features you will want to add to your program. Loading all the procedure names into a list box is a handy way to accomplish this under Windows. The user can then select a procedure by name to either execute, edit (modify), delete, or view. Having a second list box that contains the descriptions for the procedures or even static text that you update with the description of current highlighted procedure will help make the interface easier to use.
Debugging
Before you save a stored procedure to the database, it is a good idea to make sure that it parses and executes correctly. To help in this regard, the code displays the error message, if any, resulting from parsing the SQL statement. You can turn this feature on or off when compiling Listing 1. If you define _DEBUG when you compile Listing 1, this feature will be supported. The message is sent to stderr under MS-DOS and displayed in a Message Box under MS-Windows.A DBMS that supports stored procedures directly usually stores the procedures in a pre-parsed or compiled format. This eliminates the need to parse the SQL command before execution. The speed advantages are obvious. The implementation presented here for Data Manager is unable to do this.
Conclusions
Extending C by embedding SQL can have great benefits when working with a DBMS that has both a C and SQL API. Using stored procedures for embedding SQL keeps the SQL statements out of the C code and stores them in your database. By storing the SQL statements in the database, you can modify the SQL statements and maintain them independently of the C code. This provides a means to have user-defined and maintainable procedures. The maintenance of the SQL statements can proceed without modifying the C code and recompiling. Likewise, you can modify and recompile C code without change to the database.The concept of stored procedures as an extension interface is not limited to SQL and C. You can treat any language you are embedding in another in a similar fashion. Keystroke macros and specific text-processing extensions to C come to mind. If you can store the embedded language or language extensions outside the Standard C code, do it. The advantages are many. When maintenance and modification time comes, you will be glad you did.