[SOLVED]CALCULATED Fields: SQLite (I'm very close)

[What I can do]

OS: Microsoft Windows 10 Home Version 10.0.18363
Compiler: Lazarus 3.2 FPC:3.2.2
DB Engine: SQLite
Project: Egg hatching tracker
I'm very close with lots of stuff out there but I haven't found the exact stuff I need.  How do I get this to work.
I found this

Code: Pascal  [Select][+][-]

1. CREATE TABLE Products(
2.     Id INTEGER PRIMARY KEY,
3.     Name TEXT,
4.     Qty INT,
5.     Price INT,
6.     TotalValue INT GENERATED ALWAYS AS (Qty * Price)
7. );

but I have this code from [Tools] (example projects) =>AddressBook and adapted it to my egg tracker

Code: Pascal  [Select][+][-]

1. procedure TForm1.CreateTable(AFileName : String);
2. begin
3.   With DBf1 do
4.     begin
5.     Close;
6.     with FieldDefs do
7.       begin
8.       Clear;
9.       Add('Build',ftInteger);
10.       Add('Start',ftDate);
11.       Add('Hatch',ftDate);
12.       Add('Eggs',ftInteger);
13.       Add('Success',ftInteger);
14.  
15.       Add('Percent',ftFloat);
16.       Add('Days',ftInteger);
17.  
18.       end;
19.     TableName:=AFileName;
20.     CreateTable;
21.     Exclusive := true;
22.     Open;
23.     AddIndex('Build', 'Build', []);
24.     end;
25. end;

Here is what i would like to be done.
TABLE:
IDX | Start | Days | Hatch | Eggs | Success | Percent

Field Days = DaySpan(Now,Hatch)   INT
Field Percent = Success/Egg       Float

[What I can do]

UPDATE: Well this didn't work

Code: Pascal  [Select][+][-]

1. procedure TForm1.CreateTable(AFileName : String);
2. begin
3.   With DBf1 do
4.     begin
5.     Close;
6.     with FieldDefs do
7.       begin
8.       Clear;
9.       Add('Build',ftInteger);
10.       Add('Start',ftDate);
11.       Add('Hatch',ftDate);
12.       Add('Eggs',ftInteger);
13.       Add('Success',ftInteger);
14.  
15.       Add('Days INT GENERATED ALWAYS AS (Hatch-date)',ftInteger);
16.       Add('Percent Decimal(10,2) GENERATED ALWAYS AS (Success / eggs)',ftFloat);
17.       end;
18.     TableName:=AFileName;
19.     CreateTable;
20.     Exclusive := true;
21.     Open;
22.     AddIndex('Build', 'Build', []);
23.     end;
24. end;        

[dseligo]

What is DBf1?
If you use SQLite, why don't you just use SQL 'create table' (like in the first code snippet you posted).

[cdbc]

Hi
Hmmm, try this on for size:

Code: Pascal  [Select][+][-]

1. Query1.Close;
2. Query1.Sql.Text:= 'CREATE TABLE hatchtrack('+
3.                     'id_ht integer primary key, '+
4.                     'build_ht int'+
5.                     'start_ht real, '+
6.                     'hatch_ht real, '+                
7.                     'eggs_ht int, '+
8.                     'success_ht int,'+
9.                     'days_ht int GENERATED ALWAYS AS (hatch_ht-start_ht),'+
10.                     'percent_ht real GENERATED ALWAYS AS (success_ht / eggs_ht)'+
11.                    ');';
12. Query1.ExecSql;

One could also assign the sql to 'xxxConnection.ExecuteDirect();' instead...
Untested code, so you have to play 'ginny-pig' 
edit: typo
Regards Benny

[What I can do]

Hey dseligo  How you doing?
This code is from the installed examples and my first exposer to using SQLite. I had a string grid that I was doing every thing storage and calculations with and thought it would be a good project to test SQLite with. The Example AddressBook uses only two DB components TDbf and TDataSource. so if you want to see the whole package just go to [Tools] (Examples projects) scroll down to AddressBook and the mods I made are very few.  In the example they did everything except calculate columns. 
they also used more modular base script as apposed to SQL Scripts.  I was wondering if calculated columns could be done in modular code instead of SQL Script.  In the example TDbf dose all the control instead of a TSQLQuery

[TRon]

This code is from the installed examples and my first exposer to using SQLite.

The example you use does not use SQLite.

I had a string grid that I was doing every thing storage and calculations with and thought it would be a good project to test SQLite with.

You can only test SQLite when actually using SQLite, not DBF (DBase database).

they also used more modular base script as apposed to SQL Scripts.

TDBF does not use/support SQL scripting.

You are most probably mix-matching between two different database formats and a scripting language.

See also supported databases and please do read the wiki instead of YOLO-ing.

[What I can do]

I find that very informative https://wiki.freepascal.org/Databases#Supported_databases
also https://en.wikipedia.org/wiki/Access_Database_Engine is very informative. A quick search for *.DBF files found this https://www.dbase.com/Knowledgebase/INT/db7_file_fmt.htm sort of technical but simple enough to follow. https://en.wikipedia.org/wiki/.dbf also covers a lot of different formats that are mostly dBase.
So which one is Windows 10 using with TDbf component it looks like it is using Jet inside window to create a dBase file.

See also supported databases and please do read the wiki instead of YOLO-ing.

[TRon]

For more information on Free Pascal and MS access see this wiki page but I would probably suggest to use something else like SQLite ( FPC wiki ) or MySQL ( FPC wiki ).

See also Database portal and in particular SQLDB howto and the SQLDB tutorials.

[CharlyTango]

As TRon explained, obviosly you are NOT using SQLite in your code -- you are using xBASE (dBase, dBas3, dBase3+,Foxbase lot more possible formats Lazarus is capable to manage)

These xBase components you are using (you named one database component Dbf1) are managing xBase Files natively without using ANY other parts from the operating system (Windows in your case)

There may be a misunderstanding with the terms - "Access Components" or "Database Access Components" describe components that enable access to data.
This has nothing to do with the Jet Engine of MS Access (which is a Microsoft database).

After more than 30 years of database development (and certainly more than 10 years with xBase formats), I would not recommend either of the two variants to a beginner, but rather to start directly with SQLite.

SQLite is easy to set up and use, Lazarus has all the necessary components, but in any case you need the right access library (e.g. sqlite3.dll for Windows). I would recommend copying this into the Lazarus EXE directory (to be able to use it in the Lazarus GUI) and also copying a copy of it into the EXE directory of the application you have created (so that the application can access it without any problems).

The learning curve for databases (regardless of which one) is correspondingly steep, but it is worth it.
I think it's bad style to create a calculated field in a table. You can always have calculated fields created in a query (SELECT ...) without having to save them in a table.

Lazarus has its own data-sensitive components (data controls) that you can connect directly to data sources without having to programme string grids. -->TDbGrid for example

https://wiki.freepascal.org/SQLite

[Zvoni]

And if it really is SQLite, generated columns requires engine version 3.31.0 or later

[What I can do]

No it turned out to be dBase created by Microsoft Jet
and my data base was very small so I just looped through and calculated running total and it worked fine. I did do some testing with a few internet SQLite packages but they crashed so hard that not only reinstalling Lazarus didn't patch it's self but I had to remove all folders and reg keys then reinstall. So I put SQLite on pause for another day but still thank you very much