Re: [sqlite] Storing amount?

[RSmith]

On 2014/05/08 16:06, Werner Kleiner wrote:

Hmm, but would it not be better to store the value 5 as
5.0 , which would be correct for the decimal point
instead of
5000.0 ?



I think the other replies to your post missed the fact that the value is different by an order of magnitude. That's not an SQLite 
problem, you are making a mistake somewhere else.


Try doing the insert as INSERT INTO mytable (mycol) VALUES ('5');
SELECT * FROM mytable;

Replace mycol and mytable with whatever your table's name/column is.


If it does not return 5.0 or 5 or '5' (depending on the type of 
"mycol") then SQLite is broken...

(spoiler: SQLite isn't broken).

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing amount?

[Simon Slavin]

On 8 May 2014, at 3:06pm, Werner Kleiner  wrote:

> Hmm, but would it not be better to store the value 5 as
> 5.0 , which would be correct for the decimal point
> instead of
> 5000.0 ?

Numeric values in numeric fields are not really stored as text, they're stored 
as binary representations.  There's no real decimal point stored unless the 
column was declared as TEXT.

Generally speaking, if all the values in a column are expected to be integers 
(or missing), declare the column as INTEGER.  If the values are continuous 
measurements, declare the column as REAL.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing amount?

[Igor Tandetnik]

On 5/8/2014 7:13 AM, Kleiner Werner wrote:

a PHP script stores an amount into a SQLite table column "salary" which is 
defined as float(10,0)
I a user fill in 5 into a textfield, this value is stored as 5000.0

Why this? why not 5 ?


sqlite> create table t(x float(10,0));
sqlite> insert into t(x) values ('5');
sqlite> select * from t;
5.0

The problem lies elsewhere, outside of SQLite. Check the part of your 
program that takes a value from the textbox and passes it along to the 
SQL statement - you are losing a character somewhere along the way.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing amount?

[Werner Kleiner]

Hmm, but would it not be better to store the value 5 as
5.0 , which would be correct for the decimal point
instead of
5000.0 ?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Storing-amount-tp75527p75539.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing amount?

[Keith Medcalf]

If the column affinity is integer, then anything that can be represented using 
integers will be stored that way (ie, 5, 5.0 '5', '5.0').  
Things that are numbers but cannot be stored as integers (because they need a 
fractional part) will be stored as floats (5.1, '5.1').  If you specify 
a column affinity of float, all numbers (even things that could be stored as 
integers) will be stored as floats.

In either case, things which are not numbers, such as the string 'Hello 
World!', are stored as text,

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Werner Kleiner
>Sent: Thursday, 8 May, 2014 07:01
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Storing amount?
>
>>SQLite does not understand column types like "numeric(10,0)".  It has
>only
>two number types: INTEGER and >REAL.
>
>So if the column is datatype REAL and you will store amount 5, SQLite
>results in 5000.0 ?
>Why not 5.0 ?
>
>And if the columns datatype is REAL, you need a helper tool or script
>which
>adds the ".0" at the end of the amount at PHP server side, if the 
>textfield only allows numbers for salary. ?
>(if you want to store the correct value)
>
>regards
>Werner
>
>
>
>--
>View this message in context:
>http://sqlite.1065341.n5.nabble.com/Storing-amount-tp75527p75533.html
>Sent from the SQLite mailing list archive at Nabble.com.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing amount?

[Werner Kleiner]

>SQLite does not understand column types like "numeric(10,0)".  It has only
two number types: INTEGER and >REAL.

So if the column is datatype REAL and you will store amount 5, SQLite
results in 5000.0 ?
Why not 5.0 ?

And if the columns datatype is REAL, you need a helper tool or script which
adds the ".0" at the end of the amount at PHP server side, if the 
textfield only allows numbers for salary. ?
(if you want to store the correct value)

regards
Werner



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Storing-amount-tp75527p75533.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing amount?

[Simon Slavin]

On 8 May 2014, at 12:41pm, Werner Kleiner  wrote:

> I use the PDO library.
> The typeOf results in "real"
> 
> If I change the "swsalary" column to "numeric(10,0) it results in integer
> and then the value of
> 5 ist stored correct without . (dot).
> 
> So is it better to take datatype "numeric" for storing amounts without dots
> or floating points?
> (In the textfield I have forbitten to fill in non numeric characters, only
> numbers allowed)

SQLite does not understand column types like "numeric(10,0)".  It has only two 
number types: INTEGER and REAL.

If the numbers you are storing are always going to be integers (if you don't 
need to see '.0' after an integer) then the best thing would be to define your 
column as an INTEGER type.  I think if you change the definition of your table 
so that this column is INTEGER then your problem will disappear.

Here is a page about which datatypes SQLite understands:



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing amount?

[Hick Gunter]

SQLite does not care about (or enforce) magnitude and precision hints. 
"numeric" alone is just as good, or even "integer". Textual values that look 
like numbers will be stored as integer (if they evaluate to a whole number) or 
real (if not). Increasing a salary of 5000 by 3% will result in a change of the 
stored type.

-Ursprüngliche Nachricht-
Von: Werner Kleiner [mailto:sqliteh...@web.de]
Gesendet: Donnerstag, 08. Mai 2014 13:42
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Storing amount?

Hello Simon,
thanks for help.

I use the PDO library.
The typeOf results in "real"

If I change the "swsalary" column to "numeric(10,0) it results in integer and 
then the value of 5 ist stored correct without . (dot).

So is it better to take datatype "numeric" for storing amounts without dots or 
floating points?
(In the textfield I have forbitten to fill in non numeric characters, only 
numbers allowed)

regards
Werner



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Storing-amount-tp75527p75529.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing amount?

[Werner Kleiner]

Hello Simon,
thanks for help.

I use the PDO library.
The typeOf results in "real"

If I change the "swsalary" column to "numeric(10,0) it results in integer
and then the value of
5 ist stored correct without . (dot).

So is it better to take datatype "numeric" for storing amounts without dots
or floating points?
(In the textfield I have forbitten to fill in non numeric characters, only
numbers allowed)

regards
Werner



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Storing-amount-tp75527p75529.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing amount?

[Simon Slavin]

On 8 May 2014, at 12:13pm, Kleiner Werner  wrote:

> a PHP script stores an amount into a SQLite table column "salary" which is 
> defined as float(10,0)
> I a user fill in 5 into a textfield, this value is stored as 5000.0

Which way of accessing SQLite are you using ?  The PDO library or the sqlite3 
one ?

How is the column defined in the table definition ?  What type is it given ?

If you do

SELECT thisCol,typeof(thisCol) FROM myTable

what type is shown for that value ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users