0

My application uses an xml datasource file for accessing the DB..There are number of schemas which are present inside the DB and I have no problem accessing any of the schemas.

However we have started using BIRT for reporting and charts generation and as such I am facing a very typical problem.

In BIRT I need to create a datasource file.Problem is it accepts jdbc:connection url where I supply the url of the DB..However along with the URL it also ask me to specify the schema else wise BIRT shows no value in data set. I can easily specify one schema name however my query uses two schema and hence I am unable to find an alternative for the same...Creating a joint data set is an option but my queries are complex and generally it is not a viable option for me...

The connection URL which I specify is :-

jdbc:mysql://IP:port/schemaName?someString

How can I specify more than 2 schema in my jdbc connection URL..???How can I connect to two schema inside a single datasource in BIRT..

AngelsandDemons
  • 2,823
  • 13
  • 47
  • 70

1 Answers1

3

You are addressing this issue from the wrong side. MySQL allows to join tables from different schema. You should just let it know at what schema to look for which table. You can do it by prepending schema name and '.' before each table name in your query, for example:

SELECT db1.products.prod_id, db2.products.upc_code FROM db1.products LEFT JOIN db2.products ON (db1.products.prod_id=db2.products.prod_id)

In that case you would need to specify the name of either schema in your connection URL.

Olaf
  • 6,249
  • 1
  • 19
  • 37
  • 2
    +1 It may be worth mentioning that whichever schema is accessed via the connection URL will need read privileges for the other schemas. –  Feb 08 '12 at 08:50