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: 3
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: 496
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

sni
Posts: 3
Joined: Mon 22 Feb 2021 05:59

Re: TableData and queries

Post by sni » Thu 04 Mar 2021 08:22

Any update on my inquiry? I've send an example project via the contact form last week as requested (I referenced this thread)

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

Re: TableData and queries

Post by Stellar » Thu 04 Mar 2021 12:30

When did you send us a support request?
Please try to send your sample project again using the contact form.

sni
Posts: 3
Joined: Mon 22 Feb 2021 05:59

Re: TableData and queries

Post by sni » Fri 05 Mar 2021 10:41

Hi

It was send the 24 of February using the license holder account (Niels-Ole Gram)

I have just send the sample project again (using license holder account)

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

Re: TableData and queries

Post by Stellar » Fri 05 Mar 2021 14:34

Thank you, we've received your sample application.

Post Reply