Description:
When performing an UPDATE that changes no rows, the CLI reports "0 rows affected". When performing the same non-modifying UPDATE clause through ADO using Connector/ODBC, we see that rows affected is 1.
Tested against the following combinations:
Script (ADO)->ODBC 3.51.11->MySQL(win) 4.1.11->MyISAM
Script (ADO)->ODBC 3.51.11->MySQL(win) 4.1.11->InnoDB
Script (ADO)->ODBC 3.51.12->MySQL(win) 4.1.11->MyISAM
Script (ADO)->ODBC 3.51.12->MySQL(win) 4.1.11->InnoDB
Script (ADO)->ODBC 3.51.12->MySQL(win) 5.0.22->MyISAM
Script (ADO)->ODBC 3.51.12->MySQL(win) 5.0.22->InnoDB
Script (ADO)->ODBC 3.51.12->MySQL(linux) 4.1.21->MyISAM
Script (ADO)->ODBC 3.51.12->MySQL(linux) 4.1.211->InnoDB
The script was run from an XP Home workstation.
How to repeat:
Step 1: setup the test data
<TESTDATA>
USE test;
CREATE TABLE `lots` (
`LotId` int(11) NOT NULL auto_increment,
`RevisionId` int(11) NOT NULL default '-1',
`ManufId` int(11) default NULL,
`LocationId` int(11) default NULL,
`LotNb` varchar(64) NOT NULL default '',
`Status` smallint(2) NOT NULL default '0',
`Comment` varchar(255) default '',
`CreatedTime` datetime NOT NULL default '1970-01-01 00:00:00',
`Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`LotId`),
KEY `iLotsRevisionId` (`RevisionId`),
KEY `iLotsManufId` (`ManufId`),
KEY `iLotsLocationId` (`LocationId`),
KEY `iLotsLotNb` (`LotNb`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT lots (RevisionId,ManufId,LocationId,LotNb,Status,Comment,CreatedTime)
VALUES (3,6,9,'aaaa',1,'comment1',NOW()),
(4,8,12,'bbbb',2,'comment1',NOW()),
(5,10,15,'cccc',3,'comment1',NOW()),
(6,12,18,'dddd',4,'comment1',NOW()),
(7,14,21,'eeee',5,'comment1',NOW()),
(8,16,24,'ffff',6,'comment1',NOW());
</TESTDATA>
STEP 2: Create the script
<TEST SCRIPT>
OPTION EXPLICIT
' adotest.vbs
'
' Author - Shawn Green
' Written in response to CSC issue # 11895, 2006-09-02
'
' Purpose: to test the value returned by the "rows affected" variable
' when performing a noop UPDATE via ADO via Connector/ODBC
dim oConn, sConn, sSQL, iRows
SET oConn = WScript.CreateObject("ADODB.Connection")
sConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=xxxx;DATABASE=test;UID=xxx;OPTION=3;"
oconn.Open sConn
sSQL = "UPDATE lots SET ManufID=6 WHERE LotID = 1;"
WScript.echo "sSQL=" & sSQL & vbCrLF
oconn.Execute sSQL, iRows
WScript.echo "# of rows affected=" & cStr(iRows)
oConn.close
set oConn = nothing
</TESTSCRIPT>
STEP 3: run the script
<SAMPLE SESSION>
C:\Shawn\MySQL\Issues\11895>cscript adotest.vbs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
sSQL=UPDATE lots SET ManufID=6 WHERE LotID = 1;
# of rows affected=1
</SAMPLE SESSION>
Suggested fix:
Make the ODBC driver report the actual number of rows affected.
Description: When performing an UPDATE that changes no rows, the CLI reports "0 rows affected". When performing the same non-modifying UPDATE clause through ADO using Connector/ODBC, we see that rows affected is 1. Tested against the following combinations: Script (ADO)->ODBC 3.51.11->MySQL(win) 4.1.11->MyISAM Script (ADO)->ODBC 3.51.11->MySQL(win) 4.1.11->InnoDB Script (ADO)->ODBC 3.51.12->MySQL(win) 4.1.11->MyISAM Script (ADO)->ODBC 3.51.12->MySQL(win) 4.1.11->InnoDB Script (ADO)->ODBC 3.51.12->MySQL(win) 5.0.22->MyISAM Script (ADO)->ODBC 3.51.12->MySQL(win) 5.0.22->InnoDB Script (ADO)->ODBC 3.51.12->MySQL(linux) 4.1.21->MyISAM Script (ADO)->ODBC 3.51.12->MySQL(linux) 4.1.211->InnoDB The script was run from an XP Home workstation. How to repeat: Step 1: setup the test data <TESTDATA> USE test; CREATE TABLE `lots` ( `LotId` int(11) NOT NULL auto_increment, `RevisionId` int(11) NOT NULL default '-1', `ManufId` int(11) default NULL, `LocationId` int(11) default NULL, `LotNb` varchar(64) NOT NULL default '', `Status` smallint(2) NOT NULL default '0', `Comment` varchar(255) default '', `CreatedTime` datetime NOT NULL default '1970-01-01 00:00:00', `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`LotId`), KEY `iLotsRevisionId` (`RevisionId`), KEY `iLotsManufId` (`ManufId`), KEY `iLotsLocationId` (`LocationId`), KEY `iLotsLotNb` (`LotNb`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT lots (RevisionId,ManufId,LocationId,LotNb,Status,Comment,CreatedTime) VALUES (3,6,9,'aaaa',1,'comment1',NOW()), (4,8,12,'bbbb',2,'comment1',NOW()), (5,10,15,'cccc',3,'comment1',NOW()), (6,12,18,'dddd',4,'comment1',NOW()), (7,14,21,'eeee',5,'comment1',NOW()), (8,16,24,'ffff',6,'comment1',NOW()); </TESTDATA> STEP 2: Create the script <TEST SCRIPT> OPTION EXPLICIT ' adotest.vbs ' ' Author - Shawn Green ' Written in response to CSC issue # 11895, 2006-09-02 ' ' Purpose: to test the value returned by the "rows affected" variable ' when performing a noop UPDATE via ADO via Connector/ODBC dim oConn, sConn, sSQL, iRows SET oConn = WScript.CreateObject("ADODB.Connection") sConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=xxxx;DATABASE=test;UID=xxx;OPTION=3;" oconn.Open sConn sSQL = "UPDATE lots SET ManufID=6 WHERE LotID = 1;" WScript.echo "sSQL=" & sSQL & vbCrLF oconn.Execute sSQL, iRows WScript.echo "# of rows affected=" & cStr(iRows) oConn.close set oConn = nothing </TESTSCRIPT> STEP 3: run the script <SAMPLE SESSION> C:\Shawn\MySQL\Issues\11895>cscript adotest.vbs Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. sSQL=UPDATE lots SET ManufID=6 WHERE LotID = 1; # of rows affected=1 </SAMPLE SESSION> Suggested fix: Make the ODBC driver report the actual number of rows affected.