ZF-9017: Zend_Db_Select does not prepend the schema name to the table name following the SELECT clause

Description

I've personally only tried this when querying a MS SQL Server 2005 database.

Zend_Db_Select fails to add the schema name to the table name when querying a table in the database that has a schema other than the default. So, the following code:

$select = $db->select()->from('my_schema.my_table_name'); $stmt = $select->query(); $result = $stmt->fetchAll();

Will have generated a SQL string that looks something like:

SELECT "my_table_name".* FROM "my_schema"."my_table_name"

rather than:

SELECT "my_schema"."my_table_name".* FROM "my_schema"."my_table_name"

As it currently stands the SQL Server returns an error, and an exception gets thrown. I've been able to update Zend_Db_Select::_renderColumns such that the schema name is prepended to the table name after the SELECT clause, and it works for my purposes, though I am not familiar enough with the code to know if it will work for everyone all the time.

Comments

I know this is an old issue, but I work with Justus Weber and can clarify this problem. Although the database in question is hosted on a SQL Server 2005 server, the database compatibility level is set to SQL Server 2000 (80). The compatibility level was probably the result of a 2000->2005 server upgrade. Before SQL Server 2005, the schema name is required on column names.

If someone is stuck with a SQL Server 2000 database or compatibility level, you can still use Zend_Db_Select by setting aliases.

Example:

$select = $db->select()
             ->from(array('alias' => 'my_schema.my_table_name'))
             ->joinInner(array('alias2' => 'my_schema.my_table_name2'),
                         'alias.ID = alias2.ID');

If you want to reference another database with a three-part name, you probably need to use the optional schema parameter of the from() and join*() methods. Presumably this works with four-part names as well but I haven't tried.

Example:

$select = $db->select()
             ->from(array('alias' => 'my_table_name'),
                    array(),
                    'my_database.my_schema')
             ->joinLeft(array('alias2' => 'my_table_name2'),
                        'alias.ID = alias2.ID',
                        array(),
                        'my_database.my_schema');

Using this approach, I have been able to query SQL Server 2000 compatibility level databases with Zend_Db_Select and Zend_Paginator. I have tested on pristine copies of the 1.10.7 and 1.11.12 Zend Frameworks.

So the query syntax of SQL 2000 definitely doesn't work well with Zend_Db_Select, but with some tricks it does work.