Insert BLOB into SQLite ?

[USCode]

Given we will soon have the new UseSQLiteDatabase() function for 4.20 ...
How do I go about inserting a BLOB (e.g. image file, word processing document, etc.) into an SQLite database BLOB column?
Anyone have a simple example they'd care to share?

[Rook Zimbabwe]

UseODBCDatabase()

I can only think of how to do it with Access... I don't think you can do this with SQLLite yet...

You may be able to use OpenOffices XML formats to get what you want. I have been too busy to poke at them but there are info pages all over the net on the new OO Format. Google is your friend.

[pdwyer]

Using the API method you would do this:
http://www.purebasic.fr/english/viewtop ... qlite+blob

Since it can all be in the sql statement you should be able to pass a statement the same way that's mentioned on this link with other libs.

Not sure about retrieval with the PB lib, I haven't used the beta much for that. API code works fine though getting a pointer.

This should be the same whether you use the callback function or the prep-step-finalise approach. Not sure about that get-table way though, I guess it will chuck all of it to memory that way which could be kind of ugly for a big record set full of blobs

[USCode]

From poking around the web, it seems like I could maybe read the file a byte at a time, convert to hexadecimal and then a maybe concatenate it all into a string, which could then be inserted into the field?
Looks like SQLite 3 supports inserting hexadecimal data into a BLOB field like this:
X'AABBCCDD'
I'll keep digging, thanks.

[pdwyer]

Why poke around on the web? The method to do it and the functions for conversion are all written for you in my post linked above.

Examples of using that format are there, you just need to copy/paste and run to see how it works using exactly what you have just said

[netmaestro]

Here's one method that you can use for any type of binary data or file, example uses an image. Base64Encoder() is the tool used to convert the binary data to text, which can then be stored in a character field.

Code: Select all

; get an image into memory via ReadFile

UsePNGImageDecoder()
UseJPEGImageDecoder()
UseTIFFImageDecoder()
Pattern$ = "Image Files (*.bmp, *.jpg, *.png, *.tif)|*.bmp;*.jpg;*.png;*.tif" 
imagefile$ = OpenFileRequester("Choose an image file", "", pattern$, 0) 

If ReadFile(0, imagefile$)
  *mem = AllocateMemory(Lof(0))
  ReadData(0, *mem, Lof(0))
  CloseFile(0)
  encoded$ = Space(MemorySize(*mem)*1.35)
  result = Base64Encoder(*mem,MemorySize(*mem),@encoded$,Len(encoded$))
  If result
    encoded$ = RTrim(encoded$)
  EndIf
EndIf

; Create a database to contain the image and its filename
ImportC("sqlite3.lib")
  sqlite3_open(dbname$, *hDB)
EndImport
If filesize("testimg.db") <> -1
  DeleteFile("testimg.db")
EndIf
sqlite3_open("testimg.db", @hdb.l)

; Now open it and save the values, close it up
UseSQLiteDatabase()
OpenDatabase(1, "testimg.db",#NULL$,#NULL$)
DatabaseUpdate(1, "CREATE TABLE Table1 (filename, picture)")
DatabaseUpdate(1, "INSERT INTO Table1 (filename, picture) VALUES ('" + imagefile$ + "', '" + encoded$ + "')") 
CloseDatabase(1)
  
; Now open up the database and read the image and filename from it
OpenDatabase(1, "testimg.db",#NULL$,#NULL$)  
DatabaseQuery(1, "SELECT * FROM Table1")
If firstdatabaserow(1)
  imagefile$ = GetDatabaseString(1,0)
  encoded$ = GetDatabaseString(1,1)
  CloseDatabase(1)  
  *mem = AllocateMemory(Len(encoded$)*1.35)
  result = Base64Decoder(@encoded$,Len(encoded$),*mem,MemorySize(*mem))
  If result
    ReAllocateMemory(*mem, result)
    CatchImage(0, *mem)
    OpenWindow(0,0,0,ImageWidth(0),ImageHeight(0),imagefile$,#PB_Window_ScreenCentered|#PB_Window_SystemMenu)
    CreateGadgetList(WindowID(0))
    ImageGadget(0,0,0,0,0,ImageID(0))
    Repeat:Until WaitWindowEvent() = #PB_Event_CloseWindow
  EndIf
EndIf

All code is native, only one import from PB's Sqlite3 lib is necessary to create the non-preexisting database. If all goes well you should see a window opened showing your image. It was read from disk, saved in the database, read back from the database, restored and shown in a window. You should find something usable here, one approach anyway. There are others.

[pdwyer]

With this approach (if I'm reading it correctly) you're just saving the text in a text field are you? So there will be a size increase of about 25% and a decode required.

Or am I not understanding

[USCode]

Why poke around on the web? The method to do it and the functions for conversion are all written for you in my post linked above.

Examples of using that format are there, you just need to copy/paste and run to see how it works using exactly what you have just said

Thanks Paul. Sorry I missed your post somehow, looks really good, thanks again.