| Database Property Binding [message #759859] |
Wed, 30 November 2011 06:15  |
Eclipse User |
|
|
|
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 13:58  |
| Eclipse User |
|
|
|
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 #763028 is a reply to message #760347] |
Thu, 08 December 2011 21:08 |
| Eclipse User |
|
|
|
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 #764900 is a reply to message #763069] |
Mon, 12 December 2011 22:55 |
| Eclipse User |
|
|
|
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
|
|
|
Powered by
FUDForum. Page generated in 0.03685 seconds
Home