MySQL Bugs: #839: Problem using Crystal Reports on RT database using ODBC klink

Bug #839	Problem using Crystal Reports on RT database using ODBC klink
Submitted:	14 Jul 2003 2:17	Modified:	19 Oct 2007 14:26
Reporter:	Henry Jones	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (windows)
Assigned to:		CPU Architecture:	Any

Description:
I am having problems using Crystal Reports to report on the RT (Request Tracker) database which is a MySQL Database.

I am using 	Crystal Reports 8.0.1.0
		RT Version 1.0.6
		MySQL ODBC Driver 3.51
		MySQL 3.23.54

We have some reports which have been running for some time on a machine using a User DSN under Windows 2000.
I now want to run them on my machine (Windows XP) but I get an error
 
I have tried using both User and File DSNs but always get the same result. I have tried on  Windows 2000 laptop similar to the one it used to be run on with the same result. I have also tried it on a Windows NT machine. Always I get the same result.

Another colleagues working on the same NT machine with an identical User DSN can get it to run. He can even run it using a file DSN.
He has tried on my machine but it gives the same error message.

I have looked at the SQL Code after it fails for me and when it works and it is different. The good code is
 
The bad code is
 
I also enclose a copy of the report in case that helps.
 

Henry Jones
IS Manager
Dynex Semiconductor Ltd
01522 502729

How to repeat:
I have tried running the report on a number of machines and most repeat the problem see notes above

Error message

Attachment: error mess.jpg (image/pjpeg, text), 26.05 KiB.

good sql

Attachment: sql.txt (text/plain), 481 bytes.

bad sql

Attachment: badsql.txt (text/plain), 495 bytes.

report

Attachment: Time to fix date selection.rpt (application/octet-stream, text), 21.54 KiB.

Hi !!

This is a well known issue from Crystal which uses OJ/INNER JOIN syntax that MySQL doesn't support.

The hack for Crystal not to use OJ/Inner Join syntax is to try adding the following key to you registry.  It tells Crystal Reports which outer join syntax your database uses.

[HKEY_CURRENT_USER\Software\Crystal Decisions\9.0\Crystal
Reports\Database\QueryBuilder\OuterJoinEscSeq]
"NoOuterJoinEscSeq"="MyODBC3"

Let me know if that resolves your issue.

Thanks
Venu

>On Fri, 2003-07-18 at 08:10, Henry Jones wrote:
Hi,
> 
> I don't appear to have this area of the registry. I think you have probably
> supplied the fix for Version 9 of Crystal and I am still using Version 8. Do
> you have a fix for version 8?
> 
> Thanks for your help so far.
> 

Hi !! 

First, please DO NOT reply to the mail that you get to your inbox, instead always use the web-interface.

Second, for Crystal Reports 8, please check the following KB article on how to add the registry key:

http://support.crystaldecisions.com/library/kbase/articles/c2007974.asp

And odbcregs.zip can be found from:
http://support.crystaldecisions.com/communityCS/FilesAndUpdates/odbcregs.zip.asp

Please follow the instructions from readme.rtf from the same zip file.

Note: The registry key for 8 is :
[HKEY_CURRENT_USER\Software\Seagate Software\Crystal Reports\DatabaseOptions\OuterJoin]

Thanks
Venu

Hi Venu,
we followed the instructions from the Crystal Knowledge base and added the Outer Join registry key. We were then unsure what to do so we tried a few things.
Firstly we tried the NoOuterJoinEscSeq as per you previous email but that didn't work.
Finally we tried the PlusEqual key and entering MyOBBC3 in that and that appears to have worked. Does this sound okay to you?

Thanks for your help

Henry

Yes, that should be ok. They are all made due to incompatibility issues among different DB's from Crystal.

Thanks
Venu

In a side note, I just sent an e-mail to Crystal team to update the informationa about MySQL related keys from readme.rtf, so that it helps all MySQL users.

Thanks
Venu

Hi,

I experienced the same problem. I am using Crystal 8.5 and MySql-myodbc3. I added a registry entry with PlusEqual=myodbc3. It fixed the error messages, however the report does not have outerjoin statements. It changes the select statement as if the tables are inner joins.

I have strated thinking I might be using close but not the right register entry (PlusEqual). Please advise me if anybody has information about this.

