VBMySQL.com

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.

I would reccomend 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 MyODBC (currently 3.51.06) 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 SET max_allowed_packet=15M;), or use the SET max_allowed_packet=15M; syntax from within MySQL.

Connection String

Ok, 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 (MyODBC), 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, found here.

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

Ok, so 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.