Use Mysql + MongoDB in a single report

[vivek.agarwal]

Hi,

We have to generate a query including data from both mysql and mongodb. 

The result of query executed in mongodb will contains "city id".  We need to display city name instead of 'city id'

This city id has a reference to master table which is in mysql. The master table has mapping of 'city id' & 'city name'.

Please can you help me how to resolve this kind of scenarios.

[calculate.machine]

Joins across MongoDB and MySQL can be realized with virtual data source. But it is only available in commercial or high-end versions and has limited ability by supporting the combination of only two data sources.

 

You can try using esProc to help Jasper in handling this kind of join. Suppose to query records of collection emp1 in MongoDB and to switch its CityID field to CityName field of table cities in MySQL. You can do it with the following esProc code:

A1=MongoDB("mongo://localhost:27017/test?user=root&password=sa")

A2==A1.find("emp1","{'$and':[{'Birthday':{'$gte':'"+string(begin)+"'}},{'Birthday':{'$lte':'"+string(end)+"'}}]}","{_id:0}").fetch()

A3=A1.close()

A4= myDB1.query("select * from cities")

A5= A2.switch(CityID,A4)

A6=A5.new(EID,Dept,CityID.CityName:CityName,Name,Gender)

 

Jasper can connect to esProc using JDBC. The way it calls a script file is the same as that it calls the stored procedure. For more information please see http://blog.raqsoft.com/?p=2460