About the Author

Mike Hillyer is a Technical Writer for MySQL AB and lives in Alberta, Canada.

We are always looking for interesting articles about MySQL! Have you written something and would like to it published here? Please contact us via feedback form »

Accessing MySQL BLOB columns using Visual Basic 6

By Mike Hillyer

Introduction

One of MySQL's strengths is it's 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. One sample use would be the storage of images into the database to be retrieved for a website to display. In my latest project, a Customer Relationship Management (CRM) application, BLOB columns are used by MySQL to store various binary files relating to customers such as pictures of the customers and scans of their documents and correspondence. 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. This involved loading the file into a variant variable, and then sending the file to the server by breaking it into chunks and sending them one at a time in a loop. 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. Microsoft's article on using the stream object can be found here. The Microsoft article is basically a code sample and may be more appropriate for those that do not need any explanation. My code sample can be accessed here.

I would recommend you begin by making sure you have the latest service pack for Visual Basic installed. The latest service pack for VB can be found here. Installing the service pack will ensure you have the latest version of ADO installed. In a new (or existing) visual basic project, make sure that the most recent version of the Microsoft ActiveX Data Objects Library is checked in the references section of your project (Version 2.7 as of this writing). I will also assume that you have MySQL installed, as well as the latest version of Connector/ODBC NOTE: Version 3.51.03 or higher is required to avoid errors.

MySQL Configuration

Now that ADO is installed and referenced, we can use it to access a MySQL BLOB column. Our first step is to create a table to be accessed. In my file tables, I usually have four columns: an AUTO_INCREMENT column of appropriate size (UNSIGNED SMALLINT) to serve as a primary key to identify the file, a VARCHAR column that stores the filename, an UNSIGNED MEDIUMINT column that stores the size of the file, and a MEDIUMBLOB column that stores the file itself. For this example, I will use the following table definition:

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);

While logged into MySQL, we should modify the max_allowed_packet system variable. This variable determines how large of a packet (i.e. a single row) can be sent to the MySQL server. By default, the server will only accept a maximum size of 1 meg from our client application. If you do not intend to exceed 1 meg, this should be fine. If you do intend to exceed 1 meg in your file transfers, this number has to be increased. I set my max_allowed_packet value to 15M, in MySQL 3.x, this limit is 16M, in 4.x, the size is limited only to your system memory, up to a theoretical 2G maximum. I personally find 15M to be more than enough, especially since my users connect remotely through DSL modems at best, and a 15 meg transfer tends to take upwards of 5 minutes as it is. If you do need to change this value, you can either set it in the my.cnf file (add a line that reads max_allowed_packet=15M), or use the SET max_allowed_packet=15M; syntax from within MySQL.

Connection String

Now that we have configured MySQL and VB, we can move on to writing the client app. First, lets look at the connection string. The connection string is what determines how VB will connect to the server, it specifies the ODBC driver (Connector/ODBC), the address of the server, the username and password used to connect, and the database name. In addition, the specific options used to connect are also specified. Following is the connection string I use for my application.

DRIVER={MySQL ODBC 3.51 Driver};SERVER=123.456.789.100;DATABASE=mysqldatabase;UID=sampleuser;PWD=12345;OPTION=16427

I populate the UID and PWD sections using values from my login prompt, and then store the completed connection string in a global variable stored in a module. The remainder is static and is part of a pre-built string. A quick note on the option value; The options specified include the following:

1 - Client Can't handle the real column width being returned.
2 - Have MySQL return found rows value.
8 - Allow Big Values: We want the server to send out the BLOB values to our VB client (large packets), so this option must be set.
32 - Toggle Dynamic cursor support.
16384 - Convert LongLong to Int: This just helps makes sure that large numeric results get properly interpreted.

The 1,2,and 32 options are taken from the ADO code sample on the MySQL web site

Connection Object

Now that we have a connection string, we can connect to the database, first I will give a sample of how I connect.

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = GloConnectionString
conn.CursorLocation = adUseClient
conn.Open

In the first line we create an ADODB connection object in memory and in the second line we instanciate it. The alternative syntax to this is to type Dim conn As New ADODB.Connection

While I previously reccomended the 'as New' syntax, I have since learned that it slows down your code since it must check if the object is instanciated every time a reference is made to the object.

In the second line we set the Connection String of our Connection object to the global variable we specify in the login prompt. Conversely, you can always place the connection string here. In the third line we set the connection to use client-side cursors. I have found that this setting helps to prevent a lot of problems that can pop up while programming ADO with MySQL (for more details on cursorlocation, cursortype, and locktype, see this article. In the final line we open the connection object, which we will of course have to close when we are finished with conn.close.

Sending Data into the BLOB column

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
rs.AddNew

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.Open
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
rs.Update
mystream.Close
rs.Close
conn.Close

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. Is 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.

Retreiving Data

Now that our image is in the table, we need to get it back out. As we have covered them already, lets get the connection and recordset objects inititalized right away:

Dim conn As New ADODB.Connection
conn.ConnectionString = GloConnectionString
conn.CursorLocation = adUseClient
conn.Open

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

rs.Open "SELECT * FROM files WHERE files.file_id = 1", conn

We have opened a connection and a recordset, and also declared our stream. To get our file back out, we open the stream, write to it from the recordset, and then save the data to a file, as follows:

mystream.Open
mystream.Write rs!File
mystream.SaveToFile "c:\\newimage.gif", adSaveCreateOverWrite

mystream.close
rs.Close
conn.Close

We load the binary data out of the recordset using the mystream.Write rs!file syntax, where rs!file is the field of the recordset that contains the binary data we will "write" to the stream. The SaveToFile method takes two arguments: the target location, and a variable that determines the stream's actions when a file exists. When adSaveCreateOverWrite is specified, existing files will be overwritten. When adSaveCreateNotExists is specified, files will not be overwritten if they exist.

Update File

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.

Sample Code #1

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"