Table value parameter for TMSStoredProc

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
krhdevart
Posts: 12
Joined: Sat 09 Apr 2022 02:36

Table value parameter for TMSStoredProc

Post by krhdevart » Sat 09 Apr 2022 16:31

I first created the application I'm making changes to some 15 years ago. I didn't know about SDAC at the time and used all native TADO components. And there was no option in "MS SQL 2000" to use table variables as stored procedure input parameters. In vb.net projects with MS SQL I've been using table parameters with stored procedures for many years now. I have a need to use that strategy with my old delphi project that I upgraded to tokyo 10.2 (and MS SQL 2017) a few years back. I also bought the SDAC components at that time but only now have a need for them.

I'm having trouble getting this working. I am trying to figure out how to get some data into a table in code so that I can use it here:

sp.Params.ParamByName('@RawMatlIDs').AsTable := <in memory table of some kind>

In the past I've used the DevExpress TDxMemData control for all sorts of in memory table needs. That won't work here.

I've tried using TMSTableData because I found an example of that in the SDAC documentation (https://docs.devart.com/sdac/table_valu ... meters.htm).

var
tblSelRawMatls: TMSTableData;

tblSelRawMatls.Connection := KTData.dmKT.cnnKT_SDAC; //<<<<<< ERROR HERE but no explanation for it in E.Message
tblSelRawMatls.Open;
tblSelRawMatls.TableTypeName := 'TT_RawMatlIDs';
tblSelRawMatls.Append;
tblSelRawMatls.Fields[0].AsString := '1';
tblSelRawMatls.Fields[1].AsString := '2';
tblSelRawMatls.Post;

cnnKT_SDAC is connected just fine but when I apply that connection to the TMSTableData it crashes.

I tried using an actual TMSTableData component on the form and I can connect to my database but when I try to set the table type I get an error: "table-valued parameter type is not supported by sql server"

That makes no sense to me. My entire reason for getting these components was so I could pass table params to a procedure (instead of having to parse long delimited strings) and in the example above in the SDAC documentation they clearly do it. Yes TT_RawMatlIDs exists in my database.

In vb.net it's a super simple matter to create an in-code table to pass to SQL but this seems way more complex in Delphi.

Any kind assistance would be gratefully appreciated!

Thanks,

Keith

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

Re: Table value parameter for TMSStoredProc

Post by Stellar » Wed 13 Apr 2022 17:32

Hi,

Thanks for your request.

In order for us to be able to give a more concrete answer, please specify the exact error message.

Best regards,
Sergey

krhdevart
Posts: 12
Joined: Sat 09 Apr 2022 02:36

Re: Table value parameter for TMSStoredProc

Post by krhdevart » Wed 13 Apr 2022 19:13

I had an error in the code I posted in my original message. I'd swapped something just to try it out.

This is my current code. The first 2 lines run without errors. The 3rd line tells me that the table doesn't exist but it does. I use user defined table types all the time in my work. I'm sure it's something simple that I'm missing in SDAC.

Code: Select all

      tblSelRawMatls.Connection := dmKT.cnnKT_SDAC;
      tblSelRawMatls.TableTypeName := 'dbo.TT_RawMatlIDs';
      tblSelRawMatls.Open; // ERROR HERE: Table does not exist
      tblSelRawMatls.Append;
      tblSelRawMatls.Fields[0].AsInteger := 1;
      tblSelRawMatls.Post;
It doesn't matter if I specify 'dbo.TT_RawMatlIDs' or 'TT_RawMatlIDs' above. I get the same error.

This is my table definition:

CREATE TYPE [dbo].[TT_RawMatlIDs] AS TABLE(
[RawMatlID] [int] NULL
)
GO

krhdevart
Posts: 12
Joined: Sat 09 Apr 2022 02:36

Re: Table value parameter for TMSStoredProc

Post by krhdevart » Thu 14 Apr 2022 00:12

Also, I guess you didn't read my OP clearly. I did post the exact message for you.

"Table-Valued Parameter type is not supported by SQL Server."

I still get that if I use a TMSTableData object on the form and try to set the table type name there. Both errors make no sense at all.

paweld
Posts: 19
Joined: Mon 29 Sep 2014 08:56

Re: Table value parameter for TMSStoredProc

Post by paweld » Thu 14 Apr 2022 08:53

i don't confirm:

Code: Select all

uses
  MSAccess;
  
procedure TForm1.Button1Click(Sender: TObject);
var
  conn: TMSConnection;
  q: TMSQuery;
  sp: TMSStoredProc;
  td: TMSTableData;
  i: Integer;
