通过ADO创建/编辑记录时，索引的MySQL字段未保存值 [英] Indexed MySQL field not saving value when creating/editing record through ADO

问题描述

我正在使用ADO将记录添加到MySQL表中.该代码工作正常，并且数据已发布到新记录中，除了userid以外的所有内容.我不知道为什么.

I am using ADO to add a record to a MySQL table. The code works fine, and the data is posted to the new record, all except the userid. I can't figure out why.

'---------------------------------------------------------------------------------------
' Procedure : IPhpList_AddHistory
' Author    : Adam
' Date      : 12/8/2014
' Purpose   : Add a history record to the phpList account.
'---------------------------------------------------------------------------------------
'
Private Sub IPhpList_AddHistory(cUser As clsUser, cHistory As clsHistory)

    Dim strSQL As String
    Dim rst As ADODB.Recordset

    Set rst = New ADODB.Recordset
    strSQL = "phplist_user_user_history limit 0,1"
    rst.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

    With rst
        .AddNew
            !userid = cUser.PhpListID
            !ip = ""
            ![Date] = cHistory.AddDate
            !Summary = cHistory.Subject
            !Detail = cHistory.Body
            !systeminfo = "Automated syncronization process. " & VBE.ActiveVBProject.Description
        .Update
        .Close
    End With

    ' Clear reference
    Set rst = Nothing

End Sub

看起来很简单，但是MySQL中的结果记录缺少userid值.

Seems simple enough, but the resulting record in MySQL is missing the userid value.

该列上有一个索引，但这似乎不应该阻止我设置该值. (而且，此表上没有触发器.)

There is an index on the column, but it does not seem like this should prevent me from setting the value. (And no, there are no triggers on this table.)

单步执行代码时，可以看到已设置此字段的值，并且在更新记录时没有错误，但是以某种方式在添加记录时它会恢复为零.

When I step through the code, I can see that the value for this field is set, and there are no errors when the record is updated, but somehow it just reverts to zero when the record is added.

我意识到我可以使用.Execute(strSQL)添加记录，但是我想了解为什么ADO代码很难为该列设置值.

I realize that I can add the record using .Execute(strSQL), but I would like to understand why the ADO code is having difficulty setting a value for this column.

更令人感兴趣的是，当我返回并尝试更新现有记录上的userid列时，我得到了类似的结果.我设置了该值，它恢复为零(或那里的任何先前值).

Of further interest, when I go back and try to update the userid column on an existing record, I have a similar result. I set the value, and it reverts back to zero (or whatever previous value was there).

现在，这真让人感到困惑.我在表中添加了另一列具有相同数据类型，长度等的列，并且代码突然按预期工作了.我可以在新列 和/或 的userid列中编辑值. (并独立编辑值)

Now this is getting really perplexing... I added another column with the same data type, length, etc... to the table, and suddenly the code works as expected. I can edit the value in the new column and/or the userid column. (And edit the values independently)

但是，当我删除测试列时，它又回到了以前的行为，即不允许我更改userid中的值. (为什么会这样?)

But when I remove the test column, it goes back to the previous behavior, of not allowing me to change the value in userid. (why would this be??)

更进一步，我在本地MySQL实例上创建了表，以便可以在ADO查询命中数据库时对其进行概要分析.这揭示了一些非常有趣的行为.以下两个语句是由相同的ADO代码生成的，但是第二个语句将test列添加到了表中.

Taking things a step further, I created the table on a local MySQL instance so I could profile the ADO queries as they hit the database. This revealed some very interesting behavior. The following two statements were generated by the same ADO code, but the second one had the test column added to the table.

INSERT INTO `test`.`phplist_user_user_history`(`id`,`userid`,`ip`,`date`,`summary`,`detail`,`systeminfo`) 
VALUES (DEFAULT,DEFAULT,'',_binary'2014-12-18 11:51:13','My Subject','New Details','IBLP automated syncronization process. Version 1.0')

INSERT INTO `test`.`phplist_user_user_history`(`id`,`userid`,`ip`,`date`,`summary`,`detail`,`systeminfo`,`testcol`) 
VALUES (DEFAULT,456,'',_binary'2014-12-18 13:22:44','My Subject','New Details','IBLP automated syncronization process. Version 1.0',1)

请注意第一个insert语句如何输入DEFAULT而不是我通过ADO传递的值.

Notice how the first insert statement inputs DEFAULT instead of the value I passed through ADO.

第二个调用代码是相同的，只是它在testcol中设置了一个值.当有两个INT(11)列而不是一列时，为什么运行方式会不同?

The second calling code was identical, except that it set a value in testcol. Why would this run differently when there are two INT(11) columns instead of one?

进一步的线索是，当我针对测试数据库中的表运行代码时，我在VBA中收到以下错误(即使我明确设置了userid值):

A further clue is that when I run the code against the table in my test database, I get the following error in VBA (even though I am explicitly setting the userid value):

添加测试列后，错误会在测试列上弹出.在测试列上明确设置该值之后，.Update成功，并且两个字段都正确更新.

After adding the test column, the error then pops up on the test column. After explicitly setting the value on the test column, the .Update is successful, and both fields are updated correctly.

这可能是ODBC驱动程序中的错误吗?我正在使用最新的MySQL ODBC连接器驱动程序(5.3.4)和以下连接字符串:

Could this possibly be a bug in the ODBC driver? I am using the latest MySQL ODBC connector driver (5.3.4), and the following connection string:

"Driver=MySQL ODBC 5.3 Unicode Driver;SERVER=myserver;UID=myuser;PWD={mysecretpwd};DATABASE=test;PORT=3306;DFLT_BIGINT_BIND_STR=1"

推荐答案

我能够使用64位版本的MySQL ODBC 5.3 Unicode驱动程序(5.03.04.00)重新创建您的问题.当表中的最后一列的类型为TEXT时，ADO Recordset更新似乎是一个问题.我什至没有在userid上的索引，并且得到了相同的结果.

I was able to recreate your issue with the 64-bit version of the MySQL ODBC 5.3 Unicode Driver (5.03.04.00). It appears to be an issue with ADO Recordset updates when the last column in the table is of type TEXT. I did not even have an index on userid and I got the same results.

一种可能的解决方法是使用带有参数的ADODB.Command，并使用类似于以下代码的代码来执行插入操作:

One possible workaround would be to use an ADODB.Command with parameters to perform the insert using code similar to this:

Dim oConn As ADODB.Connection
Dim cmd As ADODB.Command

Set oConn = New ADODB.Connection
oConn.Open _
        "Driver=MySQL ODBC 5.3 Unicode Driver;" & _
        "SERVER=localhost;" & _
        "UID=root;" & _
        "PWD=whatever;" & _
        "DATABASE=mydb;" & _
        "PORT=3306;" & _
        "DFLT_BIGINT_BIND_STR=1"

Set cmd = New ADODB.Command
cmd.ActiveConnection = oConn
cmd.CommandText = _
        "INSERT INTO phplist_user_user_history " & _
        "(`userid`, `ip`, `date`, `Summary`, `Detail`, `systeminfo`) " & _
        "VALUES (?,?,?,?,?,?)"
cmd.Parameters.Append cmd.CreateParameter("?", adInteger, adParamInput, , 456)
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, "")
cmd.Parameters.Append cmd.CreateParameter("?", adDBTimeStamp, adParamInput, 255, Now)
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, "cHistory.Subject")
cmd.Parameters.Append cmd.CreateParameter("?", adLongVarWChar, adParamInput, 2147483647, "cHistory.Body")
cmd.Parameters.Append cmd.CreateParameter("?", adLongVarWChar, adParamInput, 2147483647, "Automated syncronization process.")
cmd.Execute

Set cmd = Nothing
oConn.Close
Set oConn = Nothing

我从Access 2010数据库进行了测试，它对我来说很好用.

I tested it from an Access 2010 database and it worked fine for me.

这篇关于通过ADO创建/编辑记录时，索引的MySQL字段未保存值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！