MySQL and BLOBs - Update File - We're Done!

(Page 6 of 6 )

We can update a column in much the same way that we write it, the only difference being that we specify a WHERE clause in our query that points to the record we wish to update.

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

And that is it. Following is the complete samplecode.

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 + 16384

 

 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"

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: MySQL and BLOBs

· "mystream.Write rs!file"should be:mystream.Write rs.Fields(0).Value   >>> Post your comment now!