DB: How to save/store really large numbers?

Getting Started
1

This is sort of a silly question! Of-course, you shall always save numbers as integers…

But what if there are REALLY large numbers, such as “944 000 000”, maybe it’s just easier to store them as strings?

Of-course, when working with the number you must remember it’s a string and so on…
An example, the large number could represent the total score in an arcade game. You get the point. It’s not the super important data for Federal Reserve that the nation depends upon…!

For me, saving the large score as a string simply will save me many errors… I mean, string(20) must take less space than Integer?
Well… I don’t know, that’s why I ask!

2

Check the docs: Integer datatypes

[quote=146017:@Jakob Krabbe]I mean, string(20) must take less space than Integer?[/quote]The opposite is true, unless the string is very very short.

3

Thank you!
I think Int64 will cover it all… Really, it’s not that important. Just something that I thought about!

4

I think your approach is wrong. Use a Field Type in the database that makes sense. If it’s a number, store it as a number. Use text for strings, etc. Really, really large numbers will be okay with Int64 (this also works with currency by the way).

It would be super silly to use a Text field in a database to represent numbers in say, an accounting application. The conversion process would suck and then you wouldn’t be able to do any calculations inside the database - where you want most of the work to occur. Summing up numbers can be done if they’re a string but why would you want to when it’s so much more efficient doing a simple sum of numbers?

Data conversion is inherently risky. Don’t do it unless you absolutely have to.

5

STRING(20) always takes 20 bytes … VARCHAR(20) will CAN take less based on string length… but I think it is a minimum of 3 or 4

6

So if you were using an SQLite database, and set the column datatype to Integer, what would happen if you entered 356.50

Would the .50 be omitted, as the column is set to Integer; and if so, how would you then get around this limitation, without changing the column datatype to String??

Thanks in advance.

7

Sqlite would let you put any value in any column
Its kind of the odd man out in terms of how it handles “types”
http://sqlite.org/faq.html#q2

8

Very strange. Still seems a little ambiguous.

So if the column type was Integer, and the value 356.50 was saved into that column, could a mathematical operation be performed on the value (including the floating point numbers), or would it simply ignore them (even though it allowed the value to be stored).

9

if you define it as integer and stuff a double in there sqlite will let you
and it will treat it as “number” - so it will add correctly

Juts tried this at the CMD line

SQLite version 3.7.13 2012-07-17 17:46:21 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table foo ( col1 integer ) ; sqlite> insert into foo ( col1 ) values ( 1 ) ; sqlite> insert into foo ( col1 ) values ( 2.5 ) ; sqlite> select sum(col1) from foo ; 3.5

10

[quote=146412:@Richard Summers]So if you were using an SQLite database, and set the column datatype to Integer, what would happen if you entered 356.50

Would the .50 be omitted, as the column is set to Integer; and if so, how would you then get around this limitation, without changing the column datatype to String??

Thanks in advance.[/quote]
Well, for one, if I declare an integer field type and I’m using the Integer accessors in Xojo 356.50 will automagically be converted to 356. If I really want that value then I would have to declare a double field and use the double accessors or declare it as Int64 and use the Currency accessors. When I say accessors the DatabaseRecord class and Recordset class have specific methods to set/get by data type.

Implicit conversions are bad because you are not in control. What if you really have a string that is 123.45 and should not be a number? Implicit conversion will do this without error and you will never know. Just one of the reasons why variants are evil things.

11

Much as I like sqlite - its a great little engine - this is the ONE thing that has annoyed me for years because most databases will bark at you if you try to insert strings into a integer field & vice versa sqlite lets you shove anything in any column.
So YOU have to do all the work to make sure you don’t abuse that ability.

It is legal to have NO TYPE on a column in sqlite

SQLite version 3.7.13 2012-07-17 17:46:21 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table foo ( col1 ) ; sqlite> insert into foo ( col1 ) values ( 1 ) ; sqlite> insert into foo ( col1 ) values ( 2.5 ) ; sqlite> select sum(col1) from foo ; 3.5

12