SQL insert
lazarus / 16-Databases

----------------------------------------------------------------------
flori · 2021-02-13 18:36

Hi everyone!
 I'd like to ask for your help!
I have a small database software which calculate nitrogen content in
soil.
If nitrogen =5 good
if nitrogen <5  bad
if nitrogen  <1 very bad
how to solve that if I press the  Calculator Nitrogen% button, the
cells (dbgrid/ NITROGEN COLUMN) will be loaded automatically and
categorize (good, bad, very bad).
Thank you.

----------------------------------------------------------------------
dseligo · 2021-02-13 22:40

Try something like this in on click event of a button:

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  
ZQuery1.SQL.Text:='select soil,nitrogen,nitrogen_category, '+    'case
when nitrogen<1 then "very bad" when nitrogen<5 then "bad" else "good"
end as "nitrogen_content" '+    'from soil';  ZQuery1.Open;

----------------------------------------------------------------------
flori · 2021-02-13 22:55

I tried but unfortunately it doesn't work. does not load cells.

----------------------------------------------------------------------
madref · 2021-02-14 00:49

It was almost good...remove the double brackets from the '...
AS'-part.
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
ZQuery1.SQL.Text :=  'select soil,nitrogen,nitrogen_category, '+ 
'case ' +    'when nitrogen < 1 then "very bad" ' +    'when nitrogen
< 5 then "bad" ' +    'else "good" ' +  'end as nitrogen_content '+ 
'from soil;';ZQuery1.Open;

----------------------------------------------------------------------
egsuh · 2021-02-14 02:23

It's not
     select * from soil, nitrogen, ....

but
     select soil, nitrogen, .....    from tablename

----------------------------------------------------------------------
flori · 2021-02-14 07:34

unfortunately I get this error again.
I attached zip

----------------------------------------------------------------------
af0815 · 2021-02-14 07:38

using the name of the table as a coloumn name is not a good idea.

----------------------------------------------------------------------
flori · 2021-02-14 07:51

Yes I change column name (SOILANAL), table name is SOIL

----------------------------------------------------------------------
balazsszekely · 2021-02-14 08:40

Calculated fields are the best solution in your case. Values for good,
bad, very bad soil might change slightly in the future. No need to
store them in the db.

----------------------------------------------------------------------
flori · 2021-02-14 10:40

Thank you!!!
How can I save nitrogen_category in GDB?

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
FCalculateValues := True;ZQuery1.Refresh;
edit /insert mode?

----------------------------------------------------------------------
balazsszekely · 2021-02-14 11:30

Quote from: flori on February 14, 2021, 11:40:23 amThank you!!!
How can I save nitrogen_category in GDB?

You can't. This is the whole ideea of calculated field, is always
calculated. If you want to save the values to db, the best solution is
to use triggers. I can make a small demo later, but if in the future
the good value changes from 5  to 6 for example, you will be in
trouble. This is why I choosed calc. Fields.

----------------------------------------------------------------------
flori · 2021-02-14 13:07

ok. thank you.
I add to xlsx. import_button in form. Because I usually import all
data in excel. (the laboratory sends it this way )
the goal would be to automatically calculate the nitrogen_categories
when I import from excel. (often thousands of data as well)

----------------------------------------------------------------------
balazsszekely · 2021-02-14 14:33

The attached import.xlsx is empty. Please upload an excel with at
least a few values. The goal is to load SOILTYPE AND NITRO% from xlsx,
calculate NITROGEN_CATEGORY and save all to db? Did I understand it
correctly? Your xlsx also contains a CATEGORIES column, what is the
purpose of this column?

----------------------------------------------------------------------
flori · 2021-02-14 14:40

Yes.
First
1. import xlsx.file to db
2. after import or during import calculate nitrogen_category(good,
bad, very bad)
3. save db, -
I attached import.xlsx with data.

----------------------------------------------------------------------
balazsszekely · 2021-02-14 17:17

@flori

Attached demo project with xlsx import. Few notes:
1. I changed the primare key to an integer(Field: ID), you had a
string as primary key which is not ideal
2. I added a generator(Soil_Gen), it will automatically increment the
ID value when you insert a new record(see db.sql inside db folder)
3. When you press the Import button, a new form appears with the xlsx
data, the program will parse the excel file and warns you if there is
a bad record(see attached screenshot)

Hope it helps.

----------------------------------------------------------------------
flori · 2021-02-14 17:26

WOW! 
amazing work! Thank you!
i come with a beer