Thanks

You need to contact Crystal Decisions support inorder to find work around for MySQL outer joins.

I need a little help here. I have V9 and downloaded the compatability rules. I am using MySQL v4.0.20a.

The Database Key in my registry did not include QueryBuilder so I added it. Within this key I added OuterJoinEscSeq. In this last key I added the String Value
NoOuterJoinEscSeq to the value MyODBC3

I started the app and found it still created the OJ syntax that provided a MySQL error.

Following the thread I created the Registry text file following the instructions using the report
How to resolve the join syntax error in Crystal Reports 9
I used the syntax
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Crystal Decisions\9.0\Crystal Reports\Database\QueryBuilder\JoinBuilder]
PlusEqual
"OracleJoinBuilder"="myodbc3"

This added the Key JoinBuilder and the stringvalue OracleJoinBuilder to myodbc3. I cannot find any entry in the registry for "PlusEqual" other than the reference in the "Seagate Software" section.

Can someone tell me what I'm missing

Hello...

I am using 
                 1.  MySql 3.23
                 2.  Crystal Reports 8.0.1.0 
                 3.  MySQL ODBC 3.51 Driver

I am getting the error listed below. please help me....

ODBC error: [MySQL][ODBC 3.51 Driver][mysqld-3.23.49-nt]You hve an error in your SQL syntax near INNER JOIN INV_DB.site site1 ON tooleqpt.tlsEqptSite=site1.id} at line 1

I am using crystal 8.5 and added "myodbc3" to the front of the "PlusEqual" registry value:

[HKEY_CURRENT_USER\Software\Seagate Software\Crystal Reports\DatabaseOptions\OuterJoin]
"PlusEqual"="myodbc3, crora07,cror707,cror709,cror711, cror712, crxql07,  vsorac32, Relativity, crgup07, qeor706, crgup09, crgup11, crgup12, sqora_73, sqo32_73, oplodbc, w32btint, ivor709, lldriver, dhodbc, dhsodbc, polite, sqrdb, sqrdb32, ivor711, qmss04, sqora73,sqora7, sqora71, sqora, sqo32_72, ivor712, sgor712, sqora73n"

Unfortunately, it didn't work for me as it did the other crystal 8.5 user.  I am using version 3.51.12.00 of the MySQL ODBC Driver.

Hi,

Here is a fix which worked for me on the following platform:
Crystal Reports 8.0.1.0
MySQL ODBC 3.51.11.00

I didn't have a key called 'OuterJoin' at all so I had to add that and then just one String value pair ("PlusEqual"="myodbc3").  Below is the exported key.  Restart Crystal and it seems to work !

[HKEY_CURRENT_USER\Software\Seagate Software\Crystal Reports\DatabaseOptions\OuterJoin]
"PlusEqual"="myodbc3"

Chris Pendse

The registry update solution has solved our problem.  But I am running into problems using the solution on a computer that has multiple users.  I want to make the update to HKEY_LOCAL_MACHINE, but there is not a Seagate entry under software.  We are running Crystal 8.5 on Windows 2000.

Hi Sally,

many thanks for your comment.
I'll re-open this bug, so that we can re-verify the issue.

Regards,

Susanne

Sally, either you installed CR only for current user (please reinstall and check "for everybody using this computer") or CR already changed hands so you should look for HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects.

Thanks for your interest in MySQL.

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

Registry problems will grow, however, due to fact that on 64bit windows all DSNs are kept together in registry so one might not be able to tell MyODBCx64 and MyODBCx32 DSNs apart...

That is not the case at all! Only the machine that the report is developed on needs the registry fix.  Then it is only a matter of "Verify Report" to correct generated SQL that is pernamently stored in the .RPT and will not change no matter what user machines have in their Crystal hive.

I there i'm from Mexico:

I'm using MySQL 5.0.83
          CR 8.0.1.0
          VB 6.0

I have the same problem and after read all articles that are exposed in this page i have reached the exit about this. All i had to do is to add the follow lines to the registry:

[HKEY_CURRENT_USER\Software\Seagate Software\Crystal Reports\DatabaseOptions\OuterJoin]
"PlusEqual"="myodbc5"

I just have to say THANK YOU VERY MUCH, FOR EVERY ONE!

And if I coul help someone i will do so!!!