hierarchical resultsets & xml

[Steve Conover <sc...@groundswell.net>]

H everyone,

For the last week I've been trying to solve the problem of how to write ONE
sql query, and get back a resultset that can be looped thru and translated
into XML.  And I want to use XSP/Cocoon.

It appears that the root of the problem is that JDBC doesn't do hierarchical
recordsets (I don't think it's in jdbc 2.0 either - does anyone know this
for sure?).  So I can't directly construct a query that brings back

department 1
	Employee 1
	Employee 2
department 2
	Employee 3
	Employee 4
etc...(that wouldn't use standard sql anyway, it'd be oracle-specific)

So my question is, can all of you who have dealt with this problem impart
some of your wisdom?  Do you go thru several xsp steps (run query, get
departments, for-each department, run query to get employees, etc)?  What's
the most elegant way of doing this?  Any performance gotchas to look out
for?

Any advice much appreciated!

Regards,
Steve

Re: hierarchical resultsets & xml

[Ron Chan <rc...@i-tao.com>]

Hi Steve

one way of doing is is to write a query that brings back

department 1 Employee 1
department 1 Employee 2
department 2 Employee 3
department 2 Employee 4

then use XSLT to do the grouping for you

another way, the way that we have implemented something similar in a
production system, is to hold the complex JDBC loops and calculations in
external classes - which returns a hierarchical recordset with sub totals
for groups all pre-calculated.

I find the second way more "elegant" since it truely seperates the styling,
content and logic -  the code is easier to read and you can offload work
done by the classes to another system if there is a need to scale.

Such is the flexibility of Cocoon - I'm sure there are many other ways of
doing this - each with their relative merits.

Hope this helps
Ron

Ron Chan
mailto:rchan@i-tao.com
www.i-tao.com

----- Original Message -----
From: Steve Conover <sc...@groundswell.net>
To: <co...@xml.apache.org>
Sent: Monday, August 07, 2000 10:54 PM
Subject: hierarchical resultsets & xml


> H everyone,
>
> For the last week I've been trying to solve the problem of how to write
ONE
> sql query, and get back a resultset that can be looped thru and translated
> into XML.  And I want to use XSP/Cocoon.
>
> It appears that the root of the problem is that JDBC doesn't do
hierarchical
> recordsets (I don't think it's in jdbc 2.0 either - does anyone know this
> for sure?).  So I can't directly construct a query that brings back
>
> department 1
> Employee 1
> Employee 2
> department 2
> Employee 3
> Employee 4
> etc...(that wouldn't use standard sql anyway, it'd be oracle-specific)
>
> So my question is, can all of you who have dealt with this problem impart
> some of your wisdom?  Do you go thru several xsp steps (run query, get
> departments, for-each department, run query to get employees, etc)?
What's
> the most elegant way of doing this?  Any performance gotchas to look out
> for?
>
> Any advice much appreciated!
>
> Regards,
> Steve
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org
>
>

Re: hierarchical resultsets & xml

[Raj Basavaraju <ra...@bidland.com>]

Steve Conover wrote:

> H everyone,
>
> For the last week I've been trying to solve the problem of how to write ONE
> sql query, and get back a resultset that can be looped thru and translated
> into XML.  And I want to use XSP/Cocoon.
>
> It appears that the root of the problem is that JDBC doesn't do hierarchical
> recordsets (I don't think it's in jdbc 2.0 either - does anyone know this
> for sure?).  So I can't directly construct a query that brings back

You are right, even jdbc 2.0 doesn't handle hierarchical recordsets.

> department 1
>         Employee 1
>         Employee 2
> department 2
>         Employee 3
>         Employee 4
> etc...(that wouldn't use standard sql anyway, it'd be oracle-specific)
>
> So my question is, can all of you who have dealt with this problem impart
> some of your wisdom?  Do you go thru several xsp steps (run query, get
> departments, for-each department, run query to get employees, etc)?  What's
> the most elegant way of doing this?  Any performance gotchas to look out
> for?

Can you use hierarchical query something like:

  select rpad(' ',level*3)||surname employee
  from employees
  start with mgr_id = 0
  connect by prior emp_id = mgr_id;

Best regards.

Raj

Re: hierarchical resultsets & xml

["Marcelo F. Ochoa" <mo...@ieee.org>]

Steve Conover wrote:

> H everyone,
>
> For the last week I've been trying to solve the problem of how to write ONE
> sql query, and get back a resultset that can be looped thru and translated
> into XML.  And I want to use XSP/Cocoon.
>
> It appears that the root of the problem is that JDBC doesn't do hierarchical
> recordsets (I don't think it's in jdbc 2.0 either - does anyone know this
> for sure?).  So I can't directly construct a query that brings back
>
> department 1
>         Employee 1
>         Employee 2
> department 2
>         Employee 3
>         Employee 4
> etc...(that wouldn't use standard sql anyway, it'd be oracle-specific)
>
> So my question is, can all of you who have dealt with this problem impart
> some of your wisdom?  Do you go thru several xsp steps (run query, get
> departments, for-each department, run query to get employees, etc)?  What's
> the most elegant way of doing this?  Any performance gotchas to look out
> for?

   Searching in Oracle Documentation CDRom, I get this examples in SQLJ that
deal with CURSOR types. SQLJ is translated by any translator (oracle sqlj
translator for example) in JDBC 1.x compliant calls.   I attached the page that
has this information.

>
>
> Any advice much appreciated!
>
> Regards,
> Steve
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org

     I think that incorporate SQLJ support in XSP, as taglib, is good
improvement in developed speed and clarify. SQLJ is standard and is supported
by many Databases  vendors.     Donald, What do you thing about this?
     Best Regards, Marcelo.