[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [CF-Database] recursion

:-P

True this works fine, still it's better to have all possible query logic within the db is it not? Better encapsulation 'n all etc etc... Then if the site has to change to ASP for instance it is much easier to do so. Also it means you can use the query as a module which can be used across several languages rather than having a language specific module...

I wouldn't of thought performance is much of an issue with what is only to likely return max 30 rows so I ain't worried about that...

...keep your hands off those ciggies Russ

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 -<