mORMot Open Source

corchi72

Member

Registered: 2010-12-10

Posts: 232

How to use FillRow FillPrepare?

Hi,
   I would like to use fillprepare() and  FillRow() to read / add / delete / edit data from a table.I post an example below:

var
  T:  TSQLTemp;
   row : Integer;
begin
   T:= TSQLTemplate.CreateAndFillPrepare(globalClient, '');
   row := 1;
   T.FillRow(row);
   Showmessage(T.Name) ; // the value is "YYY";
   //I set new value and execute update
   T.Name := 'xxxxx';
   globalClient.Update(T);
   
   //I move to next record;
    row := 2;
    T.FillRow(row);
    Showmessage(T.Name) ; // the value is "PPP";

   //now  if I read the value of the first row (I use FillRow to move between the records)

   row := 1;
   T.FillRow(row);
   Showmessage(T.Name) ; //  the value is "YYY" and not "XXXX"; Why?

thanks and sorry for my English

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: How to use FillRow FillPrepare?

 globalClient.Update(T);

Update the record content in the server, i.e. in the database.

But the JSON content stored inside T.FillTable (i.e. the internal TSQLTableJSON created by FillPrepare) is still the previous one, with the values before the update.

You'll have to explicitly refresh the TSQLTableJSON content, via:

globalClient.UpdateFromServer([T.FillTable],refreshed);
// now refreshed should be true and first row should be 'xxxxx'

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: How to use FillRow FillPrepare?

Thanks now it works. But what is the best way between the first and the following example, to work with a quantumgrid

function TcxCustomDataSource.GetRecordCount: Integer;
begin
//  Result := T.FillTable.RowCount;
  Result := fTable.RowCount;
end;

function TcxCustomDataSource.GetValue(ARecordHandle: TcxDataRecordHandle;
  AItemHandle: TcxDataItemHandle): Variant;
var
  col : TcxGridColumn;
  row : Integer;
  Dest : TSQLTemp;
  id : Integer;
begin
  col := TcxGridColumn(DataController.GetItem(integer(AItemHandle)));
  row := Integer(ARecordHandle)+1;
  try

   id := fTable.GetAsInteger(row,fTable.FieldIndex('ID'));
   Dest  := TSQLTemp.Create(globalClient, id);
   case  col.Tag  of

    IndexOfName:
      Result := Dest.Name;
    ...
   
  end;
  finally
     FreeAndNil(Dest);
  end;
end;

procedure TcxCustomDataSource.SetValue(ARecordHandle: TcxDataRecordHandle;
  AItemHandle: TcxDataItemHandle; const AValue: Variant);
var
  col : TcxGridColumn;
  row : Integer;
  Dest : TSQLTemp;
  id : Integer;
begin

  col:= TcxGridColumn(DataController.GetItem(integer(AItemHandle)));
  row := Integer(ARecordHandle)+1;
  try
  id := fTable.GetAsInteger(row,fTable.FieldIndex('ID'));
  Dest  := TSQLTemp.Create(globalClient, id);
  case  col.Tag  of

    IndexOfName:
      Dest.Name := AValue ;
    ...
  end;
  globalClient.Update(Dest);

  end;
  finally
     FreeAndNil(Dest);
  end;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: How to use FillRow FillPrepare?

I don't understand well your question...

Do you mean:

globalClient.Update(Dest);
globalClient.UpdateFromServer([fTable],refreshed); // this is to refresh the grid content
end;
  finally
     FreeAndNil(Dest);
  end;

You could use fTable.IDColumnHiddenValue(row) instead of fTable.GetAsInteger(row,fTable.FieldIndex('ID'));

BUT in all cases:

Instead of Dest := TSQLTemp.Create(globalClient, id), you should use a local TSQLTemp instance (not on the stack to avoid creation each time), then call Dest.FillPrepare(fTable), and use Dest.FillRow(row) to create the TSQLTemp content from the fTable instance and not the remote server - which is what TSQLTemp.Create(globalClient, id) does. So you don't have to retrieve the ID and the record from the Server each time. It doesn't make any sense to use a fTable in this case.

