Blob and variable

[loulou2522]

Good evening,
I would like to pass the content of a blob under postgresql in a variable but I get stuck

Code: Select all

DatabaseQuery(ouverture,"Select  xxx.* from xxx Where xxx='"+ rum+"';")
NextDatabaseRow(ouverture)
Base64MemSize.l = DatabaseColumnSize(ouverture, DatabaseColumnIndex(ouverture,"GED" ))*1.30
*Base64Memory = AllocateMemory(Base64MemSize*1.30)
GetDatabaseBlob(ouverture ,DatabaseColumnIndex(ouverture,"GED" ), *Base64Memory, Base64MemSize)

Note the content is binary and the use is for copy these content in an sql query for saving the content of my postgresql database
here i would want to recup the content of memory *Base64memory from a length of Base64memsize into
a variable. I try many solution but idont'arrive to do anything
thanks for any help

[infratec]

Code: Select all

SELECT encode(GED, 'base64') from xxx where yyy = zzz

Then use GetDatabaseString()

[infratec]

Or try if this works:

Code: Select all

SQL$ = "SELECT GED FROM xxx WHERE xxx='" + rum + "';"
If DatabaseQuery(ouverture, SQL$)
  If NextDatabaseRow(ouverture)
    BlobSize.i = DatabaseColumnSize(ouverture, DatabaseColumnIndex(ouverture, "GED"))
    *Blob = AllocateMemory(BlobSize)
    If *Blob
      If GetDatabaseBlob(ouverture, DatabaseColumnIndex(ouverture, "GED"), *Blob, BlobSize)
        Debug "Data Ok"
      Else
        Debug "No data"
      EndIf
      FreeMemory(*Blob)
    EndIf
  Else
    Debug SQL$
    Debug "SQL result is empty"
  EndIf
  FinishDatabaseQuery(ouverture)
Else
  Debug DatabaseError()
EndIf

[infratec]

Just coded a TableToCSV() procedure.
And CSVToTable()

Code: Select all

CompilerIf #PB_Compiler_IsMainFile
  EnableExplicit
CompilerEndIf


Structure TableSchema_Structure
  Name$
  Type.i
EndStructure


