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
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
I try this to read uploaded pdf file and download it but it doesn’t work
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
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
``