MySQL Bugs: #35665: adOpenForwardOnly should allow inserts

Bug #35665	adOpenForwardOnly should allow inserts
Submitted:	29 Mar 2008 8:36	Modified:	13 Sep 2012 11:09
Reporter:	Louis Breda van	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.1.2	OS:	Any (VISTA 64)
Assigned to:		CPU Architecture:	Any
Tags:	adOpenForwardOnly, insert

Description:
Hello,

For performance reasons, I always open recordsets which the least demanding options, adOpenForwardOnly for instance.

According to microsoft adOpenForwardOnly should be RW, that always worked for me, but not when I used it with MySQL (ODBC 5.1)  

http://msdn2.microsoft.com/en-us/library/ms681771(VS.85).aspx and http://msdn2.microsoft.com/en-us/library/ms681510(VS.85).aspx it should be RW when used with CS cursors.

Retested with the "old" 3.1 ODBC connector, even worse, does not work at all (not my primairy intrest).

For ODBC 5.1 there is a simple workarround, just open the recordset with adOpenDynamic

Sincerely,

Louis 

How to repeat:
'TstTable has two columns
'Field,"Type","Null","Key","Default","Extra"
'Name,"varchar(16)","NO","PRI","",""
'Something , "varchar(45)", "YES", "", "", ""

'one row present:  "Louis", "Testje"

Public Const connTstDB As String = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=test;UID=thatsme;PASSWORD=louis;OPTION=3;Packet Size=8192"
'Public Const connTstDB As String = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=thatsme;PASSWORD=louis;OPTION=3;Packet Size=8192"

Sub tstmysql()

    Dim cn_mytst As New ADODB.Connection
    Dim rstst As New ADODB.Recordset
    
    Dim Name As String
    Dim Something As String
    
    cn_mytst.Open connTstDB
    
    rstst.Open "SELECT TstTable.* FROM TstTable;", cn_mytst, adOpenDynamic, adLockOptimistic
    
    rstst.MoveFirst
    Something = rstst!Something

    rstst!Something = "This should work"        'it does
    rstst.Update
    
    Something = rstst!Something
    
    rstst.Close
    
    Stop
    
    rstst.Open "SELECT TstTable.* FROM TstTable;", cn_mytst, adOpenForwardOnly, adLockOptimistic
    
    rstst.MoveFirst
    Something = rstst!Something

    rstst!Something = "This should work too"    'but does not :<
    rstst.Update
    
    Something = rstst!Something
    
    rstst.Close
    
    Stop
    
    Set rstst = Nothing
    
    cn_mytst.Close
    
    Set cn_mytst = Nothing
    
End Sub

Louis, these kinds of updates might depend greatly on ADO and table structure... ADO dependence is listed in http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1. As you can see, default ADO behavior is to match all columns on update but that will not work for BLOB, FLOAT etc columns, so I make use of rs.Properties("Update Criteria").Value = 0 (adCriteriaKey) to make update work.

This sample presumes you have MyODBC 3.51 installed and an jpg image in the same folder where executable is:

   Dim I As Long
   Dim cx As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim stm As ADODB.Stream
   cx.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=root;PWD=*****;OPTION=16427"
   cx.Open()
   cx.Execute("DROP TABLE IF EXISTS testblob")
   cx.Execute("CREATE TABLE testblob(ndx INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,picname VARCHAR(45),picture BLOB)")
   cx.Execute("INSERT INTO testblob(ndx) VALUES (NULL)")
   I = I + 1
   rs.CursorLocation = CursorLocationEnum.adUseClient
   rs.Properties("Update Criteria").Value = 0 'adCriteriaKey <<<< HERE
   rs.Open("Select * from testblob", cx, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockOptimistic)
   stm = New ADODB.Stream
   stm.Type = StreamTypeEnum.adTypeBinary
   stm.Open()
   stm.LoadFromFile(Application.StartupPath & "\testpicture.jpg")
   rs.Fields("picname").Value = "mitADD" & CStr(I)
   rs.Fields("picture").Value = stm.Read
   rs.Update()
   stm.Close()
   stm = Nothing
   rs.Close() 

I don't think this is a bug, but you did not provided me with all necessary info to be sure.

Tonci,

I realy don't know what futher info you need! 

The code and table description I send you, is all what is required. 
The table is as simple as described. Just two fields 
a field called name varchar(16) and a field called something varchar(45).

Really, no complex things, no rocket sience.

Louis

Louis, concentrate please on my test case and proper usage of ADO options. My test case works and I think I described why. Can you repeat undesired behavior using Options("Update") properly?

Tonci,

I am lost, you inform me that it is legal that the update does nog work with blobs (wired but ok), but I do not at all complain about blobs.

I am only saying that it ins not even working with simple fields like a integer or a string.

Louis

VBS test case

Attachment: 35565.vbs (application/octet-stream, text), 1.68 KiB.

Louis, right... I have attached test case that works flawlessly with MyODBC 5.1.3:
VBScript user connection Id = 2
080404 13:47:41	      2 Connect     root@localhost on test
		      2 Query       SET NAMES utf8
		      2 Query       SET character_set_results = NULL
		      2 Query       SET SQL_AUTO_IS_NULL = 0
		      2 Query       select database()
		      2 Query       select database()
080404 13:47:42	      2 Query       SELECT @@tx_isolation
		      2 Query       USE test
		      2 Query       DROP TABLE IF EXISTS `bug35565`
		      2 Query       create table bug35565(`Name` VARCHAR(16) NOT NULL PRIMARY KEY, `Something` VARCHAR(45))
		      2 Query       INSERT INTO bug35565 VALUES('Louis', 'Testje')
		      2 Query       SELECT * FROM bug35565
