[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
RE: [CF-Database] recursion
Ah, that sounds more sensible, thanks Chris ;)
-----Original message-----
From: "Chris Thomas" Chris.Thomas@bigpictureinteractive.co.uk
Date: Wed, 20 Oct 2004 09:32:14 +0100
To: "database programming" database@list.cfdeveloper.co.uk
Subject: RE: [CF-Database] recursion
> >----------------------------<
> >- see footer for list info -<
> >----------------------------<
> Try using a temporary table variable (or a temporary table) with a while > loop to build up the data you want, then do one select to return it all > in one recordset. It'll will avoid putting all the locks on the tempdb > database that cursors do.
>
> Chris.
>
> -----Original Message-----
> From: damian [mailto:damian@greenhouse-design.co.uk]
> Sent: 20 October 2004 09:04
> To: database programming
> 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 -<