Write/Read Picture to/from SQLLite

Databases
1

Hi, I want to read and write to/from SQLLite database from a Picture object. I have my database connection set up and have a blob column and an image sat in my Picture object now what? And in reverse how do I get my blob field back into my Picture object. Thanks

2

Check out the DatabasePictureTest example.

3

Paul thanks. My code as follows is in the DropObject event of a Canvas:

 Dim sSound as Sound
 Dim p as Picture
 Dim dbRec as new DatabaseRecord
  
  
  if obj.FolderItemAvailable = true then
    p = picture.Open (obj.FolderItem)
  end if
  
  sSound = camerashutterclick01
  sSound.Play
  
  
  dbRec.PictureColumn("IMG_IMAGE") = obj.Picture
  
  MyDatabase.InsertRecord("tblImages",dbRec)
  
  CheckDatabaseErrors
  
  MyDatabase.Commit

However, I keep getting the exception " near “)”:syntax error " and I cant for the life of me see why…

4

Ok sorted one issue. I was declaring RealSQLDatabase and not SQLiteDatabase. So I am now writing the record to the database ok but the blob column is Null.

5

Hmmm odd. So changes as above to the declare, code now reads as below. If I insert a breakpoint at MyDatabase.Insertrecord… I can definately see that p has been assigned to dbRec but I am still getting Null in my database column.

[code] Dim sSound as Sound
Dim p as Picture

Dim dbRec as new DatabaseRecord

if obj.FolderItemAvailable = true then
p = picture.Open (obj.FolderItem)
end if

sSound = camerashutterclick01
sSound.Play

dbRec.PictureColumn(“IMG_IMAGE”) = p

MyDatabase.InsertRecord(“tblImages”,dbRec)

CheckDatabaseErrors

MyDatabase.Commit

[/code]

6

The table tblImages only has one column called IMG_IMAGE? Do you have any other required columns that could be causing the INSERT to fail?

7

I wonder if PictureColumn even works. Try using BlobColumn with JPEG or PNG data (depending on your needs) from picture.GetData.

8

No, I have and id column auto increment, title column and caption column. Sussed it though, I was looking at my database using SQLite Professional from the MAS. Unfortunately it appears they have a bug which doesn’t show blob data in their db browser it shows as null. I have now read my database back into my app and can display the pictures stored in each record.

That could have kept me guessing. Makes you wonder how much testing these folks do before they deploy to the app store.