Sqlite driver doesn't handle blank numeric entries

[ferrouswheel]

Platform: GNU/Linux/x86_64
grass obtained from: SVN
grass binary for platform: Compiled from Sources
GRASS Version: grass63_release

The Sqlite driver doesn't seem to handle sqlite NUMERIC types being blank.

When the the field is blank, the following is output (here it is col 8 that is NUMERIC):

D3/3: col 0, litetype 3, sqltype 13: val = 'Cortaderia selloana'
D3/3: col 1, litetype 3, sqltype 13: val = 'Pukepuke Lagoon C.A.'
D3/3: col 2, litetype 3, sqltype 13: val = 'Nicholls, V.J. and Singers, N.J.D.'
D3/3: col 3, litetype 3, sqltype 3: val = '1997    '
D3/3: col 4, litetype 3, sqltype 13: val = 'Pukepuke Lagoon C.A.?'
D3/3: col 5, litetype 1, sqltype 3: val = '2701900'
D3/3: col 6, litetype 1, sqltype 3: val = '6093700'
D3/3: col 7, litetype 3, sqltype 3: val = ''
D3/3: col 8, litetype 3, sqltype 6: val = ''
D3/3: sqlite fetched date: 
DBMI-SQLite driver error:
Cannot scan date:

D2/3: G__home home = /home/joel
WARNING: Cannot fetch next record
D3/3: db_close_database()

--

I don't understand why it's trying to scan a date? It works fine if there is a value such as 0.0021 in the column. I could replace all the blanks with 0.0, but I'd prefer to be able to keep them blank so that values don't get confused with missing data.

I'm using sqlite3 and the up to date grass63_release branch of svn.

[ferrouswheel]

The output above is from d.what.vect

[ferrouswheel]

in db/drivers/sqlite/fetch.c:

146             case SQLITE_TEXT:
147                 if (sqltype == 6 ) { /* date string */
148                    /* Example: '1999-01-25' */

shouldn't it be

146             case SQLITE_TEXT:
147                 if (sqltype == DB_SQL_TYPE_DATE ) { /* date string */
148                    /* Example: '1999-01-25' */

?

6 is equivalent to DB_SQL_TYPE_DOUBLE_PRECISION, and often it unsurprisingly fails when trying to read a date from it.

This fixes the problem I was having...

[neteler]

There were some reasons:

• Related discussion: ​http://lists.osgeo.org/pipermail/grass-dev/2007-March/thread.html#29897
• In particular: ​http://lists.osgeo.org/pipermail/grass-dev/2007-March/029915.html
• Related changeset: r23034

By looking at the code, the number (6) seems to be shifted by one (the DB_SQL_TYPE_* tyes are declared in grass/trunk/lib/db/dbmi_base/sqltype.c​ - ​http://download.osgeo.org/grass/grass6_progman/sqltype_8c-source.html).

To fix this completely would be appreciated. I wonder if the suggested fix breaks anything else.

Marku

[neteler]

I have made tests with the suggested patch and fixed it accordingly in trunk r31724 and 6.4.branch r31725.

Thanks for the report, Markus

[neteler]

Also fixed in 6.3.relbranch for 6.3.1.

Markus