[SOLVED]TDBGrid showing real numbers as integers.

[vdubeau]

I have an SQLite database with a Total field defined as real. The TDBGrid seems to be displaying them as integers. I did find the topic on how to format the number of decimal points in a column. It seemed to make sense in the context of the user since his numbers were being displayed with decimal points.

Is there any reason that my Total field is showing as integers instead of real numbers? AM I missing something.

Thanks to all.

[dsiders]

I have an SQLite database with a Total field defined as real. The TDBGrid seems to be displaying them as integers. I did find the topic on how to format the number of decimal points in a column. It seemed to make sense in the context of the user since his numbers were being displayed with decimal points.

Is there any reason that my Total field is showing as integers instead of real numbers? AM I missing something.

Thanks to all.

You're missing the fact that sqlite will let you put anything in any column regardless of the declared type. You clearly want currency amounts and not real numbers. So ask for that in your query.

Code: MySQL  [Select][+][-]

1. select invoice_no, cast(round(amount, 2) as decimal(10, 2)) "amount" from invoices;

Make sure you post values rounded to 2 decimals when updating the table.

In short, your only mistake was assuming that sqlite was a real database...

[vdubeau]

I tried using the "as decimal" in a SQL query in the SQLite editor. I barfed on the as syntax.

[dsiders]

I tried using the "as decimal" in a SQL query in the SQLite editor. I barfed on the as syntax.

It works with sqlite3 version 3.40.1. Don't have an older one to test against.

Schema is:

Code: MySQL  [Select][+][-]

1. CREATE TABLE invoices (id integer primary key, invoice_no integer, customer_no integer, amount decimal(16,2), balance decimal(16,2));

Query used:

Code: MySQL  [Select][+][-]

1. select id, invoice_no, customer_no, cast(round(amount, 2) as decimal(16, 2)) "amount", cast(round(balance, 2) as decimal(16,2)) "balance" from invoices;

Works for me.

[vdubeau]

Apparently works under MySQL but not SQLite.

[dsiders]

Apparently works under MySQL but not SQLite.

The MySQL tag is the only option available in the forum software. I used sqlite like I mentioned.

Just checked 3.41.2. Works there. Check your version number.

[Zvoni]

*sigh*
There is no "DECIMAL"-Datatype in SQLite.
If you use a CREATE TABLE with Decimal as above, SQLite assigns "Numeric" to that column
RTFM: https://www.sqlite.org/datatype3.html

A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 300000, not as the floating point value 300000.0.

Chapter 3.4 of the link clearly states he has the correct Storage-Class REAL.
The Issue probably has mor to do with DBGrid than with SQLite

[rvk]

Is there any reason that my Total field is showing as integers instead of real numbers? AM I missing something.

Real field should show decimals.
Is the Total field a real field or a calculated one?

If it's a real field, did you check (with rightclick) if the TSQLQuery has predefined fields.
(so rightclick on the TSQLQuery component, choose Edit fields and remove them (if there are any)).

BTW. What is the total in the first row? Can you make the column wider and see what the value is.
The 0's in front suggest that there is a decimal point.

[loaded]

i use it like this;

Code: Pascal  [Select][+][-]

1. procedure TForm1.ZQuery1AfterOpen(DataSet: TDataSet);
2. begin
3.  TFloatField(ZQuery1.Fields.FieldByName('MONEY')).DisplayFormat := ',0.00 MN';
4.  TFloatField(ZQuery1.Fields.FieldByName('LENGHT')).DisplayFormat := '0.00';
5. end;  
6.  

[Zvoni]

If it's a real field, did you check (with rightclick) if the TSQLQuery has predefined fields.
(so rightclick on the TSQLQuery component, choose Edit fields and remove them (if there are any)).

Was my first thought, too.
but OP clearly stated he has a Field "Total" with Type REAL, so.... *shrug*

[dsiders]

*sigh*
There is no "DECIMAL"-Datatype in SQLite.
If you use a CREATE TABLE with Decimal as above, SQLite assigns "Numeric" to that column
RTFM: https://www.sqlite.org/datatype3.html

Yes, please RTFM: https://www.sqlite.org/datatype3.html#affinity

Code: Text  [Select][+][-]

1. 3.1.1. Affinity Name Examples

Allows it in create table and cast. It can't enforce it... or any other data type for that matter. Doesn't matter what DBGrid is doing with it when the "database" accepts anything.

[Zvoni]

*sigh*
There is no "DECIMAL"-Datatype in SQLite.
If you use a CREATE TABLE with Decimal as above, SQLite assigns "Numeric" to that column
RTFM: https://www.sqlite.org/datatype3.html

Yes, please RTFM: https://www.sqlite.org/datatype3.html#affinity

Code: Text  [Select][+][-]

1. 3.1.1. Affinity Name Examples

Allows it in create table and cast. It can't enforce it... or any other data type for that matter. Doesn't matter what DBGrid is doing with it when the "database" accepts anything.

Yes, and if i say "Yankee" also everyone knows i mean an american.
Doesn't change the fact, there is no official Designation "Yankee".

Just because you use CREATE TABLE with DECIMAL, and it gets assigned the Affinity NUMERIC, doesn't mean it gets stored as an decimal!
SQLite decides on its own. If for whatever reason that "decimal" is an Integer ("123456.0") it stores it as an Integer

The OP has it correct with REAL

[vdubeau]

Casting the real value to DECIMAL didn't change anything. I did some digging on Google and came across something that worked perfectly. Some SQL can apparently us a version of the 'C' printf command. MY SQL statement in Lazarus now looks like this:
    SQLQuery1.SQL.Text := 'select invoiceid, company,date,status, printf("%.2f",total) as InvTotal from invoices';
The numbers are perfectly formatted as currency. See screen shot.

This may not work for all versions of SQLite. I have the current version 3.41.2.