Mastering Open Source

Goal : add virtual column in the left side of DBGrid component.

Why we need to add virtual column, from user experience, they need to identify how many row in the DBGrid. You can solve this using row color to differentiate between even and odd row.

In fact, when there are so many approach, to add virtual column is the close approach to this, without too much code, without changing database structure. Here is the step to do that, what we gonna do is using ZeosDB vcl component, drop TZConnection, drop TZQuery or you can use TZTable. I am using MySQL database here, and create table let say item with the structure as follow:

create item (
ID int(10),
itemname varchar(255) );

After you setup necessary properties for TZConnection, TZQuery or TZTable, add one virtual field to TZQuery or TZTable. Right click on TZQuery or TZTable, open up Field Editor.

add new field, in this example i create vRowNumber, with integer as data type, and choose calculated data.Why we choose as calculated data, because we want to assign new value to field vRowNumber each time the the OnCalc event has triggered. Delphi know very well when onCalc should be call. Here is the code :

procedure Tdm.productCalcFields(DataSet: TDataSet);
begin
productvRowNumber.AsInteger := product.RecNo;
end;

Look at the code above, virtual field vRowNumber from table product is assigned by value from method RecNo. Method RecNo will returning exact value of record number for each row in product table.

The picture at above is the result from virtual field, column Row is the representation of vRowNumber field, which is the value assigned from product.RecNo.

Note: What i am trying to show here is the one way approach, and the value from virtual field vRowNumber will not stored to database, because the goal is we do not need to add new field in item table, because i do not want to changes the database structure only to show the row number. If you want to run the MySQL script when updating client database, you can use ZeosDB component’s TZSQLProcessor. TZSQLProcessor is the VCL component to run script orSQL complex command inside Delphi. I am never test this ZeosDB using Open Source Pascal IDE such as Lazarus.

By using this approach the complexity of updating the database’s structure has been reduce, and also the code to assign row number calculation became very simple. In the next tutorial i will introduce also and write about how to use TZSQLProcessor to work with MysQL database. Remember, you became more understand if you practice more and more.