cfprocparam refers to the initialization constants of the procedure, or the
"arguments"
When you have what appears to be 4 input parameters:
bg.sp_GetRapPrice '#ruff.Shape#','#ruff.Clarity#','#ruff.Color#',
#ruff.carat#
then you need 4 procParams
<cfstoredproc procedure="bg.sp_GetRapPrice" datasource="bg"
returncode="Yes">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" variable="Shape"
null="No" value="#someVal#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" variable="Clarity"
null="No" value="#someVal#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" variable="Color"
null="No" value="#someVal#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" variable="carat"
null="No" value="#someVal#">
<cfprocresult name="GetRapPPC" maxrows="#ruff.recordcount#">
</cfstoredproc>
Now the fact that you have what appears to be an enumerated set of input
parameters might lead someone to believe order isn't important, but you
might want to make sure its in the correct order. Also MSSQL has the
@variable_name syntax to refer to parameters:
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="@carat"
null="No">
which you don't have to comply to in Oracle.
CF doesn't have the easiest procedure implementation and sometimes it takes
a little tweaking here and there to find out what parameters you need to
feed in. I have found the that CFstoredproc is quite sensitive to database
type, so ODBC errors are largely due to mismatched types or arguments.
best of luck...read the documentation, it isn't that bad.
-marcus
----Original Message Follows----
From: "Sean Faulkner" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: <[EMAIL PROTECTED]>
Subject: [CFTALKTor] Working with CFStoredproc
Date: Sat, 26 Jan 2002 17:27:53 -0800
MIME-Version: 1.0
Received: from [209.167.227.131] by hotmail.com (3.2) with ESMTP id
MHotMailBE1CA32200424136E852D1A7E38340660; Sat, 26 Jan 2002 17:30:14 -0800
Received: from localhost (mail@localhost)by hummm.infopreneur.net
(8.9.3/8.8.7) with SMTP id UAA30711;Sat, 26 Jan 2002 20:25:35 -0500
Received: by hummm.infopreneur.net (bulk_mailer v1.12); Sat, 26 Jan 2002
20:24:26 -0500
Received: (from major@localhost)by hummm.infopreneur.net (8.9.3/8.8.7) id
UAA30692for cfug-outgoing; Sat, 26 Jan 2002 20:24:26 -0500
Received: from femail37.sdc1.sfba.home.com (femail37.sdc1.sfba.home.com
[24.254.60.31])by hummm.infopreneur.net (8.9.3/8.8.7) with ESMTP id
UAA30688for <[EMAIL PROTECTED]>; Sat, 26 Jan 2002 20:24:25 -0500
Received: from seanmain ([24.79.123.244]) by femail37.sdc1.sfba.home.com
(InterMail vM.4.01.03.20 201-229-121-120-20010223) with SMTP
id <20020127012518.QLHU26483.femail37.sdc1.sfba.home.com@seanmain>
for <[EMAIL PROTECTED]>; Sat, 26 Jan 2002 17:25:18 -0800
>From [EMAIL PROTECTED] Sat, 26 Jan 2002 17:30:56 -0800
Message-ID: <00a801c1a6d1$d7f64060$[EMAIL PROTECTED]>
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.00.2615.200
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Sender: [EMAIL PROTECTED]
Hi all :
For the first time ever (and under immense time pressure as always) I'm
trying to produce what I thought was a simple output based on three tables
of data, but the problem is the price for the items is generated dynamically
based on a stored procedure, (because depending on the people who are
looking at the data, a different price is displayed to them.)
I tried first just running two seperate queries, one to grab all the data
from the table in question, and one to run the stored procedure on the first
query to generate the price.
My first query went like this (used to grab the data from the table in
question);
<cfquery name="ruff" datasource="bg">
SELECT * from bg.#url.page#
</cfquery>
<cfquery name="GetRapPPC" datasource="bg">
bg.sp_GetRapPrice '#ruff.Shape#','#ruff.Clarity#','#ruff.Color#',
#ruff.carat#
</cfquery>
And to output:
<cfoutput query="GetRapPPC">
#ruff.Carat#;#ruff.Color#;#ruff.Clarity#;#ruff.Shape#;<b>#GetRapPPC.Price#</b>;#url.page#
</cfoutput>
The problem is, obviously, this only returns one row at a time. I tried
doing the output through a CFloop, specifiying the same query name and from
1 to #ruff.recordcount#, but it also only displayed one row at a time.
A friend recommended using the CFstoredproc to me, and I've tried fooling
around with it but none of the documentation in CFstudio or online really
help much. The variable I'm trying to get at is referred to as '@Price' in
the stored procedure, and I'm a little confused with all the syntax of it,
and it seems like overkill for my simple prupose.
Anyone have any ideas??
Here was my attempt at cfstoredproc:
<cfstoredproc procedure="bg.sp_GetRapPrice" datasource="bg"
returncode="Yes">
<cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" variable="@Price"
dbvarname="@Price" null="No">
<cfprocresult name="GetRapPPC" maxrows="#ruff.recordcount#">
</cfstoredproc>
Any ideas, advice, suggestions anyone has would be greatly appreciated.
Thanks
Take care
Sean
_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "Marcus Augustus" <[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
