after I posted this. This is really cool, and works very nicely. I played
with it this weekend, I really like that you can return multiple record sets
with one procedure.
Thanks,
David
-----Original Message-----
From: Plunkett, Matt [mailto:[EMAIL PROTECTED]
Sent: Monday, February 02, 2004 11:21 AM
To: CF-Talk
Subject: RE: Oracle Stored Procedure and CF 5.0
-----Original Message-----
From: Rob Rohan
Sent: Friday, January 30, 2004 12:00 PM
To: CF-Talk
Subject: Re: Oracle Stored Procedure and CF 5.0
> <cfstoredproc procedure="sp_login"
> datasource="#request.dsn#" debug="no"
> >
> cfprocparam dbVarName="ipaddress" value="#cgi.REMOTE_ADDR#"
> CFSQLtype="CF_SQL_VARCHAR" null="No">
> <cfprocparam dbVarName="login" value="#cli_username#"
> CFSQLType="CF_SQL_VARCHAR" null="No">
> <cfprocparam dbVarName="password" value="#rmsEncrypt(cli_password)#"
> CFSQLType="CF_SQL_VARCHAR" null="No">
> <cfprocresult name="checkClientCreds" resultSet="1">
> <!--- if you had other result sets you could do this as well
> <cfprocresult name="otherstuff" resultSet="2">
> ...
> --->
> </cfstoredproc>
This procedure is not completely correct for CF 5 (I think it is correct
for
MX, but I mainly use 5, so who knows...).
In CF5, you must put the parameter in for the ref cursor and set
maxrows="-1":
<cfstoredproc procedure="sp_login" datasource="#request.dsn#" debug="no">
<cfprocparam dbVarName="ipaddress" value="#cgi.REMOTE_ADDR#"CFSQLtype="
CF_SQL_VARCHAR" null="No">
<cfprocparam dbVarName="login" value="#cli_username#" CFSQLType="
CF_SQL_VARCHAR" null="No">
<cfprocparam dbVarName="password" value="#rmsEncrypt(cli_password)#"
CFSQLType="CF_SQL_VARCHAR" null="No">
<cfprocparam type="Out" cfsqltype="CF_SQL_REFCURSOR" maxrows="-1"
variable="p_ref">
<cfprocresult name="checkClientCreds">
</cfstoredproc>
I left the rest of Rob's query the same, although I don't use dbVarName
and
I always set the type (In, Out, In/Out). Another gotcha: you need to keep
the parameters in the same order that they appear in your procedure, even
though you get to set the name.
HTH,
Matt
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
