Saving and retrieving binary files from PB SQLite

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

This is an example of saving and retrieving binary files using PB's implementation of SQLite. Maybe someone will find this to be helpful. Any thoughts on optimization would be useful.

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()

rsts · Post by **rsts** » Sat Jul 05, 2008 5:52 pm

Nice, I think fangles had done some base64 encoding also.

Would it not be better to save binaries as type blob, or doesn't the PB implementation support them?

cheers

npath · Post by **npath** » Sat Jul 05, 2008 6:31 pm

You can define the table with the blob type, such as:

"create table table1 (binaryFile BLOB)"

However, the current PB implementation does not allow for retrieving pure binary data from the database, or at least not that I have figured out. What we need is something like:

GetDatabaseBlob(#Database, BlobColumn)