Bug #42623 SQL Syntax error occurs when using SOUNDEX functionality.
Submitted: 5 Feb 2009 15:46 Modified: 6 Feb 2009 7:43
Reporter: Marty Smith Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1 OS:Windows
Assigned to: CPU Architecture:Any

[5 Feb 2009 15:46] Marty Smith
Description:
A generic sql syntax error occurs when trying to insert records containing strings returned from the SOUNDEX function.  this only pertains to strings passed to the SOUNDEX function containing 3 characters or less, ie.  USA or ATT.

AT&T works just fine.

How to repeat:
For example,

mysql> select soundex('USA');
+----------------+
| soundex('USA') |
+----------------+
| U200           | 
+----------------+
1 row in set (0.00 

If you were to then use that returned value within a dynamic variable in an INSERT statement that produces this insert statement below;

INSERT INTO TBL1 (SoundKeyFirst,SoundKeyLast) VALUES('', 'U200');

MySQL comes back with an error;

[MySQL][ODBC 5.1 Driver][mysqld-5.0.45]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''U20' at line 1
[5 Feb 2009 16:55] Marty Smith
Turns out that MySQL is returning a line feed character at the end of the results of which MySQL has issues with during the insert.
[5 Feb 2009 17:09] Tonci Grgin
Hi Marty. I don't get it, is thing working now or not? What is the exact table structure you're using to test?
[5 Feb 2009 18:07] Marty Smith
Tonci,

Here is a stripped down VB6 code snippet.

soundstr = "USA"
soundsql = "SELECT SOUNDEX('" & soundstr & "') AS S"
    
'open recordsets to call mysql soundex function
Set soundTable = New ADODB.Recordset
soundTable.Open soundsql, DSNConnection_Global

'get the soundkeys from the recordsets so we can insert into the database
s1 = soundTable("S")

'close the recordsets
soundTable.Close

'now build the sql string to insert the row into the database

sqlstr = "INSERT INTO TBL1 (Col1) VALUES ('" & s1 & "')"

'Exec sqlstr

TBL1 is InnoDB and Col1 is a varchar.

Once the code is executed, the error is generated.  As stated earlier, ODBC 5.1 was returning U20 and a line feed character after the 0 instead of a second 0.  Works fine in the command line.

We were able to workaround the issue by taking the driver out of the equation.

sqlstr = "INSERT INTO TBL1 (Col1) VALUES ((SELECT SOUNDEX('" & soundstr & "')))"
[6 Feb 2009 7:43] Tonci Grgin
Hi Marty and thanks for explanation.

Before I make my ruling let me glance over test case (which bears no importance on ruling though). You are using field after the resultset is closed. Thus you need one ".Value" to make the s1 assignment work at all.
Now, as described in http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_soundex and related bug reports, you are bound to hit a problem using SOUNDEX with MB c-sets:
  "This function is not guaranteed to provide consistent results with strings that use multi-byte character sets, including utf-8."

We were unable to confirm NL as problematic character here, but it's rather some random bytes coming from server. You can check this by printing query log or the value of s1 after closing the dataset. Also, we are looking into "wrong" metadata returned from 5.X server for "select soundex('USA');":
  Length:     9 <<< wrong
  Max_length: 4
when using MB c-set like UTF-8.
Cause of this metadata c/ODBC effectively truncates your value (start mysql cl client with -T, for 5.0, or with --column-type-info for 5.1 server).

So, we have an error in your test case (which could just be a typo) and c/ODBC truncating the random bytes from server, all related to MB charactersets used with SOUNDEX which is a documented problem.
AFAIS, the only way to deal with this is a workaround you described:
  sqlstr = "INSERT INTO TBL1 (Col1) VALUES ((SELECT SOUNDEX('" & soundstr & "')))"
so please proceed like that.

Closing as "Won't fix" as c/ODBC will not change it's behavior to work around documented server problem and server-related problem is already described in BugsDB and in manual.

Thanks again for bringing this to BugsDB, you might help many others with similar problem.