mysql blob 字节流_使用VBA在MySQL Blob中存储字节数组

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

解决方案

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