Doctrine 1
  1. Doctrine 1
  2. DC-318

Doctrine_Table::find(x) fails for odbc/mssql connection due to limit() clause

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Duplicate
    • Affects Version/s: 1.2.0
    • Fix Version/s: 1.2.2
    • Component/s: None
    • Labels:
      None
    • Environment:
      windows XP SP3

      Description

      call that fails is Doctrine::getTable('Building')->find(1). With mysql all is fine, but if I use MSSQL via odbc (SQL Native Client) I get an exception saying:
      SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][SQL Native Client][SQL Server]Er is een conflict met het type operand ontstaan: text is incompatibel met int (SQLExecute[206] at ext\pdo_odbc\odbc_stmt.c:254)
      see attached stacktrace.
      So find(1) fails but a call to e.g. findAll() works fine.

      I found out that if I leave out the limit(1) from the function find() on line 1602 it gives me the correct object and no exception!
      So actually what goes wrong is the limit(1) clause in this call:
      $object = Doctrine_Query::create()>from('Building b')>where('b.id = 1')>limit(1)>fetchOne();
      with getSqlquery() this becomes:
      SELECT TOP 1 [inner_tbl].[id] AS [b__id] FROM (SELECT TOP 1 [b].[id] AS [b__id], [b].[version] AS [b__version], [b].[name] AS [b__name], [b].[number] AS [b__number], [b].[title_nl] AS [b__title_nl], [b].[title_en] AS [b__title_en], [b].[published] AS [b__published] FROM [building] [b] WHERE ([b].[id] = ?)) AS [inner_tbl]

      If executed with sqlcmd.exe this gives an error saying 'no columnname id', what seems to be correct since it should be [inner_tbl].[b__id] iso [inner_table].[id], so I suspect that the modifyLimitQuery() function in Connection/Mssql.php is not correct either.

      I can work around this problem by doing a fetchOne() iso find().

      1. ff.txt
        1 kB
        aiso haikens
      2. mssqlimit.patch
        3 kB
        Juozas Kaziukenas
      3. mssqllimit.patch
        1 kB
        Juozas Kaziukenas
      4. mssqlllimit2.patch
        0.8 kB
        Juozas Kaziukenas

        Issue Links

          Activity

          Hide
          Juozas Kaziukenas added a comment -

          This is because of:

          $field_array = explode(',', $fields_string);
          $aux2 = explode('.', $field_array[0]);
          $key_field = trim(end($aux2));

          With query like SELECT t.id as t__id FROM X it works like this:

          SELECT t.id as t__id FROM X – >
          t.id as t__id -->
          array('t.id as t__id') -->
          array('t', '.id as t__id')

          Which is wrong. It should split on "as" an then use it.

          I created a patch for this, which should make it work. Test it if you can and I hopefully it will be included into source.

          Show
          Juozas Kaziukenas added a comment - This is because of: $field_array = explode(',', $fields_string); $aux2 = explode('.', $field_array [0] ); $key_field = trim(end($aux2)); With query like SELECT t.id as t__id FROM X it works like this: SELECT t.id as t__id FROM X – > t.id as t__id --> array('t.id as t__id') --> array('t', '.id as t__id') Which is wrong. It should split on "as" an then use it. I created a patch for this, which should make it work. Test it if you can and I hopefully it will be included into source.
          Hide
          Jonathan H. Wage added a comment -

          The patch looks good it just doesn't follow the coding standards. $variableName not $variable_name and

          }
          else
          {
          

          Should be

          } else {
          

          You might want to read up on the Doctrine coding standards. Whenever you commit something you must make 100% sure that it follows the standards. If you fix the coding standards in the patch you can commit it.

          Show
          Jonathan H. Wage added a comment - The patch looks good it just doesn't follow the coding standards. $variableName not $variable_name and } else { Should be } else { You might want to read up on the Doctrine coding standards. Whenever you commit something you must make 100% sure that it follows the standards. If you fix the coding standards in the patch you can commit it.
          Hide
          Juozas Kaziukenas added a comment -

          I reattached updated patch, please review if that's what you meant and I will commit it. Variables names were already there, but I fixed them now

          Show
          Juozas Kaziukenas added a comment - I reattached updated patch, please review if that's what you meant and I will commit it. Variables names were already there, but I fixed them now
          Hide
          Michael Card added a comment -

          All the if statements are unnecessary, look a few lines above, the 2-3 lines handle splitting on the as and "." without all the if statements. Related to issue DC-289

          Show
          Michael Card added a comment - All the if statements are unnecessary, look a few lines above, the 2-3 lines handle splitting on the as and "." without all the if statements. Related to issue DC-289
          Hide
          Juozas Kaziukenas added a comment -

          Yes, somehow missed that one. This one should fix it

          Show
          Juozas Kaziukenas added a comment - Yes, somehow missed that one. This one should fix it
          Hide
          Juozas Kaziukenas added a comment -

          I looked at DC-289 and it seems to be the same thing as this error. So I suggest closing this as duplicate and fixing that one with a patch provided.

          Show
          Juozas Kaziukenas added a comment - I looked at DC-289 and it seems to be the same thing as this error. So I suggest closing this as duplicate and fixing that one with a patch provided.
          Hide
          aiso haikens added a comment -

          I agree, it is a duplicate of DC-289. I will add my comments about this patch to that one.

          Show
          aiso haikens added a comment - I agree, it is a duplicate of DC-289 . I will add my comments about this patch to that one.
          Hide
          Michael Card added a comment -

          Should be closed as duplicate.

          Show
          Michael Card added a comment - Should be closed as duplicate.
          Hide
          Gilberto added a comment -

          Hello everyone, i'm new here but i'm having the same problem, but i'm not clear about the fixing, please could someone telme realy how can i patch the select sql problem whit mssql, i'm using symfony 1.4 and mssql server 2005, and when i do:

          Doctrine_Query::CREATE()
          ->select('u.username')
          ->from('User u')
          ->execute();

          then i get the messagge error:
          SQLSTATE[HY000]: General error: 10007 Invalid
          column name...

          to resolve this is very important for me.

          sorry my english

          Show
          Gilberto added a comment - Hello everyone, i'm new here but i'm having the same problem, but i'm not clear about the fixing, please could someone telme realy how can i patch the select sql problem whit mssql, i'm using symfony 1.4 and mssql server 2005, and when i do: Doctrine_Query::CREATE() ->select('u.username') ->from('User u') ->execute(); then i get the messagge error: SQLSTATE [HY000] : General error: 10007 Invalid column name... to resolve this is very important for me. sorry my english

            People

            • Assignee:
              Jonathan H. Wage
              Reporter:
              aiso haikens
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: