Home arrow MySQL arrow Page 4 - 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 / 100
May 18, 2004
  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

MySQL and BLOBs - Sending Data Into the BLOB Column
(Page 4 of 6 )

Let's start by loading an image into the database. In addition to our connection object, we will need a RecordSet object and a Stream object. Let's begin by declaring these two objects.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim mystream As ADODB.Stream
Set mystream = New ADODB.Stream
mystream.Type = adTypeBinary

An ADO Stream object can handle both text and binary data (and can therefore be used to get large text fields as well as BLOB fields). We have to specify which type of data we will be dealing with using the adTypeBinary value in the Type parameter.

The first thing we need to do is open a blank recordset and add a new record to it.

rs.Open "SELECT * FROM files WHERE 1=0", conn, adOpenStatic, adLockOptimistic

We now have an empty recordset (thanks to the WHERE clause) to work with, to which we have added a new row. Next we load a file to add to this recordset using the stream object.

mystream.LoadFromFile "c:myimage.gif"

Once we have a file loaded into the stream, we can populate the recordset and update it back to MySQL:

rs!file_name = "myimage.gif"
rs!file_size = mystream.size
rs!file = mystream.read

We have assigned the details of the file into the recordset, then proceeded to "read" the data out of the stream and into the file field of the recordset. Running a select statement on your MySQL server should show the row to now be present in your database. It is important to note that data will only pass to the server during the update statement of the recordset object; the stream object methods do not cause data transfers to and from the server.

blog comments powered by Disqus

- 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 

Developer Shed Affiliates


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