使用VBA在MySQL Blob中存储字节数组 [英] Storing byte array in MySQL Blob with VBA

问题描述

有人有一些VBA代码可以将字节数组存储到MySQL blob列中吗?

Anybody have some VBA code that will store a byte array into a MySQL blob column?

推荐答案

以下是一些代码.需要对Microsoft Active Data Objects 2.x库的引用.它使用MySQL的OLE DB提供程序(可能需要在客户端计算机上安装它).

Here is some code. Requires a reference to Microsoft Active Data Objects 2.x Library. It uses the OLE DB provider for MySQL (Might need to install that on the client machine).

Sub StoreBLOB(data() As Byte, key As Double)
'stores the BLOB byte array into the row identified by the key
'requires reference to Microsoft Active Data Objects 2.x Library

On Error GoTo handler:

    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim conStr As String
    Dim strSQL As String

    'have it return only the record you want to store your blob
    strSQL = strSQL & "SELECT * FROM YOURTABLE WHERE KEY = " & key

    'setup connection
    conStr = conStr & "Provider=MySQLProv;"
    conStr = conStr & "Data Source=mydb;"
    conStr = conStr & "User Id=myUsername;"
    conStr = conStr & "Password=myPassword;"

    con.ConnectionString = conStr
    con.Open

    rs.Open strSQL, con, adOpenDynamic, adLockOptimistic

    If rs.RecordCount > 1 Then
        Err.Raise 1001, "StoreBLOB", "Too many records returned from dataset.  Check to make sure you have the right key value"
    Else
        Err.Raise 1002, "StoreBLOB", "No Records found that match the key"
    End If

    rs.Fields("BLOBFIELDNAME").Value = data
    rs.Update 'store the contents to the database

    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing

Exit Sub
handler:
    Err.Raise 1003, "StoreBLOB", "Unexpected Error in StoreBLOB.  Check that server is running"
End Sub

这篇关于使用VBA在MySQL Blob中存储字节数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！