Postgres DB encode/decode PDF

Targets Web
1

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)
      
      Try
        
        blob.Write(data)
        
      Catch error as IOException
        
        DBError("Error writing to BLOB " + error.Message, True)
        bd.Close
        blob.Close
        Exit While
        
      End Try
    Wend
    
    bd.Close
    blob.Close
    
  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
  Try
    output.Write(blob.Read(1000))
  Catch err as IOException
    MessageDialog.Show("Error reading from BLOB.")
    Exit While
  End Try
Wend
blob.Close
output.Close

Return outputFile

How can I do this with Xojo Cloud and Postgres ?

2

example

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 ..."
  App.DoEvents
  
  'Alles in einem Ordner ablegen
  
  'For Each f As WebUploadedFile In Files
  'System.DebugLog f.Name + " Size " + f.Size.ToString
  'f.Save(Session.Data.Path.Child(f.Name))
  'Next
  
  'System.DebugLog "All Saved"
  
  '---------------------------------------- Connect
  
  Var db As New MyDatabase 
  
  Status "Database open ..."
  App.DoEvents
  
  '---------------------------------------- Large Objects
  
  Var d As Module1.Data = Session.Data
  
  db.BeginTransaction
  
  For Each f As WebUploadedFile In Files
    System.DebugLog f.Name + " Size " + f.Size.ToString
    Status "Speichern " + f.Name + " Size " + f.Size.ToString
    App.DoEvents
    
    'db.ExecuteSQL("BEGIN TRANSACTION")
    Var obj_id As Integer = db.CreateLargeObject
    Var largeObject As PostgreSQLLargeObject = db.OpenLargeObject(obj_id)
    largeObject.Write f.Data
    
    Status "Thumbnail ..."
    App.DoEvents
    
    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 ..."
    App.DoEvents
    
    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)
    
  Next
  
  Try
    db.CommitTransaction
  Catch e As RuntimeException
    db.RollbackTransaction
    Status "Error " +e.Message
    MessageBox e.Message
  End Try
  
  '----------------------------------------
  
  System.DebugLog "All Saved Database"
  
  Status "Ready ..."
  App.DoEvents
  
  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"
  
  Self.Connect
  
End Sub
Public Sub Destructor()
  Self.Close
End Sub

read large object

Public Function OIDPicture(db As MyDatabase, obj_id As Integer) As WebPicture
  db.BeginTransaction
  
  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)
  
  db.CommitTransaction
  
  #Pragma BreakOnExceptions False
  
  Return Picture.FromData(mb)
  
End Function
3

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

4

I use bytea

5

I have good result with images

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

App.mDB.BeginTransaction

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)

App.mDB.CommitTransaction


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

DocViewer.LoadURL(TextFile.URL)
System.DebugLog TextFile.URL
Me.GotoURL(TextFile.URL)

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

6

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?

7

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.

8

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

9

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
  b.Close
end if
 
call impPDF.CloseFile
f = nil
``
1 Like