[SOLVED] Data type for numbers with a decimal point
lazarus / 16-Databases

----------------------------------------------------------------------
Jiří Huňáček · 2020-12-25 11:17

Hello everyone,

I would like to solve one problem that I currently have. These are
numbers with a decimal point stored in the database table.

I have a table with a structure where the first field is without
specifying a type and the others have numeric types that should
correspond to decimal numbers (Figure 1). When I display the table,
decimal places are visible for all numbers in all columns (Figure 2).

If I now execute an SQL query with the statement 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 SELECT *
FROM NUMBERS (Figure 3), the result will show me decimal places only
for columns without type and with type NUMBER. For other types, the
number is displayed as INTEGER (Figure 4).

I know you don't have to specify a type, but I need the above SQL
query to really come up with a number with decimal places that I can
work with as a number. The only thing that works for me now is to use
an array in the table without type or with type NUMBER and after
running the query to refer to the value e.g. via 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
SQLQuery1.FieldByName('p_number').AsFloat.

I used to program in FoxPro and Visual Basic and I'm a little confused
about this other approach. 

Thanks for Your answers, George.

----------------------------------------------------------------------
CLA · 2020-12-25 14:54

Hi, I'm not an expert but here's what I think

SQLite has it's own way of storing data and normally you do not have
to override or change something.
You have to take action when you want to display the stored data by
formatting the stored value.

Have a look at the formatfloat function 
https://www.freepascal.org/docs-html/rtl/sysutils/formatfloat.html
that may be what you're looking for.

----------------------------------------------------------------------
Jiří Huňáček · 2020-12-28 12:54

Hi everyone,

since it didn't let me sleep  , so I did a little research and found a
surprising thing. The mistake is on my side, of course. I forgot that
in English, decimal places are separated by a dot "." - but in our
country (Czech Republic) we write the comma ",". And of course
everything about programming according to English.

So like this.   And then the standard REAL type works perfectly.

----------------------------------------------------------------------
marcov · 2020-12-28 14:39

Quote from: Jiří Huňáček on December 28, 2020, 01:54:32 pmsince it
didn't let me sleep  , so I did a little research and found a
surprising thing. The mistake is on my side, of course. I forgot that
in English, decimal places are separated by a dot "." - but in our
country (Czech Republic) we write the comma ",". And of course
everything about programming according to English.

In Dutch there is the same problem, but Dutch MS Office usually uses
Dutch settings so often you need to deal with both. For that I use:

Code: [Select]var Englishformatsettings :   tformatsettings;
    Dutchformatsettings   :   tformatsettings;


function convertlocaleindependant (const astr:string; var s :
single):boolean;
// also another version overloaded for double
begin
  if not trystrtofloat(astr,s,Englishformatsettings) then
      if not trystrtofloat(astr,s,Dutchformatsettings) then
         exit(false);
  result:=true;
end;

  Englishformatsettings:=formatsettings; // initialize with default.
  Englishformatsettings.ThousandSeparator:=',';
  Englishformatsettings.DecimalSeparator:='.';
  Dutchformatsettings:=formatsettings;
  Dutchformatsettings.ThousandSeparator:='.';
  Dutchformatsettings.DecimalSeparator:=',';

----------------------------------------------------------------------
lucamar · 2020-12-28 14:55

Quote from: marcov on December 28, 2020, 03:39:32 pmIn Dutch there is
the same problem, [...]
I think most languages other than English have exactly the same
problem. At least most (it not all) European languages.

And could be worse: we now use decimal-commas, as good little
eurocitizens, but when I was a child the decimal separator in Spain
was the apostrophe ...yet it was called "comma" when used for that: we
would write "10'50" and say "ten comma fifty" (in Spanish, o.c.: "diez
coma cincuenta", normally shortened to "diez con cincuenta", "ten with
fifty")