Possible to set SQL system variables in iReport? Or use SQL variables in report query?

[lisaws]

I want to increase the max length of the group_concat function for my report.  Normally (not in Jasper) I would do the following:

SET SESSION group_concat_max_len = 10000;[/code]

But if I add that to the beginning of my Jasper report query, I get an error when running the report ("An error has occurred...").  Is there any way to set SQL system variable such as this in Jasper iReport?

I am using Jasper iReport 5.2.0 and MySQL db.  Thanks.

 

EDIT: also, I've tried a workaround to avoid using group_concat which uses user-defined variables, for example:

SELECT resultFROM   (SELECT @result := '',               (SELECT result                FROM   (SELECT @result := CONCAT_WS(';', @result, field2) AS result,                               LENGTH(@result)                            AS blength                        FROM   some_table                        ORDER  BY blength DESC                        LIMIT  1) AS sub1) AS result) AS sub2; [/code]

But I am getting the same error, " An error has occurred. Please contact your system administrator. (6632)".  I am guessing this is because Jasper doesn't allow SQL variables?

[reportdev]

If you would have been using Oracle, I would have suggested going the route of using plsql blocks. 

[hozawa]

Unfortunately, you'll need to set in the mysql config file instead of as a session.