User Guide

20 Chapter 1 Advanced Data Source Management

3 Place the dll file generated in step 2 into the appropriate directory on the server.

For example, put the file on a server called DB2SERVER into the

C:\sqllib\function\ folder. You could also put it into the

C:\sqllib\function\unfenced\ folder.

4 Run a CREATE PROCEDURE statement to register your stored procedure.

• The CREATE PROCEDURE statement creates a row in the database catalog

(syscat.procedures table), making it visible to client applications, including

ColdFusion Server.

• The stored procedure’s name is what you called it in your SQC file. The

following example calls the stored procedure outsrv.

• The create procedure statement looks like this:

CREATE PROCEDURE server1

(OUT sal double, IN salind integer)

EXTERNAL NAME ’outsrv!outsrv’

LANGUAGE C

DETERMINISTIC

PARAMETER STYLE DB2DARI;

5 Grant users who need to run the stored procedure permission to execute it:

GRANT EXECUTE ON PACKAGE server1 TO PUBLIC;

Example

The following example demonstrates a CFSTOREDPROC tag that calls the stored

procedure named outsrv. The actual stored procedure name and the password

parameter are case sensitive.

<CFSTOREDPROC PROCEDURE="outsrv"

DATASOURCE="DB2SERVER"

USERNAME="DB2"

PASSWORD="DB2">

<CFPROCPARAM TYPE="OUT"

CFSQLTYPE="CF_SQL_DOUBLE"

VARIABLE="FOO" NULL="NO">

<CFPROCPARAM TYPE="IN"

CFSQLTYPE="CF_SQL_INTEGER"

VALUE="0"

NULL="NO">

</CFSTOREDPROC>