[DBAL-122] Impossible to save data to image/binary/varbinary Created: 16/May/11  Updated: 22/Feb/12

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0.4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Martin Weise Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

XAMP, MsSQL-Server 2008, PHP 5.3.x, MS pdo_sqlsrv_vc6_ts.dll


 Description   

When trying to insert a value into a Column with type 'image', 'binary' or 'varbinary' the SQLServer states that this is not possible.
When trying to insert into 'binary' or 'varbinary' the error message is:
=> Implicit conversion from nvarchar(max) datatype to varbinary(max) is not allowed.

When trying to insert into 'image' the error message is:
=> Operand collision: nvarchar(max) is incompatible with image.

Doctrine prepares the image/binary/varbinary column in the statements as nvarchar(max) which is wrong.

The cause of this error is that in the MsSQLPlatform::getVarcharTypeDeclarationSQLSnippet($length, $fixed),
or in the datatype mapping which is to 'text'.

The documentation for the MsSQLServer states following conversions (http://207.46.16.252/de-de/library/ms187928.aspx):

*char => binary/varbinary : Explicit conversion
nchar/nvarchar => image : IMPOSSIBLE

So the solution would be, either to leave the datatype blank or use the char/varchar datatype when saving into image/binary/varbinary, which would cause an extra datatype as those would collide with 'text' I guess.

 Comments   
Comment by Benjamin Eberlei [ 09/Jan/12 ]

I get the problem, but i don't understand the solutions

Can you explain a bit more?

1. how do i leave a datatype empty? and which one?
2. how do i use char/varchar when saving?

I think this is just a problem of unspecific descriptions

Comment by Martin Weise [ 22/Feb/12 ]

Hi Benjamin

Sorry, for this long delay.
I had a deeper inspection what happens when I persist data into a field of type varbinary(MAX) and I got this (using the SQL profiler):

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,
N'@P1 nvarchar(36),@P2 nvarchar(max),@P3 nvarchar(34)',
N'INSERT INTO mc_dokument_data (id, data, mc_dokument_id) VALUES (@P1, @P2, @P3)',
N'03DDAAD1-4BFA-416B-A0C0-9B8B7148F31C',
N'0x3c3f786d6c20766572736...',
N'mwe3bc2c0da6543d1f48d7c83e64f5c449'
select @p1

But it has to be ( removed '-quotes and @P2 changed to varbinary(MAX) ):

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,
N'@P1 nvarchar(36),@P2 varbinary(max),@P3 nvarchar(34)',
N'INSERT INTO mc_dokument_data (id, data, mc_dokument_id) VALUES (@P1, @P2, @P3)',
N'03DDAAD1-4BFA-416B-A0C0-9B8B7148F31C',
0x3c3f786d6c20766572736...,
N'mwe3bc2c0da6543d1f48d7c83e64f5c449'
select @p1

I am not really sure any more if this is caused from Doctrine or from the 'MS SQL-Server PHP-PDO-Driver 2.0.1' .
But I guess this comes from the driver.
If you can validate this, I will make a bug report to them.

The funny thing is if I do this via executeQuery it works...

$data = unpack("H*" , __some__data__);
$data = '0x'.$data[1];

$em->executeQuery(
    "INSERT INTO mc_dokument_data (id, mc_dokument_id, data)". 
   "VALUES('".$id."' , '" . $documentId . "', " . $data . " )"
);

Hopefully I could clarify my problem.

Regards
Martin Weise

Generated at Sun May 19 08:28:51 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.