Recent

Author Topic: [SOLVED]CALCULATED Fields: SQLite (I'm very close)  (Read 1467 times)

What I can do

  • Jr. Member
  • **
  • Posts: 60
[SOLVED]CALCULATED Fields: SQLite (I'm very close)
« on: May 16, 2024, 02:44:52 pm »
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
« Last Edit: May 27, 2024, 08:18:38 pm by What I can do »

What I can do

  • Jr. Member
  • **
  • Posts: 60
Re: CALCULATED Fields: SQLite (I'm very close)
« Reply #1 on: May 16, 2024, 08:21:47 pm »
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

  • Hero Member
  • *****
  • Posts: 1264
Re: CALCULATED Fields: SQLite (I'm very close)
« Reply #2 on: May 16, 2024, 08:37:34 pm »
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

  • Hero Member
  • *****
  • Posts: 1253
    • http://www.cdbc.dk
Re: CALCULATED Fields: SQLite (I'm very close)
« Reply #3 on: May 16, 2024, 08:55:09 pm »
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'  :D
edit: typo
Regards Benny
« Last Edit: May 16, 2024, 08:59:03 pm by cdbc »
If it ain't broke, don't fix it ;)
PCLinuxOS(rolling release) 64bit -> KDE5 -> FPC 3.2.2 -> Lazarus 2.2.6 up until Jan 2024 from then on it's: KDE5/QT5 -> FPC 3.3.1 -> Lazarus 3.0

What I can do

  • Jr. Member
  • **
  • Posts: 60
Re: CALCULATED Fields: SQLite (I'm very close)
« Reply #4 on: May 17, 2024, 02:43:55 am »
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

  • Hero Member
  • *****
  • Posts: 2802
Re: CALCULATED Fields: SQLite (I'm very close)
« Reply #5 on: May 17, 2024, 03:02:20 am »
This code is from the installed examples and my first exposer to using SQLite.
The example you use does not use SQLite.

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

Quote
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.
« Last Edit: May 17, 2024, 03:06:12 am by TRon »

What I can do

  • Jr. Member
  • **
  • Posts: 60
Re: CALCULATED Fields: SQLite (I'm very close)
« Reply #6 on: May 17, 2024, 04:05:21 am »
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.

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

TRon

  • Hero Member
  • *****
  • Posts: 2802
Re: CALCULATED Fields: SQLite (I'm very close)
« Reply #7 on: May 17, 2024, 04:36:55 am »
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

  • Jr. Member
  • **
  • Posts: 57
Re: CALCULATED Fields: SQLite (I'm very close)
« Reply #8 on: May 17, 2024, 11:16:27 am »
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
Lazarus stable, Win32/64

Zvoni

  • Hero Member
  • *****
  • Posts: 2412
Re: CALCULATED Fields: SQLite (I'm very close)
« Reply #9 on: May 18, 2024, 09:31:17 am »
And if it really is SQLite, generated columns requires engine version 3.31.0 or later
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

What I can do

  • Jr. Member
  • **
  • Posts: 60
Re: CALCULATED Fields: SQLite (I'm very close)
« Reply #10 on: May 27, 2024, 08:18:02 pm »
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

 

TinyPortal © 2005-2018