MSSQL Cross-database query problem (Mantis issue id 0000154)

[dstaniak]

Hi.

Recently I've begun to port some old, but very large desktop application from Borland C++ Builder to Lazarus. I've found the Zeoslib only component library suitable for replacing proprietary SQLDirect. I've ported the code to Delphi (with fantastic Perl script), then made it work, then moved to Lazarus. Everything went ok until I stucked at cross-table query. Doesn't matter if it's using FreeTDS or MS SQL ntwdblib, the cross-table query simply does not work.

I needed this feature badly, so I've managed to fix the Zeos source code by changing the file "ZDbcDbLibMetadata.pas".

I've replaced this:

Code: Select all

    with GetStatement.ExecuteQuery(
      Format('exec sp_columns %s, %s, %s, %s',
	 [ComposeObjectString(TableNamePattern),
         ComposeObjectString(SchemaPattern),
         ComposeObjectString(Catalog),
         ComposeObjectString(ColumnNamePattern)])) do

with this:

Code: Select all

    with GetStatement.ExecuteQuery(
      Format('exec %s.%s.sp_columns %s, %s, %s, %s',
        [Catalog,
         SchemaPattern,
	 ComposeObjectString(TableNamePattern),
         ComposeObjectString(SchemaPattern),
         ComposeObjectString(Catalog),
         ComposeObjectString(ColumnNamePattern)])) do

and this:

Code: Select all

    with GetStatement.ExecuteQuery(
      Format('exec sp_column_privileges %s, %s, %s, %s',
      [ComposeObjectString(Table), 
       ComposeObjectString(Schema), 
       ComposeObjectString(Catalog), 
       ComposeObjectString(ColumnNamePattern)])) do

with this:

Code: Select all

    with GetStatement.ExecuteQuery(
      Format('exec %s.%s.sp_column_privileges %s, %s, %s, %s',
      [Catalog,
       Schema,
       ComposeObjectString(Table), 
       ComposeObjectString(Schema), 
       ComposeObjectString(Catalog), 
       ComposeObjectString(ColumnNamePattern)])) do

Sorry for posting this patch here, but I could not register into Mantis. I've tested this patch with and without cross-database queries and it works fine. I think that you should include my modification in upcoming releases, because I think it fixes a major issue.

I've tested the code od Server 2012 Express and all the following queries run fine:

Code: Select all

exec ..sp_columns ...
exec .dbo.sp_columns ...
exec database.dbo.sp_columns ...

Cheers!

[EgonHugeist]

Hi,

i've already noticed your patch! Thanks a lot for sharing.
Actually i had no time to test your proposal but i think i do understand what you're doing.

Mantis is readonly, i think (except for Dev-members like me, of couse) since we use http://sourceforge.net/p/zeoslib/tickets/

I'll make a second reply if i have objections or simply apply the patch.
In addition ... Shouldn't all 'exec sp_xxxxx' be handled same way?

[dstaniak]

Thanks for your concern. I'll try to examine the Zeos source to find sp_*, but generally yes, I think, all system stored procedures should be called within proper context. This does not hurt and should fix other potential problems. My patch is quick and dirty and does not work with canonical MS SQL names. While other SQL engines use " sign to indicate object names, the MS SQL utilizes the [ ] pair.

So with this simple patch such query will work:

Code: Select all

select * from SomeBase.dbo.SomeTable

but this will not:

Code: Select all

select * from [SomeBase].dbo.[SomeTable]

But currently it's better than not working.

[EgonHugeist]

But currently it's better than not working.

Agree. Did apply your patch: R3488 \testing-7.2 (SVN)
My regression tests do not show a behavior change. Note i did NOT patch all SP_XXXX calls just some, since i've no idea if eeverything works or not.

Thanks for contribution!

[dstaniak]

Excellent! I hope someone will benefit from this patch also. At my local Zeos installation i've changed all the "sp_*" calls except these:

Code: Select all

exec sp_databases
exec sp_datatype_info
exec sp_jdbc_getschemas
exec sp_jdbc_getcatalogs
exec sp_jdbc_datatype_info

By now everything seems to be ok. Thanks for applying the patch! Cheers!

[EgonHugeist]

Excellent! I hope someone will benefit from this patch also. At my local Zeos installation i've changed all the "sp_*" calls except these:

