MySQL AB :: Accessing MySQL BLOB columns using Visual Basic 6
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, 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
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
rs.Open "SELECT * FROM files WHERE 1=0", conn, adOpenStatic, adLockOptimistic
rs.AddNew
rs!file_name = "myimage.gif"
rs!file_size = mystream.size
rs!file = mystream.read
rs.Update
mystream.Close
rs.Close
conn.Close
Retreiving Data
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
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