Hi everybody. I have a problem with the report generation. I'm using HSQLDB 1.8 as dbms. I am using Hibernate too but I am using a JDBC connection for the reports. I designed some reports with iReport 4.0. I have two types of reports in my application, the first type prints invoices and the second type prints sales reports.

For the second type I need to include dates (using this notation '2011-1-1') in the WHERE statement. This kind of reports produces an exception but not the first type. This is the message when I request a sales report (I deleted some lines):

net.sf.jasperreports.engine.JRException: Error executing SQL statement for : salescake
at net.sf.jasperreports.engine.query.JRJdbcQueryExecu ter.create Datasource(JRJdbcQueryExecuter.java:169)
at net.sf.jasperreports.engine.fill.JRFillDataset.cre ateQueryDa tasource(JRFillDataset.java:684)
at net.sf.jasperreports.engine.fill.JRFillDataset.ini tDatasourc e(JRFillDataset.java:605)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setP arameters( JRBaseFiller.java:1281)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill (JRBaseFil ler.java:901)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill (JRBaseFil ler.java:845)
at net.sf.jasperreports.engine.fill.JRFiller.fillRepo rt(JRFille r.java:58)
at net.sf.jasperreports.engine.JasperFillManager.fill Report(Jas perFillManager.java:417)
at net.sf.jasperreports.engine.JasperFillManager.fill Report(Jas perFillManager.java:247)
at com.reports.SalesByCustomerReport.showReport(Sales ByCustomer Report.java:24)
at com.presentation.SalesByCustomerInternalFrame.view InvoiceBut tonActionPerformed(SalesByCustomerInternalFrame.ja va:231)
at com.presentation.SalesByCustomerInternalFrame.acce ss$100(Sal esByCustomerInternalFrame.java:17)
at com.presentation.SalesByCustomerInternalFrame$2.ac tionPerfor med(SalesByCustomerInternalFrame.java:87)
at javax.swing.AbstractButton.fireActionPerformed(Unk nown Source)
at javax.swing.AbstractButton$Handler.actionPerformed (Unknown Source)
...
Caused by: java.sql.SQLException: Wrong data type: java.lang.IllegalArgumentException in statement [select I.INVOICE_NUMBER, I.SALE_DATE,I.CONSIGNEE,I.DESTINATION_COUNTRY,I.DE LIVERY_CON DITIONS,P.TARIFF_ENTRY,P.LOT,P.DRY_NET_WEIGHT,I.TO TAL
from INVOICE I, PRODUCT P
where I.INVOICE_ID=P.INVOICE_ID and I.CONSIGNEE='CONSORCIO MINERO S. A.' and I.SALE_DATE>='2011-1-20' and I.SALE_DATE<='2011-14-20' ]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.executeQuery (Unknown Source)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecu ter.create Datasource(JRJdbcQueryExecuter.java:163)
... 48 more

I tested this query in the HSQLDB utility and it is correct. I am thinking that the problem could be the JRE, I am using this one:

C:\Program Files\Java\jre6\bin>java -version
java version "1.6.0_24"
Java(TM) SE Runtime Environment (build 1.6.0_24-b07)
Java HotSpot(TM) Client VM (build 19.1-b02, mixed mode, sharing)

As you can see, my Windows Vista pc can't recognize the java command so I have to call it specifically from the directory where it is located. Maybe the problem is that I am using Windows Vista (I know it sucks but I had no time to change it).

When I copy my program to my Windows XP computer it works fine. All the reports are shown correctly. This pc has this JRE:

C:\Documents and Settings\Manuel & Juancho>java -version
java version "1.6.0_01"
Java(TM) SE Runtime Environment (build 1.6.0_01-b06)
Java HotSpot(TM) Client VM (build 1.6.0_01-b06, mixed mode, sharing)

There is no problem to recognize the java command.

Let me tell you something. Is it possible that HSQLDB or iReport can delete the database content? It happened to me. I was designing my last report and a "No pages in document" message was shown. I thought that I was wrong. Finally I checked the database and there was no data! What is the problem?

Please help me.

Thanks in advance.