Problem accessing binary data from mysql data base

[Don]

I am writing a macro using ADODB types to connect to a mySql database and
retrieve data. It works fine for fields in the record set that are varchar
but my database has two fields that store long text strings differently. One
is a blob (and in excel the recordset.fields(blobfield).type is 205. The
other is a binary text field (again - excel
recordset.fields(binarytextfiled).type is 204. I know both fields contain
ascii.

I had hoped I could extract data via getchunk methods but this fails. Any
suggesstions would be welcome.

 

[Tim]

I've used this in the past to access BLOB data from Oracle - might work in
your case...

'*************************
Dim oStream
Set oStream = CreateObject("adodb.stream")
With oStream
.Type = adTypeBinary
.Open
.Write (rs.Fields("content").Value)
.SaveToFile stempfile, adSaveCreateOverWrite 'stempfile is a temp
file path
.Close
End With
'*************************

Tim

 

[Don]

I did a quick test - and the data was written to a file.

So is the idea I write all these binary fields to a file, and then open the
file up and read it back?

I can do it but it sure seems awkward (not to mention extremely high
overhead of creating temp files for every field like this).

 

[Tim]

I'm not too worried about creating temp files unless v. large or v.
numerous - that what the temp folder is for. I wouldn't classify it as
"extremely high overhead".

How many records will you be processing ?

Tim

 

[Don]

I found I could write to the stream object from the recordset and then read
back from the stream into an array by backing up the .position property and
using the .read method without every having to create a file. I don't even
have to create more that one stream object. I just create one when I start
to process my recordset and close it when I am done.

Not quite what you suggested, but your idea was the inspiration that lead to
my final solution - Thanks!

BTW _ was worried about overhead because it is many thousands of records.
In fact had to upgrade to excel 2007 to get past the 64K rows limit.