Bug #21133 ERROR ON BATCH UPDATES
Submitted: 19 Jul 2006 5:20 Modified: 2 Jan 2008 20:07
Reporter: Antonio Sandoval Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.22 OS:Windows (WinXP)
Assigned to: CPU Architecture:Any
Tags: ado batch update

[19 Jul 2006 5:20] Antonio Sandoval
Description:
I have a question, the Update Batch is fully supported by ODBC 3.51 and MySQL 4.1.19?.  I have  a problem with VB 6.0 + ADO 2.8. When i try to use Updatebatch with a client-side recordset with an static cursor and in LockBatch mode the system says something like "The row that are yo triyng to update has changed since the last read", ehm, the error is in spanish and my english is not enough :-S. if i just change one or two trivial fields the error is the same. I try with InnoDB and MyISAM table types, i have a unique autoincrement field on the table... i dont know what else is important. the mysql server are in a internet server with linux. The only way in how it works is using Server-side cursor, but is necesary do an update batch before of continue any operacion with the recordset.

How to repeat:
AdoConnection.CursorLocation=adUseClint
AdoConnection.Open "DSN=Mydsn;UID=milogin;PWD=mipass"
rs.CursorLocation=adUseClient
rs.Open "SELECT * FROM tbl1 WHERE
id=12345",AdoConnection,adLockStatic,adLockBatchOptimistic

rs.Update Array("field1","field2"...),Array(value1,value2...)

rs.UpdateBatch ¿?
[19 Jul 2006 8:21] Tonci Grgin
Hi Antonio and thanks for your problem report.
Can you please post complete test case showing this error along with data dump suitable for import to MySQL database?
Can you also try adding Option 2 (and 32 if necessary) to your connect string and inform me of result.
[19 Aug 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[31 Dec 2007 15:31] Tonci Grgin
I will put this in "Analyzing" as we do not have tests for "rs.Update Array("field1","field2"...),Array(value1,value2...)" type updates.
[2 Jan 2008 20:07] Erica Moss
Tested against 3.51.22, and 5.1.1  Test case passes against both

The recordset.Update "field array" "value array"
syntax is valid and works.

However it is not correct to anticipate that this statement will trigger a commit to the server if the record set was opened in batch mode.  If it is opened adLockOptimistic then it will trigger a commit.  However adLockBatchOptimistic will require a call to recordset.UpdateBatch before changes will be reflected at the server.  This is correct behavior.

A test case for this scenario has been added to the ADO conformance suite 
/ado_conformance/trunk/tests/bugs/21131.vbs