[SOLVED]CALCULATED Fields: SQLite (I'm very close)
lazarus / 16-Databases

----------------------------------------------------------------------
What I can do · 2024-05-16 12:44

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE Products(     Id INTEGER PRIMARY KEY,     Name TEXT,    Qty
INT,    Price INT,    TotalValue INT GENERATED ALWAYS AS (Qty *
Price));
but I have this code from [Tools] (example projects) =>AddressBook and
adapted it to my egg tracker
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.CreateTable(AFileName : String);begin  With DBf1 do    begin  
Close;    with FieldDefs do      begin      Clear;     
Add('Build',ftInteger);      Add('Start',ftDate);     
Add('Hatch',ftDate);      Add('Eggs',ftInteger);     
Add('Success',ftInteger);       Add('Percent',ftFloat);     
Add('Days',ftInteger);       end;    TableName:=AFileName;   
CreateTable;    Exclusive := true;    Open;    AddIndex('Build',
'Build', []);    end;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 · 2024-05-16 18:21

UPDATE: Well this didn't work
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.CreateTable(AFileName : String);begin  With DBf1 do    begin  
Close;    with FieldDefs do      begin      Clear;     
Add('Build',ftInteger);      Add('Start',ftDate);     
Add('Hatch',ftDate);      Add('Eggs',ftInteger);     
Add('Success',ftInteger);       Add('Days INT GENERATED ALWAYS AS
(Hatch-date)',ftInteger);      Add('Percent Decimal(10,2) GENERATED
ALWAYS AS (Success / eggs)',ftFloat);      end;   
TableName:=AFileName;    CreateTable;    Exclusive := true;    Open; 
 AddIndex('Build', 'Build', []);    end;end;

----------------------------------------------------------------------
dseligo · 2024-05-16 18:37

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 · 2024-05-16 18:55

Hi
Hmmm, try this on for size:Code: Pascal  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i Query1.Close;Query1.Sql.Text:= 'CREATE TABLE hatchtrack('+    
              'id_ht integer primary key, '+                   
'build_ht int'+                    'start_ht real, '+                
  'hatch_ht real, '+                                     'eggs_ht int,
'+                    'success_ht int,'+                    'days_ht
int GENERATED ALWAYS AS (hatch_ht-start_ht),'+                   
'percent_ht real GENERATED ALWAYS AS (success_ht / eggs_ht)'+        
         ');';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 · 2024-05-17 00:43

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 · 2024-05-17 01:02

Quote from: What I can do on May 17, 2024, 02:43:55 amThis code is
from the installed examples and my first exposer to using SQLite.
The example you use does not use SQLite.

QuoteI 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).

Quotethey 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 · 2024-05-17 02:05

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.

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

----------------------------------------------------------------------
TRon · 2024-05-17 02:36

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 · 2024-05-17 09:16

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 · 2024-05-18 07:31

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

----------------------------------------------------------------------
What I can do · 2024-05-27 18:18

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