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

Description

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 foo.bar.baz INNER JOIN sample.zend.framework ON baz.some_key = framework.some_key

Note that it is not possible to do the following:

sp_columns 'foo.bar.baz' 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 ('foo.bar', '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).

Comments

Changes in the sp_columns syntax

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?

-ralph

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

Instead of:

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

and

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...

Updating the versions affected

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

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

Another good patch which is left a some time is this http://framework.zend.com/issues/browse/ZF-6666 I will solve problems between linux and windows drivers.

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

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