[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
RE: [CF-Database] recursion
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
> >-<
>