Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Database Property Binding(Property binding is not overriding Dataset database)
Database Property Binding [message #759859] Wed, 30 November 2011 11:15 Go to next message
karvesh ghunsam is currently offline karvesh ghunsamFriend
Messages: 95
Registered: July 2011
Member
Dear all,

I have created a datasource using JDBC Database connection for Query Builder.
I set to default the JDBC driver and connection to the default database, user and password. The URL is for instance: jdbc:mysql://localhost:3306/dbmedisave. Fine.

I am using two databases, one for live and one for training. The live database name is dbMedisave and the test one is dbMedisaveTrain.

I am trying the change the database using property binding. I set the the database to say params["paramDBase"].value, that i set to the required database name when calling the report.

However when i run the report, the data is that from the first database used when creating the report, that is dbMedisave. I noted that the database parameter is being well passed on. The issue is that i created the dataset in design time using the sql query designer. In the Sql Select Query window, the sql syntax is:

SELECT `dbmedisave`.`tbldoctoracc`.`VisitFK`, `dbmedisave`.`tbldoctoracc`.`PatientFK`,
`dbmedisave`.`tbldoctoracc`.`DoctorFK`, CONCAT(`dbmedisave`.`tbldoctors`.`Surname`, ' ', `dbmedisave`.`tbldoctors`.`GivenName`) AS DOCNAME,
`dbmedisave`.`tbldoctoracc`.`PayerFK`
FROM
`dbmedisave`.`tbldoctoracc`


the database name is hard coded in the query. So i deleted the `dbmedisave`.perfix in the query and clicked OK. However, i get the error:

Unale to find table 'tblDoctors..'. It might not exist or it might be filtered out. Some SQL Builder Functions have been disabled. To enable these functions, change the syntax and save the function.

In other words, i cannot do much

Can anyone help me how to solve this issue?

Thanks in advance. Its just that i donot want to create a report for each database. It here a better way out?

Karvesh
Re: Database Property Binding [message #759976 is a reply to message #759859] Wed, 30 November 2011 18:58 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Does the query work if you entry

Select VisitFK from tbldoctoracc

Jason

On 11/30/2011 6:15 AM, karvesh wrote:
> Dear all,
>
> I have created a datasource using JDBC Database connection for Query
> Builder.
> I set to default the JDBC driver and connection to the default database,
> user and password. The URL is for instance:
> jdbc:mysql://localhost:3306/dbmedisave. Fine.
>
> I am using two databases, one for live and one for training. The live
> database name is dbMedisave and the test one is dbMedisaveTrain.
>
> I am trying the change the database using property binding. I set the
> the database to say params["paramDBase"].value, that i set to the
> required database name when calling the report.
>
> However when i run the report, the data is that from the first database
> used when creating the report, that is dbMedisave. I noted that the
> database parameter is being well passed on. The issue is that i created
> the dataset in design time using the sql query designer. In the Sql
> Select Query window, the sql syntax is:
>
> SELECT `dbmedisave`.`tbldoctoracc`.`VisitFK`,
> `dbmedisave`.`tbldoctoracc`.`PatientFK`,
> `dbmedisave`.`tbldoctoracc`.`DoctorFK`,
> CONCAT(`dbmedisave`.`tbldoctors`.`Surname`, ' ',
> `dbmedisave`.`tbldoctors`.`GivenName`) AS DOCNAME,
> `dbmedisave`.`tbldoctoracc`.`PayerFK`
> FROM
> `dbmedisave`.`tbldoctoracc`
>
> the database name is hard coded in the query. So i deleted the
> `dbmedisave`.perfix in the query and clicked OK. However, i get the error:
>
> Unale to find table 'tblDoctors..'. It might not exist or it might be
> filtered out. Some SQL Builder Functions have been disabled. To enable
> these functions, change the syntax and save the function.
>
> In other words, i cannot do much
>
> Can anyone help me how to solve this issue?
>
> Thanks in advance. Its just that i donot want to create a report for
> each database. It here a better way out?
>
> Karvesh
Re: Database Property Binding [message #760033 is a reply to message #759976] Thu, 01 December 2011 02:53 Go to previous messageGo to next message
karvesh ghunsam is currently offline karvesh ghunsamFriend
Messages: 95
Registered: July 2011
Member
Dear Jason

Its to no avail. Even if the query is as simple as:

SELECT * FROM myTable


without any database prefix, its not working and it still flags the error:

unable to find table...

Do you think that i will have to write some javascript code in the script method of the databaset at beforeOpen?
I mean, i could replace the live database prefix by the one passed in from the parameter variable.

But it does not sound clean enough.. There must be a better way

Thanks

Karvesh
Re: Database Property Binding [message #760186 is a reply to message #760033] Thu, 01 December 2011 14:58 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

You could do a string replace on the dbname in the beforeOpen script,
but I will check with the dtp team for other options.

Jason

On 11/30/2011 9:53 PM, karvesh wrote:
> Dear Jason
>
> Its to no avail. Even if the query is as simple as:
>
> SELECT * FROM myTable
>
> without any database prefix, its not working and it still flags the error:
>
> unable to find table...
>
> Do you think that i will have to write some javascript code in the
> script method of the databaset at beforeOpen?
> I mean, i could replace the live database prefix by the one passed in
> from the parameter variable.
>
> But it does not sound clean enough.. There must be a better way
>
> Thanks
>
> Karvesh
Re: Database Property Binding [message #760347 is a reply to message #760186] Fri, 02 December 2011 02:56 Go to previous messageGo to next message
karvesh ghunsam is currently offline karvesh ghunsamFriend
Messages: 95
Registered: July 2011
Member
Dear Jason

Thanks for the reply. I did use the replace for the moment and it is working fine. I had to get it running for this weekend Smile

Nonetheless, please do keep me updated if you find any better way around..

Thanks a lot
Karvesh
Re: Database Property Binding [message #763028 is a reply to message #760347] Fri, 09 December 2011 02:08 Go to previous messageGo to next message
Linda ChanFriend
Messages: 845
Registered: July 2009
Senior Member
Karvesh,

I tried to omit the database name in the SQB query, and the query runs fine for me. It was tested with a MySQL DBMS connection.

BTW, the proper way to exclude the db name in the generated query is to select "Omit Current Schema..." in the context menu on the query text pane (in SQB). And in the "Omit Current Schema" dialog, select the Omit checkbox, and specify the "Schema name", e.g. dbMedisave in your case.
The generated SQL query will then exclude the db name in the table and column identifiers.

Also, when you switch to a different database, make sure the correct db name is set in both related connection properties, i.e. "Database" and "URL".

Linda
Re: Database Property Binding [message #763069 is a reply to message #763028] Fri, 09 December 2011 05:07 Go to previous messageGo to next message
karvesh ghunsam is currently offline karvesh ghunsamFriend
Messages: 95
Registered: July 2011
Member
Dear Linda

thanks for the reply. The Omit Schema works fine indeed. Now it seems much cleaner..

Thanks a lot Smile

Karvesh
Re: Database Property Binding [message #764900 is a reply to message #763069] Tue, 13 December 2011 03:55 Go to previous message
karvesh ghunsam is currently offline karvesh ghunsamFriend
Messages: 95
Registered: July 2011
Member
I also realised why I had a database prefix. Its because i was using data source for Query Builder.

When i had to use direct connection to get it working with phpjavabridge and Birt 3.7,
the database prefix is no longer applied. So the applying the parameter database name from the property binding URL worked even better

I would never have realised that using different types of connections could cause so much havoc

Karvesh
Previous Topic:running BIRT viewer within my application
Next Topic:Missing hyperlink when report loaded from hyperlink
Goto Forum:
  


Current Time: Thu Dec 18 21:08:02 GMT 2014

Powered by FUDForum. Page generated in 0.01942 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software