Forum > Databases

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

(1/3) > >>

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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
UPDATE: Well this didn't work

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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'  :D
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

Navigation

[0] Message Index

[#] Next page

Go to full version