Home arrow MySQL arrow Page 6 - MySQL and BLOBs
MYSQL

MySQL and BLOBs


One of MySQL's strengths is its use of Binary Large Object (BLOB) columns. These columns store unprocessed binary data, typically files, that can be retrieved and manipulated like the other common datatypes. The difficulty comes in accessing the BLOB column in VB. Prior to ADO 2.5, the only way to move data in and out of a MySQL BLOB column using Visual Basic was to use the appendchunk and getchunk methods. With ADO 2.5, the stream object has been added, greatly simplifying the process of working with MySQL BLOBs. In this article, I will focus entirely on using the stream object.

Author Info:
By: Mike Hillyer
Rating: 4 stars4 stars4 stars4 stars4 stars / 99
May 18, 2004
TABLE OF CONTENTS:
  1. · MySQL and BLOBs
  2. · Connection String
  3. · Connection Object
  4. · Sending Data Into the BLOB Column
  5. · Retreiving Data
  6. · Update File - We're Done!

print this article
SEARCH DEVARTICLES

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.

blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials