title: Return Two Datasets from DataSnap Server in One Request
body: <p><strong>2012-06-27 Comment</strong></p>
  
  <p>The <strong>Original Post</strong> 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 <em>Answer</em> marked as <em>Correct</em> at the very bottom of the page.</p>
  
  <hr>
  
  <p><strong>2011-08-31 Comment</strong></p>
  
  <p>Thanks to Gunny, I looked at everything again.  The vexing issue was my own bug, which is now fixed.  I <em>can</em> execute multiple SQL statements on the DataSnap server within a single client to server request by creating/destroying the <code>TSQLQuery</code> component in between each database query.</p>
  
  <p>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 <code>output</code> parameter after calling <code>TSQLStoredProc.Open</code> ( <a href="http://qc.embarcadero.com/wc/qcmain.aspx?d=90211" rel="nofollow">http://qc.embarcadero.com/wc/qcmain.aspx?d=90211</a> ).</p>
  
  <p>So even though my problem is solved, the original issues remain -- <strong>you can't call the <code>Open</code> method to pull data and then access an <code>output</code> param, and you can't access multiple datasets returned from a single stored proc.</strong></p>
  
  <p><em>Thanks again Gunny, for your suggestion.</em></p>
  
  <hr>
  
  <h1> Original Post </h1>
  
  <p>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.</p>
  
  <p>The DataSnap server has the following method:</p>
  
  <pre><code>function TDSSvrMethods.GetData(const SQL: string; var Params: OleVariant; var Key: string): OleVariant;
  var qry: TSQLQuery; cds: TClientDataSet;
  begin
    // create TSQLQuery &amp; TClientDataSet
    // Link the two components via cds.SetProvider(qry);
    // run first query, set 'Key' to the result &lt;-- this works
    qry.Close;
    // run second query &lt;-- I see this hit the database
    // return dataset via 'Result := cds.Data;'
    // destory TSQLQuery &amp; TClientDataSet
  end;
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I have tried several approaches:</p>
  
  <p><strong>ONE</strong> : Dynamically create the <code>TSQLQuery</code> component for the first request, pull the db value, destroy the <code>TSQLQuery</code>, create a new <code>TSQLQuery</code> and pull the second dataset.  That didn't help.  I can use <em>SQL Server Profiler</em> and observe both commands hit the database, but the first result set shows up as the dataset for both queries.</p>
  
  <p><strong>TWO</strong> : Do the same as #1, but use <code>TSQLStoredProcedure</code> instead of <code>TSQLQuery</code>.  Result is the same.</p>
  
  <p><strong>THREE</strong> : Use a <code>TSQLStoredProcedure</code> and return both datasets from within the same stored procedure, like this:</p>
  
  <pre><code>create procedure sp_test_two_datasets
  as
    select 'dataset1' as [firstdataset]
    select * from sometable -- 2nd dataset
  go
  </code></pre>
  
  <p>Since <code>TSQLStoredProcedure</code> has a <code>NextRecordSet</code>, I had hoped to access both datasets, but no joy.  When I call <code>NextRecordSet</code>, it returns <code>nil</code>.</p>
  
  <p><strong>FOUR</strong> : Return the two values in one call to <code>TSQLStoredProcedure</code> using a dataset and an <code>output</code> parameter:</p>
  
  <pre><code>create procedure sp_another_test
    @singlevalue varchar(255) output
  as
    select * from sometable
  go
  </code></pre>
  
  <p>The Delphi code looks something like this:</p>
  
  <pre><code>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
  ...
  </code></pre>
  
  <p>I inspect sp.Params and there is one in/out parameter named <code>value</code>.  I cannot access the <code>output</code> parameter when a dataset is also returned.  This is a KNOWN bug (for many years now): <a href="http://qc.embarcadero.com/wc/qcmain.aspx?d=90211" rel="nofollow">http://qc.embarcadero.com/wc/qcmain.aspx?d=90211</a></p>
  
  <p><strong>CONCLUSION:</strong></p>
  
  <p>Since the DataSnap server is sharing its main <code>TSQLConnection</code> with all connecting clients, and because the <code>TSQLQuery</code> (or <code>TSQLStoredProc</code>) and the <code>TClientDataSet</code> 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 <code>TSQLQuery</code> and <code>TSQLStoredProc</code> components is the <code>TSQLConnection</code> component.  I tried calling <code>TSQLConnection.CloseDataSets</code> before closing and freeing the <code>TSQLQuery</code> (or <code>TStoredProc</code>) components, but that didn't help either.</p>
  
  <p>Perhaps a closer look at <code>TSQLConnection</code> will help.  Here is how it looks in the <code>.dfm</code> file:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>The above method is included in the project (.dpr file) and self registers the driver.  The next method utilizes it to setup the <code>TSQLConnection</code> (sqlcon) at runtime (when the DataSnap server starts):</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Are any of these settings perhaps messing up the <code>TSQLConnection</code> component and making it cache datasets and return them instead of the one that the most recent <code>TSQLQuery</code> component executed?</p>
  
  <p>Any help would be greatly appreciated.  As you can tell, this is driving me crazy!</p>
  
  <p>Thanks,
  James</p>