RE: Oracle Stored Procedure and CF 5.0

[David Jones]

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]

---