[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
RE: [CF-Database] recursion
I am *pretty* sure you can just stick 10 cfprocresult tags in and if
there isn't a resultset, it will be empty (or null or just not
important)...
Why not just stick a whole bunch of procresults in?
Paul
On Wed, 2004-10-20 at 11:22, Russ Michaels (Snake) wrote:
> >----------------------------<
> >- see footer for list info -<
> >----------------------------<
> Well what I told him to do is a single query with everything in it, and just
> loop over that query in CF to get the child items, rather than recursively
> querying the database.
> But he doesn't want to do that.
>
> > -----Original Message-----
> > From: database-bounces@list.cfdeveloper.co.uk
> > [mailto:database-bounces@list.cfdeveloper.co.uk] On Behalf Of
> > Tom Smith
> > Sent: 20 October 2004 09:31
> > To: database programming
> > Subject: Re: [CF-Database] recursion
> >
> > >----------------------------<
> > >- see footer for list info -<
> > >----------------------------<
> > Damian,
> >
> > I not sure about that, it might be worth googling this for
> > "number queries returned storedproc coldfusion" or something
> > like that.
> >
> > Generally I always know the number of returns from a query...
> >
> > Question, why do you need to use a cursor for that???
> > couldn't you just loop over the query? and excute the second
> > SP from within CF? You see I don't understand the perf gains
> > (not that there are any) from using a cursor over CF in this
> > instance. Maybe someone with more knowledge of SQL server
> > could correct me on this.
> >
> > ta
> >
> > Tom
> > ----- Original Message -----
> > From: damian
> > To: database programming
> > Sent: Wednesday, October 20, 2004 9:03 AM
> > Subject: RE: [CF-Database] recursion
> >
> >
> > >----------------------------<
> > >- see footer for list info -<
> > >----------------------------<
> > ThxTom, but when there are x number of resultsets it's a
> > different matter... I can't predict what will be coming out
> > unless perhaps there is a way for SQL to output the number of
> > queries?
> >
> > ....then I can loop to create my x number of resultsets
> >
> > -----Original message-----
> > From: "Tom Smith" tomo_smith@yahoo.co.uk
> > Date: Tue, 19 Oct 2004 22:48:06 +0100
> > To: "'database programming'" database@list.cfdeveloper.co.uk
> > Subject: RE: [CF-Database] recursion
> >
> > > >----------------------------<
> > > >- see footer for list info -<
> > > >----------------------------<
> > > You need to user cfstoredproc and cfprocresult
> > >
> > > The result sets that you output (name) are in the order
> > that the cursor
> > > throws them out, so you'd need to set the result set -
> > otherwise it'll > only
> > > send back the first one.
> > >
> > > You'd end up with something like the following:
> > >
> > > <cfstoredproc procedure="a_selectPageBlocks" datasource="#ds#">
> > > <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER"
> > > variable="@pageblockID" value="0" null="No">
> > > <cfprocresult name="results1" resultset="1">
> > > <cfprocresult name="results2" resultset="2">
> > > <cfprocresult name="results3" resultset="3">
> > > <cfprocresult name="results4" resultset="4">
> > > <cfprocresult name="results5" resultset="5">
> > > </cfstoredproc>
> > >
> > >
> > > -----Original Message-----
> > > From: database-bounces@list.cfdeveloper.co.uk
> > > [mailto:database-bounces@list.cfdeveloper.co.uk] On
> > Behalf Of damian
> > > Sent: 19 October 2004 22:20
> > > To: database programming
> > > Subject: Re: [CF-Database] recursion
> > >
> > > >----------------------------<
> > > >- see footer for list info -<
> > > >----------------------------<
> > > For info, I also tried executing the sp in a cfquery but
> > with the same
> > > result:
> > >
> > > <cfquery name="selectPageBlocks" datasource="blah">
> > > EXEC a_selectPageBlocks @pageBlockID = 0
> > > </cfquery>
> > >
> > > can only return 1 row
> > >
> > > -----Original message-----
> > > From: damian damian@greenhouse-design.co.uk
> > > Date: Tue, 19 Oct 2004 19:00:24 +0100
> > > To: database programming database@list.cfdeveloper.co.uk
> > > Subject: Re: [CF-Database] recursion
> > >
> > > > >----------------------------<
> > > > >- see footer for list info -<
> > > > >----------------------------<
> > > > ....and I went and left the email subject as "List
> > muppets", what > does
> > > that tell you!
> > > >
> > > > -----Original message-----
> > > > From: damian damian@greenhouse-design.co.uk
> > > > Date: Tue, 19 Oct 2004 18:55:58 +0100
> > > > To: database programming database@list.cfdeveloper.co.uk
> > > > Subject: Re: [CF-Database] List muppets
> > > >
> > > > > >----------------------------<
> > > > > >- see footer for list info -<
> > > > > >----------------------------<
> > > > > A muppet asks ;)
> > > > >
> > > > > I got a wee recursive stored proc here which pulls
> > out 6 > (individual)
> > > rows from my db in Query Analyser:
> > > > >
> > > > > --------------------------------------------------
> > > > > CREATE PROCEDURE [dbo].[a_selectPageBlocks]
> > > > > @pageBlockID int
> > > > > AS
> > > > >
> > > > > DECLARE @parentID int
> > > > > DECLARE @pageBlockTitle varchar(500)
> > > > > DECLARE cur_Level CURSOR LOCAL FOR
> > > > >
> > > > > SELECT parentID, pageBlockID, pageBlockTitle
> > > > > FROM c_pageBlocks
> > > > > WHERE parentID = @pageBlockID
> > > > > ORDER BY listPosition
> > > > >
> > > > > OPEN cur_Level
> > > > >
> > > > > FETCH NEXT FROM cur_Level INTO @parentID, @pageBlockID,
> > > @pageBlockTitle
> > > > > WHILE @@FETCH_STATUS = 0
> > > > > BEGIN
> > > > > SELECT @parentID AS parentID, @pageBlockID AS pageBlockID,
> > > @pageBlockTitle AS pageBlockTitle
> > > > > EXEC a_selectPageBlocks @pageBlockID
> > > > > FETCH NEXT FROM cur_Level INTO @parentID, @pageBlockID,
> > > @pageBlockTitle
> > > > > END
> > > > > CLOSE cur_Level
> > > > > DEALLOCATE cur_Level
> > > > > GO
> > > > > --------------------------------------------------
> > > > >
> > > > > ok, so then I call it with:
> > > > >
> > > > > <cfstoredproc datasource="blahblah" >
> > procedure="a_selectPageBlocks">
> > > > > <cfprocparam cfsqltype="CF_SQL_INTEGER" >
> > dbvarname="@i_pageBlockID"
> > > type="in" value="0">
> > > > > <cfprocresult name="selectPageBlocks">
> > > > > </cfstoredproc>
> > > > >
> > > > > Thing is, it only outputs one row in cfoutput or
> > dump, presumably
> > > because there is a new query for each recursion in the
> > sp... anyone know > how
> > > to get round this? (compile a new query from the results
> > of the > recursive
> > > ones in the SP??)
> > > > >
> > > > > Cheers
> > > > > d
> > > > >
> > > > >
> > > > >
> > > > > -----Original message-----
> > > > > From: "Russ Michaels \(Snake\)" snake.lists@snakepit.net
> > > > > Date: Tue, 19 Oct 2004 15:43:08 +0100
> > > > > To: "'database programming'"
> > database@list.cfdeveloper.co.uk
> > > > > Subject: [CF-Database] List muppets
> > > > >
> > > > > > >----------------------------<
> > > > > > >- see footer for list info -<
> > > > > > >----------------------------<
> > > > > > Nope, I decided to make a bit more blatant for
> > Muppets like > this.
> > > > > >
> > > > > > >
> > > > > > > And here was me about to email Russ to ask
> > him if would
> > > > > > > remove the "see footer for list info" because
> > its beginning
> > > > > > > to get on my nerves. Guess it'll have to
> > stay on there for a >
> > > > > > > while longer yet :(
> > > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > For details on ALL mailing lists and for
> > joining or leaving > lists,
> > > go to http://list.cfdeveloper.co.uk/mailman/listinfo
> > > > > >
> > > > > > --
> > > > > > CFDeveloper Sponsors:-
> > > > > > >- Hosting provided by www.cfmxhosting.co.uk -<
> > > > > > >- Forum provided by www.fusetalk.com -<
> > > > > > >- DHTML Menus provided by www.APYCOM.com -<
> > > > > > >- Lists hosted by www.Gradwell.com -<
> > > > > > >- CFdeveloper is run by Russ Michaels, feel
> > free to volunteer > your
> > > help -<
> > > > >
> > > > > --
> > > > > For details on ALL mailing lists and for joining
> > or leaving lists, > go
> > > to http://list.cfdeveloper.co.uk/mailman/listinfo
> > > > >
> > > > > --
> > > > > CFDeveloper Sponsors:-
> > > > > >- Hosting provided by www.cfmxhosting.co.uk -<
> > > > > >- Forum provided by www.fusetalk.com -<
> > > > > >- DHTML Menus provided by www.APYCOM.com -<
> > > > > >- Lists hosted by www.Gradwell.com -<
> > > > > >- CFdeveloper is run by Russ Michaels, feel free
> > to volunteer > your
> > > help -<
> > > >
> > > > --
> > > > For details on ALL mailing lists and for joining or
> > leaving lists, go > to
> > > http://list.cfdeveloper.co.uk/mailman/listinfo
> > > >
> > > > --
> > > > CFDeveloper Sponsors:-
> > > > >- Hosting provided by www.cfmxhosting.co.uk -<
> > > > >- Forum provided by www.fusetalk.com -<
> > > > >- DHTML Menus provided by www.APYCOM.com -<
> > > > >- Lists hosted by www.Gradwell.com -<
> > > > >- CFdeveloper is run by Russ Michaels, feel free to
> > volunteer your > help
> > > -<
> > >
> > > --
> > > For details on ALL mailing lists and for joining or
> > leaving lists, go to
> > > http://list.cfdeveloper.co.uk/mailman/listinfo
> > >
> > > --
> > > CFDeveloper Sponsors:-
> > > >- Hosting provided by www.cfmxhosting.co.uk -<
> > > >- Forum provided by www.fusetalk.com -<
> > > >- DHTML Menus provided by www.APYCOM.com -<
> > > >- Lists hosted by www.Gradwell.com -<
> > > >- CFdeveloper is run by Russ Michaels, feel free to
> > volunteer your help > -<
> > >
> > > --
> > > For details on ALL mailing lists and for joining or
> > leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo
> > >
> > > --
> > > CFDeveloper Sponsors:-
> > > >- Hosting provided by www.cfmxhosting.co.uk -<
> > > >- Forum provided by www.fusetalk.com -<
> > > >- DHTML Menus provided by www.APYCOM.com -<
> > > >- Lists hosted by www.Gradwell.com -<
> > > >- CFdeveloper is run by Russ Michaels, feel free to
> > volunteer your help -<
> >
> > --
> > For details on ALL mailing lists and for joining or leaving
> > lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo
> >
> > --
> > CFDeveloper Sponsors:-
> > >- Hosting provided by www.cfmxhosting.co.uk -<
> > >- Forum provided by www.fusetalk.com -<
> > >- DHTML Menus provided by www.APYCOM.com -<
> > >- Lists hosted by www.Gradwell.com -<
> > >- CFdeveloper is run by Russ Michaels, feel free to
> > volunteer your help -<
> > --
> > For details on ALL mailing lists and for joining or leaving
> > lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo
> >
> > --
> > CFDeveloper Sponsors:-
> > >- Hosting provided by www.cfmxhosting.co.uk -<
> > >- Forum provided by www.fusetalk.com -<
> > >- DHTML Menus provided by www.APYCOM.com -<
> > >- Lists hosted by www.Gradwell.com -<
> > >- CFdeveloper is run by Russ Michaels, feel free to
> > volunteer your help
> > >-<
> >
>
>
> --
> For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo
>
> --
> CFDeveloper Sponsors:-
> >- Hosting provided by www.cfmxhosting.co.uk -<
> >- Forum provided by www.fusetalk.com -<
> >- DHTML Menus provided by www.APYCOM.com -<
> >- Lists hosted by www.Gradwell.com -<
> >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<