Hi everybode
I need to call a stored procedure which takes a table-valued parameter. The parameter was mapped as type Cursor in DataAbstract. Unfortunately I was unable to find any documentation on how to fill the parameter in delphi using DataAbstract.
Can anyone help me with this one?
Best regards
Samuel
We support stored procedures with output cursor parameter only.
Input cursor parameter isn’t supported.
Hmm actually I am not looking for cursor parameters (it was databastract mapping it to that parameter type). What I am looking for are table-valued parameters for stored procedures:
https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx
If you do not support this, what are the alternatives. I cannot call the stored procedure like a thousend times!
according to Delphi - pass table valued parameter to SQL Server stored procedure - Stack Overflow :
As far as I know there in no simple way to pass Table parameters, using the components shipped with Delphi. A workaround would be using a temporary table which can be used to fill a typed table variable.
see complete solution in above link.
another solution: to use 3rd-party commercial library (SDAC), but they also solve this via temporary tables: http://forums.devart.com/viewtopic.php?f=6&t=24223
Thank you very much for your response and the link to the thread on stack overflow. I have to admit I feel very annoyed - yet not surprised, that this very commonly used feature, existing since 8 years, is still not available in delphi :-/ However I am gonna try it with temporary tables. Could you please provide me with a more elaborated example of how to use a TDAMemDataTable without the Schema modeller (the schema modeler doesn’t work as the required temporary tables do not exist at compile time). I tried to do it with IDAConnection.NewDataset, but the returning structure provides no way to add new records. Also in the sample of stackoverflow I don’t see the type being used.
Many thanks and best regards
table := TDAMemDataTable.Create(nil);
table.LogicalName := 'temp';
table.Fields.Clear;
table.Fields.Add('Item1', datInteger).InPrimaryKey := true;
table.Fields.Add('Item2', datstring, 32);
table.RemoteFetchEnabled := False; // mandatory for temporary tables
table.Active := true;
table.Insert;
table.Fields[0].AsVariant := 15;
table.Fields[1].AsVariant := null;
table.Post;
if you want to use temporary table on DB server, better to use something like
// server-side code (inside a TDataAbstractService descendant)
var
st: IDASQLCommand;
i: integer;
begin
Connection.BeginTransaction;
try
st := Connection.NewCommand('Create Table #mytemp(ID int,Text varchar(100))', stSQL);
st.Execute;
st :=Connection.NewCommand('Insert into #mytemp values(:p1, :p2)', stSQL);
st.RefreshParams;
// adjust parameters here, if needed
for i := 0 to x do begin
st.Params[0].Value := ...;
st.Params[1].Value := ...;
st.Execute;
end;
// temporary table is done at this step
// ... execute your SP
st := Connection.NewCommand('Drop Table #mytemp', stSQL);
st.Execute;
Connection.CommitTransaction;
except
Connection.RollbackTransaction;
end;
Thank you a lot, that works! Unfortunately the next problem rised immediately:
DSet := Connection.NewDataset(
‘declare @Tbl MyDataTbl;’ +
‘insert into @Tbl select * from #Temp;’ +
‘exec MyStoredProc @Tbl’);
DSet.Open;
This results in an exception saying that the ‘CommandText does not return a result set’.
Well, it DOES! I am wondering on whether this problem occures because its a multistatement command text and its only the third statement returning a resultset and how this problem can be circumvented?
Best regards
try to split
DSet := Connection.NewDataset(
'declare @Tbl MyDataTbl;' +
'insert into @Tbl select * from #Temp;' +
'exec MyStoredProc @Tbl');
to 3 statements, first and second one - execute, for third one - use Open, like:
..
st :=Connection.NewCommand('insert into @Tbl select * from #Temp', stSQL);
st.execute;
// ds : IDADataset
ds := Connection.NewDataset('exec MyStoredProc @Tbl');
ds.Open;
Thanks for your quick response I really appreciate! 
Unfortunately I tried this one already - he will be complaining that the variable @Tbl needs to be defined :-/
create stored procedure and execute it.
note: you can create it in runtime with
Connection.NewCommand('...', stSQL);
I am afraid I don’t understand what you mean this time?
something like
const sql =
'CREATE PROCEDURE xxxx'+
'as'+
'begin'+
'declare @Tbl MyDataTbl;'+
'insert into @Tbl select * from #Temp;' +
'exec MyStoredProc @Tbl;'+
'end;';
st :=Connection.NewCommand(sql, stSQL);
st.execute;
Many many thanks for your endurance and effort to find a solution and I have to say, thats a smart one - creative!
Yet I also have to admit, that I don’t really get warm with the direction this is heading towards :-/
I hope I am not offending anyone, its just…
I really start wondering! All I want is simply to call a given stored procedure, it cannot be all that hard can it. I have seen code samples in C# and C++ doing exactly this and its like three lines of code (without the need of creating and deleting temporary tables, on the fly creating and deleting mock stored procedures and other cumbersome hacks resulting in another A4 page of ugly, cumbersome and unmaintainable code from hell - simply to call a stored procedure using a table valued parameter which was first introduced 8 years ago). I feel a little frustrated that simply calling a stored procedure in delphi requires half a day of elaboration, while it takes minutes to figure it out in other mainstream languages…
I start to think about whether its time to move to another technology stack in the long run if its that hard to perform a really basic and standard operation like invoking a stored procedure - FROM WITHIN A DATABASE FRAMEWORK in delphi…
from stack overflow:
As far as I know there in no simple way to pass Table parameters, using the components shipped with Delphi.
