[top] [up] [next]

Stored procedures

A procedure is an executable entity that is stored inside a database, it can be considered a permanently prepared SQL statement. For instance: a procedure can be a SQL statement that creates a top-ten of products best sold over the last month. Below there is an example of calling such a procedure by using the method execute/3 of the query class.

    query<-execute(' { call "Ten Best Sold Products" } ', Output, ErrorCode).

The name of the procedure above is enclosed by double quote (") characters, this is necessary because the name of the procedure contains whitespace characters (this is necessary for the SQL compiler inside the RDBMS). The entire call to the procedure is enclosed by single quote (') characters, this is necessary for the Prolog compiler to recognize it as a single string and not as separate atoms, the single quote characters will not be present in the data passed on to the database.

The syntax grammar for procedure calls is:

    { [?=] call procedure-name [ ( [ parameter ] [ , [ parameter ] ]... ) ] }

A procedure can have zero or more parameters. It can also return a value, as indicated by the optional parameter marker ?= at the start of the syntax. If a parameter is an input or an input/output parameter, it can be a literal or a parameter marker. If a parameter is an output parameter, it must be a parameter marker. Pay attention to the procedure name as some RDBMS systems are case-senstive when it comes to procedure names.

Input and input/output parameters may be omitted from procedure calls. If a procedure is called with parentheses but without any parameters, such as {call procedure-name()}, the driver instructs the data source to use the default value for the first parameter. If a procedure is called without parentheses, such as {call procedure-name}, the driver does not send any parameter values.

Executing a stored procedure with execute/2

To prepare a procedure with parameters it is necessary to use the method prepareProcedure/4. Compared with prepare/3 the prepareProcedure/4 method has an extra parameter that must contain the name of the procedure. The extra parameter is used to query the database catalog for type information about the parameters of the procedure. Example:

    query<-prepareProcedure(' { call CustOrd( ? ) } ', 'CustOrd', Output, ErrorCode).
    query<-prepareProcedure(' { call "Sales by Year"( ?, ? ) } ', 'Sales by Year', Output, ErrorCode).

arrow.gif (1632 bytes)


In the second example the name Sales by Year is enclosed by double quotes, this is necessary for the correct interpretation of the SQL statement, in the next parameter the name of the procedure is not enclosed by double quotes because it is not embedded in an SQL statement.

Setting values for input and input/output parameters for a procedure can be done with the method putParameter/3. Executing a prepared procedure is then done by calling execute/2. There are no other differences between executing a SQL statement and executing a stored procedure.

arrow.gif (1632 bytes)

It can be very useful to look in the system tables of the RDBMS system to determine the correct names, types of parameters, etc. of the stored procedures. For IBM DB2 are these the tables: syscat.procedures and sysibm.sysprocparms

The values of input/output and output parameters of a stored procedure can be retrieved with the method parameter/3. The index of the first parameter is 0.

Executing a stored procedure with execute/3

If a procedure has any input/output parameters then the first step is to call the method bindProcedureParameters/2. That method allocates data buffers for containing the parameter values. After the successful binding of parameters the method execute/3 can be called. The SQL text parameter of execute/3 would be the call to the stored procedure, for instance: ' { call "Sales by Year"( ?, ? ) } '.

Stored procedures with result sets

The result of a stored procedure is not always an output parameter but can be a set of records like a result set of a query. The result set is not an output parameter. You call such a procedure with the following statement:

    { call procedure-name [ ( [ parameter ] [ , [ parameter ] ]... ) ] }

See retrieving results for more information about result sets.

Checklist if a procedure call does not work

  1. Check that the call to the stored procedure is enclosed by { } characters.

  2. Some RDBMS systems store procedure names in uppercase in catalog tables that store parameters information. So try using the uppercase version of the procedure name for the 'ProcName' parameters for the methods prepareProcedure/4 and bindProcedureParameters/2. For instance:
    callProcedure( '{ call max_sbc_id( ? ) }', 'MAX_SBC_ID' )

  3. There must be a parameter marker for each input and output parameter.

  4. It is possible, after a call to bindProcedureParameters/2, to set parameter values with putParameter/3. To execute the query then call execute execute/3.
    Q new_obj query,
    Q<-putDatabase('some-database-instance', true),
    Q<-bindProcedureParameters('MAX_SBC_ID', true),
    Q<-putParameter(0, 'some-value', true),
    Q<-execute( '{ call max_sbc_id( ? ) }', QueryResult, ErrorCode ),

[top] [up] [next]

 

info@trinc-prolog.com