Save Image In database(SQlite)[Resolved]

[collectordave]

Hi all,

I have a need to store images in a database.

I can allready do that by using include binary getting address and length from the include. However i would like the user to select an image from disc and have that saved to the database.

So the question is after using LoadImage(0 how do i get the start address and length of the buffer in which the image is placed?

Maybe a dumb question?

Regards

cd

[collectordave]

May Have cracked it.

Cobbled together some bits from the forum and came up with this:-

Code: Select all

UsePNGImageDecoder()

Structure ImageData
  *address
  size.i
EndStructure

LoadImage(0,"Afghanistan.png")
StartDrawing( ImageOutput(0) )
*imagedata.ImageData = AllocateMemory( SizeOf( ImageData ) )
  With *imagedata
    \address = DrawingBuffer()
    \size    = DrawingBufferPitch() * ImageHeight(0)
  EndWith
  Debug *imagedata\address
  Debug *imagedata\size
StopDrawing()

seems to be giving me the right answers will put it into some more code to check fully and repost.

regards

cd

[srod]

Well, unless you want to display the image for example, you could simply read the image file into a memory buffer with the memory and file functions and then dump it into a blob field in your database. That works fine. Retrieving the image is then just as straight forward.

[collectordave]

Thanks srod

just tried this to load a .png image:-

Code: Select all

UsePNGImageDecoder()

file$ = OpenFileRequester("Select a file","","Text (.txt)|*.txt|All files (*.*)|*.*",0)
  If file$
    If ReadFile(0, file$)
      length = Lof(0)                            ; get the length of opened file
      *MemoryID = AllocateMemory(length)         ; allocate the needed memory
      If *MemoryID
        bytes = ReadData(0, *MemoryID, length)   ; read all data into the memory block
        Debug "Number of bytes read: " + Str(bytes)
      EndIf
      CloseFile(0)
    EndIf
  EndIf
  
  ;SetDatabaseBlob(DBID, 0, *MemoryID, length)
  Debug CatchImage(1, *MemoryID, length)
  
  wFlags = #PB_Window_SystemMenu | #PB_Window_ScreenCentered
  OpenWindow(#PB_Any, 0, 0, 320, 390, "Database Example", wFlags)
  img1 = ImageGadget(#PB_Any, 10, 10, 300, 300, 0)
  
  SetGadgetState(img1, ImageID(1))
    
Repeat
  Select WaitWindowEvent()
    Case #PB_Event_CloseWindow
      appQuit = 1
  EndSelect
Until appQuit = 1

and it worked first time. Used catchimage instead of setdatabaseblob but hopefully that will work I will know in an hour or two.

Are there any dangers in doing it this way? Does the memory need to be freed?

Regards

cd

[srod]

After you have used CatchImage() then you can free the memory buffer you saved the image into. Similar deal when retrieving blobs from your database.

[collectordave]

Hi All

think i have everything now. Saving loading and displaying images to an SQlite database code below hopefully not too many errors:-

Code: Select all

UseSQLiteDatabase()
UsePNGImageDecoder()

Enumeration FormWindow
  #WinImgToDB
EndEnumeration

Enumeration FormGadget
  #btnLoadImage
  #strImageFileName
  #img0
  #btnSaveImage
  #img1
  #btnGetImage
  #DBID
EndEnumeration

Global ImageFilename.s = ""

Macro FileExists(filename)
  Bool(FileSize(fileName) > -1)
EndMacro

Procedure CreateDB()

  If CreateFile(0, "Images.db")
    CloseFile(0)
    If OpenDatabase(#DBID, "Images.db", "", "")
      DatabaseUpdate(#DBID, "CREATE TABLE [Images] ([Image] BLOB  NULL); ")
      CloseDatabase(#DBID)     
    EndIf
  EndIf
      
EndProcedure

Procedure SaveImageToDB()
    
  If Len(ImageFilename) > 0
    If ReadFile(0, ImageFilename)
      length = Lof(0)                            ; get the length of opened file
      *MemoryID = AllocateMemory(length)         ; allocate the needed memory
      If *MemoryID
        bytes = ReadData(0, *MemoryID, length)   ; read all data into the memory block
        Debug "Number of bytes read: " + Str(bytes)
      EndIf
      CloseFile(0)
    EndIf
  EndIf
  
  If OpenDatabase(#DBID, "Images.db", "", "")
    SetDatabaseBlob(#DBID, 0, *MemoryID, length)
    queryString.s = "INSERT INTO Images (Image) values (?);"
    DatabaseUpdate(#DBID, queryString)      
    CloseDatabase(#DBID)
    FreeMemory(*MemoryID)    
  EndIf
  
EndProcedure

Procedure LoadImageFromFile()
  
  ImageFilename = OpenFileRequester("Select an Image","","Image (.png)|*.png|All files (*.*)|*.*",0)
  LoadImage(0,ImageFilename)
  SetGadgetState(#img0, ImageID(0))
  FreeImage(0)
  
EndProcedure

Procedure GetImageFromDB()

  If OpenDatabase(#DBID, "Images.db", "", "")
    
    If DatabaseQuery(#DBID, "SELECT * FROM Images;")
      
      FirstDatabaseRow(#DBID)
      pictureSize = DatabaseColumnSize(#DBID, 0)
      *picture = AllocateMemory(pictureSize)
      GetDatabaseBlob(#DBID, 0, *picture, pictureSize)
      CatchImage(1, *picture, pictureSize)
      SetGadgetState(#img1, ImageID(1))
      FinishDatabaseQuery(#DBID)
      FreeMemory(*picture)
      
    EndIf
    
  EndIf
  
EndProcedure
  
OpenWindow(#WinImgToDB, 0, 0, 550, 350, "", #PB_Window_SystemMenu)
ButtonGadget(#btnLoadImage, 460, 10, 80, 25, "Load Image")
StringGadget(#strImageFileName, 190, 10, 260, 25, "")
ImageGadget(#img0, 10, 10, 150, 150, 0, #PB_Image_Border)
ButtonGadget(#btnSaveImage, 170, 140, 80, 25, "Save Image")
ImageGadget(#img1, 10, 180, 150, 150, 0, #PB_Image_Border)
ButtonGadget(#btnGetImage, 170, 310, 80, 25, "Get Image")

If Not FileExists("Images.db")
  CreateDB()
EndIf

Repeat
  
  Event = WaitWindowEvent()
  Select event
      
    Case #PB_Event_CloseWindow
      
      End
      
    Case #PB_Event_Gadget
      
      Select EventGadget()
          
        Case #btnLoadImage
          
          LoadImageFromFile() 
          
        Case #btnSaveImage
          
          SaveImageToDB()
          
        Case #btnGetImage
          
          GetImageFromDB()
          
      EndSelect
      
  EndSelect
  
ForEver

[srod]

Possible memory leak in the cases where the database cannot be opened.

To fix, replace the SaveImageToDB() procedure with :

Code: Select all

Procedure SaveImageToDB()
  If Len(ImageFilename) > 0
    If ReadFile(0, ImageFilename)
      length = Lof(0)                            ; get the length of opened file
      *MemoryID = AllocateMemory(length)         ; allocate the needed memory
      If *MemoryID
        bytes = ReadData(0, *MemoryID, length)   ; read all data into the memory block
        Debug "Number of bytes read: " + Str(bytes)
        If OpenDatabase(#DBID, "Images.db", "", "")
          SetDatabaseBlob(#DBID, 0, *MemoryID, length)
          queryString.s = "INSERT INTO Images (Image) values (?);"
          DatabaseUpdate(#DBID, queryString)      
          CloseDatabase(#DBID)
        EndIf
        FreeMemory(*MemoryID)    
      EndIf
      CloseFile(0)
    EndIf
  EndIf
EndProcedure

The important thing in avoiding the aforementioned leak is ensuring that the FreeMemory(*MemoryID) line is executed regardless of whether the DB can be opened or not which was not the case with your original function. In your function, if the OpenDatabase() function failed then the memory would not be freed (because of where you placed the FreeMemory() call.)

[collectordave]

Thanks srod

Can see that now you have pointed it out.

Cheers

cd

[collectordave]

Hi

I know I have said resolved but just another question.

If ther user selects a rather large image is it possible to save a resized image to the database?

Basically reduce the size of the image to something manageable before saving to the database?

Regards

cd

[srod]

You can load the image and then resize it with ResizeImage(). After this use EncodeImage() to save the image to memory and then off to an SQLite blob as before.

Alternatively, load the image, encode it to a memory buffer, compress the said buffer and then save to a blob.

[collectordave]

Works again thanks for the gentle push srod

cd