|
Sample of using ADO Stream object to access BLOB data from a MySQL database. 'CREATE CONNECTION OBJECT AND ASSIGN CONNECTION STRING Dim conn As ADODB.Connection Set conn = New ADODB.Connection conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _ & "SERVER=127.0.0.1;" _ & "DATABASE=test;" _ & "UID=testuser;" _ & "PWD=12345;" _ & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 1e'84 conn.CursorLocation = adUseClient conn.Open 'CREATE TABLE FOR SAMPLE CODE conn.execute "CREATE TABLE files(" _ & "file_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, " _ & "file_name VARCHAR(64) NOT NULL, " _ & "file_size MEDIUMINT UNSIGNED NOT NULL, " _ & "file MEDIUMBLOB NOT NULL)" 'OPEN RECORDSET FOR WRITING Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Dim mystream As ADODB.Stream Set mystream = New ADODB.Stream mystream.Type = adTypeBinary rs.Open "SELECT * FROM files WHERE 1=0", conn, adOpenStatic, adLockOptimistic rs.AddNew mystream.Open mystream.LoadFromFile "c:myimage.gif" rs!file_name = "myimage.gif" rs!file_size = mystream.size rs!file = mystream.read rs.Update mystream.Close rs.Close 'OPEN RECORDSET TO READ BLOB rs.Open "Select * from files WHERE files.file_id = 1", conn mystream.Open mystream.Write rs!File mystream.SaveToFile "c:newimage.gif", adSaveCreateOverWrite mystream.close rs.close 'OPEN RECORDSET FOR UPDATE OF BLOB COLUMN rs.Open "Select * from files WHERE files.file_id = 1", conn, adOpenStatic, adLockOptimistic mystream.Open mystream.LoadFromFile "c:updateimage.gif" rs!file = mystream.Read rs.Update mystream.Close rs.Close 'OPEN RECORDSET TO READ UPDATED IMAGE rs.Open "Select * from files WHERE files.file_id = 1", conn mystream.Open mystream.Write rs!file mystream.SaveToFile "c:newupdatedimage.gif", adSaveCreateOverWrite mystream.Close rs.Close conn.execute "DROP TABLE files" conn.Close msgbox "Success! Check your C: directory for newimage.gif and newupdatedimage.gif" |