| Bug #26985 | Recordset Update() fails in 5.1 ODBC connector when using adUseClient cursor | ||
|---|---|---|---|
| Submitted: | 9 Mar 2007 3:49 | Modified: | 14 Dec 2007 16:58 |
| Reporter: | Michael Pryor (Candidate Quality Contributor) | ||
| Status: | Closed | ||
| Category: | Connector/ODBC | Severity: | S1 (Critical) |
| Version: | 5.1.0-alpha | OS: | Microsoft Windows (Windows) |
| Assigned to: | Jess Balint | Target Version: | |
| Tags: | ADO | ||
| Triage: | D3 (Medium) | ||
[9 Mar 2007 11:25]
Tonci Grgin
Hi Michael and thanks for your report. I was unable to verify it using MySQL server
5.0.34BK on Win XP Pro SP2 localhost. If you have more info to provide, please reopen the
report.
Test case:
Option Explicit
Const DSN = "Driver={MySQL connector/ODBC
v5};Uid=root;Pwd=;Server=localhost;Database=test;OPTION=1 + 2 + 32"
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adOpenUnspecified = -1
Const adUseNone = 1
Const adUseServer = 2
Const adUseClient = 3
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004
'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200
Const adInteger=3
Const adDate=7
Const adVarChar=200
Dim cnxDatabase
Dim strSQL
' connecting database
Set cnxDatabase = CreateObject("ADODB.Connection")
cnxDatabase.Open(DSN)
' querying data
strSQL = "SELECT * FROM bug26985"
Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
.ActiveConnection = cnxDatabase
.LockType = adLockOptimistic 'see definitions above
.CursorType = adOpenKeyset 'see definitions above
.Open(strSQL)
End With
rs.AddNew
rs("b") = "foo123"
rs.Update
rs.Close
Set rs = Nothing
Set cnxDatabase = Nothing
C:\mysql507\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.34-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table bug26985 ( b varchar(30) );
Query OK, 0 rows affected (0.08 sec)
mysql> select * from bug26985;
+--------+
| b |
+--------+
| foo123 |
+--------+
1 row in set (0.00 sec)
mysql>
General query log:
070309 11:18:43 6 Connect root@localhost on test
6 Query SET NAMES utf8
6 Query SELECT database()
6 Query SELECT database()
6 Query SELECT database()
6 Query SELECT * FROM bug26985
7 Connect root@localhost on test
7 Query SET NAMES utf8
7 Query INSERT INTO bug26985 ( b) VALUES('foo123')
7 Quit
6 Quit
[9 Mar 2007 17:49]
Michael Pryor
Tonci, You forgot the line rs.CursorLocation = 3 This is necessary to work around a bug in earlier versions of MyODBC. (See http://bugs.mysql.com/bug.php?id=13776 ) You are correct, without this line it works. With it, it fails. But without it, older versions of MyODBC and MySQL 5 will not work because of bug 13776
[12 Mar 2007 9:06]
Tonci Grgin
Michael you are right.
Modifying code like you suggested
With rs
.ActiveConnection = cnxDatabase
.LockType = adLockOptimistic '3
.CursorType = 2 'adOpenStatic 3, adOpenKeyset 1
.CursorLocation = 3 'adUseClient
.Open(strSQL)
leads to error reported.
The problem seems to occur only with CursorLocation = 3 'adUseClient while options 1 and 2
work as can be seen from general query log:
CS cursor:
070312 8:54:26 1 Connect root@localhost on test
1 Query SET NAMES utf8
1 Query SELECT database()
1 Query SELECT database()
1 Query SELECT database()
070312 8:54:27 1 Query SELECT * FROM bug26985
2 Connect root@localhost on test
2 Query SET NAMES utf8
2 Query SELECT COLUMN_NAME, SEQ_IN_INDEX FROM
INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='def' AND TABLE_NAME='bug26985' AND
INDEX_NAME='PRIMARY' ORDER BY SEQ_IN_INDEX
070312 8:54:28 2 Quit
3 Connect root@localhost on test
3 Query SET NAMES utf8
070312 8:54:29 3 Query SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE
TABLE_SCHEMA='def' AND TABLE_NAME='bug26985'
3 Quit
4 Connect root@localhost on test
4 Query SET NAMES utf8
5 Connect root@localhost on test
5 Query SET NAMES utf8
070312 8:54:52 5 Quit
4 Query INSERT INTO `def`.`test`.`bug26985` (`b`) VALUES ('ABCDEFGH')
4 Quit
070312 8:55:11 1 Quit
SS or Undefined cursor location:
070312 8:59:23 14 Connect root@localhost on test
14 Query SET NAMES utf8
14 Query SELECT database()
14 Query SELECT database()
14 Query SELECT database()
14 Query SELECT * FROM bug26985
15 Connect root@localhost on test
15 Query SET NAMES utf8
070312 8:59:24 15 Query INSERT INTO bug26985 ( b) VALUES('foo123123')
15 Quit
14 Quit
[13 Jul 2007 18:33]
Jason Williams
200707131016_mysql-bin.000172_242162957
[13 Jul 2007 18:33]
Jason Williams
Hi Guys, It would be very helpful to us if this could be resolved. We really need the UTF-8 abilities of Connector/ODBC 5 and this is preventing us from being able to use this. Thank you in advance.
[4 Oct 2007 21:20]
Michael Pryor
Updating this case as it still exists in the current shipping 5.1 alpha version
[5 Oct 2007 12:58]
Susanne Ebrecht
Hi Michael, many thanks for you help. Regards, Susanne
[23 Oct 2007 15:02]
Susanne Ebrecht
Bug #27903 and Bug #27961 are duplicates of this bug here.
[23 Oct 2007 22:11]
Eric MaLossi
Michael, Thanks for your patience with this. A couple of things you should make note of, first you are specifying a Keyset Cursor. I'm not sure if you are depending on this capability or not but I don't believe this is a possibility with adUseClient cursor. You will be resigned to adOpenStatic. If you need dynamic capabilities, you will need to use adUseServer and add option 32 to your connection string, although this won't get you Keyset either, it will instead open as AdOpenDynamic. Also the failure here doesn't appear to be with addNew(). The failure occurs when Update() is called, which is unfortunately quite a bit worse. Since this does appear to be working with a ServerSide cursor (adUseServer) this might be a workaround for you, if you can use it. Upgrading priority to P2 since Update() is essential functionality, however there is the Server Side cursor workaround. I do not believe it can be said at this point that bug #27903 is a duplicate of this one as stated above. UpdateBatch() is fundametally different from Update() so until Update() works this can't be proven. A test case for this issue has been added to the ADO test suite: connectors-svnroot/odbc-testing/ado-conformance/trunk/bugs/26985.vbs
[23 Oct 2007 22:17]
Michael Pryor
The workaround for Bug #13776 requires a client side cursor. As well, the documents on the MySQL site at http://dev.mysql.com/tech-resources/articles/vb-cursors-and-locks.html basically say "every single app (except for one) uses client side cursors because they are better". I'm paraphrasing, but effectively because of bug 13776, use a server side cursor is not an acceptable solution.
[23 Oct 2007 22:20]
Eric MaLossi
odbc trace for ADO bug test
Attachment: 26985.LOG (application/octet-stream, text), 100.36 KiB.
[2 Nov 2007 23:21]
Michael Pryor
see case #32064 which I discovered while trying to work around this bug.
[12 Dec 2007 20:54]
Jess Balint
Fix committed and will be released in 5.1.1.
[12 Dec 2007 21:20]
Jason Williams
Does the fix Michael's original bug? There seemed to be some back and forth towards the end of this case as to whether a resolution satisfactory to Michael's use was possible.
[14 Dec 2007 16:58]
MC Brown
A note has been added to the 5.1.1 changelog: Recordset Update() fails when using adUseClient cursor.
Description: Using the AddNew/Update functions of ADO MyODBC generates an error: [MySQL][MyODBC 5.00.12][MySQL] 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`A` (`b`) VALUES ('foo123')' at line 1 How to repeat: For code to reproduce do the following: mysql> create database test; mysql> use database test; mysql> create table A ( b varchar(30) ); Create a file called test.asp in your c:\inetpub\wwwroot directory (or any other virtual directory) Put this in it: <% Dim db: Set db = Server.CreateObject("ADODB.Connection") db.Open "DRIVER={MySQL Connector/ODBC v5};server=localhost;uid=root;pwd=password;database=test;Option=3;" Dim rs: Set rs = Server.CreateObject( "ADODB.Recordset" ) rs.CursorLocation = 3 rs.Open "SELECT b from A where b = ''", db, 1, 3 rs.AddNew rs("b") = "foo123" rs.Update %> Done. Note the 1 is adOpenKeyset, and the 3 is adLockOptimistic in the rs.Open call. This is standard for this kind of query (our app has been using it with MyODBC v3 for a few years now). There was a similar bug when MySQL 5 came out, but adding .CursorLocation = 3 to all code of this form fixed that.