Very special problem with UniDAC, SQL-Server and Primary Key

invent · Post by **invent** » Tue 19 Jul 2011 15:58

Hi,

I have a very special problem and it is not easy to explain. I hope you understand what I mean.

First, we have an application in Delphi 7 with UniDAC 3.50.0.13 running on InterBase XE, Oracle 8.0.5 and MS SQL-Server 2008.

Now I wrote a function that reads the primary key of a table. Here I use TUniMetaData from UniDAC and this works fine with all servers. Below is the code from this function, but I think it's correct.

Under SQL-Server 2008 I defined two Users "USER1" and "USER2" with own tables. But USER1 owns a table KUNDEN, which is free for USER2. USER2 can select, update, insert, delete on this table. It's defined with rights and synonyms. For example USER2 exceutes "select * from KUNDEN". This works fine too.

USER1 gets the correct primary key with my function.

Now the problem: USER2 is unable to use the function to get the primary key of table KUNDEN.

The function I wrote has an empty result.

To debug this problem I used the SQL-Server-Profiler. I found two lines from TMetadata:

exec [USER2].[sys].sp_table_constraints_rowset N'KUNDEN',NULL,N'USER2',NULL,NULL,NULL,NULL

exec [USER2].[sys].sp_indexes_100_rowset N'KUNDEN',NULL,NULL

Both were completed successful. But no result.

Now my questions:

- What can I do?
- Do USER2 needs special rigths?
- Maybe an error in TMetadata?
- Is this impossible on SQL-Server?

And at last a suggestion: It will be nice, when DevArt DBMonitor shows the TMetaData-statements.

Thanks in advance for any idea.

Kind regards,
Gerd Brinkmann
invent GmbH

Code: Select all

