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"
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'), 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.
Have you found an issue?
See the Overview section for more details.