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

Issue Type: Bug Created: 2010-01-29T13:49:17.000+0000 Last Updated: 2012-08-02T18:18:43.000+0000 Status: Open Fix version(s): Reporter: Justus Weber (justusw) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db_Select

Related issues: Attachments:


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.


Posted by Andrew Keller (akeller) on 2012-08-02T18:18:43.000+0000

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.


<pre class="literal">
$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.


<pre class="literal">
$select = $db->select()
             ->from(array('alias' => 'my_table_name'),
             ->joinLeft(array('alias2' => 'my_table_name2'),
                        'alias.ID = alias2.ID',

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.

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.