Use a Stored Procedure to return a result set via DataSnap

I have created a DataSnap server along with a client (FireMonkey XE5 - update 2) and can successfully return data from the DataSnap server (which connects to SQL Server 2008). When using a stored procedure though, I receive the following error:

Remote Error: [0x0005]: Operation not supported.

Here is my DATASNAP SERVER function:

Note when using a "SELECT FROM" query, all works perfectly. As soon as I replace it with a stored procedure, I receive an error. The stored procedure definitely exists, and works and returns data.

function TServerMethods.GetUserDetails(UserID: integer) : TDataSet;
var ds : TSQLQuery;
begin
  //Ensure the SQL Connection is open - this works 100%
  if not SQLLocalTest.Connected then
    SQLLocalTest.Connected := true;

  ds := TSQLQuery.Create(self);
  try
    ds.SQLConnection := SQLLocalTest;
    //This produces the error when running the client  
    //The sp is valid and works 100%
    ds.SQL.Text := 'exec spMobileGetUserDetails ' + UserID.ToString();

    //If I replace the above with this, then it all works perfectly 
    ds.SQL.Text := 'SELECT FirstName, Surname, Email FROM Users WHERE UserID= ' + UserID.ToString();
    ds.Open;

    result := ds
  finally
  end;
end;

DataSnap client function:

procedure TfrmTest.Button1Click(Sender: TObject);
var
  d : TClientDataSet;
  sm : TSqlServerMethod;
  dp : TDataSetProvider;
begin
  try
    sm := TSqlServerMethod.Create(Self);
    if not DS_VCL.Connected then
      DS_VCL.Connected := true;
    sm.SQLConnection := DS_VCL;

    sm.ServerMethodName := 'TServerMethods.GetUserDetails';
    sm.Params[0].AsInteger := 1;

    dp := TDataSetProvider.Create(Self);
    dp.Name := 'dspGetUserDetails';
    dp.DataSet := sm;

    d := TClientDataSet.Create(Self);
    d.ProviderName := 'dspGetUserDetails';
    d.Open;  //  <---- ERROR OCCURS HERE WHEN USING STORED PROC

    ShowMessage(d.Fields[0].ToString());
    d.Close;

    FreeAndNil(d);
    FreeAndNil(dp);
    FreeAndNil(sm);

  except on E: exception do
    ShowMessage(e.Message);
 end;
end;

Answers


My datasnap server uses Stored procedure calls virtually identical to yours but uses TAdoQueries instead of TSqlQueries and works fine, so any other readers with the same problem should maybe try substituting TAdoQueries.


try to use a SQLStoredProc component it seems to work fine.. lookup this material.

View source for Creating the Server Side with DataSnap Server


Need Your Help

Laravel 4: where's the profiler?

laravel laravel-4

I used to be able to enable the profiler using the profiler property in /application/config/application.php.

What exactly does fitsSystemWindows do?

android android-layout

I'm struggling to understand the concept of fitsSystemWindows as depending on the view it does different things. According to the official documentation it's a