ZF-6665: Metadata for tables from a different catalog in MSSQL

Issue Type: Bug Created: 2009-05-14T19:37:32.000+0000 Last Updated: 2013-02-10T16:18:01.000+0000 Status: Open Fix version(s): Reporter: Alexandre Gomes Gaigalas (alganet) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

Related issues: Attachments: - Mssql.php.patch


MSSQL servers can handle queries against multiple databases. Also, MSSQL works with schemas, so the tables could be organized in the following manner:

-Database Foo -----Schema Bar ---------Table Baz -Database Sample -----Schema Zend ---------Table Framework

Using any database, presuming you have the appropriate permissions, you can do the following query:

SELECT baz.column FROM INNER JOIN sample.zend.framework ON baz.some_key = framework.some_key

Note that it is not possible to do the following:

sp_columns '' sp_columns 'sample.zend.framework'

In order to obtain a list of columns from a table, you have to be on that table original database, like this:

USE foo; sp_columns 'baz', 'bar'; USE sample; sp_columns 'framework', 'zend';

I'm currently using workarounds in my applications to circumvent this limitation:

$db->exec('USE foo;'); //Changes current database $barBazTable->fetchAll(); //Can normally fetch metadata under the right context $db->exec('USE sample;'); $zendFrameworkTable->fetchAll();

My current approach doesn't work with tables with the same name in different schemas (sales.people, employees.people).

My Zend_Db_Table objects also had they "$_schema" properties with the whole schema identifier, including database name ('', 'sample.zend', etc..). These works fine.

I think the "USE" statements should be called while describing the table (sp_columns, sp_pkeys, etc...) inside Zend_Db_Adapter_Pdo_Mssql, and then restored to the original database (specified on connection).


Posted by Alexandre Gomes Gaigalas (alganet) on 2009-08-22T22:48:08.000+0000

Changes in the sp_columns syntax

Posted by Ralph Schindler (ralph) on 2009-08-26T07:47:30.000+0000

I think it would be bad practice to execute a USE during the lifetime of an application. This would effectively be changing the context of the primary adapter. When other sections of code attempt to execute with the primary adapter, they might be assuming they are still in fact using the schema/database that was outlined in the connection information. That said, I think its important that whatever schema/database was originally attached to be the adapter's primary context with regard to queries.

So, is it right to assume that you are talking about Zend_Db_Adapter_Pdo_Mssql::describeTable()? If so, what benefit do we see when using USE prior to calling our describeTable query? Is there really no way to get the data about a table in another schema without using USE, for example by crafting a different query?


Posted by Alexandre Gomes Gaigalas (alganet) on 2009-08-26T13:11:17.000+0000

I did some research, and seems to be pretty simple:

Instead of:

sp_columns @table_name = 'the_table_name', @table_owner = 'the_table_owner'


sp_pkeys @table_name = 'the_table_name', @table_owner = 'the_table_owner'

Just prefix it with the catalog name and two dots:

catalog_name..sp_columns @table_name = 'the_table_name', @table_owner = 'the_table_owner' catalog_name..sp_pkeys @table_name = 'the_table_name', @table_owner = 'the_table_owner'

I tested theses statements in SQL Server 2005. I'm Working on a patch right now...

Posted by Alexandre Gomes Gaigalas (alganet) on 2009-08-26T13:27:32.000+0000

Updating the versions affected

Posted by Ralph Schindler (ralph) on 2009-08-26T13:37:28.000+0000

excellent, please post patch when you got one, thanks!

Posted by Alexandre Gomes Gaigalas (alganet) on 2009-08-26T14:22:36.000+0000

Patch attached to the issue. Thanks to Jayson Reis for the help creating it.

Posted by Jayson Santos dos Reis (jaysonsantos) on 2009-08-26T14:53:12.000+0000

Another good patch which is left a some time is this I will solve problems between linux and windows drivers.

Posted by Jayson Santos dos Reis (jaysonsantos) on 2009-08-28T06:49:14.000+0000

Consider just the second one because the first was missing a "." at line 25.

Posted by Jayson Santos dos Reis (jaysonsantos) on 2009-08-28T07:38:16.000+0000

Strange, files attachment is not incremental, so, the working patch is the first one.

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.