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.
I would recommend you begin by making sure you have the latest service pack for Visual Basic installed. 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.8 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).
NOTEVersion 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.
MySQL and BLOBs - Connection String
Ok, now that we have configured MySQL and VB, we can move on to writing the client app. First, let's 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.
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 values; 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.
2048 - Enable The MySQL Compressed Protocol.
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 website, found here.
MySQL and BLOBs - 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 instantiate it. The alternative syntax to this is to type:
Dim conn As New ADODB.Connection
While I previously recommended the 'as New' syntax, I have since learned that it slows down your code since it must check if the object is instantiated 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 http://www.vbmysql.com/articles/cursorsandlocks.html.) In the final line we open the connection object, which we will of course have to close when we are finished with conn.close.
MySQL and BLOBs - 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.
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.
MySQL and BLOBs - 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, let's get the connection and recordset objects initialized 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:
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.
MySQL and BLOBs - Update File - We're Done!
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
