TableData and queries

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
sni
Posts: 1
Joined: Mon 22 Feb 2021 05:59

TableData and queries

Post by sni » Mon 22 Feb 2021 06:44

Hi

I'm trying to change several of our queries to use tabledata, but its seems that I can only make it work when executing stored procedures and not when using simple queries, ie.

Code: Select all

-- SQL server
create type MyTableData as table([id] int not null)

create procedure sp_someproc(@somedata MyTableData readonly)
as
select * from sometable t1 inner join @somedata t2 on (t1.id=t2.id)

-- Delphi
Data := TMSTableData.Create(nil);
Data.TableTypeName := 'MyTableData';
Data.Open;
Data.Append;
Data.Fields[0].AsInteger := 13;
Data.Post;

// This works
Proc := TMSStoredPRoc.Create(nil);
Proc.StoredProcName := 'sp_someproc';
Proc.ParamByName('somedata').AsTable := Data.Table;
Proc.Open;

// This does not work
Query := TMSQuery.Create(nil);
Query.SQL.Add('select * from sometable t1 inner join :somedata t2 on (t1.id=t2.id)')
Query.FieldByName('somedata').AsTable := Data.Table;
Query.Open;
Using tabledata with the simple query results in an error from SQL Server claiming the parameter must be defined as readonly. How ever the following does work fine when executed directly on SQL Server,

Code: Select all

declare @somedata MyTableData
insert into @somedata values(13)

select * from sometable t1 inner join @somedata t2 on (t1.id=t2.id)
I'm using Delphi 2007, SDAC 9.1.2 and SQL Server 2014.

Am I doing it wrong or is table data only supported when calling stored procedures and functions?

Stellar
Devart Team
Posts: 397
Joined: Tue 03 Oct 2017 11:00

Re: TableData and queries

Post by Stellar » Mon 22 Feb 2021 15:55

Unfortunately, we couldn't reproduce the issue. To investigate this behavior of SDAC, please compose a small sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

Post Reply