Inserting images as BLOBs - any way to avoid using base64encode?

[Devin Asay]

On Aug 6, 2007, at 5:38 PM, Terry Judd wrote:

> Hi Jan - I'm already doing something along these lines except with the
> base64encode/decode routines thrown in. Getting the image into the  
> database
> without encoding seems? to work ok but when I pull it out I can  
> only get
> hold of the first few characters, typically something like 'âPNG'  
> and a
> couple of returns.

Terry,

I just had a very similar problem that I finally solved after much  
hair pulling. Maybe what I discovered will be of use to you.

I was trying to download audio files (MP3) from a database, and used  
the approach that Jan outlined. But oddly enough, one of my  
revDatabaseColumnNamed function calls would always fail, while  
another, almost identical one, worked. I would get data very similar  
to what you are describing--four characters and nothing else. I  
finally discovered that the function fails consistently, *unless* you  
access the contents of the variable later. So I was doing something  
like this:
(watch for line wraps)

##
put "SELECT audio1,audio2 FROM my_db" into tQuery
put revDatabaseQueryBLOB(gConnection, tQuery) into tCursor
if tCursor is not a number then
   answer error tCursor
else
   put revDatabaseColumnNamed(tCursor,"audio1","tBinData1")
   put revDatabaseColumnNamed(tCursor,"audio2","tBinData2")

   ## then, a few lines later...

   put tBinData1 into ("binfile:" & specialFolderPath("Temporary") &  
"/myfile1.mp3"
   put tAnotherVariable into ("binfile:" & specialFolderPath 
("Temporary") & "/myfile2.mp3"
   revCloseCursor tCursor
end if
##

In other words, I inadvertently was using the incorrect variable when  
trying to save out the audio2 data. It was only after I noticed that  
and changed tAnotherVariable to tBinData2 that the  
revDatabaseColumnNamed function began to work properly. The lesson I  
took from this--and maybe it's the wrong one--is that when using  
revDatabaseColumnNamed to retrieve binary data from a query cursor,  
the variable you're loading it into *must* be accessed later in  
another statement, otherwise the download fails. It's almost like the  
variable doesn't become a valid variable unless it's accessed later.

HTH

Devin


Devin Asay
Humanities Technology and Research Support Center
Brigham Young University