How to set MySQL variable value for multiple data sets

I want to set a SQL variable before the data set query executes. I tried putting it on the database URL with in the data sources definition (jdbc:mysql://DevARDServer:3306/vid_reporting?sessionVariables=@User_UUID=';e5ce2cf6-4cf3-11e1-bccd-00155d006a32') and it works just fine for the first data set select. However, it does not apply it to the other data sets. The report has 3 data sets to get the data for the report. The first data set retreives the data properly but the other 2 do not.

I want to have a MySQL " set @User_UUID=';e5ce2cf6-4cf3-11e1-bccd-00155d006a32'; " command execute before each data set select as the view has the @User_UUID variable in the where clause.

Can you tell me how to have this session variable applied to the concurrent sessions created by the report generator so that each data set has the proper selection criteria.

Thank you for your assistance.

Find more posts tagged with

Comments

mwilliams

You're passing this value through a report parameter and using it in your queries, but it only works in the first query? Am I getting this right? Are you getting any errors at all?

SerLop

The value is passed by a report parameter but it is not used in the actual select statement within BIRT but in the underlying database view being used by the query. For example:

the report contains:
-- a report parameter to receive the ID

-- the report queries are like: select fields.. from <database_view>

The MySQL view is:

Select fields...
from table_name
where table_User_ID = @User_UUID()
--

@User_UUID is a function in MySQL that returns the session variable @User_UUID

--
That is why I need to be able to set the session variable for the multiple data sets since BIRT appears to run them concurrently as individual sessions.

There are no errors since everything runs fine. The sql variable is being set at login time but only the first data set is the only one getting data.

When I look in MySQL I see multiple sessions being executed and I'm assuming that the variable is not being set for the other data sets.

I hope this helps to clarify.

mwilliams

So, you can't use this variable that you pass in through the URL to modify your query in the beforeOpen script of each dataSet or by linking your report parameter to a dataSet parameter because you're using the database view? Is the view really this simple that you could just avoid the view and pass the value through each query?