I have an issue with V8.3 reporting. Have an existing application that uses MySQL as the database and customer wants to standardize on Microsoft SQL Server. All queries worked using MySQL as the database source, but I have one nested query that does not work using SQL server as the database source.
This nested query works. The datatable to query is based on the value {tank} returned by the parent query . Datatables are Fermtank1, Fermtank2, and Fermtank3. Parameter 1 is used to determine which datatable to query
This is the result of the child query in the report Preview Mode
When I convert the child query to use the MSSQL datatables,
An error occurs and the child query fails.
The connection SQL1 is valid.
This is the parent Query
tank is the parameter used in the child query. No issues if child query is MySQL, only an issue if SQL
Hmm OK. Not all syntax is cross compatible between MySQL and MS SQL, but nothing you’ve shown so far is jumping out at me.
Have you tried running the parent query in SSMS with the relevant parameters hard coded in for a test?
I have come to the conclusion that parent query works, as the value in {tank} is valid when the child query runs using a MySQL database connection.
It seems that the parameterized ( FROM fermtank? ) does not evaluate properly when using a SQL database connection as per the error code “Invalid object name ‘fermtank@P0’. If I hard code the from clause as (FROM fermtank2), the child query works using SQL database connection.
Is this a typo? Parent might work on MySql but might not on MS SQL.
MySQL is breaking the rules for value parameters. They are not supposed to work for SQL query structure, like table names, column names, or other keywords, as that is a security flaw.
In named queries, you would use curly braces with a query string parameter for such things. I don't know if what you are trying is possible (mixing curly brace params with question-mark value params).
1 Like
I think it's possible if you use the "Basic SQL Query" style for the data source, instead of the "standard" SQL query.
Genius!
Query now works, thanks !
