revDatabaseColumnNamed

[Devin Asay]

On Jun 4, 2009, at 3:38 PM, Björnke von Gierke wrote:

>>> Can anyone tell me how to store and retrieve binary data using sql,
>>> without it being changed in between?
>
<snip>
examples omitted
</snip>
>
> any other ideas?
> Björnke

Björnke,

Sorry I wasn't paying closer attention to this thread before. I upload  
and download binary data (mostly audio and images) to a MySQL database  
all the time. It's quick and reliable. Here is a sample handler that  
does a microphone check before the user starts a recording task (watch  
out for line wraps):

command putAndGetSndCheck
    doDBconnect  -- a handler that creates the DB connection, puts it  
into gConnID

    ## save user's ID in the DB along with the audio data
    put the currUser of this stack into tUserID

    ## suck up data from an existing audio file (MP3 in this case)
    ##  into a variable
    put "binfile:" & gLastRecording into tFileNm
    put url tFileNm into tBits

    ## create a record in the DB that contains the binary data
    put "INSERT INTO soundcheck (user,data) VALUES (:1,:2)" into tQuery
    revExecuteSQL gConnID, tQuery,"tUserID","*btBits"
    if the result is not a number then
       answer the result as sheet
    end if

    ## each record with audio data is assigned an integer id by the  
database,
    ##  in a column named 'snd_id'
    ##  This query retrieves that integer
    put revDataFromQuery(,,gConnID,"SELECT LAST_INSERT_ID()") into  
tRecordID

    # fetch recording from server
    wait 5 ticks # just to make sure the upload has finished

    ## create a file name where you want to save the binary data
    put specialFolderPath("Temporary") & "/" & the currUser of this  
stack & \
	"-SndCheck.mp3" into tSaveFile

    ## retrieve the audio data just uploaded
    put "SELECT data FROM soundcheck where snd_id = " & tRecordID into  
tQuery
    put revQueryDatabaseBLOB(gConnID,tQuery) into tCurs
    get the result
    if it is not a number then
       answer error "Could not get the recording from database:" & it  
as sheet
    else
       put empty into tRecData # declare variable to receive data
       put revDatabaseColumnNamed(tCurs,"data","tRecData")

       set the fileType to "QVODMp3 " # sets a make creator & file type
       put tRecData into url ("binfile:" & tSaveFile)

       ## play the retrieved audio file in a player object
       set the filename of player "playback" to empty
       wait 1 millisecond
       set the filename of player "playback" to tSaveFile
    end if

    ## clean up
    revCloseCursor tCurs
    closedb gConnID
end putAndGetSndCheck

As long as your network connection is reasonably fast and reliable,  
this approach should work well.

HTH

Devin

Devin Asay
Humanities Technology and Research Support Center
Brigham Young University