Code: Select all

exec sp_databases
exec sp_datatype_info
exec sp_jdbc_getschemas
exec sp_jdbc_getcatalogs
exec sp_jdbc_datatype_info

By now everything seems to be ok. Thanks for applying the patch! Cheers!

Sounds logical to me. I think i'll add your suggested code too.

[miab3]

@dstaniak, @EgonHugeist,

SELECT * FROM INFORMATION_SCHEMA.TABLES
http://www.mssqltips.com/sqlservertutor ... ematables/
http://www.mssqltips.com/sqlservertutor ... ema-views/
stopped working, why?
(FreeTDS)

Michal

[dstaniak]

Damn it! Yes,

Code: Select all

select * from INFORMATION_SCHEMA.

will not work. It seems that when the Catalog = 'INFORMATION_SCHEMA' the call to "sp_*" procedures should be executed without the "INFORMATION_SCHEMA" context. So this is an exception. I wonder if there are more such exceptions there? I don't have any idea for generic solution to this problem. The only solution I can think of now is something like this:

Code: Select all

var ProcCtxCatalog, ProcCtxSchema : String;
.
.
.
if UpperCase(Catalog) = 'INFORMATION_SCHEMA' then begin
  ProcCtxCatalog := '';
  ProcCtxSchema := '';
end else begin
  ProcCtxCatalog := Catalog;
  ProcCtxSchema := Schema;
end;

with.GetStatement.ExecuteQuery(
  Format('exec %s.%s. ...
              [ProcCtxCatalog, ProcCtxSchema, ...

[EgonHugeist]

Puff and now? From my point of view: Rollback. I'm affraid!.

Current proposal is a true ad hock thing.
Michal, are there more issues to expect? OR is there really only an information_schema issue?

Might be a good idea to add this case to a test!

[dstaniak]

Okay, so the problem exists with access to "master" database. The MS SQL parser allows maximum of 3 prefixes, so when it sees something like

Code: Select all

master.INFORMATION_SCHEMA.COLUMNS

it thinks that "master" is the identifier of linked server object. I've managed to overcome this issue by applying a simple patch to my earlier solution.

Here:

Code: Select all

function TZMsSqlDatabaseMetadata.UncachedGetColumns(const Catalog: string;
  const SchemaPattern: string; const TableNamePattern: string;
  const ColumnNamePattern: string): IZResultSet;
var
  SQLType: TZSQLType;
  Context : String;
begin
    Result:=inherited UncachedGetColumns(Catalog, SchemaPattern, TableNamePattern, ColumnNamePattern);

    Context := Catalog + '.';
    if LowerCase(Catalog) <> 'master' then Context := Context + SchemaPattern;

    with GetStatement.ExecuteQuery(
      Format('exec %s.sp_columns %s, %s, %s, %s',
        [Context,
	 ComposeObjectString(TableNamePattern),
         ComposeObjectString(SchemaPattern),
         ComposeObjectString(Catalog),
         ComposeObjectString(ColumnNamePattern)])) do

and here

Code: Select all

function TZMsSqlDatabaseMetadata.UncachedGetColumnPrivileges(const Catalog: string;
  const Schema: string; const Table: string; const ColumnNamePattern: string): IZResultSet;
var Context : String;
begin
    Result:=inherited UncachedGetColumnPrivileges(Catalog, Schema, Table, ColumnNamePattern);

    Context := Catalog + '.';
    if LowerCase(Catalog) <> 'master' then Context := Context + Schema;

    with GetStatement.ExecuteQuery(
      Format('exec %s.sp_column_privileges %s, %s, %s, %s',
      [Context,
       ComposeObjectString(Table), 
       ComposeObjectString(Schema), 
       ComposeObjectString(Catalog), 
       ComposeObjectString(ColumnNamePattern)])) do

Now my cross-database queries, normal queries and INFORMATION_SCHEMA queries work ok. You just need to change the INFORMATION_SCHEMA query from

Code: Select all

select * from INFORMATION_SCHEMA.TABLES

to

Code: Select all

select * from master.INFORMATION_SCHEMA.TABLES

Due to complexity of object prefixing in MS SQL and to fragility of changes I've decided only to patch two functions in "ZDbcDbLibMetadata.pas", which are those seen above.

Maybe such solution would be ok for most people?