That is:
1. retrieve all data from server in a fTable;
2. create a local tmpRec: TSQLTemp instance; (you can use tmpRec := TSQLTemp.CreateAndFillPrepare to make both step 1 and 2)
3. use tmpRec.FillRow(row) in TcxCustomDataSource.GetValue to retrieve the row data to be displayed;
4. use tmpRec.FillRow(row) + update tmpRec field (you can also use tmpRec.FieldProp(fieldname).SetValue(tmpRec,...) with the field name) + send to server via globalClient.Update(tmpRec) + refresh the table via globalClient.UpdateFromServer([tmpRec.FillTable]) in TcxCustomDataSource.SetValue to update the row data;

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: How to use FillRow FillPrepare?

Thanks for "fTable.IDColumnHiddenValue (riga) "

ok maybe now, I understand how it works,
but then I ask you is it right to call for each GetValue  the method : Dest.FillPrepare ( fTable) as an example, or is it better to create it only once in the function ?

function TcxCustomDataSource.GetValue(ARecordHandle: TcxDataRecordHandle;
  AItemHandle: TcxDataItemHandle): Variant;
....
begin

try
  Dest: = TSQLTemplate.Create;
Dest.FillPrepare ( fTable)
Dest.FillRow (row)
...
finally
     FreeAndNil(Dest);
  end;
end;

function TcxCustomDataSource.GetValue(ARecordHandle: TcxDataRecordHandle;
  AItemHandle: TcxDataItemHandle): Variant;
var
  col : TcxGridColumn;
  row : Integer;
  Dest : TSQLTemplate;
//  id : Integer;
begin
  col := TcxGridColumn(DataController.GetItem(integer(AItemHandle)));
  row := Integer(ARecordHandle)+1;
  try

  // id := fTable.IDColumnHiddenValue(row);
  Dest  := TSQLTemp.Create;
  Dest.FillPrepare(fTable);
  Dest.FillRow(row);
  case  col.Tag  of

    IndexOfNome:
      Result := Dest.Name;
    IndexOfDesc:
      Result := Dest.Desc;
    IndexOfID:
      Result := Dest.ID;
 
  end;
  finally
     FreeAndNil(Dest);
  end;
end;

procedure TcxCustomDataSource.SetValue(ARecordHandle: TcxDataRecordHandle;
  AItemHandle: TcxDataItemHandle; const AValue: Variant);
var
  col : TcxGridColumn;
  row : Integer;
  Dest : TSQLTemp;
//  id : Integer;
  refreshed :Boolean;
begin

  col:= TcxGridColumn(DataController.GetItem(integer(AItemHandle)));
  row := Integer(ARecordHandle)+1;
  try

  Dest  := TSQLTemp.Create;
  Dest.FillPrepare(fTable);
  Dest.FillRow(row);

  if assigned(Dest) then
  begin
  case  col.Tag  of
    IndexOfNome:
      Dest.Name := AValue ;
    IndexOfDesc:
      Dest.Desc := AValue ;
    IndexOfID:
      Dest.ID := AValue ;

  end;
  globalClient.Update(Dest);
  globalClient.UpdateFromServer([fTable],refreshed); // this is to refresh the grid content

  end;
  finally
     FreeAndNil(Dest);
  end;
end;

I ask this because in the quantum grid getValue procedure is launched for each column. Thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: How to use FillRow FillPrepare?

Since GetValue is called for each cell, it's definitively better to define it globally once for the grid.

That's what I meant writing:
"create a local tmpRec: TSQLTemp instance; (you can use tmpRec := TSQLTemp.CreateAndFillPrepare to make both step 1 and 2)"

This tmpRec is local to the TcxCustomDataSource instance...

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: How to use FillRow FillPrepare?

Did you take a look at our internal virtual grid component?

Of course, there is less options than in Quantum Grid, but it well integrated with the framework: quite all data types are handled and displayed directly, without any code to write. For instance, you can get all enumeration types displayed as text, booleans are displayed as checkbox, and such.
The only missing feature is the in-grid editing. This is what you want to use, I imagine.

See http://synopse.info/forum/viewtopic.php?id=140

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: How to use FillRow FillPrepare?