080404 13:47:43	      2 Query       UPDATE `test`.`bug35565` SET `Something`='This should work too' WHERE `Name`='Louis' AND `Something`='Testje'
		      2 Quit       
Mysql cl client user connection id = 4
080404 13:48:02	      4 Connect     root@localhost on test
		      4 Query       select @@version_comment limit 1
080404 13:48:11	      4 Query       select * from bug35565
mysql> select * from bug35565;
+-------+----------------------+
| Name  | Something            |
+-------+----------------------+
| Louis | This should work too |
+-------+----------------------+
1 row in set (0.00 sec)

I really can't tell what is the problem here... As for my previous comments, observe default (and insane) ADO behavior when forming updates...

Hum,

Saw that you where running 5.1.3 did not know it wast there. But installed it.

I get another error now, "SQL query is said to be wrong (nonsens)", will try to find out more later.

rstst.Open "SELECT TstTable.* FROM TstTable;", cn_mytst, adOpenDynamic, adLockOptimistic (SQL accepted and working)

rstst.Open "SELECT TstTable.* FROM TstTable;", cn_mytst, adOpenDynamic, adLockOptimistic (error in sql syntax)

Some times you simply do not understand :)

Louis

Louis, I retested with 5.1.4 and 3.51.25, found no problem:
080424  9:50:28	      9 Connect     root@localhost on test
		      9 Query       SET NAMES utf8
		      9 Query       SET character_set_results = NULL
		      9 Query       SET SQL_AUTO_IS_NULL = 0
		      9 Query       select database()
		      9 Query       select database()
080424  9:50:29	      9 Query       SELECT @@tx_isolation
		      9 Query       USE test
		      9 Query       DROP TABLE IF EXISTS `bug35565`
		      9 Query       create table bug35565(`Name` VARCHAR(16) NOT NULL PRIMARY KEY, `Something` VARCHAR(45))
		      9 Query       INSERT INTO bug35565 VALUES('Louis', 'Testje')
		      9 Query       SELECT * FROM bug35565
080424  9:50:30	      9 Query       UPDATE `test`.`bug35565` SET `Something`='This should work too' WHERE `Name`='Louis' AND `Something`='Testje'
		      9 Quit       
Relevant part of VBS attached above:
Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .CursorLocation = adUseClient
    .Open(strSQL)
End With
Wscript.Echo "rs opened"
rs.MoveFirst
rs("Something") = "This should work too"
rs.Update

What is the development too you're using?

One more remark, if I were to use "rs.Properties("Update Criteria").Value = 0" update statement would be 080424  9:54:28	     10 Query       UPDATE `test`.`bug35565` SET `Something`='This should work too' WHERE `Name`='Louis'
 instead of 080424  9:54:28	     10 Query       UPDATE `test`.`bug35565` SET `Something`='This should work too' WHERE `Name`='Louis' AND `Something`='Testje'

This is what "UpdateCriteria" does.

Hoi Tonci,

The moment I recieved the bug update mail, I was testing piece of code updating a recordset. The code was not working.

When running with an Access RS the code used:
- RS_Myset.Open MySQL1, cn, adOpenForwardOnly, adLockOptimistic
No problem. It is not working now that way, that is for sure

I was just trying
- RS_Myset.Open MySQL1, cn, adOpenDynamic, adLockOptimistic
which seems to work, but is (in case MySQL) very slow

Reading your mail, I think we have more than one aspect causing the problems, and that makes it difficult:
- I normally use adOpenDynamic or adOpenForwardOnly both I think cusors at the server side by default
- I normally do not use "UpdateCriteria" (no reason to)
- I normally do not include the PK in a query unless I need it
- Normally where clauses to determinate the recordset do not affect update possiblity.

However, I discovered that all these factors do matter now!

Looking at your testcase, I see that you are using aduseclient.
That could perhaps explain that it seems to work. I mean it could very well be that we made the wrong conclusion before. Not the forwardonly being the problem but the fact that "nothing works" unless you use:
- aduseclient
- use "criteria"
- include the PK
- do not have certain where clauses (realy do not know why some do not work, in conjunction with updates; most do)

Not sure but It might be that aduseclient also automaticly implies a static cursor.

Still modifying and testing, see if it works, retesting etc. Having better understanding now in which earia the problems are. Still can not 100% predict what will work and what not :)

Louis

Tonci, 

I was just reading your mail again. Especially the latest one.
Are you saying that "Update Criteria" is infact modifing the 
"SQL internal" query!? 

Adding arguments not given by me!??

Do not understand, but I have to think about a where clause, which I had to modify to make thinks work, despite the fact that it was OK IMHO.

Related to your question about the development tool: I am just using MsAccess internal VBA-editor. Can do every thing I need from there / Can control every application which can be accesed by VBA, ODBC or commandline. 
Must admit that Visual Studio whould have some advantages now.  

Louis

Louis:

> I was just reading your mail again. Especially the latest one. Are you saying that "Update Criteria" is infact modifing the  "SQL internal" query!?  Adding arguments not given by me!??

Yes I am. This is exact purpose of API's, to do things for you! And especially ADO API and it's insane updates based on all fields. timestamp filed etc (of course, Access functions the same way). One needs to dig deep and hard into specs to figure out what's going on...

> Do not understand, but I have to think about a where clause, which I had to modify to make thinks work, despite the fact that it was OK IMHO.

Yes, you may be required to do so. Many things appear "right" but are not recommendable and/or inside this or that specification.

There is also a matter of known problem in MySQL server, and thus in MyODBC too, which makes adUseClient necessary for your code to work.

I think we covered everything in this rather long discussion, so I would like to close this report as !Bg now, if you agree.

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".