stored procedures, but also how to do so using compile time-bound ref
cursors rather than runtime-bound ref cursors. There is a big difference in
performance, especially under load.
It's listed in The ColdFusion MX Bible in the Oracle section. There are
some differences for doing this in CF5, mainly that you have to use the
maxRows="-1" kludge and you also have to specify cfprocparams for each ref
cursor, but the internal technique shown in the package definition is
exactly the same.
If you don't have a copy of the book then email me offlist at
[EMAIL PROTECTED] and I'll dig up some code for you.
Respectfully,
Adam Phillip Churvis
Member of Team Macromedia
Advanced Intensive ColdFusion MX Training:
* ColdFusion MX Master Class
* Advanced Development with CFMX and SQL Server 2000
http://www.ColdFusionTraining.com
Download CommerceBlocks V2.1 and LoRCAT from
http://www.ProductivityEnhancement.com
The ColdFusion MX Bible is in bookstores now!
----- Original Message -----
From: "David Jones" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, February 05, 2004 10:16 AM
Subject: RE: Oracle Stored Procedure and CF 5.0
> Matt,
>
> Do you know off hand how to return multiple record sets this way?
>
> I want to return the results from the first query to CF. Then I want to
run
> some other queries using data from the query above and return them too, is
> this possible?
>
> For instance....
> select EmpID, EmpName from Employee <--- I want this in the first
> CFPROCRESULT
>
> Then I want to loop these results inside the SP and create other
recordsets
>
> select EmpPosition from EmployeePosition where EmpID = Employee.EmpID
> (This is where I am not sure, I don't how to do this) <--- I also want
these
> queries returned to CF
>
>
> I would end up
>
> with
> <cfprocresult name="Employees">
>
> I would loop these based on how many there are and replace the
1,2,3,ect...
>
> <cfprocresult name="EmployeePosition1" resultSet="2">
> <cfprocresult name="EmployeePosition2" resultSet="3">
> <cfprocresult name="EmployeePosition3" resultSet="4">
> <cfprocresult name="EmployeePosition4" resultSet="5">
> <cfprocresult name="EmployeePosition5" resultSet="6">
> <cfprocresult name="EmployeePosition6" resultSet="7">
>
> Can I even do this?
>
> 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]
