import & export (binary) image data using SQLite

[bobcoffee]

I have been experimenting and banging my head against blobs for the last two days. I was hoping someone could help me out.
I would like to
// store binary image file in SQLite, then
// select binary image file from SQLite db, and
// put the binary data into an image object

there seems to be two methods that I can discern from reading forum posts
// method 1: keep everything in binary and don't use base64encode - base64decode
// method 2: use base64encode and base64decode before and after database manipulation

I have experimented extensively and have read relevant posts
http://forums.runrev.com/viewtopic.php? ... ary#p40458
http://forums.runrev.com/phpBB2/viewtop ... =49&t=8320

I can store and retrieve text and numbers from a database and display them in various way but this is the first time I have to work with images and audio.
1) I seem to be able to store 'something' in the database but I'm not sure what it is
2) when I go to retrieve it I retrieve this 'something' but it won't display
Here is some code that I have been fighting with

Code: Select all

     //attempt #1
     connectToDb  --wrapper handler for connecting and passing global dbID and error handling 
     answer file ""
     put URL ("binfile:" & it) into tImageData
     set the text of image "testImage" to tImageData --this displays correctly
     
     //update binary image file to db
     put "1" into tIndex
     put "UPDATE categories SET catImage = :1 WHERE catID_pk = :2" into tQuery
     revExecuteSQL gDatabaseID, tQuery, "*btImageData", "tIndex"  --'something' (binary data??) saved to blob type in db
     
     //try to extract text data from db
     put "1" into tIndex
     put "SELECT :1 FROM categories WHERE catID_pk = :2" into tQuery
     put revQueryDatabase(gDatabaseID, tQuery, "*bcatImage", "tIndex") into tCursorID
     put revDatabaseColumnNamed(tCursorID, "catImage") into tImageDataExtracted
     set the text of image "extracted" to tImageDataExtracted    --!!nothing displays
     revCloseCursor tCursorID
     closeDb gDatabaseID  --close db handler

nothing displays in image "extracted"
Note: I also tried all the combinations of including or not including *b. there are two locations above where *b is used but none of the 4 combinations work

The other variation is

Code: Select all

     //before saving to db
     put base64Encode(tImageData) into tDataEncoded

     //after extracting from db
     put base64Decode (tImageDataExtracted) into tImageDataExtracted

but I still can't display the image data after trying both methods.
NOTE: I also tried using both 'text' type and 'blob' types for the base64encoded data
If I can get that method to work, does anyone have any idea which db type (text or blob etc) I should use to store the encoded data?

Anyone have any ideas? I am completely exhausted.

Coffee

[phaworth]

Hi,

I believe the trick is to use a slightly different form of the revDatabaseColumnNamed command:

Code: Select all

put revDatabaseColumnNamed(tCursorID, "catImage","tImageDataExtracted") into tResult

This form puts the the contents of the catimage column directly into the timageDataExtracted variable. Check the dictionary entry for revColumnNamed for more info.


HTH,
Pete
http://www.lcsql.com

[bobcoffee]

I believe the trick is to use a slightly different form of the revDatabaseColumnNamed command:

Code: Select all

Code: Select all
    put revDatabaseColumnNamed(tCursorID, "catImage","tImageDataExtracted") into tResult

This form puts the the contents of the catimage column directly into the timageDataExtracted variable.

Thanks, Pete.
I tried that and it doesn't work. I mean, it would probably work if the function didn't throw an error...

There seems to be a more fundamental problem. When I step through the code I am getting
1) a value for the database Cursor for the variable tCursorID, but
2) I am also getting 'revdberr: invalid column name' at the revDatabasecolumnNamed line, however
3) I'm sure there is a column named 'catImage' in the db

this is driving me crazy.

