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
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?
-ralph
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'
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...
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 http://framework.zend.com/issues/browse/ZF-6666 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.