Saving and retrieving binary files from PB SQLite

npath · Post by **npath** » Sat Jul 05, 2008 5:03 pm

I am having no problem saving and retrieving binary files using PB's implementation of SQLite (thanks to netmaestro, and of course Fred and Company). However, I would like to optimize this. Because of the requirement that the Base64Encoder output buffer be 33% larger than the input buffer, the binary file that is retrieved from the database is always around 33% larger than the original file. I am working on a solution, but I thought it would be best to get some input from the excellent PB community. Any thoughts would be greatly appreciated.

Code: Select all

Procedure.s GetBase64StringFromFile(file.s)
  ; This uses code posted by netmaestro
  ; http://www.purebasic.fr/english/viewtopic.php?t=32638&highlight=blob

  If ReadFile(0, file)
    *inputbuffer = AllocateMemory(Lof(0))
    If *inputbuffer
      ReadData(0,*inputbuffer, Lof(0))
      ; The output buffer (*encodedbuffer) needs to be approximately 35% larger than the input buffer
      *encodedbuffer = AllocateMemory(MemorySize(*inputbuffer) * 1.35)
      If *encodedbuffer
        encodedlength = Base64Encoder(*inputbuffer, MemorySize(*inputbuffer), *encodedbuffer, MemorySize(*encodedbuffer))
      EndIf
      CloseFile(0)
      FreeMemory(*inputbuffer)
    EndIf
  EndIf
  
  base64string.s = PeekS(*encodedbuffer)
  FreeMemory(*encodedbuffer)
  
  ProcedureReturn base64string
EndProcedure

Procedure DecodeBase64StringAndSaveAsBinary(base64string.s, file.s)
  ; This uses code posted by netmaestro
  ; http://www.purebasic.fr/english/viewtopic.php?t=32638&highlight=blob
  *decodedbuffer = AllocateMemory(Len(base64string))
  Base64Decoder(@base64string, Len(base64string), *decodedbuffer, Len(base64string))
  
  OpenFile(0, file)
  WriteData(0, *decodedbuffer, Len(base64string))
  CloseFile(0)
  
  FreeMemory(*decodedbuffer)
EndProcedure

Procedure main()
  ; Select file to add to database
  filePath.s = OpenFileRequester("Choose a file to add to database.", "", "*.*", 0)
  
  If filePath = ""
    MessageRequester("", "You did not choose a file!")
    End
  EndIf
  
  fileString.s = StringField(filePath, CountString(filePath, "\") + 1, "\")
   
  ; Create database and add file
  UseSQLiteDatabase()
  
  If CreateFile(0, "test.db")
    CloseFile(0)
  EndIf
  
  If OpenDatabase(0, "test.db", "", "")
    If DatabaseUpdate(0, "create table table1 (binaryFile TEXT)") = 0
      MessageRequester("", DatabaseError() )
    EndIf
    
    If DatabaseUpdate(0, "insert into table1 values ('" + GetBase64StringFromFile(filePath) + "')") = 0
      MessageRequester("", DatabaseError() )
    EndIf
     
    ; Get file from database and save as a copy in the current working directory
    DatabaseQuery(0, "select binaryFile from table1")
     
    While NextDatabaseRow(0)
      DecodeBase64StringAndSaveAsBinary(GetDatabaseString(0, 0), GetCurrentDirectory() + "Copy_" + fileString)
    Wend
    
    CloseDatabase(1)
    
    MessageRequester("", "Done")
  EndIf
EndProcedure

main()

npath · Post by **npath** » Sat Jul 05, 2008 5:17 pm

Well, the solution was simple. Just needed some morning coffee.

Code: Select all

Procedure.s GetBase64StringFromFile(file.s)
  ; This uses code posted by netmaestro
  ; http://www.purebasic.fr/english/viewtopic.php?t=32638&highlight=blob
  
  If ReadFile(0, file)
    *inputbuffer = AllocateMemory(Lof(0))
    If *inputbuffer
      ReadData(0,*inputbuffer, Lof(0))
      ; The output buffer (*encodedbuffer) needs to be approximately 35% larger than the input buffer
      *encodedbuffer = AllocateMemory(MemorySize(*inputbuffer) * 1.35)
      If *encodedbuffer
        encodedlength = Base64Encoder(*inputbuffer, MemorySize(*inputbuffer), *encodedbuffer, MemorySize(*encodedbuffer))
      EndIf
      CloseFile(0)
      FreeMemory(*inputbuffer)
    EndIf
  EndIf
  
  base64string.s = PeekS(*encodedbuffer)
  FreeMemory(*encodedbuffer)
  
  ProcedureReturn base64string
EndProcedure

Procedure DecodeBase64StringAndSaveAsBinary(base64string.s, file.s)
  ; This uses code posted by netmaestro
  ; http://www.purebasic.fr/english/viewtopic.php?t=32638&highlight=blob
  *decodedbuffer = AllocateMemory(Len(base64string))
  Base64Decoder(@base64string, Len(base64string), *decodedbuffer, Len(base64string) * 0.75 )
  
  OpenFile(0, file)
  WriteData(0, *decodedbuffer, Len(base64string) * 0.75)
  CloseFile(0)
  
  FreeMemory(*decodedbuffer)
EndProcedure

Procedure main()
  ; Select file to add to database
  filePath.s = OpenFileRequester("Choose a file to add to database.", "", "*.*", 0)
  
  If filePath = ""
    MessageRequester("", "You did not choose a file!")
    End
  EndIf
  
  fileString.s = StringField(filePath, CountString(filePath, "\") + 1, "\")
  
  ; Create database and add file
  UseSQLiteDatabase()
  
  If CreateFile(0, "test.db")
    CloseFile(0)
  EndIf
  
  If OpenDatabase(0, "test.db", "", "")
    If DatabaseUpdate(0, "create table table1 (binaryFile TEXT)") = 0
      MessageRequester("", DatabaseError() )
    EndIf
    
    If DatabaseUpdate(0, "insert into table1 values ('" + GetBase64StringFromFile(filePath) + "')") = 0
      MessageRequester("", DatabaseError() )
    EndIf
    
    ; Get file from database and save as a copy in the current working directory
    DatabaseQuery(0, "select binaryFile from table1")
    
    While NextDatabaseRow(0)
      DecodeBase64StringAndSaveAsBinary(GetDatabaseString(0, 0), GetCurrentDirectory() + "Copy_" + fileString)
    Wend
    
    CloseDatabase(1)
    
    MessageRequester("", "Done")
  EndIf
EndProcedure

main()

npath · Post by **npath** » Sat Jul 05, 2008 5:28 pm

I posted this under tips and tricks. A working example may be useful to someone down the road.