Save Image in database (BLOB)

[Little_man]

What is wrong with this code, get error on line 92.

Code: Select all

UseSQLiteDatabase()

UsePNGImageDecoder()
UsePNGImageEncoder()

Enumeration FormWindow
  #WinImgToDB
EndEnumeration

Enumeration FormGadget
  #btnLoadImage: #strImageFileName: #img0: #btnSaveImage: #img1: #btnGetImage: #SQL: #btnEnd
EndEnumeration

Global ImageFilename.s = ""
Global SQLRequest.s

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

Procedure CreateDB()
  If CreateFile(0, "Home_Delivery.sql")
    CloseFile(0)
    If OpenDatabase(#SQL, "Home_Delivery.sql", "", "", #PB_Database_SQLite)
    SQLRequest = "CREATE TABLE IF NOT EXISTS Company_Data "
    SQLRequest + "("
    SQLRequest + "CompanyName TEXT, Address TEXT, Homenumber TEXT, ZipCode TEXT, City TEXT, "
    SQLrequest + "PhoneNumber TEXT, BankNumber TEXT, KvK_Number TEXT, E_Mail_Address TEXT, "
    SQLRequest + "Logo_Name TEXT, Logo_Path TEXT, Logo_Photo BLOB"
    SQLRequest + ")"
    DatabaseUpdate(#SQL, SQLRequest)
    
    FinishDatabaseQuery(#SQL)
    CloseDatabase(#SQL)
    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
        MessageRequester("1__Number of bytes read: ", Str(ReadData(0, *MemoryID, length))) ;Read all data into the memory block.

        OpenDatabase(#SQL, "Home_Delivery.sql", "", "", #PB_Database_SQLite)
          SQLRequest = "INSERT INTO Company_Data ("
          SQLRequest + "CompanyName, Address, Homenumber, ZipCode, City, PhoneNumber, BankNumber, KvK_Number, E_Mail_Address, "
          SQLRequest + "Logo_Name, Logo_Path, Logo_Photo"
          SQLrequest + ") VALUES ("
          SQLRequest + "'" +  "0" + "', "
          SQLRequest + "'" +  "1" + "', "
          SQLRequest + "'" +  "2" + "', "
          SQLRequest + "'" +  "3" +"', "
          SQLRequest + "'" +  "4" + "', "
          SQLRequest + "'" +  "5" + "', "
          SQLRequest + "'" +  "6" + "', "
          SQLRequest + "'" +  "7" + "', "
          SQLRequest + "'" +  "8" + "', "
          SQLRequest + "'" +  "9" + "', "
          SQLRequest + "'" + "10" + "', "
          SQLRequest       + "?" + ")"

          If *MemoryID
              SetDatabaseBlob(#SQL, 0, *MemoryID, Length)
              MessageRequester("2__Number of bytes read: ", Str(ReadData(0, *MemoryID, length))) ;Read all data into the memory block.
            Else
              ReplaceString(SQLRequest, "?", "'0'")
          EndIf
          R = DatabaseUpdate(#SQL, SQLRequest)
          MessageRequester("Info", Str(R))
        EndIf
      EndIf
      FinishDatabaseQuery(#SQL)
      CloseFile(0)

      FreeMemory(*MemoryID)
    EndIf
EndProcedure

Procedure GetImageFromDB()
  OpenDatabase(#SQL, "Home_Delivery.sql", "", "", #PB_Database_SQLite)
    DatabaseQuery(#SQL, "SELECT * FROM Product_Description")
      FirstDatabaseRow(#SQL)
      pictureSize = DatabaseColumnSize(#SQL, 11)

      MessageRequester("pictureSize=", Str(pictureSize))

      *picture = AllocateMemory(pictureSize)
      GetDatabaseBlob(#SQL, 11, *picture, pictureSize)
      CatchImage(1, *picture, pictureSize)
      SetGadgetState(#img1, ImageID(1))
      FinishDatabaseQuery(#SQL)
      FreeMemory(*picture)
      CloseDatabase(#SQL)
EndProcedure

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

OpenWindow(#WinImgToDB, 50,  50, 650, 450, "", #PB_Window_SystemMenu)
ButtonGadget(#btnLoadImage, 560,  10,  80,  25, "Load Image")
StringGadget(#strImageFileName, 290,  10, 260,  25, "")
ImageGadget(#Img0, 10,  10, 238, 194, 0, #PB_Image_Border)
ButtonGadget(#btnSaveImage, 260, 180,  80,  25, "Save Image")
ImageGadget(#Img1, 10, 230, 238, 194, 0, #PB_Image_Border)
ButtonGadget(#btnGetImage, 260, 400,  80,  25, "Get Image")
ButtonGadget(#btnEnd, 475, 420, 150,  25, "Einde programma")

If Not FileExists("Home_Delivery.sql"): 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()
          
        Case #btnEnd
          End

      EndSelect
  EndSelect
ForEver

Little_man

[Marc56us]

Hi,

Just / always add some 'If' test before using query result, you will see error

Code: Select all

If DatabaseQuery(#SQL...
        ...
    Else
        Debug DatabaseError()
    EndIf 

Code: Select all

Procedure GetImageFromDB()
    OpenDatabase(#SQL, "Home_Delivery.sql", "", "", #PB_Database_SQLite)
    If DatabaseQuery(#SQL, "SELECT * FROM Product_Description")
        FirstDatabaseRow(#SQL)
        pictureSize = DatabaseColumnSize(#SQL, 11)
        
        MessageRequester("pictureSize=", Str(pictureSize))
        
        *picture = AllocateMemory(pictureSize)
        GetDatabaseBlob(#SQL, 11, *picture, pictureSize)
        CatchImage(1, *picture, pictureSize)
        SetGadgetState(#img1, ImageID(1))
        FinishDatabaseQuery(#SQL)
        FreeMemory(*picture)
        CloseDatabase(#SQL)
    Else
        Debug DatabaseError()
    EndIf 
EndProcedure

Result:

Code: Select all

no such table: Product_Description

So pictureSize = 0
Then

Code: Select all

[ERROR] Line: 92
[ERROR] AllocateMemory(): Can't allocate a memory block of size 0.

[Little_man]

Solved:

Code: Select all

If DatabaseQuery(#SQL, "SELECT * FROM Company_Data")

Little_man