MySQL Bugs: #10153: Crystal Report for .Net 1.1 and MyODBC 3.51.11 (OuterJoin syntax problem)

Bug #10153	Crystal Report for .Net 1.1 and MyODBC 3.51.11 (OuterJoin syntax problem)
Submitted:	25 Apr 2005 18:05	Modified:	25 Oct 2005 15:11
Reporter:	Pascal	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.11	OS:	Windows (Windows XP)
Assigned to:	Peter Harvey	CPU Architecture:	Any

Description:
Hello,
my company is actually developing a product using the .Net framework 1.1 (with the SP1).
The product should work on multiple database like MySQL for example.
We are using Crystal Report and for better performance we have to use ODBC with it rather than a DataSet and the Mysql .Net connector (it is 10 times faster).

Our Reports contains OuterJoin syntax on multiple tables and when I execute the program, I get the error : "failed to open a rowset". And if I clic OK, the report is empty.
If I use an ODBC connection to SQLServer with the same .RPT, I don't get this error.

Here you can find another article (and a solution) for Crystal Report 9 and 8 :
http://bugs.mysql.com/bug.php?id=839
And here is the associated bug in the Crystal Decision kbase :
http://support.businessobjects.com/library/kbase/articles/c2007974.asp

But every registry keys I have tried have not worked with my version.

I have these 2 entries in my Registry for Crystal Report :
[HKEY_CURRENT_USER\Software\Seagate Software\Crystal Reports]
[HKEY_CURRENT_USER\Software\Crystal Decisions\Crystal Reports Designer Component\vsnet]

Here is my configuration :
- Windows XP (but it does not work with a Windows 2000 pro too)
- Visual Studio 2003 with the .Net framework 1.1 SP1 (french version)
- MySql ODBC 3.51.11 connector
- All the last Crystal Decision updates for the french version.

Thanks for every help.

How to repeat:
1) In Visual Studio 2003, create a new solution and a new WindowsForm project.
2) Add a new Crystal Report file (.RPT)
3) Make a test with multiple tables and with OuterJoin relations
4) In the Form1, add a CrystalReportViewer.
5) Add the assembly in the ToolBox if you don't see your CrystalReport.
6) Add your CrystalReport to the Form1 and set it as the ReportSource of the CrystalReportViewer.
7) Compile and run the application.

Suggested fix:
As explain in the above articles (and other ones), I have tried these entries :
[HKEY_CURRENT_USER\Software\Seagate Software\Crystal Reports\DatabaseOptions\OuterJoin]
[HKEY_CURRENT_USER\Software\Crystal Decisions\9.0\Crystal Reports\Database\QueryBuilder\OuterJoinEscSeq]
[HKEY_CURRENT_USER\Software\Crystal Decisions\9.0\Crystal Reports\Database\QueryBuilder\SQLServerJoinBuilder]
[HKEY_CURRENT_USER\Software\Crystal Decisions\9.0\Crystal Reports\Database\QueryBuilder\OracleJoinBuilder]

and for each entry, I have tried all of these values :
@="PlusEqual"
"PlusEqual"="MYODBC3"
"NoOuterJoinEscSeq"="MYODBC3"
"Informix"="MYODBC3"
"StarEqual"="MYODBC3"
"Sql2OuterJoin"="MYODBC3"
"OJSyntax"="MYODBC3"
"OpenIngres"="MYODBC3"
"SpecialDll"="MYODBC3"
"PlusJoinDBMS"="MYODBC3"
"StarJoinDBMS"="MYODBC3"
"Sql2JoinDBMS"="MYODBC3"
"AccessDriver"="MYODBC3"
"SimbaDriver"="MYODBC3"
"SQLServerJoinBuilder"="MYODBC3"
"OracleJoinBuilder"="MYODBC3"

None of these have worked.

Could you please provide a test case with tables dump (with data) and the .NET project ?

Thanks in advance.

Tables test schema and data

Attachment: crystal_bug.sql (text/plain), 751 bytes.

.Net test project

Attachment: MySQLBugReport_DotNet_Project.zip (application/x-zip-compressed, text), 14.45 KiB.

I have added 2 files : one with the tables schema and data, the other with the .Net project.
Thanks for help

Thank you for the feedback.
I was able for get the behavior reported, however I am not sure if that
can be considered a Connector/ODBC bug instead maybe an incompability
of the way which Crystal Report does the SQL query, looking the Show
SQL Query you can see:

SELECT `client`.`Id`, `client`.`Label`, `order2`.`Price`, `order2`.`LabelOrder`
 FROM   {oj `order2` `order2` RIGHT OUTER JOIN `client` `client` ON `order2`.`Fk_cli`=`client`.`Id`}
 ORDER BY `client`.`Id`;

the above FROM {oj...} can't be handled by the server.
I tested the same query with Access 2003 and created a report but
using a query like below:

SELECT `client`.`Id`, `client`.`Label`, `order2`.`Price`, `order2`.`LabelOrder`
 FROM   `order2` `order2` RIGHT OUTER JOIN `client` `client` ON `order2`.`Fk_cli`=`client`.`Id`
 ORDER BY `client`.`Id`;

I attached a picture to this bug report which shows the above.
Please verify if you can change the Report's SQL Query or consult
the support of Crystal Report for.

Thank you for the bug report.

Same report with Access

Attachment: report-access.PNG (image/png, text), 32.89 KiB.

Thanks for help. I am happy you have reproduce my problem.
As you said it works with Access, and as I said in the "Description" of the bug above, it works with an ODBC connection to SQLServer too.

I can't change the generated query (or I don't know how I can do that dynamically without the Designer) and I am already in relation with the Crystal Report support. We are working on it.
The only think I have to know and I have not found is the correct registry key to use for saying Crystal to not use this "oj" syntax. See the "Description" and the "Suggested fix" of the bug I have written above.
I have tried lot of keys, but if anyone can found the working key, I will be grateful forever.

I have the solution !
A lot of thanks to Crystal Decision support which has found the solution after several email exchange.

The key to use is :
[HKEY_CURRENT_USER\Software\Crystal Decisions\Crystal Reports\VSNET\DatabaseOptions\QueryBuilder\OuterJoinEscSeq]
"NoOuterJoinEscSeq"="MyODBC3"

Hope it can help other people in trouble like we were !

Noted - thanks for taking the time to provide us all with that solution.

This (and potentially other) issues with ODBC's Outer Join Escape Sequence have been fixed in Bug#28317.