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

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