picoSQL rel.2x - Stored Procedures

Corso Italia, 178 - 56125 Pisa Telefono/fax 050 46380 e-mail picosoft@picosoft.it

This document illustrates the mechanism of stored procedures that we are going to implement in picoSQL. We publish it before the developing in order collect comments and suggestions, so as to obtain a better result. You can send comments and suggestions using the forum or by e-mail.

Scope

Stored procedures are ruotines written in some programming language, usually stored inside the database. They can be activated using a specific SQL statement, CALL. The usefullness of these ruotines resides substantially, in the fact that, being strored in the database, can access the data more fastly, avoiding the communication protocols. Naturally they can be used also in order to automate frequently repeated operations, but for this use, the programming languages, and in particular OOPL, usually allow better results. Languages used to write stored procedures vary from product to product, and a true standard still does not exist. Having to choose a language to use for stored the procedures in picoSQL, we winnowed various alternatives and, at the end, we chose the C language; advantages and disadvantages of this choice are the following.
Pros
- speed
- large diffusion
- small footprint
- simple implementation
- it can iterface many other languages
Cons
- it needs a compilation out of database
- it is not the most used language by database oriented application developers
- it can abort the database server

About the interface with other languages, someone requested us, using the forum, to interface any language; we took in consideration this request and we'll see how it possible.
The next problem is how to bind the stored porcedures. There are many systems to bind 'plug-in' out there and we looked for them. Unfortunately all theese systems are rather complex, mainly due to the different implementation of dynamic libraries in different operating systems. To speed the process without depending from operiting systems, we decided, at least in this first phase, to link directly the procedures in the database server. This system is simple, fast and indipendent from the operating system, but it is rather uncomfortable when the procedures are changed frequently. In order to mantain th system as simple as possible, we chose to access the data base using the picoSQL API, that are analogous to ODBC API.

Implementation

In picoSQL 2x distribution will be a new directory, named calls, containing a source C language file named picocall.c and a makefile to compile it. In this file a structure array of kind CallTable, named Calls is declared. This array contains all the procedures entry points. The CallTable structure is defined in picocall.h and has the following attributes.

char *dbName	Database name that owns the procedure. This name can contain wildcards, the same used in the LIKE function; this feature allows to call the same procedure from different databases.
char *procName;	Procedure name. Also this name can contain wildcards: this feature allows to call a single procedure with differenr names and therefore to develop generic interfaces. For example, we can redirect on a single procedure all the calls whose name start with 'ruby_' and therefore to call the RUBY interpreter to execute a procedure with the exact name. The exact name is passed to the procedure as an argument.
int (*call)();	Pointer to the procedure.
short numArgs;	Procedure arguments number. If this value is < 0, picoSQL do not check the argument number.
short return_type;	It indicate if the procedure return a value (return_type > 0), or not (return_type == 0); setting return_type < 0, picoSQL do not check the ruturn value.

An example of procedure is the following one:

extern int SPTest(Env *e, Argument args[], int nArgs, Argument *rc);

struct CallTable Calls[] = {
   {"%", "SPTEST", SPTest, 0, 0},
   {0, 0, 0, 0, 0}
};

int
SPTest (Env *e, Argument args[], int nArgs, Argument *rc)
{
   return SQLExecDirect (e->hstmt, "SELECT * FROM AUTORI", SQL_NTS);
}

In this example, procedure "SPTEST", correspondent to the C function SPTest, is callable from any database. Notice that the array containing all the procedures is terminated by an item of all 0 elements. A procedure receives four arguments, the first one containing the environment data, the second one that is an array containing all the arguments set by the caller, the third one with the number of the arguments and finally the eventual pointer to the procedure return value. This last one can be equal to 0 if the call it has not return value. The function must return SQL_SUCCESS or SQL_ERROR in case of problems. In this last case the procedure can signal the type of problem. The structures Env and Argument are defined in picocall.h. The elements of the Env structure are following:

HSTMT hstmt	Statement handle to use in the queries. A procedure can use as much statements as it need, but all the future operation on the handle binded to the current CALL are executed on the handle specified by this variable.
char *userName	User who made the CALL.
char *dbName	Database name.
char *procName	Exact name of the procedure.
char exceptionMsg[256]	Memory area to use to report error messages.

For each argument, the structure Argument contains the following items, that are the same to use in the SQLBindParameter.

short ioType	The type of the argument, it is one of the following values: SQL_PARAM_INPUT, SQL_PARAM_OUTPUT or SQL_PARAM_INPUT_OUTPUT.
short cType	The C data type of the argument.
short sqlType	The SQL data type of the argument.
unsigned long columnSize	Length of the argument.
short decimalDigits	Number of decimal digits.
valuePtr	A pointer to a buffer for the argument data.
valueLen	Maximum length of the data area.
strLenOrIndPtr	A pointer to a buffer for the argument data.