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

Re: [CF-Database] recursion

But the tree can be ordered/ built in the query... so the looping stuff doesn't have to be rewritten, a simple output is all that is required as long as there is a param to detect where in the tree one is (a suitable param would be parentID).

But we're arguing the same thing bar encapsulation, I'd just like to learn how to do it in the db.

:-P

Russ Michaels (Snake) wrote:

----------------------------<
- see footer for list info -<
----------------------------<


Who said anything about moving the query logic out of the database?
My suggestion doesn't change that. You can still use an SP to get the data.
Any language can loop over a query, which you have to do anyway to build the
tree.



-----Original Message-----
From: database-bounces@list.cfdeveloper.co.uk [mailto:database-bounces@list.cfdeveloper.co.uk] On Behalf Of Damian Watson
Sent: 20 October 2004 12:08
To: database programming
Subject: Re: [CF-Database] recursion



----------------------------<
- see footer for list info -<
----------------------------<


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








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