It might be easier for you to troubleshoot this in Toad or some other Oracle
editor, to get the stored procedure working correctly before trying to call it
from CF. Otherwise, it can be confusing for you as to which end is causing the
problem (like sometimes I can verify code in Toad but
Don't loop over the cursor in the proc; CF does that for you with the
procresult tag. Also, the only argument you need is the cursor for the
procresult; get rid of the rest.
--
WSS4CF - WS-Security framework for CF
http://wss4cf.riaforge.org/
On 14 July 2010 08:48, Chad Baloga
Shannon is right on. However, I tend to use a weak ref cursor like
PROCEDURE procName (
(input params)
p_cursorOUT sys_refcursor)
Makes thing much easier.
Your example is not returning cursors. All you are doing is looping over a
cursor without outputting anything except to the
I have never used stored procedures before but have been working on some code
where they might be helpful to use with CFSTOREPROC instead of CFQUERY since I
am dealing with a ton of records. Can anyone provide an example of a simple
stored procedure which will return a set of data with more
I'm not completely clear if you're asking about how to write the proc or how to
call the proc. You call the proc via ColdFusion like so:
cfstoredproc procedure=
datasource=
username=
password=
I can't seem to get cfprocresult to dump anything (it's undefined). The proc
runs and returns data but for some reason it doesn't put the record set in it.
Any ideas?
I'm not completely clear if you're asking about how to write the proc
or how to call the proc. You call the proc via
We'll really need to know which DB you're using.
--
WSS4CF - WS-Security framework for CF
http://wss4cf.riaforge.org/
On 14 July 2010 08:23, Chad Baloga cbal...@gmail.com wrote:
I can't seem to get cfprocresult to dump anything (it's undefined). The proc
runs and returns data but for
Oracle 10g
We'll really need to know which DB you're using.
--
WSS4CF - WS-Security framework for CF
http://wss4cf.riaforge.org/
On 14 July 2010 08:23, Chad Baloga cbal...@gmail.com wrote:
I can't seem to get cfprocresult to dump anything (it's undefined).
The proc runs and
Have you declared your result as a ref cursor in the PL/SQL?
--
WSS4CF - WS-Security framework for CF
http://wss4cf.riaforge.org/
On 14 July 2010 08:31, Chad Baloga cbal...@gmail.com wrote:
Oracle 10g
We'll really need to know which DB you're using.
--
WSS4CF - WS-Security framework
I tried but get an error. Here is my stored procedure:
create or replace
PROCEDURE GETALL_SP (
v_a OUT table.a%TYPE,
v_b OUT NOCOPY table.b%TYPE,
v_c OUT NOCOPY table.c%TYPE
) IS
CURSOR Cursor_table IS
select distinct a, b, c
from mytable
where d not in (select d from getDeleted_V);
BEGIN
My error is:
Error(2,1): PLS-00410: duplicate fields in RECORD,TABLE or argument list are
not permitted when I have...
create or replace
PROCEDURE GETALL_SP (
v_a OUT table.a%TYPE,
v_b OUT NOCOPY table.b%TYPE,
v_c OUT NOCOPY table.c%TYPE,
Cursor_table sys_refcursor
) IS
Jessica Kennedy wrote:
I have looked over the various methods for displaying hierarchal data with
MySql, and there don't really seem to be many elegant solutions-- the best
method (according to many) seemed to be creating a column for lft rgt
values as a nested tree structure
That is
Jessica Kennedy wrote:
I have looked over the various methods for displaying hierarchal
data with MySql, and there don't really seem to be many elegant
solutions-- the best method (according to many) seemed to be creating
a column for lft rgt values as a nested tree structure
That is
Jessica Kennedy wrote:
Jochem van Dieten wrote:
What I have done before is used a denormalized model where the records
themselves had an adjacency list model, but I maintained a nested set in
a separate table. With a trigger on the records themselves that only
fired when the ID and/or
Hey, I have posted a couple of times on getting a hierarchal tree 22 levels
deep programmed for an MLM company. My original coding was using too much
memory, as I was more or less creating a FILO type structure with several
arrays. I have looked over the various methods for displaying
We have built several programs that build a tree structure an infinite amount
of levels deep.
the best way we have found to do it is to have a table that contains all items
each with a unique reference. for example with 2 columns folderId and
folderName;
Then have another table called
try this link we found it really useful although we built the database tables
slightly different:
http://www.dougboude.com/blog/1/2006/06/Recursive-Functions-in-ColdFusion.cfm
~|
ColdFusion is delivering applications solutions
Does anyone know how to loop over a list that you have passed into a
stored procedure (from within the stored procedure)?
I am trying to insert some relationships into a table:
i.e. insert user details and pass a list of permissions (e.g. 1, 4, 6, 8)
loop over user permission list
insert
from the list
SET @list = SUBSTRING(@list, LEN(@item) + 2, LEN(@list))
END
SELECT @item 'List Item'
END
Ade
-Original Message-
From: Mark Drew [mailto:[EMAIL PROTECTED]
Sent: 10 January 2006 17:52
To: CF-Talk
Subject: OT: Stored Procedure Help
Does
Does anyone know how to loop over a list that you have passed into a
stored procedure (from within the stored procedure)?
I am trying to insert some relationships into a table:
i.e. insert user details and pass a list of permissions (e.g. 1, 4, 6, 8)
loop over user permission list
insert
To: CF-Talk
Subject: OT: Stored Procedure Help
Does anyone know how to loop over a list that you have passed into a
stored procedure (from within the stored procedure)?
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4
Hi Mark
Check out this post from Neil Robertson-Ravo on the CF-SQL HOF list:
http://www.houseoffusion.com/cf_lists/message.cfm/forumid:6/messageid:2346
He uses a UDF to convert your list into a 'table' variable that you could
then iterate through in your SP.
HTH
On 1/11/06, Mark Drew [EMAIL
I'm new to stored procedures ( in MS SQL ) and need help with the following
query...
Essentially what my procedure will do is take an argument of CLIENT_ID and
return a list of COUNTRIES. Simple enough, cept for the fact that the database
does not return a single ID, but a list of IDS that
so what I need to do in MSSQL is perform a query to grab that list of comma
seperated
numbers, put those numbers into an array OR a list and then loop over that
array/list
and and return the results ( which is a list of country names ).
What you're going to need to investigate to do this
Why not make a new SELECT statement in the countries table and put a
WHERE country_id IN (the list)?
Where the list is te 6,7,8, etc
Good luck!
Fabio Terracini
Protoculture wrote:
I'm new to stored procedures ( in MS SQL ) and need help with the following
query...
Essentially what my
Did you have your answer?
I believe you are trying to get the country's names of the country's ids
'6,31,32,33,35,36,', right?
I would do this:
SELECT
COUNTRY_NAME
FROM
COUNTRY
WHERE
COUNTRY_ID IN (SELECT COUNTRY_ID FROM SOMETABLE WHERE CLIENT_ID = 15421)
Johnny
I'm new to stored
If you want to pass comma separated IDs to a stored procedure then
use it in WHERE clause:
CREATE PROCEDURE dbo.getCountryNames
@inputIDs varchar(255)
AS
SELECT countryname FROM Countries
WHERE countryID in (@inputIDs)
GO
If you need to loop through a query inside a stored procedure, use
I have a header and detail table. The detail tables stores product(s) associated with
the header (user information). Whenever I have more than one detail line, I get more
than one record returned. I only need to get the amount of headers back that match the
input, not detail.
I have a header and detail table. The detail tables stores product(s)
associated with the header (user information). Whenever I have more than
one detail line, I get more than one record returned. I only need to get
the amount of headers back that match the input, not detail.
I'm not sure I
:[EMAIL PROTECTED]]
Sent: Wednesday, July 24, 2002 10:10 AM
To: CF-Talk
Subject: Re: OT - Stored Procedure Help
I have a header and detail table. The detail tables stores product(s)
associated with the header (user information). Whenever I have more than
one detail line, I get more than one
Try a LEFT JOIN with Count of LEFT JOINED TABLE
Joe
- Original Message -
From: Houk, Gary [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, July 24, 2002 8:49 AM
Subject: OT - Stored Procedure Help
I have a header and detail table. The detail tables stores product(s
DETAIL B
ON A.HeaderID=B.HeaderID
where whatever clause
END
Joe
Certified Advanced ColdFusion Developer
- Original Message -
From: Houk, Gary [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, July 24, 2002 10:23 AM
Subject: RE: OT - Stored Procedure Help
Hello,
Here
2 tables - weborderheader,weborderdetail
1 stored procedure joining the 2 tables on the batch_number field. 1 input
for the procedure - searchstring
Objective: pass either a btn or batch_number to the stored procedure.
The stored procedure should then return the results of the search to
a
I have written a very simple stored proc to insert a record into a table =
and to return the value of the identity column that has been created by =
SQL. I have done this dozens of times in SQL 7. For some reason, I get =
a syntax error with what I have done. Below is the Table design and the
Skinner [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 12, 2002 11:15 AM
To: CF-Talk
Subject: Stored Procedure Help
I'm trying to write my first Cold Fusion Stored Procedure.
And I have run into a problem. Can someone help explain
what this error means: NULL Interface Ptr? I've looked
I'm trying to write my first Cold Fusion Stored Procedure.
And I have run into a problem. Can someone help explain
what this error means: NULL Interface Ptr? I've looked in
the online help documentation, and on Macromedia/Allair's
web site and there is absolutely no reference to this error,
provider=SQLOLEDB
-Original Message-
From: Ian Skinner [mailto:[EMAIL PROTECTED]]
Sent: 12 March 2002 16:15
To: CF-Talk
Subject: Stored Procedure Help
I'm trying to write my first Cold Fusion Stored Procedure.
And I have run into a problem. Can someone help explain
what
I know that some people are going to disagree with me.. but I hate using the
storedproc tag.
Try running this through cfquery and see what happens...
cfquery name=YestPayments datasource=Paragon
execute sp_ktChgsPmtsByDate sdate='#sdate#',Edate='#edate#'
/cfquery
My 2cents
Clint
CFSET
Subject: RE: Stored Procedure Help
I know that some people are going to disagree with me.. but I
hate using the
storedproc tag.
Try running this through cfquery and see what happens...
cfquery name=YestPayments datasource=Paragon
execute sp_ktChgsPmtsByDate @sdate='#sdate#',@Edate
: Bosky, Dave [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 14, 2002 5:00 PM
To: CF-Talk
Subject: CF-Query to a sql-server stored procedure..Help
I'm trying to convert this cold fusion query to stored
procedure but can't
get the correct syntax due to the dynamic where clause.
Any help
I'm trying to convert this cold fusion query to stored procedure but can't
get the correct syntax due to the dynamic where clause.
Any help would be really cool.
CFQUERY NAME=qResultsOfS DATASOURCE=HTCYP BLOCKFACTOR=100
SELECT CustomerID,Telephone,AreaCode,BusinessName,Address,
WHERE FirstName LIKE @FirstName AND LastName = @LastName
GROUP BY FirstName, LastName
HAVING COUNT 0
sebastian palmigiani [EMAIL PROTECTED] on 09/13/2000 06:12:28 PM
Please respond to [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
cc:(bcc: Jerry Tolentino/SIAC)
Subject: Stored Procedure Help
-
From: Jerry Tolentino [mailto:[EMAIL PROTECTED]]
Sent: 14 September 2000 14:34
To: [EMAIL PROTECTED]
Subject: Re: Stored Procedure Help Again
You don't have to perform another query. The last query that is performed
on a Stored Proc is the one that gets returned at the end.
In your example the query
How do you use the wildcard character % with an input parameter?
This does not work:
SELECT FirstName, LastName
FROM Members
WHERE FirstName LIKE '@Firstname%' AND LastName = @LastName
Sebastian
--
Archives:
: Jerry Tolentino/SIAC)
Subject: Stored Procedure Help
How do you use the wildcard character % with an input parameter?
This does not work:
SELECT FirstName, LastName
FROM Members
WHERE FirstName LIKE '@Firstname%' AND LastName = @LastName
Sebastian
Please respond to [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
cc:(bcc: Jerry Tolentino/SIAC)
Subject: Stored Procedure Help
How do you use the wildcard character % with an input parameter?
This does not work:
SELECT FirstName, LastName
FROM Members
WHERE FirstName LIKE '@Firstname
on 9/13/00 3:35 PM, Jerry Tolentino at [EMAIL PROTECTED] wrote:
If @FirstName is a CHAR or VARHCAR then you can just concatenate the
strings like so:
SET @FirstName = @FirstName + '%'
Then just @FirstName in your query. SQL should handle all the quoting
issues... if I am not mistaken.
Jerry:
Your suggestion works. It was another part of the stored procedure which is
at fault.
Thanks,
Sebastian
on 9/13/00 3:35 PM, Jerry Tolentino at [EMAIL PROTECTED] wrote:
If @FirstName is a CHAR or VARHCAR then you can just concatenate the
strings like so:
SET @FirstName =
The only way I can make this work is to have 2 queries. It seems that I
should be able to use just one query. But if I have just 1 query and say
IF @@rowcount 1 RETURN 2
It does not work
SET @FirstName = @FirstName + '%'
SELECT FirstName,
How do you refer to the result of a query in a stored procedure? The
statement "IF MemberStatus = 0" gives an error of "invalid column name for
MemberStatus"
-- Check Member Status
SELECT MemberStatus
FROM Members
WHERE MemberID = @ThisMemberID
-- If they are not currently active RETURN
September 2000 17:01
To: [EMAIL PROTECTED]
Subject: Stored Procedure Help
How do you refer to the result of a query in a stored procedure? The
statement "IF MemberStatus = 0" gives an error of "invalid column name for
MemberStatus"
-- Check Member Status
SELECT MemberStatu
-- Check Member Status
DECLARE @memerStatus int
SELECT @memberStatus = MemberStatus
FROM Members
WHERE MemberID = @ThisMemberID
-- If they are not currently active RETURN 2
IF @memberStatus = 0
--
/2000 12:01:07 PM
Please respond to [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
cc:(bcc: Jerry Tolentino/SIAC)
Subject: Stored Procedure Help
How do you refer to the result of a query in a stored procedure? The
statement "IF MemberStatus = 0" gives an error of "inva
I hit a snag trying to convert a cold fusion query into a stored procedure...
I am using SQL server 7.0..
I want to use a correlated subquery to insert a list of values into a
single field. With coldfusion, I use valuelist to convert the subquery into
1 entity that I can insert.. is there a
This is my first time working with a stored procedure and I'm not sure that
I'm doing it right.
I can get the procedure to run but I can't get the values to display. What
am I missing
!-- cfcode --
CFSTOREDPROC PROCEDURE="p_pricing_pipeline"
DATASOURCE="#application.datasource#"
exists#!br
/cfoutput
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 10, 2000 2:07 PM
To: [EMAIL PROTECTED]
Subject: Stored Procedure Help
This is my first time working with a stored procedure and I'm not sure that
I'm doing it right.
I can get the
:
07/10/00 12:03Subject: RE: Stored Procedure Help
PM
Now the list appears to be operational again, I'll try and throw this back
out and see if anyone has any ideas! :^)
I have a small stored procedure (do_something) that takes a parameter, adds
a value to it, and then returns a value, straight forward enough, right
but what I'd like to do
58 matches
Mail list logo
