Solved

Strange ODBC error encounter if my database has a 'dash' in the name

Posted on 2014-04-14
14
1,023 Views
Last Modified: 2014-04-20
Hi Team, I am using Crystal Reports to connect to a mysql backend server via ODBC.  For both windows 7 and Windows XP with ODBC versions 5.1, 5.2 and 5.3, I notice that if I try to connect to a database where the name has a dash, in my case it was called dbmaster-test, I would *ALWAYS* get the error message below, as though it is interpreting the dash as a minus sign.  But if I run the same command with a name with just dbmaster, everything works fine.   I am able to repeat the behavior with different versions of ODBC and for both XP and Windows 7.   Unfortunately, I can not change my dbname from dbmaster-test to dbmaster, as I have numerous external apps that refernce that name on my web site.
Would anyone know how I can remedy this?  Maybe I can hack into the odbc config somewhere and harcode some ' or "" or back apostropheinto the database name, so it won't treat the dash as some kind of minus sign?

Thanks very much.

Database Connector Error: '42000:[MySQL][ODBC 5.1 Driver][mysqld-5.0.77]You have an error in your SQL syntax; check the manual theat corresponds to your MySQL server version for the right tyntax to user near '-test.Cert_Inventory_Master_List where 0=1' at line 1 [Database Vendor code: 1064]'

Open in new window

0
Comment
Question by:rleyba828
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 100 total points
ID: 39999236
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39999402
What version of Crystal?

Are you trying to create a command or just use tables?

WHen do you get the error?

mlmcc
0
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 150 total points
ID: 39999554
Try using back-ticks like ` to quote the database name.  Back-ticks are used in MySQL as identifier quotes.

`dbmaster-test.Cert_Inventory_Master_List`
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:rleyba828
ID: 40001492
Hi Team,

1. For Dan, it DOES look like this bug --> http://bugs.mysql.com/bug.php?id=36241
2. For mlmcc,   I want to qualify what I mentioned yesterday,  after further investigation,  I checked all the staff in my team who use Crystal Reports (we are using XI),  and it is actually a 3rd party product called Visual Cut (http://www.milletsoftware.com/Visual_CUT.htm) that seems to be susceptible to this anomaly.   For all my other reports using Visual cut, it works perfectly as long as the dbase I am connecting to via ODBC doesn't have this dash (-) in its name.

As for Crystal Reports using the same ODBC connections as the Visual Cut software, it always works whether I am connecting to some backend database with a "dash" in it's name or any other name.

3. For Dave, where in ODBC or Crystal can I actually specify the backtick.  When I tried to put in the ODBC (when creating a User DSN), and I put the name `dbmaster-test`, then i click TEST, it doesn't like the backticks.   The TEST DOES succeed though, without the backticks.

*I'll contact the vendor/developer of Visual cut to see if this is a known bug, but I am sure it is just a quirk in ODBC, that's why I am curious on how to trick ODBC into adding those backticks.

Thanks.
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 40001595
Please contact the developer of Visual CUT directly for support.

While this is being chased down, nothing should stop you from adding another ODBC DSN that doesn't use a dash.  This should have no impact on the other applications that use the DSN with a dash.
0
 
LVL 23

Assisted Solution

by:Ido Millet
Ido Millet earned 150 total points
ID: 40001711
Based on many mentions of this problem on the web, I believe you need to try Dave Baldwin's suggestion ("back-ticks like ` to quote the database name"). Here is another thread discussing this bug and solution:
http://bugs.mysql.com/bug.php?id=461
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 100 total points
ID: 40001801
Is it the database name that has the HYPHEN or is it the ODBC name?

If it is the database name then you can't just create another DSN.

mlmcc
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 40001809
mlmcc, you are correct.  The situation indeed involves the database name.
0
 

Author Comment

by:rleyba828
ID: 40001875
Hi,

  I have tried both windows 7 64 bit and windows XP 32 bit and with mysql ODBC drivers 5.1.8, 5.2.6 and 5.3.2 and the error is consistent.   Anything with a dash is being rejected.

By any chance, does anyone know of ANOTHER mysql ODBC driver that I can try?  Something not created by Oracle or the original mysql company?
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 40001885
Did you try the suggestion above by Dave Baldwin?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40002362
I just tried MySQL ODBC versions 5.1 and 3.51 with a database named 'fire-test' using PHP 5.2.17 on Windows XP  and they both worked fine.  Both worked fine with Classic ASP also.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40002378
Just a weird thought.  Microsoft Word often substitutes a long-dash for a hyphen which would not match a regular hyphen in a name.  Anyway to check to make sure that's not happening?
0
 

Author Comment

by:rleyba828
ID: 40011301
Hi Team, unfortunately, I ran out of time and can no longer troubleshoot this one, as I had a deadline to meet.  As a workaround, I just created a cron job that would regularly copy all my tables from dbmaster-test  database to dbmaster.    This way, all my external apps still accesses the dbmaster-test database, whereas my Visual Cut software would access the dbmaster "replica".

Thanks to all for your help.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
migrating to phpbb forum from vBulletin 4.2 3 95
Additional letters added to Fieldnames - 4 29
restriction of entering a a page 5 44
Not listening to where 1 42
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question