CODEWITHSUNDEEP
c#sql-servergzipcompression
Furkan Gözükara
Furkan Gözükara

Reputation: 23830

Argument data type varchar(max) is invalid for argument 1 of Decompress function

I am saving my string values as GZIP into a SQL Server 2016 VARCHAR(max) column.

Converting string into GZIP form is done by using C#.

An example value

This is my trial string

The produced GZIP value

FwAAAB+LCAAAAAAABAALycgsVgCi3EqFkqLMxByFYiCVlw4AJnX75BcAAAA=

Is that possible to select this value as unzipped from SQL Server?

Or do I have to unzip it inside my C# application?

I have tried the following function but it fails. Tried as NVARCHAR(max) as well

enter image description here

The gzip function i use

public static string GZip_String(string text)
{
    using (var memoryStream = new MemoryStream())
    {
        byte[] buffer = Encoding.UTF8.GetBytes(text);
        using (var gZipStream = new GZipStream(memoryStream, System.IO.Compression.CompressionMode.Compress, true))
        {
            gZipStream.Write(buffer, 0, buffer.Length);
        }

        memoryStream.Position = 0;

        var compressedData = new byte[memoryStream.Length];
        memoryStream.Read(compressedData, 0, compressedData.Length);


        var gZipBuffer = new byte[compressedData.Length + 4];
        Buffer.BlockCopy(compressedData, 0, gZipBuffer, 4, compressedData.Length);
        Buffer.BlockCopy(BitConverter.GetBytes(buffer.Length), 0, gZipBuffer, 0, 4);
        return Convert.ToBase64String(gZipBuffer);
    }
}

Upvotes: 5

Views: 3799

Answers (2)

Hamed Mahdizadeh
Hamed Mahdizadeh

Reputation: 976

Probably the data type of Compressed field in your table is not valid and you should change it to varbinary(MAX). when you use Compress function wrong type of field cause no exception (for example nvarchar(MAX)) but when you want to use decompress function it will cause this error.

you can simply change your field's data type and rerun the query.

Upvotes: 1

Will S
Will S

Reputation: 439

The Decompress function works against binary data, so you might try storing your bytes in a varbinary field. Here's how it works in pure SQL (haven't tried it from C#):

CREATE TABLE #tmp
(
    F1 varchar(100)
  , F2 varbinary(MAX)
);

INSERT INTO #tmp
(
    F1
  , F2
)
VALUES
    ('Hey there.'           -- F1 - varchar(100)
   , COMPRESS('Hey there.') -- F2 - varbinary(max)
    );

SELECT F1
     , F2
     , CAST(DECOMPRESS(F2) AS varchar(100))
  FROM #tmp;

Upvotes: 4

Related Questions