none
sp to insert base64binary image to varchar(max) RRS feed

  • Question

  • I'm trying to insert a base64binary image on a SQL Azure table whose record is already inserted in a varchar (max) field @paramFile with the following procedure:

    CREATE PROCEDURE [dbo].[addimmaginiAzureNew] @paramID int, @paramExtn varchar(5), @paramFile varchar(MAX) 
    AS BEGIN
      SET  NOCOUNT ON;
      UPDATE     dbo.immagini
      SET estensione = @paramextn, immagine = @paramfile
      WHERE dbo.immagini.idimmagine = @paramID
    END

      When I call the procedure I get this error:
    The Parameter object has not been defined correctly. The information provided is inconsistent or incomplete.

    Can anyone tell me where the mistake is?
    Thank you

    Marco Dell'Oca

    Monday, July 1, 2019 4:02 PM

Answers

  • Try setting the parameter length to the actual column length:

    Cmd1.Parameters(3).Value = EncodeFileToBase64Binary(strFileName)
    Cmd1.Parameters(3).Size = Len(Cmd1.Parameters(3).Value)
     

    Alternatively, set the size to -1 to denote a MAX value of up to 2GB:

    Cmd1.Parameters(3).Size = -1

    Also the proc parameter declaration should be varchar(MAX) rather than varbinary(MAX) since you are passing string containing the base64 encoded value.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, August 30, 2019 9:32 AM added size -1 note
    • Marked as answer by Marco Dell'Oca Friday, August 30, 2019 9:40 AM
    Friday, August 30, 2019 1:02 AM
  • Did you try this?

    Cmd1.Parameters(3).Size = -1


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Marco Dell'Oca Saturday, August 31, 2019 10:17 PM
    Saturday, August 31, 2019 9:27 PM