begin
  Memo1.Lines.Clear;
  conn := TMSConnection.Create(nil);
  conn.Server := '.';
  conn.Database := 'testdb';
  conn.Username := 'sa';
  conn.Password := '123';
  conn.Connect;
  q := TMSQuery.Create(nil);
  q.Connection := conn;
  q.SQL.Add(' if not exists (select 1 from sys.types where name=''type_table_sdac'' and is_table_type=1) ');
  q.SQL.Add(' create type type_table_sdac as table (id int) ');
  q.ExecSQL;
  q.SQL.Clear;
  q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_table_type'') ');
  q.SQL.Add(' drop procedure proc_test_table_type ');
  q.ExecSQL;
  q.SQL.Clear;
  q.SQL.Add(' create procedure proc_test_table_type @tab type_table_sdac readonly, @text nvarchar(max) output ');
  q.SQL.Add(' as ');
  q.SQL.Add(' begin ');
  q.SQL.Add(' select @text=stuff((select isnull('', ''+convert(varchar(20), id), '''') from @tab for xml path('''')), 1, 2, '''') ');
  q.SQL.Add(' end ');
  q.ExecSQL;
  sp := TMSStoredProc.Create(nil);
  sp.Connection := conn;
  td := TMSTableData.Create(nil);
  td.Connection := conn;
  td.TableTypeName := 'type_table_sdac';
  td.Open;
  for i := 0 to 9 do
  begin
    td.Insert;
    td.FieldByName('id').AsInteger := Random(999) + 1;
    td.Post;
  end;
  sp.StoredProcName := 'proc_test_table_type';
  sp.ParamByName('tab').AsTable := td.Table;
  sp.Execute;
  for i := 0 to sp.Params.Count - 1 do
  begin
    Memo1.Lines.Add('Param index: ' + IntToStr(i));
    Memo1.Lines.Add('Param name: ' + sp.Params[i].Name);
    Memo1.Lines.Add('Param value: ' + sp.Params[i].Text);
    Memo1.Lines.Add('====================================');
  end;
  sp.Free;
  q.SQL.Clear;
  q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_table_type'') ');
  q.SQL.Add(' drop procedure proc_test_table_type ');
  q.ExecSQL;
  q.SQL.Clear;
  q.SQL.Add(' if exists (select 1 from sys.types where name=''type_table_sdac'' and is_table_type=1) ');
  q.SQL.Add(' drop type type_table_sdac ');
  q.ExecSQL;
  q.Free;
  conn.Disconnect;
  conn.Free;
end;   
Tested on Lazarus 2.3 with FPC 3.2.2 x86, Windows 10 x64, SDAC 10.0.2 and MSSQL 2017

krhdevart
Posts: 12
Joined: Sat 09 Apr 2022 02:36

Re: Table value parameter for TMSStoredProc

Post by krhdevart » Thu 14 Apr 2022 13:45

I figured it out. The way you set up your connection object gave me an idea. I have a TMSConnection object (for new things) on my main data form along with my original TADOConnection object. I was using the same connection string for both of them. The TMSConnection object connected just fine and most things worked fine but something in my connection string made TMSConnection unhappy so I simplified it and it solved the entire problem.

krhdevart
Posts: 12
Joined: Sat 09 Apr 2022 02:36

Re: Table value parameter for TMSStoredProc

Post by krhdevart » Tue 07 Jun 2022 15:08

This issue is acting up again. Up until today I was doing development work with this on my local development system. This morning I deployed the updated version to my client. When my application starts up, the TMSConnection connects just fine. In a form where I use it in the same TMSStoredProc not working again. I'm getting a "table does not exist" error here again (in the 3rd line).

tblSelRawMatls.Connection := dmKT.cnnKT_SDAC;
tblSelRawMatls.TableTypeName := 'dbo.TT_RawMatlIDs';
tblSelRawMatls.Open;

I'm using the same connection string but with a few differences of course because the sql db name on my client's system is slightly different and the sql password is different. I know the TMSConnection is good because I use a TMSStoredProc in another part of my application to update some other data and that behaves as expected. I don't use any table types with that one though so it's something with the table type again. Why is it telling me that my table type does not exist? I've checked backend permissions, schema, and other properties for the table type. When I log into the backend using the same credentials that the front end is using, I can see the table type. I can run the stored procedure its used in just fine from the backend in SSMS on my client's system. It's just that either TMSConnection or TMSStoredProc is having trouble finding it. Why might this be happening?

Thank you in advance!

krhdevart
Posts: 12
Joined: Sat 09 Apr 2022 02:36

Re: Table value parameter for TMSStoredProc

Post by krhdevart » Fri 10 Jun 2022 01:59

I've tried all sorts of things here to determine what's going on.

I added code to find the table names:

Code: Select all

      x := TStringList.Create;
      dmKT.cnnKT_SDAC.GetTableTypeNames(x);    // CRASHES HERE
      tableTypeNames := '';
      for i := 0 to x.Count - 1 do
      begin
        tableTypeNames := tableTypeNames + '; ' + x[i];
      end;
      showmessage(tableTypeNames);
The above works fine on my development machine.

I'm wondering if something from SDAC isn't getting compiled properly into my application. That's the only thing that makes sense to me right now. The above code works flawlessly on my dev machine but it crashes on the 2nd line on the client's system.

paweld
Posts: 19
Joined: Mon 29 Sep 2014 08:56

Re: Table value parameter for TMSStoredProc

Post by paweld » Fri 10 Jun 2022 19:27

An error may occur when retrieving a list of tables because the user may not have permissions.
To rule out a table type handling error on the SDAC side and verify that the user has permissions to the type, try the following code:

Code: Select all

MSQuery1.SQL.Clear;
MSQuery1.SQL.Add(' declare @idtab TT_RawMatlIDs ');
MSQuery1.SQL.Add(' insert into @idtab(RawMatlID) values (1), (2), (3) ');
MSQuery1.SQL.Add(' select * from @idtab ');
try
  MSQuery1.Open;
  MSQuery1.Close;
  ShowMessage('Permissions OK');
except
  on E: Exception do
    ShowMessage('Error: ' + #13#10 + E.Message);
end;

krhdevart
Posts: 12
Joined: Sat 09 Apr 2022 02:36

Re: Table value parameter for TMSStoredProc

Post by krhdevart » Fri 10 Jun 2022 23:10

Thanks for the suggestion. It's definitely not a permissions issue. All the backend objects have the same owner and same rights. Front end permissions are handled outside of SQL security. For kicks I tried your code below and as I expected it works just fine. I'm convinced it's either something funky with TMSConnection not liking my connection string for getting at table types (while EVERYTHING else works fine) or something from SDAC isn't making it into my exe. Not sure how that could be though.

Post Reply