How to use FillRow FillPrepare?

corchi72 · 2011-01-11 11:15:56

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 · 2011-01-11 13:23:19

 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 · 2011-01-11 14:09:46

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 · 2011-01-11 15:03:56

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 · 2011-01-11 16:37:28

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 · 2011-01-12 08:06:16

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 · 2011-01-12 09:09:25

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 · 2011-01-12 14:02:23

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 · 2011-01-12 16:37:09

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 · 2011-01-12 16:44:51

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 · 2011-01-12 16:45:54

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 · 2011-01-12 16:53:44

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 · 2011-01-13 09:46:00

Is there something wrong with your code?

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

corchi72 · 2011-01-13 10:21:13

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 · 2011-01-13 10:49:12

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.

mORMot Open Source

#1 2011-01-11 11:15:56

How to use FillRow FillPrepare?

#2 2011-01-11 13:23:19

Re: How to use FillRow FillPrepare?

#3 2011-01-11 14:09:46

Re: How to use FillRow FillPrepare?

#4 2011-01-11 15:03:56

Re: How to use FillRow FillPrepare?

#5 2011-01-11 16:37:28

Re: How to use FillRow FillPrepare?

#6 2011-01-12 08:06:16

Re: How to use FillRow FillPrepare?

#7 2011-01-12 09:09:25

Re: How to use FillRow FillPrepare?

#8 2011-01-12 14:02:23

Re: How to use FillRow FillPrepare?

#9 2011-01-12 16:37:09

Re: How to use FillRow FillPrepare?

#10 2011-01-12 16:44:51

Re: How to use FillRow FillPrepare?

#11 2011-01-12 16:45:54

Re: How to use FillRow FillPrepare?

#12 2011-01-12 16:53:44

Re: How to use FillRow FillPrepare?

#13 2011-01-13 09:46:00

Re: How to use FillRow FillPrepare?

#14 2011-01-13 10:21:13

Re: How to use FillRow FillPrepare?

#15 2011-01-13 10:49:12

Re: How to use FillRow FillPrepare?

Board footer