DBAL-122: Impossible to save data to image/binary/varbinary #1167

Closed
doctrinebot opened this Issue May 16, 2011 · 16 comments

2 participants

@doctrinebot

Jira issue originally created by user le_shatai:

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.

@doctrinebot

Comment created by @beberlei:

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 :-)

@doctrinebot

Comment created by le_shatai:

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

@doctrinebot

Comment created by @deeky666:

Is this now a bug in Doctrine? Can you please confirm this? I was not able to reproduce this issue. The only thing I can see is that back then we did not have a BLOB type in Doctrine that supplies the correct binding type for prepared statements. I don't know HOW you actually inserted the data which lead to an error but I guess it was a binding type problem in the driver coming from a wrong Doctrine type mapping (text instead of blob).
BLOB type mapping for SQL Server platform was introduced in this commit: 854a67d
What is STILL wrong is the image and binary mapping. I will fix the image and binary type mappings to blob.

@doctrinebot

Comment created by @ocramius:

[~deeky666] is this solved with c727c03 ?

@doctrinebot

Comment created by @deeky666:

[ocramius]I don't know really. I cannot reproduce this error and I have to less additional information to do so. Obviously there have been wrong type mappings for the binary database types which al resolved to TextType. I think we have to wait for Feedback from [le_shatai]then.

@doctrinebot

Comment created by @deeky666:

[~le_shatai] As you did not provide further feedback and we introduced dedicate binary/varbinary Doctrine types that also provide the correct param mapping types and fixed the image/blob type mappings, I consider this ticket as resolved. We also have a lot of tests covering this. If you still encounter this issue, feel free to reopen :)
Anyways, thanks for reporting this!

@doctrinebot

Comment created by @deeky666:

Lol okay funny thing. I got to reproduce this now but only with PDO_SQLSRV. This is definitely a bug in the driver. See here:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5a755bdd-41e9-45cb-9166-c9da4475bb94/how-to-set-null-for-varbinarymax-using-bindvalue-using-pdosqlsrv?forum=sqldriverforphp

[~beberlei] What to do? Mention it anywhere in the docs? I don't know if it is fixed in a newer version. I am using version 3.0.3421.0. The native driver does not have this problem.

@doctrinebot

Comment created by @beberlei:

[~deeky666] There is a known problems sections in the docs, we should mention it there. And then also mention this problem to the SQL Server team I guess.

@doctrinebot

Comment created by @deeky666:

[~beberlei] I am currently fixing the functional test suites for the SQL Server drivers and will add that to the docs. Also I will skip the BlobTests for pdo_sqlsrv then for now. I think (according to the link I provided) the SQL Server team is already aware of that. It seems they haven't released a new version for quite a long time. But I will see if I can ping them again on this.

@doctrinebot

Comment created by @deeky666:

btw: See: http://sqlsrvphp.codeplex.com/SourceControl/latest#pdo*sqlsrv/pdo_stmt.cpp and search for "// TODO: This will eventually be changed to SQLSRV_PHPTYPE*STREAM when output streaming is implemented.". Then you know why varbinary/lob binding does not work.

@doctrinebot

Comment created by le_shatai:

Hi @all

Sorry for the long delay, but the project I am working on, did not update Doctrine for a long time and I started with a the latest version on another project only a month ago. Therefore I could not provide any further feedback and due the fact, that there were some Xmas things going on... ;)

Anyway, it seems that the reason for this 'bug' is found. Is there anything I can do to help you ?

@doctrinebot

Comment created by @deeky666:

[~leshatai] There is nothing you can do about when using pdosqlsrv. We added a notice to the docs to stick with sqlsrv if possible instead and skipped the BLOB related tests in the test suite. See: 637ef6a

@doctrinebot

Comment created by @deeky666:

I will close this for now as we cannot fix this driver bug. This might be reopended as soon as Microsoft fixes the bug in a newer version and we maybe can handle it properly.

@doctrinebot

Issue was closed with resolution "Can't Fix"

@doctrinebot

Comment created by le_shatai:

Ok... Thank you for your help. :) Hopefully MS will fix this...

@doctrinebot doctrinebot added the Bug label Dec 6, 2015
@deeky666 deeky666 was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment