Doctrine DBAL

Impossible to save data to image/binary/varbinary

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Major 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

Ascending order - Click to sort in descending order
Hide
Permalink
Benjamin Eberlei added a comment -

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 - 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
Hide
Permalink
Martin Weise added a comment -

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 - 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
Benjamin Eberlei made changes -
Field Original Value New Value
Workflow jira [ 12637 ] jira-feedback2 [ 17573 ]
Benjamin Eberlei made changes -
Workflow jira-feedback2 [ 17573 ] jira-feedback3 [ 19927 ]
This list may be incomplete, as errors occurred whilst retrieving source from linked applications:
  • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-122, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Martin Weise
Vote (0)
Watch (0)

Dates

  • Created:
    Updated: