BIRT学习

BIRT reports use a data source element to connect to a database or other data provider. BIRT uses a data set element to retrieve data from the data provider. Think of the data source as a connection, and the data set as a query.

[遇到的问题]

1.当我点击Data Sets下的数据库时出现一个问题，问题如下：

后来上网一查，说一个.jar的包没有导进来，一时云里雾里的，到底是哪个包需要放到哪个地方自己全不知道。只能继续查，一篇文章点击打开链接提到一种解决方案，感觉和我遇到的这个问题还是有点区别，因为里面提到的是Oracle的数据库，我用的却是MySQL数据库。但是它却给我提供了解决问题的一个思路。于是我在那篇文章中提到的目录找到我对应的目录，发现里面有个readme.txt文件，一看内容竟是“Place JDBC drivers' JAR files in this directory to make them available to BIRT.” 看来果真问题是出在这个.jar文件上，但是该放哪个JAR文件呢，又去点击打开链接里面看，找到了对应MySQL数据库的应该是mysql-connector-java-5.1.5-bin.jar这个文件，下载这个文件到我对应的目录下：eclipse-reporting-mars-R-win32-x86_64\eclipse\plugins\org.eclipse.birt.report.data.oda.jdbc_4.5.0.v201506092134\drivers，这个目录应该是eclipse下的plugins下的一个目录。然后再试，发现就好了。

mysql-connector-java-5.1.5-bin.jar下载地址：点击打开链接

2.当我执行一段比较复杂的mysql查询代码时出现如下的错误：

org.eclipse.birt.report.engine.api.EngineException: Error happened while running the report.
	at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:316)
	at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.runDataset(DatasetPreviewTask.java:278)
	at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.execute(DatasetPreviewTask.java:89)
	at org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:68)
	at org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:372)
	at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:119)
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the result set metadata.
    org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=500' at line 1
 ;
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=500' at line 1
	at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)
	at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
	at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)
	at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:414)
	at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:377)
	at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doGetMetaData(PreparedStatement.java:347)
	at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:563)
	at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:978)
	at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:607)
	at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1251)
	at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)
	at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)
	at org.eclipse.birt.data.engine.impl.QueryResults.getResultMetaData(QueryResults.java:132)
	at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.extractQuery(DatasetPreviewTask.java:350)
	at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:307)
	... 5 more
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=500' at line 1
 ;
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=500' at line 1
	at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:481)
	at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingPolicy1(Statement.java:420)
	at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:316)
	at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.getMetaData(BidiStatement.java:56)
	at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doGetMetaData(OdaQuery.java:423)
	at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:390)
	at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:407)
	... 16 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=500' at line 1
	at sun.reflect.GeneratedConstructorAccessor75.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.Util.getInstance(Util.java:381)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2537)
	at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1463)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1875)
	at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:477)

... 22 more

错误原因是因为数据库驱动文件版本较老导致的，解决方法是下载最新的驱动文件版本，下载地址： MySql Connector

注意是下载.tar.gz格式的包，而不是下载.msi文件。然后将下载文件解压，将其中的mysql-connector-java-5.1.36-bin.jar包替换问题1中的jar包即可。

重要参看page：链接一，链接2