Article Discussion: 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.


Read the full article here: MySQL and BLOBs

Hi,

I find your program very useful and tried to create a form in MSAccess to pull and push the blobs. The push works fine, but when trying to pull the blob (mystream.write) I get this error message: Arguments are of wrong type, are out of acceptable range or are in conflict with one another.



Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=localhost;" _
& "DATABASE=testmediarepo;" _
& "UID=root;" _
& "PWD=;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
conn.CursorLocation = adUseClient
conn.Open

'OPEN RECORDSET
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim openStr As String
Dim regstr As String
Dim FileStr As String

'OPEN RECORDSET TO READ BLOB
If REGDATE <> "" Then
regstr = Trim(Str(DatePart("yyyy", REGDATE))) & Trim(Str(DatePart("m", REGDATE))) & Trim(Str(DatePart("d", REGDATE))) & Trim(Str(DatePart("h", REGDATE))) & Trim(Str(DatePart("n", REGDATE))) & Trim(Str(DatePart("s", REGDATE)))
openStr = "Select * from OVERVIEW_DESCRIPTION WHERE REGDATE=" & regstr

rs.Open openStr, conn
'OPEN STREAM
Dim mystream As New ADODB.Stream
' Set mystream = New ADODB.Stream
mystream.Type = adTypeBinary
mystream.Open
FileStr = "c:\newimage5." & Right(Trim(MAPSOURCE), 3)


mystream.Write rs!TOURMAP


FileStr = "c:\newimage5." & Right(Trim(MAPSOURCE), 3)
mystream.SaveToFile FileStr, adSaveCreateOverWrite
Image29.Picture = FileStr
Image29.Visible = True
mystream.Close
rs.Close
End If

Err_Resume:
conn.Close
Exit Sub
Err_Load:
MsgBox Err.Description
Resume Err_Resume

conn.Close


ssaamg

This is a great article thanks.

My situation is slightly different though and I wondered if you had any idea if this solution might still work.

My web server is a windows 2003 server and all my forms and pages are in ASP (all the same so far) but the mySQL server i wish to use is actually living on a seperate UNIX box. In theory should all I need to do is update the connection string?

Hope so

Any help would be great.

Regards
Chris

You might change this length of code to:

regstr = Format(Trim(Str(REGDATE)),"yyyymdhns")

or even just

regstr = Format(Str(REGDATE),"yyyymdhns")

convention would list the data type first in the variable name - strReg