Possilbe bug with SQLExecute/SQLRowCount and updating rows

Hi,

I'm using mysql-connector-odbc-3.51.12 and MySQL 5.0.15.

I think this is a possible bug:

I have an update which returns success but SQLRowCount returns 0 rows affected.
I actually found it in Perl using DBI 1.50 and DBD::ODBC but have reproduced
with unixODBC's isql and also directly with ODBC calls from C.

It is easily reproduced with:

create table test (a int, b char(100))
insert into test values (1, 'B')
# SQLExecute returns success
# SQLRowCount = 1
update test set b = 'B' where a = 1
# SQLExecute returns success
# SQLRowCount returns 0
update test set b = 'B' where a = 99999999
# SQLExecute returns success
# SQLRowCount returns 0

As you can see, there is no way to differentiate between an update on a
non-existant row and an update on an existant row with no changes. Even though
the first update does not actually change any of the columns in the row it
should return SQLRowCount = 1.

Also, the normal way to indicate the row selected in the update did not exist
is to return SQL_NO_DATA_FOUND.

I've tried this on MS SQL Server and Oracle and they work as per my reading of
the ODBC spec.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: Possilbe bug with SQLExecute/SQLRowCount and updating rows

Sorry, I forgot to mention I know all about option = 2 described as:

"The client can't handle that MySQL returns the true value of affected rows. If
this flag is set, MySQL returns "found rows" instead. You must have MySQL
3.21.14 or newer to get this to work."

but although it helps a little it does not fix the problem with updates with a
non-existant key.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com


On 03-Feb-2006 Martin J. Evans wrote:
> Hi,
>
> I'm using mysql-connector-odbc-3.51.12 and MySQL 5.0.15.
>
> I think this is a possible bug:
>
> I have an update which returns success but SQLRowCount returns 0 rows
> affected.
> I actually found it in Perl using DBI 1.50 and DBD::ODBC but have reproduced
> with unixODBC's isql and also directly with ODBC calls from C.
>
> It is easily reproduced with:
>
> create table test (a int, b char(100))
> insert into test values (1, 'B')
># SQLExecute returns success
># SQLRowCount = 1
> update test set b = 'B' where a = 1
># SQLExecute returns success
># SQLRowCount returns 0
> update test set b = 'B' where a = 99999999
># SQLExecute returns success
># SQLRowCount returns 0
>
> As you can see, there is no way to differentiate between an update on a
> non-existant row and an update on an existant row with no changes. Even
> though
> the first update does not actually change any of the columns in the row it
> should return SQLRowCount = 1.
>
> Also, the normal way to indicate the row selected in the update did not exist
> is to return SQL_NO_DATA_FOUND.
>
> I've tried this on MS SQL Server and Oracle and they work as per my reading
> of
> the ODBC spec.
>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> http://www.easysoft.com
>
>
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe:
> http://lists.mysql.com/myodbc?unsub=martin.evans@easysoft.co m

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org