Table value parameter for TMSStoredProc
Table value parameter for TMSStoredProc
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
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
Re: Table value parameter for TMSStoredProc
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
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
Re: Table value parameter for TMSStoredProc
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.
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
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;
This is my table definition:
CREATE TYPE [dbo].[TT_RawMatlIDs] AS TABLE(
[RawMatlID] [int] NULL
)
GO
Re: Table value parameter for TMSStoredProc
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.
"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.
Re: Table value parameter for TMSStoredProc
i don't confirm:
Tested on Lazarus 2.3 with FPC 3.2.2 x86, Windows 10 x64, SDAC 10.0.2 and MSSQL 2017
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;
Re: Table value parameter for TMSStoredProc
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.
Re: Table value parameter for TMSStoredProc
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!
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!
Re: Table value parameter for TMSStoredProc
I've tried all sorts of things here to determine what's going on.
I added code to find the table names:
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.
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);
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.
Re: Table value parameter for TMSStoredProc
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:
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;
Re: Table value parameter for TMSStoredProc
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.