[#DBAL-122] Impossible to save data to image/binary/varbinary - Doctrine Project

Details

Type: Bug
Status: Open
Priority: Major
Resolution: Unresolved
Affects Version/s: 2.0.4
Fix Version/s: None
Component/s: Platforms
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.

Activity

Descending order - Click to sort in ascending order

Hide

Permalink

Martin Weise added a comment - 22/Feb/12 4:28 PM

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

Show

Martin Weise added a comment - 22/Feb/12 4:28 PM 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

Hide

Permalink

Benjamin Eberlei added a comment - 09/Jan/12 1:59 PM

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

Show

Benjamin Eberlei added a comment - 09/Jan/12 1:59 PM 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

Impossible to save data to image/binary/varbinary

Details

Description

Activity

People

Dates