Thanks! now the code is as follows:

constructor TcxCustomDataSource.Create(const ATemplate: TSQLTemplate);
begin
  inherited Create;
  Dest := ATemp;
end;

destructor TcxCustomDataSource.Destroy;
begin

  inherited Destroy;
end;
procedure TcxCustomDataSource.DeleteRecord(
  ARecordHandle: TcxDataRecordHandle);
begin
  //T.Delete(Integer(ARecordHandle));
end;

function TcxCustomDataSource.GetRecordCount: Integer;
begin
  Result := Dest.FillTable.RowCount;
end;

function TcxCustomDataSource.GetValue(ARecordHandle: TcxDataRecordHandle;
  AItemHandle: TcxDataItemHandle): Variant;
var
  col : TcxGridColumn;
  row : Integer;
// Dest : TSQLTemp;
//  id : Integer;
begin
  col := TcxGridColumn(DataController.GetItem(integer(AItemHandle)));
  row := Integer(ARecordHandle)+1;
  try

  // id := fTable.IDColumnHiddenValue(row);
//  Dest  := TSQLTemp.Create;
//  Dest.FillPrepare(fTable);
  Dest.FillRow(row);
  case  col.Tag  of

   IndexOfName:
      Result := Dest.Name;
    IndexOfDesc:
      Result := Dest.Desc;
    IndexOfID:
      Result := Dest.ID;

  end;
  finally
//     FreeAndNil(Dest);
  end;
end;

procedure TcxCustomDataSource.SetValue(ARecordHandle: TcxDataRecordHandle;
  AItemHandle: TcxDataItemHandle; const AValue: Variant);
var
  col : TcxGridColumn;
  row : Integer;
// Dest : TSQLTemp;
//  id : Integer;
  refreshed :Boolean;
begin

  col:= TcxGridColumn(DataController.GetItem(integer(AItemHandle)));
  row := Integer(ARecordHandle)+1;
  try

//  Dest  := TSQLTemp.Create;
//  Dest.FillPrepare(fTable);
  Dest.FillRow(row);

  if assigned(Dest) then
  begin
  case  col.Tag  of
    IndexOfName:
      Dest.Name := AValue ;
    IndexOfDesc:
      Dest.Desc := AValue ;
    IndexOfID:
      Dest.ID := AValue ;

  end;
  globalClient.Update(Dest);
  globalClient.UpdateFromServer([Dest.FillTable],refreshed); // this is to refresh the grid content

  end;
  finally
//     FreeAndNil(Dest);
  end;
end;

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: How to use FillRow FillPrepare?

I Don't want to take advantage of your kindness but I have another doubt.

I created a class TSQLTemp  connected to two other classes, if I wanted to run the merge of the two classes what to do?.

User.Temps + User.Roles.Temps = All the elements of a single user "temp" and "temp" all the elements of all the roles of the same single user

unit FileTables;

interface

uses
  SynCommons,
  SQLite3Commons;

