Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query

Hi guys,

I need to save a string into column image type to MSSQL but i recieve this error: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

code below:

$orcamento->ZCO_OBS = 'Test 123'; $orcamento->Save();

Can you help-me?

pet1330

7 years ago

Level 10

Did you ever solve this issue?

airportjule

6 years ago

Level 1

I've run into this problem today. Will report back if I can find the answer.

lara168466

6 years ago

Level 1

I have found a solution, in my case file_content is a binary ( for me temporary file storage solution)

public function setFileContentAttribute($value)
    {
        if ('sqlsrv' === config('database.default')) { // todo or some other way to check if its sql server connection
            return $this->attributes['file_content'] = DB::raw('CONVERT(VARBINARY(MAX), 0x' . bin2hex($value) . ')');
        }
        return $this->attributes['file_content'] = $value;
    }

bin2hex for 2 reasons:

prevent sql injection since its unparameterized
to avoid utf-8 exceptions (binary not matching utf8 characters)

2 likes

martleby

2 years ago

Level 4

@[email protected] Thanks very much for this - was going insane trying to work out what was going on when saving an image in the DB!

ryatkins

4 years ago

Level 2

This is my solution to this problem and I didn't need to use CONVERT(VARBINARY(MAX), ? )

   public function setAttachmentAttribute($value)
    {
        return $this->attributes['Attachment'] = DB::raw('0x'.bin2hex($value));
    }

You must use DB::raw or the value goes in as a string.

To get the value out of the database use hex2bin($attachment)

Please or to participate in this conversation.