| Bug #26651 | MyODBC / ADO Set data type is frequently failing to update when no Primary Key | ||
|---|---|---|---|
| Submitted: | 27 Feb 2007 0:12 | Modified: | 27 Feb 2007 0:20 |
| Reporter: | Eric MaLossi | ||
| Status: | Verified | ||
| Category: | Connector/ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.12 | OS: | Microsoft Windows (win xp) |
| Assigned to: | Eric MaLossi | Target Version: | 5.1 |
| Triage: | D2 (Serious) | ||
[27 Feb 2007 0:18]
Eric MaLossi
script unit test fle
Attachment: setbug.vbs (application/octet-stream, text), 3.61 KiB.
[27 Feb 2007 0:19]
Eric MaLossi
data file
Attachment: setTest.csv (application/vnd.ms-excel, text), 442 bytes.
[27 Feb 2007 8:20]
Eric MaLossi
Additional Information: It appears that if a Primary Key is added to the data set and the necessary small changes are made to the script file, then this test will complete successfully against the 3.51 drivers, so with this as a workaround I'm lowering the priority of this bug, however the question remains why isn't it able to handle the case where there is no primary key? The worst that should happen here is that the update might not have the desired results because there was more than one match. It shouldn't fail to update at all.
[1 Aug 2007 14:53]
Tonci Grgin
Eric, I think this is either a) SQLPos problem or b) you fail to control your updates depending on schema: http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1 "Update Criteria" property: The available constants for this property are as follows: adCriteriaKey = 0 Uses only the primary key adCriteriaAllCols = 1 Uses all columns in the recordset adCriteriaUpdCols = 2 (Default) Uses only the columns in the recordset that have been modified adCriteriaTimeStamp = 3 Uses the timestamp column (if available) in the recordset NOTE: Specifying adCriteriaTimeStamp may actually use adCriteriaAllCols method to execute the Update if there is not a valid TimeStamp field in the table. Also, the timestamp field does not need to be in the recordset itself. What do you think?
Description: This was discovered while running the fldupdate.vbs ADO compliance test. I've attached a smaller vbs script setbug.vbs to this report. When run against 3.51 this script will occasionally pass for a few records but will generally fail within 10 record updates. If you manual select from mysql after the running the test, it is evident that the script is reporting correctly that the updates stop after a few records. Often it will fail on the very first record. When run against myODBC 5.0 it never passes. This will be filed in a separate report for tracking purposes. How to repeat: Create test DB ado: mysql>CREATE DATABASE ado; To run the attached files in the ADO/script-unit test framework, drop the attached script file in the 'level 0' dir, and the data file in the common dir. Open script-unit, drag the script file to the work area, highlight and run it. What the test does: 1. Creates a test table as: CREATE TABLE set_test (C1 SET ('MySQL', 'is', 'number', '1'), C2 Timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) 2. Adds 100 records to the new table 3. Opens a new connection and record set as: SELECT * FROM ado_test 4. Calls Update on each field giving it a new value 5. Compares the field.OriginalValue property with the newly assigned value to make sure that the new value was updated in the DB, and fails if they don't match.