2012-06-27 Comment

The Original Post has some useful code, but doesn't really illustrate how to return multiple datasets from a DataSnap server in one request from a client app. To see an example of how to do that, look at the Answer marked as Correct at the very bottom of the page.

2011-08-31 Comment

Thanks to Gunny, I looked at everything again. The vexing issue was my own bug, which is now fixed. I can execute multiple SQL statements on the DataSnap server within a single client to server request by creating/destroying the TSQLQuery component in between each database query.

My problem occurred when I left a debug line of code in my stored proc while trying to work around a well known issue that prevents you from accessing an output parameter after calling TSQLStoredProc.Open ( http://qc.embarcadero.com/wc/qcmain.aspx?d=90211 ).

So even though my problem is solved, the original issues remain -- you can't call the Open method to pull data and then access an output param, and you can't access multiple datasets returned from a single stored proc.

Thanks again Gunny, for your suggestion.

Original Post

I am trying to return two different datasets from a DataSnap server in one request. Both come from the same database. One is a single field / single record value, the other is a multi-field / multi-record dataset.

The DataSnap server has the following method:

function TDSSvrMethods.GetData(const SQL: string; var Params: OleVariant; var Key: string): OleVariant;
var qry: TSQLQuery; cds: TClientDataSet;
begin
  // create TSQLQuery & TClientDataSet
  // Link the two components via cds.SetProvider(qry);
  // run first query, set 'Key' to the result <-- this works
  qry.Close;
  // run second query <-- I see this hit the database
  // return dataset via 'Result := cds.Data;'
  // destory TSQLQuery & TClientDataSet
end;

This doesn't work. Even though I can see both individual requests hit the database, I cannot access the second result set. When I try, the first result set is returned (again) instead of the second result set.

Before I created/destroyed the query components (with each client to server request), all subsequent client to server requests would return the very first dataset. Very frustrating. Creating/destroying the query components fixed that problem, but now that I execute multiple queries in one client to server request, the problem has returned -- the first dataset is returned even when a new query is executed.

I have tried several approaches:

ONE : Dynamically create the TSQLQuery component for the first request, pull the db value, destroy the TSQLQuery, create a new TSQLQuery and pull the second dataset. That didn't help. I can use SQL Server Profiler and observe both commands hit the database, but the first result set shows up as the dataset for both queries.

TWO : Do the same as #1, but use TSQLStoredProcedure instead of TSQLQuery. Result is the same.

THREE : Use a TSQLStoredProcedure and return both datasets from within the same stored procedure, like this:

create procedure sp_test_two_datasets
as
  select 'dataset1' as [firstdataset]
  select * from sometable -- 2nd dataset
go

Since TSQLStoredProcedure has a NextRecordSet, I had hoped to access both datasets, but no joy. When I call NextRecordSet, it returns nil.

FOUR : Return the two values in one call to TSQLStoredProcedure using a dataset and an output parameter:

create procedure sp_another_test
  @singlevalue varchar(255) output
as
  select * from sometable
go

The Delphi code looks something like this:

var sp: TSQLStoredProc; cds: TClientDataSet;
...
cds.SetProvider(sp);
...
sp.CommandText := 'sp_another_test :value output';
sp.Params.ParamByName('value').Value := Key; // in/out method parameter from above
sp.Open;
Key := sp.Params.ParamByName('value').Value; // single string value
Result := cds.Data; // dataset
...

I inspect sp.Params and there is one in/out parameter named value. I cannot access the output parameter when a dataset is also returned. This is a KNOWN bug (for many years now): http://qc.embarcadero.com/wc/qcmain.aspx?d=90211

CONCLUSION:

Since the DataSnap server is sharing its main TSQLConnection with all connecting clients, and because the TSQLQuery (or TSQLStoredProc) and the TClientDataSet components are all created / freed with each request, the only thing left that could be holding on to the prior dataset and returning it to the TSQLQuery and TSQLStoredProc components is the TSQLConnection component. I tried calling TSQLConnection.CloseDataSets before closing and freeing the TSQLQuery (or TStoredProc) components, but that didn't help either.

Perhaps a closer look at TSQLConnection will help. Here is how it looks in the .dfm file:

object sqlcon: TSQLConnection
  DriverName = 'MSSQL'
  GetDriverFunc = 'getSQLDriverMSSQL'
  LibraryName = 'dbxmss.dll'
  LoginPrompt = False
  Params.Strings = (
    'SchemaOverride=%.dbo'
    'DriverUnit=DBXMSSQL'

      'DriverPackageLoader=TDBXDynalinkDriverLoader,DBXCommonDriver150.' +
      'bpl'

      'DriverAssemblyLoader=Borland.Data.TDBXDynalinkDriverLoader,Borla' +
      'nd.Data.DbxCommonDriver,Version=15.0.0.0,Culture=neutral,PublicK' +
      'eyToken=91d62ebb5b0d1b1b'

      'MetaDataPackageLoader=TDBXMsSqlMetaDataCommandFactory,DbxMSSQLDr' +
      'iver150.bpl'

      'MetaDataAssemblyLoader=Borland.Data.TDBXMsSqlMetaDataCommandFact' +
      'ory,Borland.Data.DbxMSSQLDriver,Version=15.0.0.0,Culture=neutral' +
      ',PublicKeyToken=91d62ebb5b0d1b1b'
    'GetDriverFunc=getSQLDriverMSSQL'
    'LibraryName=dbxmss.dll'
    'VendorLib=sqlncli10.dll'
    'HostName=localhost'
    'Database=Database Name'
    'MaxBlobSize=-1'
    'LocaleCode=0000'
    'IsolationLevel=ReadCommitted'
    'OSAuthentication=False'
    'PrepareSQL=True'
    'User_Name=user'
    'Password=password'
    'BlobSize=-1'
    'ErrorResourceFile='
    'OS Authentication=False'
    'Prepare SQL=False')
  VendorLib = 'sqlncli10.dll'
  Left = 352
  Top = 120
end

And at runtime, I do a few things so that I don't have to deploy the .INI file for the DBX drivers. First, the unit that lets me register my own INI-less driver:

unit DBXRegDB;

interface

implementation

uses
  DBXCommon, DBXDynalinkNative;

type
  TDBXInternalDriver = class(TDBXDynalinkDriverNative)
  public
    constructor Create(DriverDef: TDBXDriverDef); override;
  end;

  TDBXInternalProperties = class(TDBXProperties)
  private
  public
    constructor Create(DBXContext: TDBXContext); override;
  end;

{ TDBXInternalDriver }

constructor TDBXInternalDriver.Create(DriverDef: TDBXDriverDef);
begin
  inherited Create(DriverDef, TDBXDynalinkDriverLoader);
  InitDriverProperties(TDBXInternalProperties.Create(DriverDef.FDBXContext));
end;

{ TDBXInternalProperties }

constructor TDBXInternalProperties.Create(DBXContext: TDBXContext);
begin
  inherited Create(DBXContext);

  Values[TDBXPropertyNames.SchemaOverride]         :=       '%.dbo';
  Values[TDBXPropertyNames.DriverUnit]             :=       'DBXMSSQL';
  Values[TDBXPropertyNames.DriverPackageLoader]    :=       'TDBXDynalinkDriverLoader,DBXCommonDriver150.bpl';
  Values[TDBXPropertyNames.DriverAssemblyLoader]   :=       'Borland.Data.TDBXDynalinkDriverLoader,Borland.Data.DbxCommonDriver,Version=15.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b';
  Values[TDBXPropertyNames.MetaDataPackageLoader]  :=       'TDBXMsSqlMetaDataCommandFactory,DbxMSSQLDriver150.bpl';
  Values[TDBXPropertyNames.MetaDataAssemblyLoader] :=       'Borland.Data.TDBXMsSqlMetaDataCommandFactory,Borland.Data.DbxMSSQLDriver,Version=15.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b';
  Values[TDBXPropertyNames.GetDriverFunc]          :=       'getSQLDriverMSSQL';
  Values[TDBXPropertyNames.LibraryName]            :=       'dbxmss.dll';
  Values[TDBXPropertyNames.VendorLib]              :=       'sqlncli10.dll';
  Values[TDBXPropertyNames.HostName]               :=       'ServerName';
  Values[TDBXPropertyNames.Database]               :=       'Database Name';
  Values[TDBXPropertyNames.MaxBlobSize]            :=       '-1';
  Values['LocaleCode']                             :=       '0000';
  Values[TDBXPropertyNames.IsolationLevel]         :=       'ReadCommitted';
  Values['OSAuthentication']                       :=       'False';
  Values['PrepareSQL']                             :=       'True';
  Values[TDBXPropertyNames.UserName]               :=       'user';
  Values[TDBXPropertyNames.Password]               :=       'password';
  Values['BlobSize']          开发者_高级运维                     :=       '-1';
  Values[TDBXPropertyNames.ErrorResourceFile]      :=       '';
  Values['OS Authentication']                      :=       'False';
  Values['Prepare SQL']                            :=       'True';
  Values[TDBXPropertyNames.ConnectTimeout]         :=       '30';

  // Not adding connection pooling to the default driver parameters
end;

var
  InternalConnectionFactory: TDBXMemoryConnectionFactory;

initialization
  TDBXDriverRegistry.RegisterDriverClass('MSSQL_NoINI', TDBXInternalDriver);
  InternalConnectionFactory := TDBXMemoryConnectionFactory.Create;
  InternalConnectionFactory.Open;
  TDBXConnectionFactory.SetConnectionFactory(InternalConnectionFactory);

end.

The above method is included in the project (.dpr file) and self registers the driver. The next method utilizes it to setup the TSQLConnection (sqlcon) at runtime (when the DataSnap server starts):

procedure SetupConnection(const hostname, port, dbname, username, password, maxcon: string);
begin
  if sqlcon.Connected then
    Exit;

  // Our custom driver -- does not use DBXDrivers.ini
  sqlcon.Params.Clear;
  sqlcon.DriverName := 'MSSQL_NoINI';
  sqlcon.VendorLib := sqlcon.Params.Values[TDBXPropertyNames.VendorLib];
  sqlcon.LibraryName := sqlcon.Params.Values[TDBXPropertyNames.LibraryName];
  sqlcon.GetDriverFunc := sqlcon.Params.Values[TDBXPropertyNames.GetDriverFunc];

  sqlcon.Params.Values[TDBXPropertyNames.HostName]           := hostname;
  sqlcon.Params.Values[TDBXPropertyNames.Port]               := port;
  sqlcon.Params.Values[TDBXPropertyNames.Database]           := dbname;
  sqlcon.Params.Values[TDBXPropertyNames.UserName]           := username;
  sqlcon.Params.Values[TDBXPropertyNames.Password]           := password;
  sqlcon.Params.Values[TDBXPropertyNames.DelegateConnection] := DBXPool.sDriverName;
  sqlcon.Params.Values[DBXPool.sDriverName + '.' + TDBXPoolPropertyNames.MaxConnections]    := maxcon;
  sqlcon.Params.Values[DBXPool.sDriverName + '.' + TDBXPoolPropertyNames.MinConnections]    := '1';
  sqlcon.Params.Values[DBXPool.sDriverName + '.' + TDBXPoolPropertyNames.ConnectTimeout]    := '1000';
  sqlcon.Params.Values[DBXPool.sDriverName + '.' + 'DriverUnit']                            := DBXPool.sDriverName;
  sqlcon.Params.Values[DBXPool.sDriverName + '.' + 'DelegateDriver']                        := 'True';
  sqlcon.Params.Values[DBXPool.sDriverName + '.' + 'DriverName']                            := DBXPool.sDriverName;
end;

Are any of these settings perhaps messing up the TSQLConnection component and making it cache datasets and return them instead of the one that the most recent TSQLQuery component executed?

Any help would be greatly appreciated. As you can tell, this is driving me crazy!

Thanks, James

What happens if you close the CDS as well?

function TDSSvrMethods.GetData(const SQL: string; var Params: OleVariant; var Key: string): OleVariant; 
var qry: TSQLQuery; cds: TClientDataSet; 
begin 
  // create TSQLQuery & TClientDataSet
  // Link the two components via cds.SetProvider(qry);
  // run first query, set 'Key' to the result <-- this works
  qry.Close;
  cds.Close;
  // run second query <-- I see this hit the database
  cds.Open
  // return dataset via 'Result := cds.Data;'
  // destory TSQLQuery & TClientDataSet end;

As mentioned, in trying to work around the two DBX Framework bugs, I introduced a bug that made it look like the TSQLConnection was handing back a previous dataset for a subsequent data request. Once I fixed my bug, I just had to work around the two DBX Framework bugs (since we can't fix / recompile the framework ourselves):

ONE : You can't call the Open method and access an output param.

TWO : You can't access multiple datasets returned from a single stored proc.

Workaround : I simply execute two queries from the DataSnap server to the database and then process / package the individual datasets to send back to the client (in one response).

2012-06-27 Comment

Since this thread gets several views, I thought I'd explain how I package multiple datasets into a single response from the DataSnap server.

DataSnap can return an OleVariant to the client app. It is easy to create an OleVariant that is an array of OleVariant. Since the TClientDataSet.Data property is an OleVariant, we can create an array of datasets to pass back to the client. This example returns 5 datasets. Assuming these methods exist in the DataSnap server:

function TServerMethods1.GetData(SQL: string): OleVariant;
var cds: TClientDataSet;
begin
  cds := TClientDataSet.Create(nil);
  try

    { setup 'cds' to connect to database }
    { pull data }

    Result := cds.Data;
  finally
    FreeAndNil(cds);
  end;
end;

function TServerMethods1.GetMultipleDataSets: OleVariant;
begin
  Result := VarArrayCreate([0, 4], varVariant);
  Result[0] := GetData('select * from Table1');
  Result[1] := GetData('select * from Table2');
  Result[2] := GetData('select * from Table3');
  Result[3] := GetData('select * from Table4');
  Result[4] := GetData('select * from Table5');
end;

You can assign the data on the client side by placing 5 TClientDataSet components on your form and assigning their Data property to the elements from the OleVariant.

procedure X;
var DataArray: OleVariant;
begin
  try
    with ProxyMethods.TServerMethods1.Create(SQLConnection1.DBXConnection, True) do
    try
      DataArray := GetMultipleDataSets;
    finally
      Free;
    end;

    ClientDataSet1.Data := DataArray[0];
    ClientDataSet2.Data := DataArray[1];
    ClientDataSet3.Data := DataArray[2];
    ClientDataSet4.Data := DataArray[3];
    ClientDataSet5.Data := DataArray[4];
  finally
    VarClear(DataArray);
  end;
end;

(I typed this example without testing it. My actual code includes variant array bounds checking and other dynamic elements.)