[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
RE: [CF-Database] recursion
here's an example of it working but I've had to statically code the loops' to="" attribute:
<cfstoredproc datasource="blahblah" procedure="a_selectPageBlocks">
<cfprocparam cfsqltype="CF_SQL_INTEGER" dbvarname="@i_pageBlockID" type="in" value="0">
<cfloop from="1" to="6" index="resultCount">
<cfprocresult name="selectPageBlocks#resultCount#" resultset="#resultCount#">
</cfloop>
</cfstoredproc>
<!---generate list of queries--->
<cfparam name="variables.queryList" default="">
<cfloop from="1" to="6" index="resultCount">
<cfset variables.queryList="#variables.queryList#, selectPageBlocks#resultCount#">
</cfloop>
<cfset selectPageBlocks = queryNew("pageBlockID, pageBlockTitle, parentID")>
<cfloop list="#variables.queryList#" index="queryName">
<cfset newRow = queryAddRow(selectPageBlocks)>
<cfset newValue = querySetCell(selectPageBlocks, "pageBlockID", evaluate(queryName & '.pageblockID'))>
<cfset newValue = querySetCell(selectPageBlocks, "pageBlockTitle", evaluate(queryName & '.pageBlockTitle'))>
<cfset newValue = querySetCell(selectPageBlocks, "parentID", evaluate(queryName & '.parentID'))>
</cfloop>
--------
I want an output var from the sp that gives me the number of queries to then put in those loops...
It also seems to be a bit of a hack doing this stuff in the cfm template, if anyone knows other ways???
thx
d
-----Original message-----
From: damian damian@greenhouse-design.co.uk
Date: Wed, 20 Oct 2004 09:03:53 +0100
To: database programming database@list.cfdeveloper.co.uk
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 -<