problem with metadata in SQL 2008 using sdac 4.7

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
masi56
Posts: 2
Joined: Wed 25 Jul 2012 05:46

problem with metadata in SQL 2008 using sdac 4.7

Post by masi56 » Wed 25 Jul 2012 05:57

when i use the query below in sql 2000 using sdac 4.7 everything is ok, i mean the metadata for fields is ok, i can find key fields... DetectIdentityField works fine, where field1 is autoincrement:

select field1, field2
from table1
where condition1
union all
select field1, field2
from table1
where condition2


but when i use this query in sql 2008, i face problem and sdac cannot find autoincrement field...



so, is there any problem with 2008 or i should change any configuration?

AndreyZ

Re: problem with metadata in SQL 2008 using sdac 4.7

Post by AndreyZ » Wed 25 Jul 2012 10:43

Hello,

The point is that in this case SQL Server 2008 doesn't return information about IDENTITY fields (autoincrement). We cannot influence such SQL Server behaviour. As a solution, you can use the following SQL statement:

Code: Select all

select field1, field2
from table1
where (condition1) or (condition2)

masi56
Posts: 2
Joined: Wed 25 Jul 2012 05:46

Re: problem with metadata in SQL 2008 using sdac 4.7

Post by masi56 » Wed 25 Jul 2012 11:00

ok
but it's not possible to use operator OR always..
such as

Code: Select all

select top 100 field1, field2
from table1
where condition1
union all
select field1, field2
from table1
where condition2

where i need to fetch 100 top records according to condition1 plus one extra record according condition2 !
is there any solution ?

AndreyZ

Re: problem with metadata in SQL 2008 using sdac 4.7

Post by AndreyZ » Wed 25 Jul 2012 13:47

Unfortunately there is no way to obtain the result of this query as one resultset and still obtain the information about IDENTITY fields. That is the SQL Server 2008 behaviour.
As a workaround, you can split two SQL statements with the ';' symbol, like this:

Code: Select all

select top 100 field1, field2
from table1
where condition1;
select field1, field2
from table1
where condition2
, and sequentially work with two result sets:

Code: Select all

MSQuery1.Open; // here the result of the first statement (before the ';' symbol) is obtained
while not MSQuery1.Eof do begin
  // some work
  MSQuery1.Next;
end;
MSQuery1.OpenNext; // here the result of the second statement (after the ';' symbol) is obtained
while not MSQuery1.Eof do begin
  // some work
  MSQuery1.Next;
end;

Post Reply