MACROMEDIA COLDFUSION 4.5-ADMINISTRING COLDFUSION..., Manual

Page: 132 / 350

110 Administering ColdFusion Server
2. Compile and link the .c file generated in step 1. At this point the .c file needs to be
precompiled and this process will explode the source into the appropriate C code.
Compile the C file and link to get .dll
3. Place the library file (.dll) generated in step 2 in the appropriate directory on the
server. For example, put the file on a server called DB2SERVER in the
C:\sqllib\function\
folder. It could also be put in 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
example which follows 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. Note that the actual stored procedure name is case sensitive,
as is the password parameter.
<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>