PureBasic Forums - English

Posted: **Fri Feb 21, 2025 11:16 am**

Hi,

How insert an image generated by PB into a blob field in a SQLite database?

All the examples I've found here use an image loaded from disk (an existing file), but never an image created by the program (or an image already in an ImageGadget which we can get using GetGadgetState().

I can't insert an image created by my program

The image I've created is used as an icon (32 x 32 in rgba (this is for the next version of my HelpDesk)).
Ico image size is known (32 * 32 * 4) (4 : rgba) so 4096
I can't manage to copy its contents into the buffer (if that's how you do it) Poke ? CopyImage ? I've been trying all versions for several days.

The AI didn't understand but answered something anyway

(which doesn't work

)

I'm still having trouble with image, image id, pointer, memory area, etc.

Here's my code based on other examples made by several contributors to this forum. Many thanks to them

Code: Select all

EnableExplicit

UseSQLiteDatabase()

Enumeration 
    #ID_Img
    #hDB
    #Win
    #Img_Out
    #Img_Gadget
EndEnumeration

Global DB_Test$ = GetTemporaryDirectory() + "Test_Blob.db"
Debug "DataBase: " + DB_Test$

Procedure Img_Write()
    Debug #CRLF$ + "-------------------- WRITE "
    ; Need for writing image in blob ?
    Protected Size_Img
    Protected Buffer
    
    ; Create Test image
    CreateImage(#ID_Img, 32, 32, 32, #Blue)
    ShowLibraryViewer("image", #ID_Img)
    
    ; If DB not exist, create it and create table
    If FileSize(DB_Test$) < 0 
        CreateFile(#hDB, DB_Test$) 
        CloseFile(#hDB)
        OpenDatabase(#hDB, DB_Test$, "", "")
        DatabaseUpdate(#hDB, "CREATE TABLE test_blob (id, blob BLOB);")
    Else
        OpenDatabase(#hDB, DB_Test$, "", "")
    EndIf
    
    ; Verify DB is init OK
    If Not IsDatabase(#hDB) 
        Debug "!! Error: " + DB_Test$ + " is not initialized :-( Bye." 
        ProcedureReturn 1
    EndIf
    
    Size_Img        = ImageHeight(#ID_Img) * ImageWidth(#ID_Img) * 4 ; (= 4096)
    Define Buffer   = AllocateMemory(Size_Img)
    ; ------> How To put image in buffer ? Poke ? CopyImage ? GrabImage ?
    
    SetDatabaseLong(#hDB, 0, 1)
    SetDatabaseBlob(#hDB, 1, Buffer, Size_Img) ; <-- Here error ?
    
    If DatabaseUpdate(#hDB, "INSERT INTO test_blob VALUES (?, ?);")
        Debug "OK INSERT DB"
        Debug "Row(s): " + AffectedDatabaseRows(#hDB)
    Else    
        Debug "Error INSERT db" 
        Debug DatabaseError() 
        ProcedureReturn 2 
    EndIf
    
    CloseDatabase(#hDB)
EndProcedure


Procedure Img_Read()
    ; This works with an image inserted in DB with a Db editor (i.e: SQLiteStudio)
    Debug #CRLF$ + "-------------------- READ"
    Protected  Img_Size
    Protected *Img_Buf
    Protected  Img_Blob
    
    If Not OpenDatabase(#hDB, DB_Test$, "", "")
        Debug "READ - Can't open DB " + DB_Test$
        ProcedureReturn 3
    Else
        Debug "DB open for Read"
    EndIf
    
    Protected SQL$ = "SELECT * FROM test_blob;"
    If Not DatabaseQuery(#hDB, SQL$)
        Debug "!! Query SELECT KO :-("
        ProcedureReturn 4
    EndIf
    
    If FirstDatabaseRow(#hDB)
        Img_Size = 4096 ; (all my ico have same size: long x larg x 4) (4 = RGBA)
        *Img_Buf = AllocateMemory(4096)
        Img_Blob = GetDatabaseBlob(#hDB, 1, *Img_Buf, Img_Size)
        FinishDatabaseQuery(#hDB)
        
        If CatchImage(#Img_Out, *Img_Buf)
            OpenWindow(#Win, 0, 0, 100, 100, "Image in DB", #PB_Window_ScreenCentered | #PB_Window_SystemMenu)
            ImageGadget(#Img_Gadget, 10, 10, 32, 32, ImageID(#Img_Out))
            Repeat 
            Until WaitWindowEvent() = #PB_Event_CloseWindow
        Else
            Debug "Error - No image in DB " + DB_Test$
            MessageRequester("Error", "No image in DB " + DB_Test$)
        EndIf
        
    Else
        Debug "!! Nothing match"
        ProcedureReturn 5
    EndIf
    CloseDatabase(#hDB)
EndProcedure


Img_Write()
Img_Read()

; Open SQLiteStudio to see result
; RunProgram("C:\Program Files\SQLiteStudio\SQLiteStudio.exe", DB_Test$, GetTemporaryDirectory())

End

(

Insert a breakpoint (F8) before End if you want to see image with ShowLibraryViewer)

The READ procedure works well. I tested it by putting an image directly with an SQL editor

Probably a simple trick, but after trying everything, I can't find my way around (for the past 2 weeks)

Translated with DeepL.com (free version)

Posted: **Fri Feb 21, 2025 1:33 pm**

Hi,
you are trying to save the image. I wouldn't do this. Try to save it as a file which could contain anything -> see EncodeImage. This is the same as SaveImage, but it stores the image file not in disk, but in memory. Then you can save the memory lock into the blob.
For reading you'll need CatchImage.

Cont provide code right now, I'm at work. But knowing you, this will already be enough to help you.

Posted: **Fri Feb 21, 2025 1:51 pm**

EncodeImage() is the way to go: https://www.purebasic.com/documentation ... image.html

Posted: **Fri Feb 21, 2025 2:26 pm**

Yes !!!

Thank you

Code: Select all

    ; --- OLD
    ;     Size_Img        = ImageHeight(#ID_Img) * ImageWidth(#ID_Img) * 4 ; (= 4096)
    ;     Define Buffer   = AllocateMemory(Size_Img)
    ;     SetDatabaseLong(#hDB, 0, 1)
    ;     SetDatabaseBlob(#hDB, 1, Buffer, Size_Img) ; <-- Here error ?
    ; --- NEW    
    Protected *Buffer = EncodeImage(#ID_Img)
    SetDatabaseLong(#hDB, 0, 1)
    SetDatabaseBlob(#hDB, 1, *Buffer, MemorySize(*Buffer))
    ; --- YES :-)

One line. So simple...

PureBasic Forums - English

How insert an image generated by PB into a blob field in a SQLite database?

How insert an image generated by PB into a blob field in a SQLite database?

Re: How insert an image generated by PB into a blob field in a SQLite database?

Re: How insert an image generated by PB into a blob field in a SQLite database?

Re: How insert an image generated by PB into a blob field in a SQLite database?