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: | |
Category: | Connector / ODBC | Severity: | S2 (Serious) |
Version: | 5.1 | OS: | Windows |
Assigned to: | CPU Architecture: | Any |
[5 Feb 2009 15:46]
Marty Smith
[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.