Search code examples
delphi-xe2datasnap

execute dynamic sql in datasnap client


Is it possible to share database connection between datasnap server and client?

I want to execute dynamic sql in client and receive results.


Solution

  • I found 2 ways to execute dynamic sql in client side

    1) Using DbxConnection (dbExpress)

    // Server code
    function TServerMethods1.GetConnection: TDbxConnection;
    begin
      Result := DBConnection.DBXConnection;
    end;
    
    
    //Client code
    dbxConnection := ServerMethods.GetConnection;
    command := dbxConnection.CreateCommand;
    command.Text := 'SELECT COUNT(1) FROM clients WHERE name LIKE :name';
    param := com.CreateParameter;
    param.Name := 'name';
    param.DataType := TDBXDataTypes.WideStringType;
    param.Value.SetString('%name%');
    command.Parameters.AddParameter(param);
    reader := command.ExecuteQuery;
    reader.Next; // to Fetch row
    ShowMessage(reader.Value[0].AsString);
    

    2) Using DataSet descendant

    Server side components

    ServerMethods class must be TDSServerModule descendant

    • TConnection descendant
    • TQuery descendant
    • TDataSetProvider (set poAllowCommanedText to true)

    Client side components

    • TSqlConnection (for DataSnap connection)
    • TDsProviderConnection (for DataSnap server methods)
    • TClientDataSet (set provider)

    code to execute query

    CDS.Close; // TClientDataSet
    CDS.CommandText := 'SELECT COUNT(*) FROM clients WHERE name LIKE :name';
    CDS.Params.ParamByName('name').AsString := '%name%';
    CDS.Open;
    ShowMessage(CDS.Fields[0].AsString);
    

    Server side code:

    MainMethods.pas

    TMainMethods = class(TDSServerModule)
      PgQuery: TPgQuery;
      PgQueryProvider: TDataSetProvider;
      PgConnection: TPgConnection;
    end;
    

    MainMethods.dfm

    object MainMethods: TMainMethods
      Height = 248
      Width = 440
      object PgConnection: TPgConnection
        Left = 200
        Top = 32
        ...
      end
      object PgQuery: TPgQuery
        Connection: PgConnection
        Left = 32
        Top = 24
      end
      object PgQueryProvider: TDataSetProvider
        DataSet = PgQuery
        Options = [poAllowCommandText, poUseQuoteChar]
        Left = 120
        Top = 24
      end
    end
    

    Client side code:

    client.pas

    TVerusClient = class(TDataModule)
      dbxVerusConnection: TSQLConnection;
      dbxSqlConnectionProvider: TDSProviderConnection;
      cdsSqlDataSet: TClientDataSet;
    end;
    

    client.dfm

    object VerusClient: TVerusClient
      Height = 271
      Width = 415
      object dbxVerusConnection: TSQLConnection
        DriverName = 'DataSnap'
        LoginPrompt = False
        ...
      end
      object dbxSqlConnectionProvider: TDSProviderConnection
        ServerClassName = 'TMainMethods'
        SQLConnection = dbxVerusConnection
        Left = 176
        Top = 32
      end
      object cdsSqlDataSet: TClientDataSet
        ProviderName = 'PgQueryProvider'
        RemoteServer = dbxSqlConnectionProvider
        Left = 176
        Top = 104
      end
    end