[jdom-interest] SQL join -> Structured XML

[Philippe Lang]

Hi,

By "straight traversal", do you mean sending queries to the database
without joins, and so avoid having duplicated records from the table on
the "one" side? This is far from optimal, since you need a subquery per
row. With a single big query, you get the result much more quickly, but
the price you have to pay for that are duplicated records, which can be
quite annoying when you need to print a report. That's exactly why I
have developed these classes, in fact. They allow you to "recreate" the
initial structure of your data, just like a reporting tool, but with
code. Not graphically.

I have the impression this could be interesting for other people, so I
will send you these classes, as soon as I have been able to "polish"
them.

I remember the hierarchical queries from Microsoft, but really, we want
to be portable here.

-----Message d'origine-----
De : Shaun Smith [mailto:shauns at pixology.com]
Envoyé : vendredi, 26. avril 2002 10:44
À : 'jdom-interest at jdom.org'
Objet : RE: [jdom-interest] SQL join -> Structured XML


I think the important thing about Philippe's classes is that
it deduplicates records to actually provide a hierarchical view
of the joined resultset.  I have found this approach incredibly
useful, especially when the required dataset has grown large.
Phillipe, you may also be interested to try straight traversal
of the database to producte the results - this may actually
be faster.

For instance, in a one to many relationship, the tabular result
will include the one record as many times as there are many records.
This can be quite wasteful to process.

Microsoft do have a technology called ADO Shape which allows
hierarchical
queries to be submitted, although I have not actually used it myself
it does look great.

Shaun.

> These classes are useful for reporting, not exactly for 
> search. We send a query with joins to the database, get back 
> a tabular result (CachedRowSet), create a structured XML file 
> from this result, and sent it to FOP, which can ouput PDF, 
> PCL, PS... The classes do not generate code.
> 
> Is there another way to get XML from a database query with 
> joins? I know some databases can actually give back XML 
> directly, instead of a tabular result, but this is not a 
> feature we can rely on. It's not portable enough.
> 
_______________________________________________
To control your jdom-interest membership:
http://lists.denveronline.net/mailman/options/jdom-interest/youraddr@you
rhost.com