Procedure.s TableToCSV(DB.i, Table$, Condition$ = "", Filename$ = "")
  
  Protected.i FirstCol, ColCount, i, File
  Protected SQL$, Line$, CSV$
  Protected *Buffer
  Protected NewList TableSchemaList.TableSchema_Structure()
  
  
  SQL$ = "SELECT * FROM " + Table$
  If Condition$ <> ""
    SQL$ + " WHERE " + Condition$
  EndIf
  If DatabaseQuery(DB, SQL$)
    
    If Filename$ <> ""
      File = CreateFile(#PB_Any, Filename$)
    EndIf
    
    FirstCol = #True
    While NextDatabaseRow(DB)
      If FirstCol
        Line$ = ""
        ColCount = DatabaseColumns(DB) - 1
        For i = 0 To ColCount
          AddElement(TableSchemaList())
          TableSchemaList()\Name$ = DatabaseColumnName(DB, i)
          TableSchemaList()\Type = DatabaseColumnType(DB, i)
          Line$ + #DQUOTE$ + TableSchemaList()\Name$ + " " + Str(TableSchemaList()\Type) + #DQUOTE$ + ";"
        Next i
        Line$ = RTrim(Line$, ";")
        
        If IsFile(File)
          WriteStringN(File, Line$)
        Else
          CSV$ = Line$ + #CRLF$
        EndIf
        FirstCol = #False
      EndIf
      
      i = 0
      Line$ = ""
      ForEach TableSchemaList()
        Select TableSchemaList()\Type
          Case #PB_Database_String
            Line$ + #DQUOTE$ + GetDatabaseString(DB, i) + #DQUOTE$
          Case #PB_Database_Blob
            Line$ + #DQUOTE$
            *Buffer = AllocateMemory(DatabaseColumnSize(DB, i), #PB_Memory_NoClear)
            If *Buffer
              If GetDatabaseBlob(DB, i, *Buffer, MemorySize(*Buffer))
                Line$ + Base64Encoder(*Buffer, MemorySize(*Buffer))
              EndIf
              FreeMemory(*Buffer)
            EndIf
            Line$ + #DQUOTE$
          Default
            Line$ + GetDatabaseString(DB, i)
        EndSelect
        Line$ + ";"
        i + 1
      Next
      Line$ = RTrim(Line$, ";")
      If IsFile(File)
        WriteStringN(File, Line$)
      Else
        CSV$ + Line$ + #CRLF$
      EndIf
    Wend
    
    If IsFile(File)
      CloseFile(File)
      CSV$ = "OK"
    EndIf
    
    FinishDatabaseQuery(DB)
  EndIf
  
  ProcedureReturn CSV$
  
EndProcedure




Procedure.i CSVToTable(DB.i, DBType.i, Table$, CSV$, Truncate.i = #False)
  
  Protected.i File, Pos1, Pos2, FirstCol, i, Size
  Protected SQL$, Line$, Field$
  Protected NewList BlobList.i()  ; if there is more then one blob field in the table a list is needed.
  Protected NewList TableSchemaList.TableSchema_Structure()
  
  
  If FileSize(CSV$)
    File = ReadFile(#PB_Any, CSV$)
  EndIf
  
  If Truncate
    DatabaseUpdate(DB, "DELETE FROM " + Table$)
  EndIf
  
  Pos1 = 1
  Pos2 = 1
  FirstCol = #True
  DatabaseUpdate(DB, "BEGIN")
  
  While Pos2
    
    ClearList(BlobList())
    
    If IsFile(File)
      Line$ = ReadString(File)
    Else
      Pos2 = FindString(CSV$, #CRLF$, Pos1)
      If Pos2
        Line$ = Mid(CSV$, Pos1, Pos2 - Pos1)
        Pos1 = Pos2 + 2
      EndIf
    EndIf
    
    If Pos2
      Debug Line$
      
      If FirstCol
        i = 1
        Field$ = StringField(Line$, i, ";")
        While Field$ <> ""
          AddElement(TableSchemaList())
          TableSchemaList()\Name$ = StringField(Field$, 1, " ")
          TableSchemaList()\Type = Val(StringField(Field$, 2, " "))
          i + 1
          Field$ = StringField(Line$, i, ";")
        Wend
        FirstCol = #False
      Else
        
        i = 0
        SQL$ = "INSERT INTO " + Table$ + " VALUES ("
        ForEach TableSchemaList()
          Field$ = StringField(Line$, i + 1, ";")
          If DBType = #PB_Database_PostgreSQL
            SQL$ + "$" + Str(i + 1) + ","
          Else
            SQL$ + "?,"
          EndIf
          Select TableSchemaList()\Type
            Case #PB_Database_String
              SetDatabaseString(DB, i, Trim(Field$, #DQUOTE$))
            Case #PB_Database_Blob
              AddElement(BlobList())
              BlobList() = AllocateMemory(Len(Field$), #PB_Memory_NoClear)
              If BlobList()
                Size = Base64Decoder(Trim(Field$, #DQUOTE$), BlobList(), MemorySize(BlobList()))
                If Size
                  ;ShowMemoryViewer(BlobList(), Size)
                  SetDatabaseBlob(DB, i, BlobList(), Size)
                EndIf
              EndIf
            Case #PB_Database_Double
              SetDatabaseDouble(DB, i, ValD(Field$))
            Case #PB_Database_Float
              SetDatabaseFloat(DB, i, ValF(Field$))
            Case #PB_Database_Long
              SetDatabaseLong(DB, i, Val(Field$))
            Case #PB_Database_Quad
              SetDatabaseQuad(DB, i, Val(Field$))
          EndSelect
          i + 1
        Next
        
        SQL$ = RTrim(SQL$, ",")
        SQL$ + ")"
        
        Debug SQL$
        If DatabaseUpdate(DB, SQL$) = 0
          Debug DatabaseError()
        EndIf
        
        ForEach BlobList()
          FreeMemory(BlobList())
        Next
        
      EndIf
    EndIf
    
    If IsFile(File)
      If Eof(File)
        Pos2 = 0
      EndIf
    EndIf
    
  Wend
  
  DatabaseUpdate(DB, "COMMIT")
  
  If IsFile(File)
    CloseFile(File)
  EndIf
  
EndProcedure




CompilerIf #PB_Compiler_IsMainFile
  
  Define.i DB
  Define CSV$
  
  
  UseSQLiteDatabase()
  
  DB = OpenDatabase(#PB_Any, ":memory:", "", "")
  If DB
    DatabaseUpdate(DB, "CREATE TABLE test (f1 text, f2 integer, f3 real, f4 blob)")
    DatabaseUpdate(DB, "INSERT INTO test VALUES ('text1', 1, 1.1, X'41')")
    DatabaseUpdate(DB, "INSERT INTO test VALUES ('text2', 2, 2.2, X'4242')")
    DatabaseUpdate(DB, "INSERT INTO test VALUES ('text3', 3, 3.3, X'434343')")
    
    CSV$ = TableToCSV(DB, "test")
    Debug CSV$
    CSVToTable(DB, #PB_Database_SQLite, "test", CSV$)
    Debug ""
    Debug TableToCSV(DB, "test", "f1 = 'text2'")
    Debug ""
    Debug TableToCSV(DB, "test", "", "c:\tmp\TableToCSV.csv")
    CSVToTable(DB, #PB_Database_SQLite, "test", "c:\tmp\TableToCSV.csv")
    
    
    Debug ""
    Debug ""
    If DatabaseQuery(DB, "SELECT * FROM test")
      While NextDatabaseRow(DB)
        Debug GetDatabaseString(DB, 0) + " " + GetDatabaseString(DB, 3)
      Wend
      FinishDatabaseQuery(DB)
    EndIf
    
    
    CloseDatabase(DB)
  EndIf
CompilerEndIf

[loulou2522]

Or try if this works:

Code: Select all

SQL$ = "SELECT GED FROM xxx WHERE xxx='" + rum + "';"
If DatabaseQuery(ouverture, SQL$)
  If NextDatabaseRow(ouverture)
    BlobSize.i = DatabaseColumnSize(ouverture, DatabaseColumnIndex(ouverture, "GED"))
    *Blob = AllocateMemory(BlobSize)
    If *Blob
      If GetDatabaseBlob(ouverture, DatabaseColumnIndex(ouverture, "GED"), *Blob, BlobSize)
        Debug "Data Ok"
      Else
        Debug "No data"
      EndIf
      FreeMemory(*Blob)
    EndIf
  Else
    Debug SQL$
    Debug "SQL result is empty"
  EndIf
  FinishDatabaseQuery(ouverture)
Else
  Debug DatabaseError()
EndIf

The main problem of this routine which works well is that i can't have the decode result in a variable and that's what i found.
Is it possible to recup the content of the memory at adress *Blob, BlobSize to copy in a string and after decoding the string ?

[mk-soft]

The type blob is only raw data or binary data.
If you know that it is a string (ASCII, UTF8 or UNICODE), then use PeekS.

[infratec]

Since a blob is binary, you need Base64Encoder() to get it into a string variable.
I showed this one post below.

Code: Select all

...
*Buffer = AllocateMemory(DatabaseColumnSize(DB, i), #PB_Memory_NoClear)
If *Buffer
  If GetDatabaseBlob(DB, i, *Buffer, MemorySize(*Buffer))
    Line$ + Base64Encoder(*Buffer, MemorySize(*Buffer))
  EndIf
  FreeMemory(*Buffer)
EndIf
...

I now also added the other way round.