Postgres DB encode/decode PDF

For a new project I want to encode pdf file select by user from it’s computer to a Postgress data base

I already do that on a desktop app with an SQLite database using BLOB using this code

Write method

// Encode PDF File
If row.FolderToEncode <> Nil Then
  // Add the file as blob to the newly added row
  Var blob As SQLiteBlob
  blob = db.CreateBlob("Document", "FileData", row.ID, row.FolderToEncode.Length)
  If blob <> Nil Then
    Var bd As BinaryStream
    bd = BinaryStream.Open(row.FolderToEncode)
    Var data As String
    While Not bd.EndOfFile
      data = bd.Read(1000)
      Catch error as IOException
        DBError("Error writing to BLOB " + error.Message, True)
        Exit While
      End Try
  End If
End If

Load method

// Get the BLOB from the DB into a string
Var blob As SQLiteBlob = DB.OpenBlob("Document", "FileData", id, False)

// No PDF file in DB ?
If blob = Nil Then Return Nil

// Convert bob to tempory File
Var outputFile As FolderItem = SpecialFolder.Temporary.Child(id.ToString+".pdf")
Var output As BinaryStream
output = BinaryStream.Create(outputFile, True)

While Not blob.EndOfFile
  Catch err as IOException
    MessageDialog.Show("Error reading from BLOB.")
    Exit While
  End Try

Return outputFile

How can I do this with Xojo Cloud and Postgres ?


Sub UploadFinished(Files() As WebUploadedFile) Handles UploadFinished
  System.DebugLog CurrentMethodName
  'die müssen alle hier gespeichert werden weil die nach dieser Methode weg sind^^
  Status "Speichern ..."
  'Alles in einem Ordner ablegen
  'For Each f As WebUploadedFile In Files
  'System.DebugLog f.Name + " Size " + f.Size.ToString
  'System.DebugLog "All Saved"
  '---------------------------------------- Connect
  Var db As New MyDatabase 
  Status "Database open ..."
  '---------------------------------------- Large Objects
  Var d As Module1.Data = Session.Data
  For Each f As WebUploadedFile In Files
    System.DebugLog f.Name + " Size " + f.Size.ToString
    Status "Speichern " + f.Name + " Size " + f.Size.ToString
    Var obj_id As Integer = db.CreateLargeObject
    Var largeObject As PostgreSQLLargeObject = db.OpenLargeObject(obj_id)
    largeObject.Write f.Data
    Status "Thumbnail ..."
    Var obj_id_thumbnail As Integer = db.CreateLargeObject
    Var largeObject_thumbnail As PostgreSQLLargeObject = db.OpenLargeObject(obj_id_thumbnail)
    Var pic As Picture = Module1.Thumbnail(f.Data,128.0)
    largeObject_thumbnail.Write pic.ToData(Picture.Formats.JPEG,80)
    'db.ExecuteSQL("END TRANSACTION")
    Status "Insert Record ..."
    Var row As RowSet = db.SelectSQL("insert into files (obj_id,mime_type,name,datetime,data_id,obj_id_thumbnail) values($1,$2,$3,$4,$5,$6) returning files_id",obj_id,f.MIMEType,f.Name,DateTime.Now,d.ID,obj_id_thumbnail)
  Catch e As RuntimeException
    Status "Error " +e.Message
    MessageBox e.Message
  End Try
  System.DebugLog "All Saved Database"
  Status "Ready ..."
End Sub
Public Sub Ready()
  LabelStatus.Text = "Fertig"
  ButtonReady.Enabled = True
  FileUploader1.Enabled = True
  ButtonUpload.Enabled = True
End Sub
Public Sub Status(msg As String)
  LabelStatus.Text = msg
End Sub

Class MyDatabase Super PostgreSQLDatabase

Public Sub Constructor()
  Self.AppName = "Foto Doku"
  Self.UserName = "test"
  Self.Password = "test"
End Sub
Public Sub Destructor()
End Sub

read large object

Public Function OIDPicture(db As MyDatabase, obj_id As Integer) As WebPicture
  Var largeObject As PostgreSQLLargeObject = db.OpenLargeObject(obj_id)
  Var l As Integer = largeObject.Length
  'System.DebugLog "File Size " + l.ToString
  Var mb As MemoryBlock = largeObject.Read(l)
  #Pragma BreakOnExceptions False
  Return Picture.FromData(mb)
End Function

Thank your for your help. What data type do you used on the Postgres table for ?

I use bytea

I have good result with images

I try this to read uploaded pdf file and download it but it doesn’t work :frowning:


Var largeObject As PostgreSQLLargeObject = App.mDB.OpenLargeObject(oid)

Var l As Integer = largeObject.Length

System.DebugLog "File Size " + l.ToString

Var mb As MemoryBlock = largeObject.Read(l)


Var TextFile As New WebFile
 // TextFile is a property of the web page
TextFile.MimeType = "application/pdf"
TextFile.ForceDownload = True // If False, the browser may try to display the file instead of download it
TextFile.FileName = "file.pdf"
TextFile.Data = mb

System.DebugLog TextFile.URL

I also try to display it on HTMLViewer (DocViewer) with no more success

What data type do you used on the Postgres table for ?

i guess it was a type oid
it is just a link to other table where the objects are stored.

i don’t know why it doesn’t work with your pdf files?

I had the same problem a long time ago.
To fix it, I went step by step.

The first step was to make sure the complete PDF reached my database.

The second, make sure it is down correctly.

In that solution, I to prove to upload the file with the original binary formats.

If necessary, I can pass you the code, so you can see a solution that works.

I really appreciate to see your code @Jose_Fernandez_del_Valle :+1:

We do this for storing a pdf - created from a golang / jscript web app in a postgres db:

  • use a text field, not a bytea
  • convert the pdf to base64
  • store the text in the postgres file with identifier ‘data:image/pdf;base64,iVBORw0KGgoAAAA…’
  • to read the file in xojo. We use dynapdf, code not complete, but u get the idea…
dim f as FolderItem = ... // your output location
impPDF = new DynapdfMBS
call impPDF.SetImportFlags BitwiseOr(impPDF.kifImportAll, impPDF.kifImportAsPage)
// delete identifier
dim sourcePDFdata as string = cPDF.mid(29)
// open file from memory
if sourcePDFdata.Len > 10 then
  call impPDF.CreateNewPDF(nil) // create pdf in memory
  call impPDF.OpenImportBuffer(DecodeBase64MBS(sourcePDFdata), impPDF.kptOpen, "")
  call impPDF.ImportPDFFile(1,1.0,1.0)
  call impPDF.FlattenForm() // if it's a pdf form
  call impPDF.CloseFile
end if

// save the pdf
dim outputPDFData as string = impPDF.GetBuffer

// and write to file
dim b as BinaryStream = f.CreateBinaryFile("")
if b<>Nil then
  b.Write outputPDFData
end if
call impPDF.CloseFile
f = nil
1 Like