Doctrine 1
  1. Doctrine 1
  2. DC-545

MSSQL Server: Inserting blank records throws exception / Invalid casting of bound query parameters in sub-selects.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2.1
    • Fix Version/s: 1.2.2
    • Component/s: Query
    • Labels:
      None
    • Environment:
      PHP 5.3.1
      Windows XP
      Microsoft SQL Server 2008

      Description

      I've two issues with saving records with SQL Server.

      Firstly, SQL Server does not allow NULL or DEFAULT values to be used against identity columns. When inserting a record without any modified fields, Doctrine_Record_UnitOfWork::processSingleInsert sets all fields to NULL - including the identity columns. This causes this error:

      "SQLSTATE[42000]: Syntax error or access violation: 339 [Microsoft][SQL Server Native Client 10.0][SQL Server]DEFAULT or NULL are not allowed as explicit identity values"

      Ideally identity columns could be excluded but that would break tables that consist of those columns. Instead I've patched Doctrine_Connnection_MSSQL to, in this 'blank insert' circumstance only, insert default values and then update the inserted row with null values as necessary (the latter may be unnecessary but I wanted to preserve the original behaviour).

      Secondly, there exists a bug in (as I understand it) Microsoft's ODBC driver [1], that causes all bound parameters within a sub-select to be cast as strings before being interpreted by SQL Server. This results in the following error:

      SQLSTATE[22018]: Invalid character value for cast specification: 206

      [1] http://bugs.php.net/36561

      When using SQL Server through ODBC all Doctrine queries that use sub-selects are failing, for example the query generated by a Doctrine_Table->find()

      SELECT * FROM (SELECT TOP 1 * FROM (SELECT TOP 1 [m].[id] AS [m__id], [m].[name] AS [m__name] FROM [model] [m] WHERE ([m].[id] = ?)) AS [inner_tbl]) AS [outer_tbl]

      To combat this I've extended the Doctrine_Connection_Common execute() method in Doctrine_Connection_Mssql to replace bound parameters so they're inline instead. This has its drawbacks, like reduced protection against SQL injection, but since the alternative is the majority of my queries failing I don't see a better option.

      I realise this second issue isn't a Doctrine concern per se, but I thought I'd bring it up since I imagine anyone using SQL server will be experiencing it.

      A patch against 1.2.1 is attached. This patch also incorporates the latest patch in http://www.doctrine-project.org/jira/browse/DC-289, which has now been committed to trunk; my additions are at the bottom of the file.

        Activity

        Hide
        Craig Marvelley added a comment -

        Updated original ticket as another issue I found is closely linked and involved refactoring of my original patch.

        Show
        Craig Marvelley added a comment - Updated original ticket as another issue I found is closely linked and involved refactoring of my original patch.
        Hide
        Craig Marvelley added a comment -

        Updated patch

        Show
        Craig Marvelley added a comment - Updated patch
        Hide
        Jonathan H. Wage added a comment -

        Hi, I tried applying your patch to Doctrine 1.2 latest SVN to give it a test and it did not apply cleanly. Please re-open if you can provide an updated patch.

        Thanks, Jon

        Show
        Jonathan H. Wage added a comment - Hi, I tried applying your patch to Doctrine 1.2 latest SVN to give it a test and it did not apply cleanly. Please re-open if you can provide an updated patch. Thanks, Jon
        Hide
        Craig Marvelley added a comment -

        Updated patch that should apply cleanly.

        Show
        Craig Marvelley added a comment - Updated patch that should apply cleanly.
        Hide
        Craig Marvelley added a comment -

        Hi, thanks for looking at this. I've updated the patch against 1.2 HEAD, hopefully that'll apply for you.

        Show
        Craig Marvelley added a comment - Hi, thanks for looking at this. I've updated the patch against 1.2 HEAD, hopefully that'll apply for you.

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Craig Marvelley
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: