Doctrine 1

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

Details

  • Type: Bug Bug
  • Status: Closed 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. Text File
    ff.txt
    03/Dec/09 2:39 PM
    1 kB
    aiso haikens
  2. Text File
    mssqlimit.patch
    07/Dec/09 8:48 PM
    3 kB
    Juozas Kaziukenas
  3. Text File
    mssqllimit.patch
    05/Dec/09 5:02 AM
    1 kB
    Juozas Kaziukenas
  4. Text File
    mssqlllimit2.patch
    09/Dec/09 3:09 AM
    0.8 kB
    Juozas Kaziukenas

Issue Links

is duplicated by

Bug - A problem which impairs or prevents the functions of the product. DC-289 Using MSSQL connection the find method for models does not work

  • Major - Major loss of function.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed can be reopened.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
Michael Card added a comment -

Should be closed as duplicate.

Show
Michael Card added a comment - Should be closed as duplicate.
Hide
Permalink
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
Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: