I am new to Jaspersoft and trying to create my first report. Database connects OK, but no tables in the list. I manually enter query SELECT date, format FROM tournaments and get the following error:
net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:
SELECT date,
format
FROM tournaments
at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:553)
at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$18(ReportController.java:528)
at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:446)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:
SELECT date,
format
FROM tournaments
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:555)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:299)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1273)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:734)
at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:585)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: [SQLITE_CORRUPT] The database disk image is malformed (malformed database schema (tournamentRoundMatches) - near "WITHOUT": syntax error)
at org.sqlite.DB.newSQLException(DB.java:383)
at org.sqlite.DB.newSQLException(DB.java:387)
at org.sqlite.DB.throwex(DB.java:374)
at org.sqlite.NestedDB.prepare(NestedDB.java:134)
at org.sqlite.DB.prepare(DB.java:123)
at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
at org.sqlite.Conn.prepareStatement(Conn.java:404)
at org.sqlite.Conn.prepareStatement(Conn.java:399)
at org.sqlite.Conn.prepareStatement(Conn.java:383)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:404)
... 7 more
Of course, my database is not corrupt, but some of the tables (not tournaments) were created with the "WITHOUT ROWID" clause. I suspect the version of the JDBC driver: org.sqlite.JDBC is old and does not support the WITHOUT ROWID keyword, but I cannot figure out what version org.sqlite.JDBC I have. There are a number of up to date sqlite.JDBC's available on the web but I don't know how to install them into the Jaspersoft studio.
Thanks -- Bob
2 Answers:
To update your SQLite driver you will need to edit the Driver Classpath on the data adapter. That points to the jdbc driver the connection will use.
With the driver mentioned above, I created an SQLite table without the ROWID using the example from https://www3.sqlite.org/withoutrowid.html and then successfully made a report using it without error.
CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY KEY, cnt INTEGER ) WITHOUT ROWID; INSERT INTO wordcount(word,cnt) VALUES ('one',1);
Fantastic answer jgust!! Worked perfectly. Thank you very much.
BTW in case others are interested I found the lates driver here: GitHub - xerial/sqlite-jdbc: SQLite JDBC Driver
--Bob
