Creating XML from Query

[2001 JI Open Discussion]

By: Keith Berman - bobbassen

Creating XML from Query

2002-07-09 09:54

Is there a way to extend JasperReports to create an XML file from the ResultSet of the SQL query before or after it creates the .jrprint file?

 

I need to add an option in my Web Application for my users to export the results of the query to XML or Excel as well as PDF and obviously the .jrpXML is of no use to the user.

 

I was thinking I could pass a flag to the JRQueryExecuter class and if the flag was true it would spit out the ResultSet to a file in XML format.

 

Do you see any problems with that?

 

Thanks

 

 

By: Teodor Danciu - teodord

RE: Creating XML from Query

2002-07-10 10:00

 

Hi,

 

Since the structure of the XML file you want to

obtain is specific to your application, I suggest

you create your own JRPrinterXML class that

will translate the .jrprint file into your XML format.

 

This way you make sure there wont be any

problems when you upgrade to a new version

of JasperReports.

If you work on JRQueryExecuter, you will have

to modify it every time you get a new JasperReports.

 

Good luck!

Teodor

 

 

 

By: Keith Berman - bobbassen

RE: Creating XML from Query

2002-07-11 11:56

Thanks Teodor,

 

Instead, I have modified the JRBaseFiller.fill() method to write out the columns and the data from the ResultSet to a tab delimited file which Excel can read.

 

The only problem I had was that I tried to set the ResultSet back to the beginning (after the filling process) to reuse it by using rs.first() method and I got the following error:

 

fill:

[java] java.sql.SQLException: This function is not supported

[java] at org.hsqldb.Trace.error(Trace.java:192)

[java] at org.hsqldb.jdbcResultSet.first(jdbcResultSet.java:1034)

[java] at dori.jasper.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:225)

 

I even tried to create the PreparedStatement in JRQueryExecuter.executeQuery() with these parameters:

PreparedStatement pstmt = conn.prepareStatement(queryString, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

 

That still didn't work so I had to create 2 different ResultSets. Do you know why the first() method is failing?

 

Thanks

 

 

By: Keith Berman - bobbassen

RE: Creating XML from Query - answer

2002-07-12 11:18

I found the problem, for some reason the hsqldb database does not support the first() method. When I used it with our Oracle Database, there were no problems.