Accessing MySQL BLOB columns using Visual Basic 6
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.
Any chance of a visual basic 2005 or .net version of this. Having problems getting it to work. Im likely to turn to SQL Server Express soon if i cant get any answers.
Thanks
This sample will not work! Compare Bug-Report 19065 under mysql.org
Please let me know how to update the fields in the database. The update is not possible after retrieving the details from the table…
how can the picture be shown in the picture box or image in a form?? pls give me the code..
Does any body know how to call a stored procedure from MySQL in VB6.0
Jed. Try this. http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html
I save the image to a temporary file and then use LoadPicture to load it into a picture box. If anyone has a better way to do this please post =)
Hello! Good Site! Thanks you! xjdxmwdikla
this code is running, but cannot update the image in the field….
I think to update the image, you should delete the current record the add a new record again with the same data in the deleted record then insert the new image …(^___^*)
Help please I really nedd it . My problem is what I have the data base conected to visual basic 6 ( and evething is good, the sintaxi to conect it and others things are ok) and I can add only 1 Record in the data base MYSQL, then when i want to add new Rocord since Visual basic, the RECORD that I had added was replaced with the 2 Record that i wanted to add. what is the problem. why can I add only 1 Record, and Why can I add 20 Record One after other one.
please help……
Rogelio you must produce your code in a sample, which everyone can understand. Otherwise nobody can assist you.
HI again Thanks for your Help Thomas , But I’ve resolved the problem
Now I have another problem.
The problem is that when I wanna add recor whith the same name or ID for example there is an error, Because the ID is my primary key in my data base and doesn’t aceept duplicate. so i need a funtion to validate this opcion. this is my code that im triying to do:
Private Function ValidarRegistro() As Boolean
Dim rsTemp As New ADODB.Recordset
rsTemp.Open “Select Cod_Alum from alum”, conn, adOpenStatic, adLockOptimistic
If Me.Text14.Text = “” & rsTemp(“Codigo_Alumno”) Then
MsgBox ” You cant put the same value ”
End If
ValidarRegistro = True
End Function
I have the table Alum whit many fields but I onl wanna validate the ID.
All working accept update command? any solution to this bug yet?..error cannot update field blaa. blaa.. read bout this bug in mysql forum
to regelio…
if your ID is your primary key then you dont have to validate it for a code. just put an error routine bec. i am very much sure that it will generate an error when duplicated.
for example:
On Error Goto ErrorRoutine
ErrorRoutine:
If Err.number = Then
MsgBox “ID already exist. Please re-enter.”
txt you so much, my problem was solve…but i hav problem in my CrystalReport in vb6..can someone help me about this matter..i want to display the current record that is displayed in the recordset, but it displays all when i preview,,..please help me…GOD Bless…….
Please help make a program using Visual Basic 6.0 as front-end and MYSQl as back-end?
This is a big part of our subject project.
Reply me pls….
I have a problem… I only want the Retreiving Data step, but I have an error… it says in spanish: “Error 3001 en tiempo de ejecucion. Argumentos incorrectos, fuera del intervaloo en conflicto con otros”
Please help me!!
The message is “Arguments are of the wrong type, out of acceptable range or are in conflict with one another”
Hello staff.
My problem: following the steps of this article, I had problems in time to see the image of the bank.
…
mystream.Type = adTypeBinary
mystream.Open
mystream.Write rs!image
mystream.SaveToFile “c:\newimage.gif”, adSaveCreateOverWrite
…
The image has generated size of 1k and can not be read by any viewer of image.
someone has had this problem?
Thank’s
(Sorry for English)
Does any body successfully UPDATE your BLOB field with new images? I am having problem trying to UPDATE and Existing BLOB field after INSERT
Hi, to Update Your Blob field, Please specify Rs.CursorLocation = adUseClient (Default is adUseServer)
If you have others field in your table, do not select them in the SELECT Statement When you Open the RecordSet, Only Select Those Field that you want to update from the table else you will have the E_FAIL error, if some of the field is NULL or empty your will not able to update it
TABLE Have field1 to field10
Rs.CursorLocation = adUseClient
SELECT * FROM Table
Rs.CursorLocation = adUseClient
SELECT field1,field2 FROM Table ‘Only select field1 and field2
You can check if you get different result from the query you will see the problem
check you have any DateField WHICH is ’0000-00-00′ if bring error to you when you update the rs
Can this method be used with a parameter query where the parameters come from a DAP?
how to create database and table on mysql
Know how to do the same but using a picturebox instead of a file (the image is first loaded into a picturebox) and a stored procedure to save the image into a table column?
In vb you can use the LoadPicture method, manage to put the path of the file in the tag property of your picturebox.
I still couldn’t find a way to update an existing blob (except for deleting the record to insert again), does someone have any news?
is it possible to update images in the database with using a SELECT query? INSERT or UPDATE may do.
hi to all! i’ve tried all the steps shown on how to save images to mysql from vb6 but i get an error,an error which says that the column in my table which is of blob type cannot be null. i’ve already read the image using mystream.read but still nothing. any1 can help me? tnx!
Hello evryone. Is anybody know how to view/retrieve image from MySQL (blob) into picture box in visual basic 6? Because the idea given here is only to save to c:\filename.gif. I want it directly to picture box without saving/retrieving it to c:\ directory. Just to view it only to picture box..
Any solution is very much appreciated.
Thanks in advance…
More powers to everybody.
this site is very important and helpful to developers, I would like to participate fully. thank you keep it up.
“Hello evryone. Is anybody know how to view/retrieve image from MySQL (blob) into picture box in visual basic 6? Because the idea given here is only to save to c:\filename.gif. I want it directly to picture box without saving/retrieving it to c:\ directory. Just to view it only to picture box..
Any solution is very much appreciated.
Thanks in advance…
More powers to everybody.”
————————————————-
Loading Picture to a Picture Box
Picture1.Picture = LoadPicture (FileName)
@jimnast
glad i came back here, well about your concern, there is always a solution for that. you must have a source code that will read the blob field and will convert the actual datafile to temporary file which can be loaded automatically using this command:
If ShowPic(rsFill!photo, Image1) = False Then
Set Image1.Picture = LoadPicture(App.Path & “\pics\temp.jpg”)
End If
if the function ShowPic returns true it means that the picture from the blob field (photo) was already loaded to Image1, which is either an image or picturebox control. And if it returns false then load a picture from directory as temporary.
Well, try to look for this source as i’m using this in my SQL server DB. Consider this one if it may help you.
Public Function ShowPic(ByRef fieldname As Field, ByRef cImage As Object) As Boolean
On Error GoTo Out
ShowPic = False
DataFile = 1
Open “pictemp” For Binary Access Write As DataFile
Fl = fieldname.ActualSize ‘ Length of data in file
If Fl = 0 Then Close DataFile: Exit Function
Chunks = Fl \ ChunkSize
Fragment = Fl Mod ChunkSize
ReDim Chunk(Fragment)
Chunk() = fieldname.GetChunk(Fragment)
Put DataFile, , Chunk()
For i = 1 To Chunks
ReDim Buffer(ChunkSize)
Chunk() = fieldname.GetChunk(ChunkSize)
Put DataFile, , Chunk()
Next i
Close DataFile
Filename = “pictemp”
‘ShowPic = LoadPicture(FileName)
cImage.Picture = LoadPicture(Filename)
ShowPic = True
Exit Function
Out:
ShowPic = False
End Function
i forgot this one, declaration of variables…
Dim DataFile As Integer, Fl As Long, Chunks As Integer
Dim Fragment As Integer, Chunk() As Byte, i As Integer, Filename As String
Private Const ChunkSize As Integer = 16384
Private Const conChunkSize = 100
muy bien todo pero yo necesito conectar visual basic con mysql 5.0 tengo el conector odbc 3.5,,,, no se como conectar con la base de datos ni donde colocar la programacion ayuda por fa. y como buscar modifiocar agregar y eliminar
Hi! Everyone..I need help how to retrieve fast my table with LONGBLOB field, the table has a thousands of images.
Here is my code and it seems that the recordset took a while before proceeding with the next command.
With rdoIMG
.Open “SELECT * FROM CHEQUES”, cnn, adOpenStatic, adLockOptimistic
End With
I tried also this one but still took a while to process…
Dim cnMySql As New rdoConnection
Dim rdoQry As New rdoQuery
Dim rdoRS As rdoResultset
With rdoQry
.Name = “test”
.SQL = “select * from CHEQUES”
.RowsetSize = 1
Set .ActiveConnection = cnMySql
Set rdoRS = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
End With
Appreciate much your kind support…
This is a very important post, I was looking for this knowledge. Just so you know I found your blog when I was searching for blogs like mine, so please check out my site sometime and leave me a comment to let me know what you think.
I am using VB6 to insert image into mysql database when i clik my submit button everything is happening without any error but the image which i have declared as mediumblob is not inserting into the database Please help
Really excellent desgin of your web page. It’s individual and compares for your posts. Don´t give up and make your own factor!
way in vista
not work
Set file = New ADODB.Stream
file.Type = adTypeBinary
file.Open
file.LoadFromFile (Text6.Text)
RSSOAL!flash = file.Read
RSSOAL.Update
file not update in my database