Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

I get wrong values from the database when using node-sqlite #17

Open
sam1919 opened this Issue Jan 29, 2011 · 2 comments

Comments

Projects
None yet
2 participants

sam1919 commented Jan 29, 2011

I have a table column where I store large numbers (seconds since 1970).

The bigints are stored correctly (I checked it on the sqlite console) but with the sqlite driver I get negative(!) values. What's wrong here?

sqlite3 console:
sqlite> SELECT date FROM seen WHERE user='root' COLLATE NOCASE;
1296333940003

node-sqlite:
{ date: -746187876 }

I also tried "BIGINT UNSIGNED" with the same (wrong) result.

@ghost

ghost commented Jan 30, 2011

Hi Sam,

It seems your 'seconds' values are actually in milliseconds.
If you SELECT date/1000 you should get correct results until 2038 or so ;)

I have committed a "fix" for this issue in changeset 20ed195db941163848313f74755d77ec0c0f6084. The cause was the node-sqlite code calling sqlite3_column_int rather than sqlite3_column_int64.

Sadly there is no support for 64bit integers in Node JS (that I know of) so the numbers are represented as doubles ... so you only get 52 bits of precision.

We could consider using a BigNum library for these values...

Owner

orlandov commented Jan 31, 2011

Sam, what you're running into is an unfortunate cross-section of limitations present both in JavaSript and SQLite. SQLite's type affinity is forcing the driver to interpret the value in your select as an INTEGER column type. As Steven mentioned, JavaScript doesn't support very large integers. Because SQLite stores everything under the hood as strings, you could simply request your data as TEXT using CAST:

sqlite = require('./sqlite');
common = require('./tests/lib/common.js');

db = new sqlite.Database();

db.open(":memory:", function (error) {
  if (error) {
    console.log("Tonight. You.");
    throw error;
  }

  common.createTable
    ( db
    , 'a'
    , [ { name: 'b', type: 'INTEGER' } ]
    , function (error) {
        insertRow();
      }
    );

  function insertRow() {
    db.execute
      ( "INSERT INTO a VALUES (1296333940003)"
      , function (error, rows) {
          if (error) throw error;
          readRow2();
        }
      );
  }

  function readRow1() {
    db.execute
      ( "SELECT b FROM A"
      , function (error, rows) {
          if (error) throw error;
          console.dir(rows);
        }
      );
  }

  function readRow2() {
    db.execute
      ( "SELECT CAST(b AS TEXT) FROM A"
      , function (error, rows) {
          if (error) throw error;
          console.dir(rows);
        }
      );
  }
});


[ { b: '1296333940003' } ]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment