How do I execute multiple mysql queries in Jasper Reports (not what you think...)

Question

I have a complex query that requires a rank in it. I've learned that the standard way of doing that is by using the technique found on this page: http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/. I'm using Infobright as the back end and it doesn't work quite as expected. That is, while a standard MySQL engine would show the rank as 1, 2, 3, 4, etc... Brighthouse (Infobright's engine) would return 1, 1, 1, 1, etc.... So I came up with a strategy of setting a variable, a function, and then execute it in the query. Here's a proof of concept query that does just that:

SET @rank = 0;

DROP FUNCTION IF EXISTS __GetRank;
DELIMITER $$
CREATE FUNCTION __GetRank() RETURNS INT
BEGIN
    SET @rank = @rank + 1;
    return @rank;
END$$

DELIMITER ;

select __GetRank() AS rank, id from accounts;

I then copied and pasted the function into Jasper Report's iReport and then compiled my report. After executing it, I get syntax errors. So I thought that perhaps the ; was throwing it off. So at the top of the query, I put in DELIMITER ;. This did not work either.

Is what I'm wanting to do even possible? If so, how? And if there's an Infobright way of getting a rank without writing a function, I'd be open to that too.

Answer 1

Here's how I solved this. I had my server side program execute a mysql script. I then took the output and converted it to a CSV. I then used this as the input data for my report. A little convoluted, but it works.

Answer 2

Infobright does not support functions. From the site: http://www.infobright.org/forums/viewthread/1871/#7485

Indeed, IB supports stored procedures, but does not support stored functions nor user defined functions.

select if(@rank is null,@rank:= 0,@rank:= @rank +1) as rank, id from accounts

Does not work, because you cannot write to @vars in queries.

This:

 SELECT 
   (SELECT COUNT(*) 
    FROM mytable t1 
    WHERE t1.rankedcolumn > t2.rankedcolumn) AS rank, 
 t2.rankedcolumn
 FROM mytable t2 WHERE ...;  

will work, but is very slow of course.

Disclaimer, not my code, but Jakub Wroblewski's (Infobright founder)

Hope this helps...