FW: BLOBs - from files to code to DB and back

zryip theSlug zryip.theslug at gmail.com
Fri Dec 18 19:50:21 EST 2009

Hi Alex,
To read the binary contents of a file into a variable you need some tricks
1) open the file into binary read
2) read the file to the end
3) endode it in base64
To update the dataBase
4) use the name of the variable which contains your file instead of
the data itself
You'll obtain something like this :

answer file "Please select a file..."
put it into lFileToRead

-- Read the binary contents of a file into a variable
open file lFileToRead for binary read
read from file lFileToRead until end
put base64Encode(it) into lBinaryFile
close file lFileToRead
put revOpenDatabase("mysql", "localhost", "myDB", "root", "") into lDBConnector
put "INSERT INTO fileTable (title,myBlob) VALUES ('My blob',:1)" into
lSQLQuery -- :1, :2, etc will be replace by the content of the
respective variables passed to the revExecuteSQL command. i.e. the
lBinaryFile variable.
revExecuteSQL lDBConnector,lSQLQuery,"lBinaryFile"
revCloseDatabase lDBConnector

Now to read the blob in the database and to restore it in a file you need :
1) open file for binary write
2) decode the blob field

put revOpenDatabase("mysql", "", "myDB", "root", "") into lDBConnector
put theID into lRecordID
put "SELECT myBlob FROM fileTable WHERE id = :1" into lSQLQuery
put revQueryDatabase(lDBConnector,lSQLQuery,"lRecordID") into lTheRecord
put revDatabaseColumnNamed(lTheRecord,"myBlob") into lBinaryFile
ask file "Extract file from the DB as..."
put it into lFileToOpen
open file lFileToOpen for binary write
write base64decode(lBinaryFile) to file lFileToOpen
close file lFileToOpen
revCloseCursor lTheRecord
revCloseDatabase lDBConnector

In your database don't forget to choose the correct type for your blob
field (at least mediumblob, I board longblob)

Hope this helps.

-Zryip TheSlug- which you the best ! 8)

2009/12/18 Alex Adams <alex at a2technology.com>
> I need some guidance in accomplishing the following:
> - Read the binary contents of a file into a variable
> - Put the variable into a database BLOB field and update the database
> - Read from a database BLOB field into a variable
> - Recreate the file on any OS
> I could diddle around with this for a while and come up with methods of
> doing this, but a lot of you guys have more experience with rev than I do
> and you¹re probably better programmers to boot.
> All help is appreciated,
> --
> Alex Adams
> hawkVision ‹ tools for solving Wicked Problems
> (a)2 Technology Partners, Inc.
> 831-726-8013
> alex at a2tecnology.com
> www.a2technology.com
> www.promisstudio.com
> universalconnector.wordpress.com
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution

More information about the Use-livecode mailing list