How to Update a Varbinary Field with a Specific Value

How to update a varbinary field with a specific value?

Try this:

UPDATE dbo.Login
SET
  Salt=CAST('bPftidzyAQik' AS VARBINARY),
  Password=0x2B89C2954E18E15759545A421D243E251784FA009E46F7A163926247FDB945F85F095DBB1FFF5B2B43A6ADAE27B8C46E176902412C4F8943E39528FF94E0DD5B
WHERE LoginID=10947

(you don't need to cast a string storing a hex value into varbinary - that's just what it is by default, without the quotes)

The question is, how are you generating that password hex value, and are you using the same encoding to generate it as you are to read it? If you are planning on converting that data back into a string, you'll need some code to do so. Here's a function I wrote that does this:

CREATE FUNCTION ConvertBinary
(  
    @value AS varbinary(max)
) RETURNS VARCHAR(MAX) AS BEGIN  

    DECLARE @result AS varbinary(max),
            @result2 AS varchar(max),
            @idx AS bigint;

    IF @value IS null
        return null;

    SELECT @result = @value;  
    SELECT @result2 = '';
    SELECT @idx = 1;

    WHILE substring(@result, @idx, 1) != 0 AND @idx < len(@result)
        BEGIN
            SET @result2 = @result2 + cast(substring(@result,@idx,1) as char(1));  
            SET @idx = @idx + 1;
        END

    RETURN @result2;  

END 

I don't know how helpful this will be for you, though, since it's very possible that whatever app is using these fields is handling the values differently than this function expects. For the record, this function takes a varbinary value which was originally utf-8 string, and returns the varchar value of that string. Good luck!

How to set varbinary column in SQL server

A varbinary isn't displayed with an odd number of digits. The value contains a specific number of bytes, and each byte is displayed as two digits.

(You can write a binary literal with an odd number of digits, for example 0x123, but then it means the same things as 0x0123.)

As you copy a value that has 43679 digits, it's not a correct value from the database. Most likely it's because it gets concatenated, either when it is displayed or when you copy it.

Update varbinary(MAX) column

The extra 0 is being added because the entire value you are setting it to is odd. SQL Server will pad it with an extra 0 to make it even.

declare @varmax varbinary(max) 
set @varmax = 0x1234567         --odd
select @varmax                  --returns 0x01234567 with the padded 0

set @varmax = 0x12345678        --even
select @varmax                  --returns 0x12345678

Your 9 is being dropped because you are entering an odd number of bytes that fills the max value. So, a 0 is inserted but this overflows the max number of bytes and thus is also truncated it seems. I was able to replicate your error... but don't know a way around it yet.

Here is the value i used for your test. Ignore the results... but you can copy them into your own SSMS and see the correct output.

http://rextester.com/LMGQ8686

Update table inserting VARBINARY data

From SQL Server 2005 onwards CONVERT does what you want:

CONVERT(varbinary(2000), '00001340132401324...', 2)

The styles for converting to/from binary are:

• 0: Raw data, ascii codepoints become binary bytes, UTF-16 codepoints become two bytes each.
• 1: Hex format, prefixed with '0x'
• 2: Hex format, not prefixed with '0x'

For converting characters to binary in format 0:

• char or varchar data (e.g. ASCII, ISO-8859-1) become binary bytes. For single character encodings this means one byte per character.
• nchar or nvarchar data (i.e. UTF-16) become two bytes each, in big-endian format, so N'ABC' becomes 0x410042004300

For converting hex to binary in formats 1 and 2:

• Each two input hex digits become one byte
• If input is not valid hex an error occurs
• Whitespace and punctuation are not allowed

See MSDN:

• https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

If you need UTF-8, please see my answer here for a UDF that will convert text to UTF-8:

• Compute MD5 hash of a UTF8 string

Update varbinary(MAX) field in SQLServer 2012 Lost Last 4 bits

It seems that the binary constant 0xFFD8F...6DC0676 that you used for update contains odd number of hex digits. And the SqlServer added half-byte at the beginning of the pattern so that it represent whole number of bytes.

You can see the same effect running the following simple query:

select 0x1, 0x104

This will return 0x01 and 0x0104.

The truncation may be due to some limitaions in SSMS, that can be observed in the following experiment:

declare @b varbinary(max)
set @b = 0x123456789ABCDEF0
set @b = convert(varbinary(max), replicate(@b, 65536/datalength(@b)))
select datalength(@b) DataLength, @b Data

The results returned are 65536 and 0x123456789ABCDEF0...EF0123456789ABCD, however if in SSMS I copy Data column I'm getting pattern of 43677 characters length (this is without leading 0x), which is 21838.5 bytes effectively. So it seems you should not (if you do) rely on long binary data values obtained via copy/paste in SSMS.

The reliable alternative can be using intermediate variable:

declare @data varbinary(max)
select @data = DataXXX from Table_XXX where ID = XXX
update Table_YYY set DataYYY = @data where ID = YYY

How can I update varbinary(max) columns in SQL Server with flask?

Your update syntax is off. Use this version:

file = request.files['file']

saveFileToDB = "UPDATE City SET pic = ? WHERE id = 2"
CU.execute(saveFileToDB, (file.read(),))
CU.commit()

As a note, I used (file.read(),), with a trailing comma here. This is to ensure that Python reads it as a tuple and not a scalar variable.

H2 : how to insert/update a varbinary column

I find the solution my self, for those who face the same issue here is what to do :

UPDATE TABLE SET COLUMN= FILE_READ('FILE.extension'));

---

Related Topics