JBDC MySQL query in BIRT -- keyword: LIMIT not working

I have set up a connection to a MySQL database by pointing to a JBDC driver which I downloaded from <a data-ipb='nomediaparse' href='https://dev.mysql.com/downloads/connector/j/'>here.</a>
 
I am able to ping and retrieve data sets from the database.
 
I cannot, however, use LIMIT, as an SQL query keyword, to dynamically set the number of rows to show in the report I would generate.
 
I have also tried googling around and found some workarounds that would be used for non-MySQL databases, but they also did not work (stuff like TOP, or FETCH FIRST 10 ROWS ONLY, etc.)
 
Any help would be appreciated.

Find more posts tagged with

Comments

jfranken

When you say that you cannot use LIMIT, does that mean that you are getting an error or that you are not allowed to use it? I tried using LIMIT in a query going to MySQL in both the open source and professional versions of BIRT and they both worked. I have version 5.1.20 of the driver although it's unlikely that a newer version of the driver would be the problem.
 
My query looks like:
 
select * 
from tablename 
LIMIT 2,3
 
So it should work. What error are you getting if you are getting an error?

micajblock

or you can change the settings on the data source

grenpose

Thank you for the responses.
 
The Query Builder seems to not recognise LIMIT as an SQL keyword. The word remains black, rather than turning purple like SELECT, WHERE, etc..
 
The error I get after clicking OK, is: "This SQL syntax is either invalid or is not currently supported. You can manually enter SQL. Some SQL builder functions have been disabled. To enable these functions, change the syntax."
 
Just in case it was the driver, I went and downloaded an older version (5.1.20) from <a data-ipb='nomediaparse' href='http://mvnrepository.com/artifact/mysql/mysql-connector-java/5.1.20'>here</a>. Still no luck.
 
I cannot work around the issue by modifying the settings on the data source, because I would like to pass the row limit into the report as a parameter.
 
Here is an image of the driver location(s).
 
<img src="http://www.downwardspiral.plus.com/pics/jbdc_driver_location.png" alt="jbdc_driver_location.png">

jfranken

Can you try it without using Query Builder? Just enter the SQL statement in the Data Set editor (select "Query" in the left panel).