MACROMEDIA COLDFUSION 5-ADVANCED ADMINISTRATION, Manual, Page: 38 / 372

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>
<CFOUTPUT>#FOO#</CFOUTPUT>