type
  TSQLTempUsers = class;
  TSQLTempRoles = class;

  TSQLTemp = class(TSQLRecord)
  private
    fCodice: Integer;
    fName : RawUTF8;
  published
    property Codice: Integer read fCodice write fCodice;
    property Name: RawUTF8 read fName write fName;
  end;

  TSQLUser = class;
  TSQLTempUsers = class(TSQLRecordMany)
  private
    fSource: TSQLTemp;
    fDest: TSQLUser;
  published
    property Source: TSQLTemp read fSource;
    property Dest: TSQLUser read fDest;
  end;

  TSQLUserRole = class(TSQLRecord)
  private
    fRoleName: RawUTF8;
    fTemps: TSQLTempRoles;
  public
    // create standard roles: admin & user
    class procedure CreateStandardRoles(const ADatabase: TSQLRest);
  published
    property RoleName: RawUTF8 read fRoleName write fRoleName;
    property Temps: TSQLTempRoles read fTemps;
  end;

  TSQLUserRoles = class(TSQLRecordMany)
  private
    fValidUntil: TTimeLog;
    fSource: TSQLUser;
    fDest: TSQLUserRole;
  published
    property ValidUntil: TTimeLog read fValidUntil write fValidUntil;
    property Dest: TSQLUserRole read fDest;
    property Source: TSQLUser read fSource;
  end;

  TSQLTempRoles = class(TSQLRecordMany)
  private
    fSource: TSQLTemp;
    fDest: TSQLUserRole;
  published
    property Source: TSQLTemp read fSource;
    property Dest: TSQLUserRole read fDest;
  end;

  // user of our system
  TSQLUser = class(TSQLRecord)
  private
    fRoles: TSQLUserRoles;
    fLogin, fPassword: RawUTF8;
    fName : RawUTF8;
    fSubName: RawUTF8;
    fTemps: TSQLTempUsers;
    procedure SetPassword(const APwd: RawUTF8);
  public
    function HasRole(const ARoleName: RawUTF8; const ARoleID: integer = -1): boolean; overload;
    // returned aRowID is an ID of row in PIVOT TABLE !!!
    // so that we can access additional data stored in pivot connection
    function HasRole(const ARoleName: RawUTF8; const ARoleID: integer; var ARowID: integer): boolean; overload;
    // returns 0 on fail, UserID on success
    class function SignIn(const ALogin, APassword: RawUTF8): Integer;
    // counts all users
    // -1 on error
    class function GetCount(): integer;
  published
    property Roles: TSQLUserRoles read fRoles write fRoles;
    property Name: RawUTF8 read fName write fName;
    property SubName: RawUTF8 read fSubName write fSubName;
    property Login: RawUTF8 read fLogin write fLogin;
    property Password: RawUTF8 read fPassword write SetPassword;
    property Temps: TSQLTempUsers read fTemps;
  end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: How to use FillRow FillPrepare?

Could you post the whole implementation of these classes in some place to be downloaded?

It could help me make a better idea of your implementation.

Thanks for your interest.

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: How to use FillRow FillPrepare?

I posted the correct version of the class TSQLTemp = class (TSQLRecord)

TSQLTemp = class(TSQLRecord)
  private
    fCodice: Integer;
    fName : RawUTF8;

    fUsers: TSQLTempUsers;
    fRoles: TSQLTempRoles;
  published
    property Codice: Integer read fCodice write fCodice;
    property Name: RawUTF8 read fName write fName;

    property Users: TSQLTempUsers read fUsers;
    property Roles: TSQLTempRoles read fRoles;
  end;

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: How to use FillRow FillPrepare?

I used your example "Synops-sqlite-demo-read-only" and I added a single class TSQLTemp "then I wanted to clump them elements of subclasses User.Temps Role.Temps and where the role is the role of the user. I wrote this function:

procedure TForm1.LoadTemplatesForUserAndRole(const AUser: TSQLUser);
var
Temp: TSQLTemp;
fIds: TIntegerDynArray;
ARole: TSQLUserRole;
begin
  AUser.Temps.SourceGet(globalClient, AUser.ID, fIds);
  AUser.Roles.FillMany(globalClient, AUser.ID);

  while AUser.Roles.FillOne do
    begin
      if AUser.Roles.Dest <> nil then
        try
          ARole:= TSQLUserRole.Create(globalClient, integer(AUser.Roles.Dest));
//But at this point is overwritten Fids
          ARole.Temps.SourceGet(globalClient, ARole.ID, fIds);
         

        finally
          FreeAndNil(ARole);
        end;
    end;

  Temp:= TSQLTemp.CreateAndFillPrepare(globalClient, fIds);

  DisplayTempDataset(Temp);

end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: How to use FillRow FillPrepare?

Is there something wrong with your code?

I don't understand exactly what your code is supposed to do.

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: How to use FillRow FillPrepare?

Of course, I have to compile a list of items of SQLTemp class  that belong to a one user (SQLUser) and a one role (SQLUserRole), if I apply the code I have written above only see the items in the second filter, because the array fIDs is overwritten . What should I write to obtain the sum of the two results?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: How to use FillRow FillPrepare?

Use a global TIntegerDynArray list, in which you add all fIDs retrieved inside the loop.

If you use a sorted TIntegerArray, it could be faster. See AddSortedInteger() function.