Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

About RemoteDB TXDataset(stored procedure&#

 Post Reply Post Reply
Author
YANG ZONGYU View Drop Down
New Member
New Member
Avatar

Joined: 17 Jan 2018
Posts: 6
Post Options Post Options   Quote YANG ZONGYU Quote  Post ReplyReply Direct Link To This Post Topic: About RemoteDB TXDataset(stored procedure&#
    Posted: 17 Jan 2018 at 9:02am
Whether the TXDataset can execute the stored procedure and get the return value or the output parameter value?
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2403
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 17 Jan 2018 at 12:45pm
TXDataset can execute any SQL statement. The only difference is that a specific component for stored procedures are not provided, so you just need to build the SQL yourself and get the results using FieldByName. It depends on the underlying component you use server-side. For example, if using FireDac, you can execute stored procedures with TFDQuery (and thus with TXDataset): 
Back to Top
YANG ZONGYU View Drop Down
New Member
New Member
Avatar

Joined: 17 Jan 2018
Posts: 6
Post Options Post Options   Quote YANG ZONGYU Quote  Post ReplyReply Direct Link To This Post Posted: 17 Jan 2018 at 3:17pm
The stored procedure in MSSQLServer2008:

===================================================
create proc sp_test
@InVal varchar(100),
@OutVal varchar(200) output
as
begin
  select @OutVal='Hello' + @InVal;
  RETURN 0
end
GO

===================================================
// Execute the stored procedureand and get the 2nd OutPut value.
//The server-side uses a TFDQuery. Stored procedure execute OK, but the OUTPUT parameter can not return to the client(XDataset1). Possible??

var
  Params: TParams;
  AParam: TParam;

  Params := TParams.Create(nil);
  AParam := TParam.Create(Params, ptInputOutput);
  with AParam do
  begin
    Name := 'out';
    DataType := ftString;
  end;
  try
    XDataset1.Database.ExecSQL('EXEC sp_test ''This is input Value'', :out', Params);
    ShowMessage(Params.ParamByName('out').AsString);
  finally
    AParam.Free;
    Params.Free;
  end;

Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2403
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 17 Jan 2018 at 5:35pm
Use the following SQL statement:

DECLARE @out varchar(200)
EXEC sp_test ', World', @out OUTPUT
SELECT @out AS OUT

then just open the dataset and get the value using field by name:

XDataset1.Open;
OutValue := XDataset1.FieldByName('OUT').AsString;

Back to Top
YANG ZONGYU View Drop Down
New Member
New Member
Avatar

Joined: 17 Jan 2018
Posts: 6
Post Options Post Options   Quote YANG ZONGYU Quote  Post ReplyReply Direct Link To This Post Posted: 18 Jan 2018 at 1:35am
OK, Thanks!
Back to Top
Michael B View Drop Down
New Member
New Member
Avatar

Joined: 30 Nov 2010
Posts: 2
Post Options Post Options   Quote Michael B Quote  Post ReplyReply Direct Link To This Post Posted: 22 Apr 2018 at 9:07pm
Hi Wagner,

Is this also be possible with ElevateDB? I have an stored procedure with parameters in ElevateDB which I would execute similar to the described approach.
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2403
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 23 Apr 2018 at 12:39pm
Hi Michael, that depends on the specific ElevateDB syntax. Doesn't ElevateDB provide an SQL statement that returns values from stored procedures in a dataset format?
Back to Top
Michael B View Drop Down
New Member
New Member
Avatar

Joined: 30 Nov 2010
Posts: 2
Post Options Post Options   Quote Michael B Quote  Post ReplyReply Direct Link To This Post Posted: 23 Apr 2018 at 1:29pm
According to information from ElevateDB manual.

You can only retrieve result sets from procedures from a client (TEDBStoredProc).

So it seems it isn't possible.

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down