Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-122

Impossible to save data to image/binary/varbinary

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Can't Fix
    • 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.

        Issue Links

          Activity

          Martin Weise created issue -
          Hide
          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
          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 ]
          Hide
          Steve Müller added a comment -

          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: https://github.com/doctrine/dbal/commit/854a67da503a9fd5a21bece282e3e32581d24d75
          What is STILL wrong is the image and binary mapping. I will fix the image and binary type mappings to blob.

          Show
          Steve Müller added a comment - 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: https://github.com/doctrine/dbal/commit/854a67da503a9fd5a21bece282e3e32581d24d75 What is STILL wrong is the image and binary mapping. I will fix the image and binary type mappings to blob.
          Steve Müller made changes -
          Assignee Benjamin Eberlei [ beberlei ] Steve Müller [ deeky666 ]
          Steve Müller made changes -
          Link This issue relates to DBAL-711 [ DBAL-711 ]
          Steve Müller made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Show
          Marco Pivetta added a comment - Steve Müller is this solved with https://github.com/doctrine/dbal/commit/c727c032a876e703ab964848ebf0a1eefed32a9a ?
          Hide
          Steve Müller added a comment -

          Marco PivettaI 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 Martin Weisethen.

          Show
          Steve Müller added a comment - Marco Pivetta 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 Martin Weise then.
          Steve Müller made changes -
          Link This issue relates to DBAL-714 [ DBAL-714 ]
          Hide
          Steve Müller added a comment -

          Martin Weise 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!

          Show
          Steve Müller added a comment - Martin Weise 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!
          Steve Müller made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Fix Version/s 2.5 [ 10523 ]
          Resolution Fixed [ 1 ]
          Hide
          Steve Müller added a comment -

          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

          Benjamin Eberlei 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.

          Show
          Steve Müller added a comment - 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 Benjamin Eberlei 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.
          Steve Müller made changes -
          Resolution Fixed [ 1 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Hide
          Benjamin Eberlei added a comment -

          Steve Müller 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.

          Show
          Benjamin Eberlei added a comment - Steve Müller 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.
          Hide
          Steve Müller added a comment -

          Benjamin Eberlei 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.

          Show
          Steve Müller added a comment - Benjamin Eberlei 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.
          Hide
          Steve Müller added a comment - - edited

          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.

          Show
          Steve Müller added a comment - - edited 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.
          Hide
          Martin Weise added a comment -

          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 ?

          Show
          Martin Weise added a comment - 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 ?
          Hide
          Steve Müller added a comment -

          Martin Weise There is nothing you can do about when using pdo_sqlsrv. 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: https://github.com/doctrine/dbal/commit/637ef6a1826a6937a5ab4fc4c73b8ede46732a73

          Show
          Steve Müller added a comment - Martin Weise There is nothing you can do about when using pdo_sqlsrv. 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: https://github.com/doctrine/dbal/commit/637ef6a1826a6937a5ab4fc4c73b8ede46732a73
          Hide
          Steve Müller added a comment -

          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.

          Show
          Steve Müller added a comment - 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.
          Steve Müller made changes -
          Status Reopened [ 4 ] Resolved [ 5 ]
          Fix Version/s 2.5 [ 10523 ]
          Resolution Can't Fix [ 7 ]
          Hide
          Martin Weise added a comment -

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

          Show
          Martin Weise added a comment - Ok... Thank you for your help. Hopefully MS will fix this...

          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[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

            People

            • Assignee:
              Steve Müller
              Reporter:
              Martin Weise
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: