| Database Property Binding [message #759859] |
Wed, 30 November 2011 11:15  |
 karvesh ghunsam
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  |
|
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 #760186 is a reply to message #760033] |
Thu, 01 December 2011 14:58 |
|
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 #763028 is a reply to message #760347] |
Fri, 09 December 2011 02:08 |
Linda Chan
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
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.01925 seconds
] 
Search
Help
Register
Login
Home