procedure BestimmePrimaerSchluesselFelder (    DBConnection : TUniConnection;
                                               TabellenName : string;
                                           var arPKFelder   : TWwsStringArray);

  var tmpUniMetaData : TUniMetaData;
      PKName,
      AktFeldName    : string;
      i,
      arPos          : integer;
      FeldVorhanden  : boolean;

  begin
  setLength (arPKFelder, 0);
  PKName := '';

  tmpUniMetaData := TUniMetaData.Create (nil);
  tmpUniMetaData.Connection   := DBConnection;

  tmpUniMetaData.MetaDataKind := 'Constraints';
  tmpUniMetaData.Restrictions.Clear;
  tmpUniMetaData.Restrictions.Add ('TABLE_NAME=' + TabellenName);
  tmpUniMetaData.Open;

  if not tmpUniMetaData.IsEmpty
    then begin
         tmpUniMetaData.First;
         repeat
           if   UpperCase (tmpUniMetaData.FieldByName ('CONSTRAINT_TYPE').AsString)
              = 'PRIMARY KEY'
             then begin
                  if UniDacTreibername (DBConnection) = 'INTERBASE'
                    then PKName := tmpUniMetaData.FieldByName ('INDEX_NAME').AsString
                    else PKName := tmpUniMetaData.FieldByName ('CONSTRAINT_NAME').AsString;
                  break;
                  end;
           tmpUniMetaData.Next;
         until tmpUniMetaData.Eof;
         end;
  tmpUniMetaData.Close;


  tmpUniMetaData.MetaDataKind := 'IndexColumns';
  tmpUniMetaData.Restrictions.Clear;
  tmpUniMetaData.Restrictions.Add ('TABLE_NAME=' + TabellenName);
  tmpUniMetaData.Restrictions.Add ('UNIQUE=1');
  tmpUniMetaData.Open;

  if not tmpUniMetaData.IsEmpty
    then begin
         tmpUniMetaData.First;
         repeat
           AktFeldName   := tmpUniMetaData.FieldByName ('COLUMN_NAME').AsString;
           FeldVorhanden := false;
           if     (UpperCase (AktFeldName)  'ROWID')
              and (tmpUniMetaData.FieldByName ('INDEX_NAME').AsString = PKName)
             then begin
                  for i := 0 to High (arPKFelder) do
                    begin
                    if AktFeldName = arPKFelder [i]
                      then FeldVorhanden := true;
                    end; { for }
                  if not FeldVorhanden
                    then begin
                         arPos := High (arPKFelder) + 1;
                         SetLength (arPKFelder, ArPos + 1);
                         arPKFelder [arPos] := AktFeldName;
                         end;
                  end; { AktFeldname  'RowId }
           tmpUniMetaData.Next;
         until tmpUniMetaData.Eof;
         end;

  tmpUniMetaData.Close;
  tmpUniMetaData.Free;
  end; { BestimmePrimaerSchluesselFelder }

AndreyZ · Post by **AndreyZ** » Wed 20 Jul 2011 12:20

Hello,

The point is that the sp_table_constraints_rowset system stored procedure returns information only about server objects the user owns. We will investigate ways to avoid this problem.

invent · Post by **invent** » Wed 20 Jul 2011 17:41

Hello,

thanks for this fast reply. I hope there is a solution for this problem.

Kind regards,
Gerd Brinkmann
invent GmbH

AndreyZ · Post by **AndreyZ** » Fri 26 Aug 2011 11:36

We have investigated the problem. This problem occurs only with SQL Server 2000, and it is caused by a bug in the sp_table_constraints_rowset stored procedure. You will not have such problem with newer versions of SQL Server.

invent · Post by **invent** » Fri 26 Aug 2011 17:35

Hello,

what can I say? Like I wrote in the original post, this happens with SQL Server 2008 R2 Standard Edition. What can I do?

Kind regards,
Gerd Brinkmann
invent GmbH

AndreyZ · Post by **AndreyZ** » Tue 30 Aug 2011 14:09

Please describe in details the steps you performed to create the USER1 and USER2 users, and the rights you gave them.

invent · Post by **invent** » Mon 19 Sep 2011 13:46

Hello,

sorry for the late reply, but I had 2 weeks holiday.

I don't think that there is a problem with the rights. We checked this and gave USER2 all rights for the tables of USER1.

We made some tests with the stored procedure and i think there is a problem with the sql-command from TMetadata. I try to explain this:

USER1 owns a table KUNDEN
USER2 has defined a synonym called KUNDEN for USER1.KUNDEN.
TUniConnection.Connect for USER2.

Then TMetaData read the Primary Key with

Code: Select all

exec [USER2].[sys].sp_table_constraints_rowset N'KUNDEN',NULL,N'USER2',NULL,NULL,NULL,NULL

But this is wrong, because KUNDEN is not a table from USER2.

The correct code is:

Code: Select all

exec [USER1].[sys].sp_table_constraints_rowset N'KUNDEN',NULL,N'USER1',NULL,NULL,NULL,NULL

This works for connected USER2!!!

So the investigation of the primary key needs different commands for tables and synonyms.

Kind regards,
Gerd Brinkmann
invent GmbH

AndreyZ · Post by **AndreyZ** » Tue 20 Sep 2011 14:18

The point is that the sp_table_constraints_rowset system stored procedure returns information only about tables, but not synonyms. If user2 doesn't have rights to see the KUNDEN table, sp_table_constraints_rowset will not return information about this table.

invent · Post by **invent** » Tue 20 Sep 2011 14:54

Hi,

when you say

sp_table_constraints_rowset system stored procedure returns information only about tables, but not synonyms

then TMetaData is wrong, because with the command

Code: Select all

exec [USER2].[sys].sp_table_constraints_rowset N'KUNDEN',NULL,N'USER2',NULL,NULL,NULL,NULL

TMetaData looks for information about synonyms.

And again, USER2 has all rights for USER1's table.

Kind regards,
Gerd Brinkmann
invent GmbH

AndreyZ · Post by **AndreyZ** » Wed 21 Sep 2011 10:30

The sp_table_constraints_rowset doesn't use user names in its code. It uses table name and database name (table catalog). Here is syntax of the sp_table_constraints_rowset stored procedure:

Code: Select all

sp_table_constraints_rowset @table_name, @table_schema, @table_catalog, @constraint_name, @constraint_schema, @constraint_catalog, @constraint_type

If USER2 has all needed rights for a table, then execution of the following code will return information about table constraints:

Code: Select all

exec [table_catalog].[sys].sp_table_constraints_rowset N'tablename',NULL,N'table_catalog',NULL,NULL,NULL,NULL

You can see the text of the sp_table_constraints_rowset stored procedure by executing the following code:

Code: Select all

sp_helptext sp_table_constraints_rowset

Here is the code of this stored procedure:

Code: Select all

create procedure sys.sp_table_constraints_rowset
(
    @table_name         sysname,
    @table_schema       sysname = null,
    @table_catalog      sysname = null,
    @constraint_name    sysname = null,
    @constraint_schema  sysname = null,
    @constraint_catalog sysname = null,
    @constraint_type    nvarchar(255) = null
)
as
    select
        CONSTRAINT_CATALOG  = db_name(),
        CONSTRAINT_SCHEMA   = schema_name(o.schema_id),
        CONSTRAINT_NAME     = o.name,
        TABLE_CATALOG       = db_name(),
        TABLE_SCHEMA        = schema_name(t_obj.schema_id),
        TABLE_NAME          = t_obj.name,
--        CONSTRAINT_TYPE     = case (syscon.status & 0xf)
        CONSTRAINT_TYPE     = case syscon.type
--                                when 1 then N'PRIMARY KEY'
                                when 'PK' then N'PRIMARY KEY'
--                                when 2 then N'UNIQUE'
                                when 'UQ' then N'UNIQUE'
--                                when 3 then N'FOREIGN KEY'
                                when 'F' then N'FOREIGN KEY'
--                                when 4 then N'CHECK'
                                when 'C' then N'CHECK'
                                else null
                              end,
        IS_DEFERRABLE       = convert(bit, 0),
        INITIALLY_DEFERRED  = convert(bit, 0),
        DESCRIPTION         = convert(nvarchar(1), null)
    from
        sys.all_objects o,
        sys.all_objects t_obj,
--        sysconstraints syscon
        sys.all_objects syscon
    where
        t_obj.name  = @table_name and
        t_obj.type in ('U','S') and
        (@table_catalog is null or @table_catalog = db_name()) and
        (@table_schema is null or @table_schema = schema_name(t_obj.schema_id)) and
        syscon.parent_object_id = t_obj.object_id and
        (syscon.type in ('PK', 'UQ', 'F', 'C')) and
        o.object_id    = syscon.object_id and
        o.schema_id = schema_id() and
        (@constraint_name is null or o.name = @constraint_name) and
        (@constraint_catalog is null or @constraint_catalog = db_name()) and
        (@constraint_schema is null or @constraint_schema = schema_name(o.schema_id)) and
        (@constraint_type is null or
         syscon.type = case @constraint_type

                       when N'PRIMARY KEY' then 'PK'
                       when N'UNIQUE' then 'UQ'
                       when N'FOREIGN KEY' then 'F'
                       when N'CHECK' then 'C'
                       end)
    order by 2,3,5,6,7

As you can see, in the Where clause it has the following code:

Code: Select all

t_obj.type in ('U','S') and

It means it looks only for tables and system tables, and cannot return information about synonyms (the 'SN' object type).

invent · Post by **invent** » Thu 22 Sep 2011 08:36

Hello AndreyZ,

I want to stop this discussion because we are not writing about the same things. I'm talking about TUniMetaData und you give me informations about SQL Server 2000, user rights and stored procedures. That's interesting but it doesn't solve the problem. I wished you had said something about TUniMetaData, because there is the problem:

With Oracle TUniMetaData shows for Synonyms the correct Primary Key.
With SQL Server TUniMetaData gives for Synonyms NOT the Primary Key.

IMHO this is a bug in TUniMetaData, nothing else!

UniDAC stand for "one code and the sames results on different databases". When I create two Databases in Oracle and SQL Server at the same way (same tables, fields, indexes, synonyms), then I expect the same results. That's the reason, why your customers buy UniDAC.

And I cannot understand, why you don't want to fix this bug, because it's very easy:

Code: Select all

if TABLE_TYPE = SYNONYM then read CATALOG_NAME from sys.synonyms and use that

This cannot be very difficult. But no matters, again I have to write a workaraound. I will post this for the other members soon.

BTW: If you don't want to change TUniMetaData, it would have been a good support, if you had answered:

"To read information about synonyms in SQL Server, you have to set TABLE_CATALOG:"

Code: Select all

tmpUniMetaData.Restrictions.Add ('TABLE_CATALOG=' + TablesDatabasename);

Kind regards,
Gerd Brinkmann
invent GmbH

AndreyZ · Post by **AndreyZ** » Mon 26 Sep 2011 13:23

To get information about constraints, the TUniMetaData component uses the sp_table_constraints_rowset SQL Server system stored procedure. This stored procedure doesn't return information about synonyms. We cannot fix it because it is not our functionality, but SQL Server's. For Oracle, TUniMetaData uses query to the SYS.ALL_CONSTRAINTS system table, that doesn't contain information about synonyms as well.
If I understood correctly, you have two databases: USER1 and USER2. When you are connecting to the USER2 database (using user2 login and password) and trying to obtain constraints of the KUNDEN table, you don't get information about constraints because there is no KUNDEN table in the USER2 database. When you are connecting to the USER1 database (using user2 login and password) and trying to obtain constraints of the KUNDEN table, you get this information because there is KUNDEN table in the USER1 database.