import & export (binary) image data using SQLite

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
bobcoffee
Posts: 10
Joined: Tue Jan 15, 2013 7:24 am

import & export (binary) image data using SQLite

Post by bobcoffee » Sat Mar 16, 2013 9:04 am

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
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: import & export (binary) image data using SQLite

Post by phaworth » Sat Mar 16, 2013 8:30 pm

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
Posts: 10
Joined: Tue Jan 15, 2013 7:24 am

Re: import & export (binary) image data using SQLite

Post by bobcoffee » Sun Mar 17, 2013 1:08 am

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
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: import & export (binary) image data using SQLite

Post by phaworth » Sun Mar 17, 2013 2:20 am

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
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: import & export (binary) image data using SQLite

Post by phaworth » Sun Mar 17, 2013 2:32 am

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
Posts: 10
Joined: Tue Jan 15, 2013 7:24 am

Re: import & export (binary) image data using SQLite

Post by bobcoffee » Sun Mar 17, 2013 7:30 am

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
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: import & export (binary) image data using SQLite

Post by phaworth » Sun Mar 17, 2013 8:10 pm

Great glad you got it working.
Pete

Post Reply

Return to “Databases”