Save and retrieve picture from sqlite Database

[Pierre Mercier]

Need to save and retrieve picture using Sqlite Db using Purebasic.
Thank for your support.

[srod]

It has been asked lots of times before. Use the search button and you should be able to quickly find an example or two.

[pdwyer]

I can't retrieve!

How can you with these?

GetDatabaseDouble
GetDatabaseFloat
GetDatabaseLong
GetDatabaseQuad
GetDatabaseString

Perhaps I'm missing something, I'm new to PB's db engine.

I posted a question about in the general discussion forum. Netmeastro has some code up there but he encode64's it then save it into the db as a string. It's a workaround I suppose.

http://www.purebasic.fr/english/viewtop ... abase+blob

[Tranquil]

Dont even know if it is possible to store real binary in a db. But then a command like GetDatabaseBinary() and SetDatabaseBinary() would be nice.

Atm I would use encode my binary too.

[pdwyer]

It is possible, I'm just not sure if PB's native cmds can do it. I've held raw files in sqlite dbs

Perhaps what we need is:

Code: Select all

Datalen.l = GetDatabaseLen(#Database, Column)
*Data = GetDatabaseData(#Database, Column)

This is basically what you do with the SQLite API.

You can bind a var to insert or insert in hex with a X'' prefix

[Edwin Knoppert]

Binary data is possible (any char), it is called a blob field.
Search for blob and sqlite..

[netmaestro]

Try this out (also uses pdwyer's nice memory database tip):

Code: Select all

; Save and retrieve picture from SQLite database 
; netmaestro, May 2008 

; Convert the image file to a base64 string 
If ReadFile(0, #PB_Compiler_Home + "examples\sources\data\purebasiclogo.bmp") 
  *inputbuffer = AllocateMemory(Lof(0)) 
  If *inputbuffer 
    ReadData(0,*inputbuffer, Lof(0)) 
    *encodedbuffer = AllocateMemory(MemorySize(*inputbuffer) * 1.35) 
    If *encodedbuffer 
      encodedlength = Base64Encoder(*inputbuffer, MemorySize(*inputbuffer), *encodedbuffer, MemorySize(*encodedbuffer)) 
    EndIf 
    CloseFile(0) 
    FreeMemory(*inputbuffer) 
  EndIf 
EndIf 

pic64string.s = PeekS(*encodedbuffer) ; We'll save this in the database 
FreeMemory(*encodedbuffer) 

UseSQLiteDatabase() 
If OpenDatabase(0, ":memory:", "", "") 
    DatabaseUpdate(0, "CREATE TABLE MyTable (test VARCHAR);") 
      
    ; Save the string        
    DatabaseUpdate(0, "insert into MyTable (test) values('" + pic64string+ "')") 
    
    ; Retrieve the string 
    DatabaseQuery(0, "SELECT * FROM MyTable") 
    If NextDatabaseRow(0) 
      pic64in.s = GetDatabaseString(0, 0) 
      
      ; Decode it 
      *decodedbuffer = AllocateMemory(Len(pic64in)) 
      Base64Decoder(@pic64in, Len(pic64in), *decodedbuffer, Len(pic64in)) 
      
      ; Convert it to an image 
      CatchImage(0, *decodedbuffer) 
      FreeMemory(*decodedbuffer) 
      
      ; Look at it.. 
      OpenWindow(0,0,0,ImageWidth(0),ImageHeight(0),"Image Test") 
      CreateGadgetList(WindowID(0)) 
      ImageGadget(0,0,0,0,0,ImageID(0)) 
      Repeat:Until WaitWindowEvent()=#PB_Event_CloseWindow 
      
      CloseDatabase(0)
      
    EndIf 
Else 
    Debug DatabaseError()    
EndIf 

I'm sure a blob would be more efficient, but this does work well for me. The downside of this method is that the stored images will take up around 30% more space than they really need to. If you're storing bmp's, you could use PackMemory before the Base64Encoder and you'd actually save some space, but for jpeg's or png's that won't help.

[Pierre Mercier]

THANK YOU FOR ALL YOUR HELP.

THANK'S NETMAESTRO FOR YOUR CODE.

FROM YOUR CODE I TRIED TO HAVE AN IMAGE SAVER. BUT I HAVE AN ERROR MESSAGE #IMAGE OBJECT NOT INITIALIZE.

HERE IS MY CODE USING PURE BASIC 4.2

;- Gadget Constants
;
;Window Constant

Enumeration
#Window_0
#Window_1
EndEnumeration

;- DATABASE Constants
Enumeration
#Database = 0
EndEnumeration

Enumeration
#Frame3D_0
#Image_0
#Button_SavePic
#Button_Begin
#Button_Previous
#Button_Next
#Button_End
#String_PicName
EndEnumeration

;- Image Plugins

;variable
Global StrgPath.s
Global SQLiteDatabaseName.s
Global StrgQuery.s
Global StrgPic64.s
Global StrgImagename.s ;Image id To save
Global StrgPicName.s ;Picture name in the database
Global StrgPattern.s



; get an image into memory via ReadFile

Procedure.s ExePath() ;To find exe path For database path
ExePath$ = Space(100)
GetModuleFileName_(0,@ExePath$,100)
ProcedureReturn GetPathPart(ExePath$)
EndProcedure
; Save picture name and picture in the database
Procedure SavePic()
UsePNGImageDecoder()
UseJPEGImageDecoder()
UseTIFFImageDecoder()
StrgPattern = "Image Files (*.bmp, *.jpg, *.png, *.tif)|*.bmp;*.jpg;*.png;*.tif"
StrgImageName = OpenFileRequester("Choose an image file", "", StrgPattern, 0)
; Convert the image file to a base64 string
If ReadFile(0, StrgImageName)
*inputbuffer = AllocateMemory(Lof(0))
If *inputbuffer
ReadData(0,*inputbuffer, Lof(0))
*encodedbuffer = AllocateMemory(MemorySize(*inputbuffer) * 1.35)
If *encodedbuffer
encodedlength = Base64Encoder(*inputbuffer, MemorySize(*inputbuffer), *encodedbuffer, MemorySize(*encodedbuffer))
EndIf
CloseFile(0)
FreeMemory(*inputbuffer)
EndIf
EndIf

StrgPic64 = PeekS(*encodedbuffer) ; We'll save this in the database
FreeMemory(*encodedbuffer)

; Save the string (IMAGE)

StrgQuery = "INSERT INTO Lit_Metal (Picture_Name,Picture)Values( '" + StrgImageName + "','" + StrgPic64 + "');"
DatabaseUpdate(0,StrgQuery)
EndProcedure

Procedure GetPic()
; Retrieve the string
StrgPic64 = GetDatabaseString(0, 1)
; Decode it
*decodedbuffer = AllocateMemory(Len(StrgPic64))
Base64Decoder(@StrgPic64, Len(StrgPic64), *decodedbuffer, Len(StrgPic64))
; Convert it to an image
CatchImage(0, *decodedbuffer)
;FreeMemory(*decodedbuffer)
EndProcedure

Procedure OpenWindow_0()
If OpenWindow(#Window_0, 443, 0, 315, 323, "", #PB_Window_SystemMenu | #PB_Window_SizeGadget | #PB_Window_TitleBar )
If CreateGadgetList(WindowID(#Window_0))
Frame3DGadget(#Frame3D_0, 5, 5, 300, 225, "")
ImageGadget(#Image_0, 5, 5, 0, 0, 0)
ButtonGadget(#Button_SavePic, 10, 270, 100, 40, "Sauvegarder")
ButtonGadget(#Button_Begin, 120, 280, 50, 30, "Début")
ButtonGadget(#Button_Previous, 170, 280,40, 30, "<")
ButtonGadget(#Button_Next, 210, 280, 40, 30, ">")
ButtonGadget(#Button_End, 250, 280, 50, 30, "Fin")
StringGadget(#String_PicName,10,238,290,25,"")
EndIf
EndIf
EndProcedure

ExePath()
StrgPath = ExePath() ;initializing StrgPath
SQLiteDatabaseName = "M:\IMAGE A db\DB\FBG.S3DB"
;SQLiteDatabaseName$ = StrgPath + "DB\FBG.s3db" ; ID Database
result = UseSQLiteDatabase() ; opening sqlite library
result = OpenDatabase(0,SQLiteDatabaseName,User$,Password$)
If Result = 0
MessageRequester("","Database / Base de donnée ???") ;Can't find the database
EndIf
DatabaseQuery(0, "SELECT * FROM Lit_Metal")
FirstDatabaseRow(0)
StrgImageName = GetDatabaseString(0,0)
OpenWindow_0() ;Opening main window, waiting For event
SetGadgetText(#String_PicName,StrgImageName)
Repeat
Event = WaitWindowEvent() ; This line waits until an event is received from Windows

WindowID = EventWindow() ; The Window where the event is generated, can be used in the gadget procedures

GadgetID = EventGadget() ; Is it a gadget event?

EventType = EventType() ; The event type


If Event = #PB_Event_CloseWindow
CloseDatabase(0)
End
ElseIf Event = #PB_Event_Gadget
If GadgetID = #Button_SavePic
SavePic()
getPic()
ElseIf gadgetID = #Button_Begin
result = FirstDatabaseRow(0)
If result
StrgPicname = GetDatabaseString(0,0)
SetGadgetText(#String_PicName,StrgPicname)
GetPic()
SetGadgetState(#Image_0,ImageID(0))
EndIf
ElseIf gadgetID = #Button_Previous
result = PreviousDatabaseRow(0)
If result
StrgPicname = GetDatabaseString(0,0)
SetGadgetText(#String_PicName,StrgPicname)
GetPic()
SetGadgetState(#Image_0, ImageID(0))
EndIf
ElseIf gadgetID = #Button_Next
result = NextDatabaseRow(0)
If result
StrgPicname = GetDatabaseString(0,0)
SetGadgetText(#String_PicName,StrgPicname)
GetPic()
SetGadgetState(#Image_0, ImageID(0))

EndIf
EndIf
EndIf

Until Event = #PB_Event_CloseWindow ; End of the event loop

[pdwyer]

could you put the [ code ] [ / code ] tags in please

Thanks

[Pierre Mercier]

Here is the code Paul. I finally found the problem.
As i use a procedure SavePic() to save the image and a procedure GetPic() to retrieve the image, had to put the image encoder in the procedure.
Now everithing work fine.

Code: Select all

;Image Saver And VIEWER  PB4.2
;
;Window Constant
 ;
 Enumeration
  #Window_0
  #Window_1
EndEnumeration
;
;- DATABASE Constants 
Enumeration
#Database = 0 
#Image = 0
EndEnumeration
 ;
;- Gadget Constants
Enumeration
  #Frame3D_0
  #Image_0
  #Button_SavePic
  #Button_Begin
  #Button_Previous
  #Button_Next
  #Button_End
  #String_PicName
EndEnumeration
;
;- Fonts
Global FontID1                               ;begin font constant init*********************    
FontID1 = LoadFont(1, "Arial", 8)
Global FontID2
FontID2 = LoadFont(2, "Arial", 12,#PB_Font_Bold)

;- Image Plugins
;
;variable
Global StrgPath.s               
Global SQLiteDatabaseName.s                          
Global RowCount.l                                      ;Init Rowcount in a table 
Global DBRow.l                                         ;Row counter To move back And forth in the db
Global Ptr.l                                           ;Row Pointer
Global StrgQuery.s                                     ;query's string
Global StrgPic64.s                                     ; encode picture
Global StrgImageFileName.s                             ; Path and omage name
Global StrgImagename.s                                 ;Image name To save
Global StrgImageNameLenght.l                           ;lenght of Image path and name with no extension
Global StrgPicName.s                                   ;Picture name from the database
Global StrgPattern.s                                   ;Picture pattern to be save
                                                       ; you could add up other picture type of files
                                                       ; by adding more image encoder
; get an image into memory via ReadFile 

Procedure.s ExePath()                           ;To find exe path For database path
  ExePath$ = Space(100) 
  GetModuleFileName_(0,@ExePath$,100) 
  ProcedureReturn GetPathPart(ExePath$)   
EndProcedure
                                                            ; Save picture name and picture in the database
Procedure SavePic()
UseJPEGImageDecoder() 
StrgPattern = "Image Files (*.bmp, *.jpg, *.png, *.tif)|*.bmp;*.jpg;*.png;*.tif" 
StrgImageFileName = OpenFileRequester("Choose an image file", "", StrgPattern, 0) 
; Convert the image file to a base64 string 
ReadFile(0, StrgImageFileName) 
If  StrgImageFileName <> ""  
    *inputbuffer = AllocateMemory(Lof(0)) 
    If *inputbuffer 
        ReadData(0,*inputbuffer, Lof(0)) 
        *encodedbuffer = AllocateMemory(MemorySize(*inputbuffer) * 1.35) 
        If *encodedbuffer 
              encodedlength = Base64Encoder(*inputbuffer, MemorySize(*inputbuffer), *encodedbuffer, MemorySize(*encodedbuffer)) 
        EndIf 
        CloseFile(0) 
        FreeMemory(*inputbuffer) 
    EndIf 
    StrgPic64 = PeekS(*encodedbuffer) ; We'll save this in the database 
    FreeMemory(*encodedbuffer) 
EndIf 
; Save the string (IMAGE)

StrgImageName = GetFilePart(StrgImageFileName)
StrgImageNameLenght = Len(StrgImageName)
StrgImageName = Left(StrgImageName,(StrgImageNameLenght - 4))
result = OpenDatabase(0,SQLiteDatabaseName,User$,Password$) 
StrgQuery = "INSERT INTO YourTable (Picture_Name,Picture)Values( '" + StrgImageName + "','" + StrgPic64 + "');"
DatabaseUpdate(0,StrgQuery)
CloseDatabase(0)
EndProcedure

Procedure GetPic()                                  ;Retrieve picture from database
; Retrieve the string 
UseJPEGImageDecoder() 
StrgPic64 = GetDatabaseString(1, 1) 
; Decode it 
If StrgPic64 <> ""
    *decodedbuffer = AllocateMemory(Len(StrgPic64)) 
    Base64Decoder(@StrgPic64, Len(StrgPic64), *decodedbuffer, Len(StrgPic64)) 
    ; Convert it to an image 
    CatchImage(0, *decodedbuffer) 
    FreeMemory(*decodedbuffer) 
EndIf
EndProcedure

Procedure OpenWindow_0()
  If OpenWindow(#Window_0, 443, 0, 310, 315, "",  #PB_Window_SystemMenu | #PB_Window_SizeGadget | #PB_Window_TitleBar ) 
      If CreateGadgetList(WindowID(#Window_0))
        ImageGadget(#Image_0, 10, 10, 0, 0, 0)
        ButtonGadget(#Button_SavePic, 10, 270, 100, 40, "Save Picture")
        ButtonGadget(#Button_Begin, 120, 280, 50, 30, "Begin")
        ButtonGadget(#Button_Previous, 170, 280,40, 30, "<")
        ButtonGadget(#Button_Next, 210, 280, 40, 30, ">")
        ButtonGadget(#Button_End, 250, 280, 50, 30, "End")
        StringGadget(#String_PicName,10,238,290,25,"", #PB_String_ReadOnly |#PB_Text_Center |#PB_String_BorderLess)
        SetGadgetFont(#String_PicName, FontID2)
      EndIf
  EndIf
EndProcedure

StrgPath = ExePath()          ;initializing StrgPath
SQLiteDatabaseName = "M:\IMAGE A db\DB\FBG.S3DB"
;SQLiteDatabaseName$ = StrgPath + "DB\FBG.s3db"  ; ID Database
result = UseSQLiteDatabase()                       ; opening sqlite library
result = OpenDatabase(1,SQLiteDatabaseName,User$,Password$) 
If Result = 0 
  MessageRequester("","Database / Base de donnée ???")  ;Can't find the database
EndIf
result = DatabaseQuery(1, "select count(*) FROM  Your DB") 
FirstDatabaseRow(1)
RowCount = (Int(GetDatabaseLong(1,0)))-1

result = DatabaseQuery(1, "SELECT * FROM Your DB") 
OpenWindow_0()                         ;Opening main window, waiting For event

UseJPEGImageDecoder() 
Repeat 
  Event = WaitWindowEvent() ; This line waits until an event is received from Windows 
  
  WindowID = EventWindow() ; The Window where the event is generated, can be used in the gadget procedures 
  
  GadgetID = EventGadget() ; Is it a gadget event? 
  
  EventType = EventType() ; The event type 
 
    
    If Event =  #PB_Event_CloseWindow 
        CloseDatabase(1)
        End 
    ElseIf Event =  #PB_Event_Gadget
        If GadgetID = #Button_SavePic
            SetGadgetText(#String_PicName,"")
            SetGadgetState(#Image_0,0)
            SavePic()
            result = DatabaseQuery(1, "select count(*) FROM Your DB") 
            FirstDatabaseRow(1)
            RowCount = GetDatabaseLong(1,0)
            result = DatabaseQuery(1, "SELECT * FROM You DB") 
            
        ElseIf gadgetID = #Button_Begin
              result = FirstDatabaseRow(1)
              If result
                  DbRow = 1
                  StrgPicname = GetDatabaseString(1,0) 
                  SetGadgetText(#String_PicName,StrgPicname)
                  GetPic()    
                  ResizeImage(0, 290, 215)
                  SetGadgetState(#Image_0,ImageID(0))
              EndIf
        ElseIf gadgetID = #Button_Previous
                      Ptr = DbRow-1
                      FirstDatabaseRow(1)
                      DbRow = 1
                      If Ptr > 1
                          Repeat 
                              NextDatabaseRow(1)
                              Dbrow = Dbrow + 1
                          Until DbRow = Ptr
                      EndIf    
                      StrgPicname = GetDatabaseString(1,0) 
                      SetGadgetText(#String_PicName,StrgPicname)
                      GetPic()    
                      ResizeImage(0, 290, 215)
                      SetGadgetState(#Image_0,ImageID(0))
                      
        ElseIf gadgetID = #Button_Next      
              result = NextDatabaseRow(1)
              If result
                  Dbrow = Dbrow + 1
                  StrgPicname = GetDatabaseString(1,0) 
                  SetGadgetText(#String_PicName,StrgPicname)
                  GetPic()
                  ResizeImage(0, 290, 215)
                  SetGadgetState(#Image_0,ImageID(0))
              EndIf
        ElseIf gadgetID = #Button_End     
                  If DbRow < Rowcount-1
                      FirstDatabaseRow(1)
                      dbRow = 1
                      Repeat
                          NextDatabaseRow(1)
                          DbRow = DbRow + 1
                      Until DbRow = RowCount
                      StrgPicname = GetDatabaseString(1,0) 
                      SetGadgetText(#String_PicName,StrgPicname)
                      GetPic()
                      ResizeImage(0, 290, 215)
                      SetGadgetState(#Image_0,ImageID(0))
                  EndIf
        EndIf  
    EndIf

Until Event = #PB_Event_CloseWindow ; End of the event loop 

[Fangbeast]

Check this one.....Retrieve Image from Database

Kerry

You do realise that you are replying to a posting that is 8 years old?

[netmaestro]

Fangs, Fangs, Fangs. You do realize you're talking to a bot... I guess some of you lonely fellows way out in the outback are just glad to have someone to talk to!

[Fangbeast]

Fangs, Fangs, Fangs.

Oi!! Stop looking at my fangs, they are shy!

You do realize you're talking to a bot.

No, guess I didn't. There have been legitimate users before who have necroposted so I never thought about it.

I guess some of you lonely fellows way out in the outback are just glad to have someone to talk to!

Not in the outback (Wish I were, it's quieter out there!). Though I took care of the loneliness problem by being an amateur radio operator so now I can talk dingos underwater all over the world:):)

I am shocked that you came out of your shell to talk to me though!! :):)

[Blue]

@netmaestro :

I was very surprised to read If OpenDatabase(0, ":memory:", "", "") in your code (from 2008, i know, but still valid).
Until now, I had no idea you could work a database in ":memory:" like that. What a wonderful trick.

So, I'm wondering how you came across that very useful trick.
I haven't found any reference to it in the PB documentation, and a forum search for ":memory:" yields nothing : using ":memory:" as a search string does not seem feasible.

Is it simply common knowledge amongst database gurus ?
Or is there some reference you know of that explains it ?

[Blue]

Never mind.
I found the answer at www.sqlite.org/inmemorydb.html.
I must have read the SQLite information 10 times, but this particular item never stuck.