Hmmm...the docs do say that...but how do you get that value back out?
Retreiving it as text doesn't work.
You still don't say what you're planning on doing with these number...just
displaying them?
I think the docs may be misleading...here is the comment in sqlite3.c
/*
** Try to convert a value into a numeric representation if we can
** do so without loss of information. In other words, if the string
** looks like a number, convert it into a number. If it does not
** look like a number, leave it alone.
*/
The "loss of information" simply means it still looks like a number...not that
we lost any significant digits. I think the docs should be clearer about that.
So storing them as text appears to be your only option.
The following just prints out the same truncated real number even though it's
retrieved as text (and all by design).
You'll also find that a dump shows the same thing.
1.23456789123457e+17
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/wait.h>
#include <unistd.h>
#include "sqlite3.h"
int
main (int argc, char *argv[])
{
sqlite3 *db = NULL;
int ret = -1;
char *sql0 = "drop table tab1";
char *sql1 = "create table tab1(a integer,c real)";
char *sql2 = "insert into tab1 values(1,'123456789123456789.123456')";
char *sql3 = "select * from tab1";
char *errmsg;
sqlite3_stmt *p_stmt;
ret = sqlite3_open ("prec.db", &db);
if (ret != SQLITE_OK) {
fprintf (stderr, "open error\n");
exit (-1);
}
sqlite3_exec (db, sql0, NULL, NULL, &errmsg);
if (ret != SQLITE_OK) {
fprintf (stderr, "exec error: %s\n", errmsg);
exit (-1);
}
sqlite3_exec (db, sql1, NULL, NULL, &errmsg);
if (ret != SQLITE_OK) {
fprintf (stderr, "exec error: %s\n", errmsg);
exit (-1);
}
sqlite3_exec (db, sql2, NULL, NULL, &errmsg);
if (ret != SQLITE_OK) {
fprintf (stderr, "exec error: %s\n", errmsg);
exit (-1);
}
ret = sqlite3_prepare_v2 (db, sql3, -1, &p_stmt, NULL);
if (ret != SQLITE_OK) {
fprintf (stderr, "prepare error: %s\n", sqlite3_errmsg (db));
}
ret = sqlite3_step (p_stmt);
if (ret == SQLITE_ROW) {
const unsigned char *myval = sqlite3_column_text (p_stmt, 1);
printf ("%s\n", myval);
}
sqlite3_finalize (p_stmt);
sqlite3_close (db);
return 0;
}
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
________________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of LacaK [la...@users.sourceforge.net]
Sent: Tuesday, March 22, 2011 6:25 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns
Hi Michael,
thank you for response.
So only solution is use TEXT columns (with TEXT affinity) ? There is no
way how to use DECIMAL columns (with NUMERIC affinity) ?
My goal is store numeric values with big precision (as declared per
column DECIMAL(30,7)).
I do not want any conversion to floating-point values ... because such
conversion loses digits and is not reversible to original value.
What I will expect is: If supplied value can not be "reversibly"
converted to floating-point representation (REAL storage class), then
store it as text with TEXT storage class ... but this does not happen
(SQLite converts to floating-point and stores it and looses digits).
But on this page http://www.sqlite.org/datatype3.html is written:
"When text data is inserted into a NUMERIC column, the storage class of
the text is converted to INTEGER or REAL (in order of preference) if
such conversion is lossless and reversible. For conversions between TEXT
and REAL storage classes, SQLite considers the conversion to be lossless
and reversible if the first 15 significant decimal digits of the number
are preserved. *If the lossless conversion of TEXT to INTEGER or REAL is
not possible then the value is stored using the TEXT storage class*."
Laco.
> Hi,
> I have table like this:
> CREATE TABLE tab1 (
> a INTEGER,
> c DECIMAL(30,7),
> ...
> );
>
> When I am trying insert values like:
> INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
> INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');
>
> values for c column are always rounded or cast to :
> 1.23456789012346e+19
>
> If I understand correctly column c has NUMERIC affinity, but when
> storing values, they are stored using REAL storage class.
> But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15
> significant digits are not preserved)
> Is there way how to store numeric values, which are out of REAL range ?
>
> TIA
> -Laco.
>
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
