MYSQL vs. SQLite update inconsistency

[Ralph DiMola]

I ran into a this while trying to sync up 2 DBs. DB 1 is a MySql DB the
other is a SQLite DB. The number of rows reported to be updated in MySql is
different than SQLite. The SQLite update returned 1 row being updated but
the MySql returned 0. After some head scratching I found out the field in
the row in both DBs was being updated to the same value. As it turns out
MySql has a flag==> CLIENT_FOUND_ROWS in the connection string. This flag
when specified makes the affected-rows returned the number of rows "found";
that is, matched by the WHERE clause. It seems the LC is not setting this
flag. As such updates to the same value in MySql returned 0 rows updated.
This is not a bug per say but creates a procedural inconsistency between
MySql and SQLite DBs. The workaround is to do a "SELECT Count(*) FROM xxx
WHERE where_cond" before every update and use this to find out how many rows
are being "updated".


Ralph DiMola
IT Director
Evergreen Information Services
rdimola at evergreeninfo.net