Storing and retrieving jpg images in SQL Server

Databases
1

Hello.

I have succesfully managed to get jpg files saved and retrieved from SQLite, MySQL and PostGRE. I’m having trouble getting my desktop app do to the same with SQL SERVER!

Basics of a remote database connection are covered: I can read/write/update/delete records of any kind, including the BLOB (varbinary(max), according to SQL SERVER terminology). I’m using MSSQLServerDatabase class with native Client. It took a while until I figured out the need to use the ‘CONVERT’ function on SQL SERVER… Now it is working.

Issue is that data retrieved from the database is different than what is stored fro the same image. Can you guys take a look at the code below and point out a solution or different approach? I think there is some misunderstanding on how I’m using EncodeHex/DecodeHex. EncodeHex works OK, but DecodeHex alwyas comes empty, although definitely there is data on the varbinary field.

To record data on the database:

Grava_Blob(tabela As String, Idi As String, registro As DatabaseRecord, optional insere As Boolean) As Integer

  Dim Existe As String
  Dim rs As RecordSet
  
  if not insere then
    Existe = App.dBAS.Opera_Database(False, "Dlookup", "tbl_Anexos", "Origem = '" + tabela + "' AND Ref_ID = " + Idi, "ID")
  end 
  
  dim s As String = EncodeHex(registro.BlobColumn("Objeto"))
  dim frase As String
  
  If Existe = "" Or Existe = "0" then
    frase = "INSERT INTO tbl_Anexos (Origem, Ref_ID, Arquivo, Objeto, Largura, Altura, data, usuario)  VALUES ('"+ registro.Column("Origem") + "', "_
    + registro.Column("Ref_ID") + ", '" + registro.Column("Arquivo") + "', CONVERT(VARBINARY(MAX), '"+ s + "', 0), " + registro.Column("Largura") + ", "_
    + registro.Column("Altura") + ", '" + registro.Column("data") + "', '" +  registro.Column("usuario") + "')"
    
  else
    frase = "UPDATE tbl_Anexos SET "_
    +" Objeto = CONVERT(VARBINARY(MAX), '"+ s + "', 0), "_
    + "data = '" + registro.Column("data") + "', "_ 
    + "usuario = '" + registro.Column("usuario") + "' WHERE ID = " + Existe
    
  end if
  
  self.SQLExecute(frase)
  
  if self.Error then 
    MsgBox(Str(self.ErrorCode) + " - " + self.ErrorMessage)
    Return 0
  else
    Return 1
  end if
  
  if rs<> Nil then
    rs.Close
    rs = Nil
  end if

To retrieve data and put the image on a canvas:

Carrega_Blob(Orig As String, Idi As String, Canv As Canvas)
  Dim mrecset As RecordSet
  
      mrecset = App.dBAS.SQLSelect("SELECT CONVERT(VARCHAR(MAX), Objeto, 0) As Objeto FROM tbl_Anexos WHERE Origem = '" + Orig + "' AND Ref_ID = " + Idi)
      
      if  mrecset.Field("Objeto").Value <> Nil then
        Dim s As string
        Dim mb As MemoryBlock
        s = mrecset.Field("Objeto").StringValue
        mb = DecodeHex(s)
        Dim ppic As Picture
        ppic = Picture.FromData(mb)
        Canv.Backdrop = Suporte.ResizeToFit(ppic, canv.Width, canv.Height)
      else
        Canv.Backdrop = Nil
        Canv.RefreshRect(Canv.Left, canv.top, canv.Width, canv.Height, True)
      End If
      
  App.MouseCursor =System.Cursors.StandardPointer

Underlying table “tbl_Anexos” looks like this on SQL SERVER:

CREATE TABLE "tbl_Anexos" (
	"ID" INT NOT NULL,
	"Origem" VARCHAR(100) NULL DEFAULT NULL,
	"Ref_ID" INT NULL DEFAULT NULL,
	"Arquivo" VARCHAR(500) NULL DEFAULT NULL,
	"Tipo" VARCHAR(100) NULL DEFAULT NULL,
	"Objeto" VARBINARY NULL DEFAULT NULL,
	"Largura" INT NULL DEFAULT NULL,
	"Altura" INT NULL DEFAULT NULL,
	"data" DATE NULL DEFAULT NULL,
	"usuario" VARCHAR(100) NULL DEFAULT NULL,
	PRIMARY KEY ("ID")
)
;

I really appreciate any help!

2

MBS Plugin did the trick.

The above code for “Grava_Blob” was ok. The issue was with SQL Native Client’s inability to retrieve varbinary(max) or varchar(max) fields properly, impairing “Carrega_Blob” method (see fixed code below).

In order to make it work properly, I had to include the line below at the App’s Open event:

dim d as new SQLDatabaseMBS

and the new code for “Carrega_Blob” is:

Carrega_Blob(Orig As String, Idi As String, Canv As Canvas)
      Dim mrecset As RecordSet
      Dim scon As String
      Dim con as new SQLConnectionMBS
      
      scon = App.v_Local +"@" + App.v_Nome
      con.Option("OLEDBProvider") = "SQLNCLI11"
      con.Connect(scon, App.User_BD_Externo, App.Password_BD_Externo, SQLConnectionMBS.kSQLServerClient)
      dim d as new SQLDatabaseMBS
      mrecset = con.SQLSelectAsRecordSet("SELECT Objeto FROM tbl_Anexos WHERE Origem = '" + Orig + "' AND Ref_ID = " + Idi)
      
      if  mrecset.Field("Objeto").Value <> Nil then
        Dim s As string
        Dim len_s As Integer
        Dim mb As MemoryBlock
        s = mrecset.Field("Objeto").StringValue
        len_s = len(s)
        s = mid(s, 2, len_s - 2)
        mb = DecodeHex(s)
        Dim ppic As Picture
        ppic = Picture.FromData(mb)
        Canv.Backdrop = Suporte.ResizeToFit(ppic, canv.Width, canv.Height)
      else
        Canv.Backdrop = Nil
        Canv.RefreshRect(Canv.Left, canv.top, canv.Width, canv.Height, True)
      End If
      
      con.Disconnect

I was actually kind of upset that nobody cared to answer this post… Turns out it was a simple question and I can even Imagine some of you guys secretly laughing or frowning at such a newbie question…

Regards.

Leonidas