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.
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).
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.
Tags: database design, database technology, mySQL, sql programming, sql tips, tutorials
Comment on "MySQL and BLOBs"
| Related News: |
