When using TracerPlus Connect to make an ODBC connection to a MySQL Database/Table, you may experience unexpected data row INSERTS when using the UpdateExistingAppendIfNotFound update option.
When using this update option with a MySQL database, a new row may be added when not expected since it already exists based on the defined Update Based On definition in TracerPlus Connect.
If the row data has not been changed prior to the update to MySQL, MySQL returns an error indicating the row is not found. TracerPlus Connect treats this the same as the row not existing (since it is the same error generated by MySQL) and proceeds to insert the row as NEW (Append if not found).
To fix this, you can configure your MySQL ODBC driver properties to "Return matched rows instead of affected rows". This is a checkbox option under the "Cursors/Results" tab exposed by clicking the of the Details button when editing your driver in the PC ODBC Data Source Administrator tool.
Setting this option ON allows MySQL to correctly report the existence of the row in question even though the data has not changed.
Note: This setting may be located under a different tab depending on the version of the MySQL ODBC driver being used. In some cases, we have seen this appear under a Flags1 tab.
Article is closed for comments.