Save PDF File in sql server database

Targets Web
1

Hi guys i make a simple insert into a sql server table but one field is varbinary(max), becouse i want to converted pdf file that was upload with a fileupload, then in the event uploadcompleted i have the following code

Dim xSqlAddDoc as String
Dim xBin as BinaryStream
Dim xMem as MemoryBlock
Dim xPdfFinal as String
dim saveFile As FolderItem

session.mDB = New MSSQLServerDatabase
session.mDB.Host = “xxx.xxx.xxx.xxx”
session.mDB.Port = 1433
session.mDB.DatabaseName = “xDBName”
session.mDB.UserName = “xUserName”
session.mDB.Password = “xPassword”

If session.mDb.Connect Then

For Each file As WebUploadedFile In Files

Try
 saveFile = GetFolderItem("DocPDF").Child(file.Name)
  file.Save(saveFile)
  
  //Convertir Documento a Binario
  xBin = BinaryStream.Open(saveFile,False)
  xMem = xBin.Read(xBin.Length)
  xBin.Close
  xPdfFinal = xMem 
  
  //Guarda en la base de datos en la tabla Documentos
  xSqlAddDoc= ""
  xSqlAddDoc = xSqlAddDoc  + " Insert into Documentos (" 
  xSqlAddDoc = xSqlAddDoc  + " NombreAr, Tamano, Archivo, Usuario, FechaMod) Values ("
  xSqlAddDoc = xSqlAddDoc  + "'" + CStr(file.Name) + "', "
  xSqlAddDoc = xSqlAddDoc  + "'" + CStr(file.Size) + "', " 
  xSqlAddDoc = xSqlAddDoc  + "'" + xPdfFinal + "', " 
  xSqlAddDoc = xSqlAddDoc  + "'" + "xUsuTeste" + "', "
  xSqlAddDoc = xSqlAddDoc  + "'" + "2020-11-11 20:00:00"+"')"
  Session.mDB.SQLExecute (xSqlAddDoc)
  
Catch e As IOException
  // File Error, skip file
  Continue
End Try

Next

''ConnectStatusLabel.Text = “Connected to MS SQL Server”
Else
MsgBox “Error de Coneccion”
Return
''ConnectStatusLabel.Text = "Error connecting to MS SQL Server: " + mDb.ErrorMessage
End If

The problem is in the var xPdfFinal that supose if a string for save into a Varbarinary(max) field, any sugestiion or what i make wrong ?

2

For more information i used
Xojo 2018 r2
OS Windows 10

The variables values of xMem and xPdfFinal are diferent

xPdfFinalValue
xPdfFinalValue1145×315 53.2 KB

xMemValue
xMemValue1134×316 160 KB

3

What is the encoding of xPdfFinal?

4

Tks for reply Jeannot, i try default nil and utf-8 but i get the same value

1 Like
5

If you have more pdf or large ones it is better not using blobs and only storing a unique filename pointing to the file stored on the filesystem. But you probably know that ;-).

6

yes the pdf file is just 15 KB meaby max can be 200 kb

1 Like
7

try using http://documentation.xojo.com/index.php/EncodeBase64 and decode respectively.

8

I very much doubt you’ll ever get dynamic SQL to write pure binary to a database table that way.

  • Try a prepared statement with correct database binding
  • Base64 encode the binary value and decode it in the insert statement if you want to use dynamic SQL
  • The database record object used to have a blob type column, put seem to have disappeared…
1 Like
9

But you normally should not need to use it. I just looked at my code from an old project. It looks the same than yours but is working:

bs = BinaryStream.Open(f, False)
datastream = bs.read(bs.Length)
bs.Close

Did you check that your database has UTF-8? Did you try if the pdf is workable when you download it via Xojo? Your encoding should match the one from your database. Did you look at the binary tab?

10

Me too, but I was surprised seeing it working on a customer side when I had to add functionality to existing code and they had no budget to refactor the whole code ;-).

They started small too, and have now 8 GB of blobs. Roddney, I suggest using at least using an own table for the blobs and only using the ID of that table in your main table. With blobs you are usually slowing down any database editor for instance sooner or later.

11

Tks very much Jeannot and James, i will try with your sugestion and comment

1 Like