All replies

  • You need to declare @paramFile as varbinary(max) and not varchar(max).


    Monday, July 1, 2019 4:55 PM
  • Thanks Hilary

    I tried declaring the variable with varbinary (max) but the result is identical, same problem.

    If we execute the procedure from MSSMS and manually insert the parameters I don't receive any error.

    If instead I execute the procedure from MS Access it always gives me the same error.

    Marco Dell'Oca

    Wednesday, July 3, 2019 1:36 PM
  • Hi Marco,

    Because the data type of the column is still varchar, so your data will be shown up as (unicode) strings.

    I guess it would be better to add a new column with data type varbinary(max), copy the data from old to new column using the convert function and then drop the old column.

    This is from an older MSDN post (link).

    Regards,

    Mike

    Wednesday, July 17, 2019 1:53 PM
    Moderator
  • Additionally, the following Blog provides another option: Streaming Blobs To and From SQL Azure (link).

    Wednesday, July 17, 2019 1:56 PM
    Moderator
  • Hello Mike,
    this is certainly not the problem as the table is a new table of a new database of azure and the field has been created original as varchar (max).
    Now I have a doubt, base64binary strings must be stored in varchar (max) or varbinary (max) fields?
    thanks anyway

    Marco Dell'Oca
    Wednesday, July 17, 2019 3:54 PM
  • Hi Marco,

    base64binary should be stored in varbinary(max) field. You can refer below document for the same.

    Reference doc : https://docs.microsoft.com/en-us/sql/t-sql/data-types/binary-and-varbinary-transact-sql?view=sql-server-2017

    Hope this helps.

    Monday, July 29, 2019 12:08 PM
    Moderator
  • I've been saving my images in SQL as column type "Image". Is that wrong then?

    JavaScript uses Ajax to upload the form, controller converts the image to base64, then back to a byte array for storage.

    The code below has always worked for me. This is an MVC controller action, receiving a normal form post with the file attached. I'm uploading the image in DataUrl format.

    if (Request.Form["file"] != null)
    {
        var fileData = Request.Form["file"];
    
        string base64 = fileData.Substring(fileData.IndexOf(',') + 1);
        byte[] data = Convert.FromBase64String(base64);
    
        var dbFile = new SnapshotFile
            {
                //...other properties...
                ImageBytes = data
            };
    
        db.Snapshots.Add(dbFile);
        await db.SaveChangesAsync();
    }

    All the best,
    Pete


    #PEJL
    Got any nice code? If you invest time in coding an elegant, novel or impressive answer on MSDN forums, why not copy it over to TechNet Wiki, for future generations to benefit from! You'll never get archived again, and you could win weekly awards!

    Have you got what it takes to become this month's TechNet Technical Guru? Join a long list of well known community big hitters, show your knowledge and prowess in your favoured technologies!

    Monday, July 29, 2019 12:28 PM
    Moderator
  • Hello Angoyal-msft,
    sorry for the delay in answering but I was on vacation.
    I tried to use varbinary (max) and varchar (max) but I get the same problem.
    I try to explain the problem:
    I need to insert a file in Base64Binary format (previously it was an Acrobat Reader file) in an Azure Sql Database using ODBC or OLEDB.
    No problem if the database is SQL Server, but with Azure I can't do it.
    It is impossible using OLEDB, impossible using a stored procedure, impossible using a trigger.
    Some idea?
    Many Thanks 

    Marco Dell'Oca
    Thursday, August 29, 2019 9:44 AM
  • Are you using the latest SQL Server OLE DB or ODBL driver? Post a code snippet of the parameter definitions proc call.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, August 29, 2019 12:00 PM
  • Hi Dan

    I'm using Microsoft OLE DB 18.2.2 driver for SQL Server.
    This is the method I would like to use to insert the file in the Base64Binary format in an Azure table (using a stored procedure):

    Public Function InsertarchiviatuttoA(lngFKID As Variant, strTable As String, strFileName As String) As Boolean
    On Error GoTo CloseUp
        InsertarchiviatuttoA = False
        Dim objStream As New ADODB.Stream
        Dim objCmd As New ADODB.Command
        Dim varFileBinary As Variant
        
        objStream.Type = adTypeBinary
        objStream.Open
        objStream.LoadFromFile strFileName
        varFileBinary = objStream.Read
        objStream.Close
        Set objStream = Nothing
        
        Dim Conn1 As New ADODB.Connection
        Dim Cmd1 As New ADODB.Command
        Dim Rs1 As New ADODB.Recordset
       
        Conn1.ConnectionString = FunzOlEDB(oledbstrconnect): Conn1.Open
        Set Cmd1 = New ADODB.Command
        Cmd1.ActiveConnection = Conn1
        Cmd1.CommandText = "addImmagini"
        Cmd1.CommandType = adCmdStoredProc
        Cmd1.Parameters.Refresh
        Cmd1.Parameters(1).Value = lngFKID
        Cmd1.Parameters(2).Value = right(strFileName, Len(strFileName) - InStrRev(strFileName, "."))
        Cmd1.Parameters(3).Value = EncodeFileToBase64Binary(strFileName)
        Set Rs1 = Cmd1.Execute()
       
        InsertarchiviatuttoA = True
        
    CloseUp:
        On Error Resume Next
        Set objStream = Nothing
        Set objCmd = Nothing
        Set Rs1 = Nothing
        Set Cmd1 = Nothing
        Conn1.Close
        Set Conn1 = Nothing
    End Function
    
    Public Function EncodeFileToBase64Binary(strPicPath As String) As String
     
        Const adTypeBinary  As Variant = 1
        
        Dim objXML As Variant
        Dim objDocElem As Variant
    
        Dim objStream As New ADODB.Stream
    
        objStream.Type = adTypeBinary
        objStream.Open
        objStream.LoadFromFile strPicPath
    
        Set objXML = CreateObject("MSXml2.DOMDocument")
        Set objDocElem = objXML.createElement("Base64Data")
        objDocElem.DataType = "bin.base64"
    
        objDocElem.nodeTypedValue = objStream.Read()
    
        EncodeFileToBase64Binary = objDocElem.Text
    
        Set objXML = Nothing
        Set objDocElem = Nothing
        Set objStream = Nothing
    
    End Function
    
    This is the Stored Procedure
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[addimmagini] @paramID int, @paramExtn varchar(5), @paramFile varbinary(MAX) 
    AS BEGIN
      SET  NOCOUNT ON;
      UPDATE     dbo.immagini
      SET estensione = @paramextn, immagine = @paramfile
      WHERE dbo.immagini.idimmagine = @paramID
    end
    GO

    Thanks

    Marco Dell'Oca

    Thursday, August 29, 2019 2:15 PM
  • Try setting the parameter length to the actual column length:

    Cmd1.Parameters(3).Value = EncodeFileToBase64Binary(strFileName)
    Cmd1.Parameters(3).Size = Len(Cmd1.Parameters(3).Value)
     

    Alternatively, set the size to -1 to denote a MAX value of up to 2GB:

    Cmd1.Parameters(3).Size = -1

    Also the proc parameter declaration should be varchar(MAX) rather than varbinary(MAX) since you are passing string containing the base64 encoded value.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, August 30, 2019 9:32 AM added size -1 note
    • Marked as answer by Marco Dell'Oca Friday, August 30, 2019 9:40 AM
    Friday, August 30, 2019 1:02 AM
  • Hi Dan
    Thank you so much for solving my dilemma, I would never have solved it without your help.
    I take advantage of your experience to ask for one last thing, I had to change the field type of the table from varbinary (max) to varchar (max). Can I use Varbinary (max) in the table maybe doing a conversion with cast or convert in the stored procedure?

    thank you so much

    Marco Dell'Oca
    Friday, August 30, 2019 9:39 AM
  • Can I use Varbinary (max) in the table maybe doing a conversion with cast or convert in the stored procedure?

    For a new application, I'd store the raw file contents in a varbinary(MAX) column and specify that type for the proc and application parameters. If application needs the data formatted differently, such as base64 string encoding for transfer over http, it would be best to do that in the application layer rather than T-SQL. It's generally best to perform formatting in the application layer and use relational databases like SQL Server only for data storage and retrieval purposes. This practice will improve scalability and performance while reducing costs.

    If your application is an existing one with data already stored as varchar(MAX), you'll need to decide if it's worth the effort to convert existing data to varbinary(MAX) along with the corresponding app changes I mentioned above. The advantage is that varbinary(MAX) type will reduce storage (about 50%) compared to varchar(MAX) base64 encoded strings.

    The compromise approach is to incur technical debt and perform the conversion in T-SQL to avoid changes to the existing app code. I'm personally not a fan of acquiring technical debt unless there's a good business justification to do so.

     



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, August 30, 2019 11:03 AM
  • Hi Dan
    Mine is a new application that uses a database on Azure and I know that the filestream archive is not supported on Azure and then must necessarily be stored on base64binary format in varchar field (max).
    I was wondering if I could transform the parameter from varchar (max) into varbinary (max) in the stored procedure and store it in azure on a varbinary (max) field instead of on a varchar (Max) field.

    Marco Dell'Oca
    Friday, August 30, 2019 12:34 PM
  • One does not need to use filestream in order to use varbinary(MAX). The varbinary(MAX) type is available in all editions of SQL Server, including Azure SQL Database. I tested the modified code with Azure SQL Database and it works fine with varbinary(MAX).

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, August 31, 2019 1:40 AM
  • Hi Dan
    Thanks so much for everything

    Marco Dell'Oca
    Saturday, August 31, 2019 9:34 AM
  • One last information.
    Is it possible to read a file and insert it directly into an Azure table in the varbinay(max) field?
    I tried but I didn't succeed.
    This is the program step I'm using and it gives me error 3421 on execute.


    Public Function InsertImmaginiA(lngFKID As Variant, strTable As String, strFileName As String) As Boolean
    On Error GoTo CloseUp
    
        Dim objStream As New ADODB.Stream
        Dim objCmd As New ADODB.Command
        Dim varFileBinary As Variant
            
        objStream.Type = adTypeBinary
        objStream.Open
        objStream.LoadFromFile strFileName
        varFileBinary = objStream.Read
        objStream.Close
        Set objStream = Nothing
        
        Dim Conn1 As New ADODB.Connection
        Dim Cmd1 As New ADODB.Command
        Dim Rs1 As New ADODB.Recordset
    
      Conn1.ConnectionString = FunzOlEDB(oledbstrconnect): Conn1.Open
        Set Cmd1 = New ADODB.Command
        Cmd1.ActiveConnection = Conn1
        Cmd1.CommandText = "addimmagini"
        Cmd1.CommandType = adCmdStoredProc
        Cmd1.Parameters.Refresh
        Cmd1.Parameters(1).Value = lngFKID
        Cmd1.Parameters(2).Value = Trim(right(strFileName, Len(strFileName) - InStrRev(strFileName, ".")))
        Cmd1.Parameters(3).Value = varFileBinary
        Cmd1.Parameters(3).Size = Len(Cmd1.Parameters(3).Value)
        Set Rs1 = Cmd1.Execute()
    
       InsertImmagini = True
        
    CloseUp:
        On Error Resume Next
        Set objStream = Nothing
        Set objCmd = Nothing
        Set Rs1 = Nothing
        Set Cmd1 = Nothing
    End Function
    Marco Dell'Oca

    Saturday, August 31, 2019 3:25 PM
  • This is the program step I'm using and it gives me error 3421 on execute. 

    I think 3421 is a VBA type conversion run-time error. That would occur, for example, if you call the function with a string instead of an integer like:

    Call InsertImmaginiA("this is not an integer", "tablename", "C:\Files\YourFile.pdf")

    Step through the code in a debugger to identify the root cause. Consider using stongly-typed variables (e.g. "lngFKID As Integer" in the function declaration) so that type errors are detected earler and easier to troubleshoot. It seems to me the error handling code could be improved but what I don't know about VBA is a lot so I can't offer specific recommendations.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, August 31, 2019 4:18 PM
  • Hi Dan

    I don't think Vba" is the problem, and I don't think it's the type of variable.

    This is because the program has worked for a long time and still works perfectly connected to SQL Server locally.
    So there mast be another problem.
    This was why I wanted to store my files in base64binary format.

    Marco Dell'Oca

    Saturday, August 31, 2019 5:05 PM
  • I can only say that I ran your code successfully against Azure SQL Database. However, I do get the error below when I comment out the On Error statement and pass an invalid value to the function. 

    The problem line I see is Cmd1.Parameters(1).Value = lngFKID, which is of course due to the invalid data I passed to the proc call. What is the problem line of code in your case? 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, August 31, 2019 5:37 PM
  • Hi Dun,

    I tried to exclude parameter 3, also removing it from the stored procedure and everything is working properly.

    The record is previously inserted in the table.

    In parameter(1) there is the table key.

    Parameter(2) contains the file extension (.pdf, .doc, .txt ...) to be inserted

    In parameter(3) there is the content of the file to be inserted.

    So the error occurs only for the insertion of the third parameter.

    I have no problem with parameter 1.

    This is the script of the Azure table.

    /****** Object:  Table [dbo].[Immagini]    Script Date: 31/08/2019 22:46:44 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Immagini](
    	[IDimmagine] [int] IDENTITY(1,1) NOT NULL,
    	[Computer] [varchar](20) NULL,
    	[disco] [varchar](10) NULL,
    	[directory] [varchar](20) NULL,
    	[categoria] [varchar](20) NULL,
    	[nome] [varchar](150) NULL,
    	[numimmagine] [int] NULL,
    	[targa] [varchar](10) NULL,
    	[telaio] [varchar](20) NULL,
    	[autore] [varchar](30) NULL,
    	[epoca] [varchar](35) NULL,
    	[dimH] [real] NULL,
    	[dimL] [real] NULL,
    	[dimP] [real] NULL,
    	[pesoKg] [real] NULL,
    	[stato] [tinyint] NULL,
    	[invia] [smallint] NULL,
    	[imma1] [int] NULL,
    	[imma2] [int] NULL,
    	[imma3] [int] NULL,
    	[imma4] [int] NULL,
    	[sicurezza] [tinyint] NULL,
    	[codfornitore] [varchar](6) NULL,
    	[codcliente] [varchar](6) NULL,
    	[IDGUID] [uniqueidentifier] NOT NULL,
    	[immagine] [varbinary](max) NULL,
    	[note] [varchar](max) NULL,
    	[descrizimma] [varchar](max) NULL,
    	[venditore] [varchar](max) NULL,
    	[estensione] [varchar](15) NULL,
    	[tipoarch] [varchar](1) NULL,
    	[datainser] [smalldatetime] NULL,
    	[datamovins] [smalldatetime] NULL,
    	[usermovins] [varchar](50) NULL,
    	[tipobinario] [tinyint] NULL,
     CONSTRAINT [PK_Immaginiaa] PRIMARY KEY CLUSTERED 
    (
    	[IDimmagine] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[Immagini] ADD  CONSTRAINT [DF_Immaginiaa_IDGUID]  DEFAULT (newid()) FOR [IDGUID]
    GO
    
    ALTER TABLE [dbo].[Immagini] ADD  CONSTRAINT [DF__Immagini__tipobi__239F1926]  DEFAULT ((0)) FOR [tipobinario]
    GO


    Thanks again

    Marco Dell'Oca

    Saturday, August 31, 2019 8:49 PM
  • Did you try this?

    Cmd1.Parameters(3).Size = -1


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Marco Dell'Oca Saturday, August 31, 2019 10:17 PM
    Saturday, August 31, 2019 9:27 PM
  • Eureka,
    it works perfectly.
    Thank you so much Dan
    You are a genius.

    Marco Dell'Oca

    Saturday, August 31, 2019 10:17 PM