Code: Select all

     //attempt #1
     connectToDb --wrapper handler for db connection error handling     
     put "1" into tIndex
     put "UPDATE categories SET catImage = :1 WHERE catID_pk = :2" into tQuery
     revExecuteSQL gDatabaseID, tQuery, "*btImageData", "tIndex"  --somethign is saved to blob type in db
     
     // extract text data from db
     put "1" into tIndex
     put "catImage" into tImageCol
     put "SELECT :1 FROM categories WHERE catID_pk = :2" into tQuery
     put revQueryDatabase(gDatabaseID, tQuery, "*btImageCol", "tIndex") into tCursorID
     //tCursorID is returned, but
     //revdberr: invalid column name appears for revDatabasecolumnNamed
     //But I'm sure there is a column named 'catImage' in the db
     put revDatabaseColumnNamed(tCursorID, "catImage", "tImageDataExtracted") into tErrorMsg
     set the text of image "extracted" to tImageDataExtracted  --!!nothing appears 
     revCloseCursor tCursorID
     closeDb gDatabaseID 

[phaworth]

It sounds like your SELECT statement is not finding any rows. I think you'd get the invalid column name error if the cursor has no rows in it.

Are you checking the result after revExecuteSQL? Should contain either 1 or an error message.

After revQueryDatabase, do revNumberOfRecords(tCursorID) and make sure it has 1 in it.

Do you have any sqlite admin tools? If you're on a Mac, sqlite3 comes preinstalled. Open a terminal window and:

sqlite3 <full path to your database>
SELECT catID_pk,catimage FROM categories WHERE catID_pk=1;
--you should see your ressults here, but not sure how it displays blobs
.exit

Pete
www.lcsql.com

[phaworth]

One other thing - I assume catImage is defined with a data type of BLOB in your schema? Shouldn't matter since sqlite lets you put any type of data into any column no matter what it's type, but it's possible LC might be doing things differently if it thinks the column isn't a blob.
Pete
www.lcsql.com

[bobcoffee]

Pete

Thanks a lot for giving me that advice. I was able to figure it out from your hints.

Here is the code that works for anyone that may need it in the future
(the db table name and columns will need to be adjusted etc.)

Code: Select all

    //put binary image into db
     connectToDb   --db connect wrapper function
     answer file ""
     put URL ("binfile:" & it) into tImageData
     put "1" into tIndex
     put "UPDATE categories SET catImage = :1 WHERE catID_pk = :2" into tQuery
     revExecuteSQL gDatabaseID, tQuery, "*btImageData", "tIndex"
     //!!WORKS    
     
     //retrieve binary image from db and display in image object
     put "SELECT * FROM categories WHERE catID_pk = :1" into tQuery
     put revQueryDatabase(gDatabaseID, tQuery, "tIndex") into tCursorID
     put revDatabaseColumnNamed(tCursorID, "catImage", "tImageData") into tErrorMsg
     put tImageData into image "extracted"
     revCloseCursor tCursorID
     closeDb gDatabaseID  --db close wrapper function
     //!!WORKS

the big three problems for me were
1) initially not using the 3 argument form of revDatabaseColumnNamed
eg. revDatabaseColumnNamed(recordSetID, columnName [, holderVariable ]) with the holderVariable for storing the binary data not the two argument form
(as it says in the dictionary -heh )

2) initially not including the *b when using updating or adding binary records to the db:

Code: Select all

 revExecuteSQL   gDatabaseID, tQuery, "*btImageData"

apparently a *b is not needed when reading from the db as in:

Code: Select all

put "SELECT * FROM categories WHERE catID_pk = :1" into tQuery
put revQueryDatabase(gDatabaseID, tQuery, "tIndex") into tCursorID
put revDatabaseColumnNamed(tCursorID, "catImage", "tImageData") into tErrorMsg

however the following also works

Code: Select all

//the only difference from the code above is the *b 
put revDatabaseColumnNamed(tCursorID, "*bcatImage", "tImageData") into tErrorMsg

and
3) trying to use a variable for a column name when you can't use that syntax as in

Code: Select all

//wrong way
     put "UPDATE :1 SET catImage = :2 WHERE catID_pk = :3" into tQuery

//right way
     put "UPDATE categories SET catImage = :1 WHERE catID_pk = :2" into tQuery

thanks again
coffee

[phaworth]

Great glad you got it working.
Pete