[GRASS-dev] [GRASS GIS] #180: Sqlite driver doesn't handle blank numeric entries

GRASS GRASS developer
1

#180: Sqlite driver doesn't handle blank numeric entries
--------------------------+-------------------------------------------------
Reporter: ferrouswheel | Owner: grass-dev@lists.osgeo.org
     Type: defect | Status: new
Priority: minor | Milestone: 6.3.1
Component: default | Version: 6.3.0
Keywords: sqlite db |
--------------------------+-------------------------------------------------
{{{
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.

--
Ticket URL: <http://trac.osgeo.org/grass/ticket/180&gt;
GRASS GIS <http://grass.osgeo.org>

2

#180: Sqlite driver doesn't handle blank numeric entries
---------------------------+------------------------------------------------
  Reporter: ferrouswheel | Owner: grass-dev@lists.osgeo.org
      Type: defect | Status: new
  Priority: minor | Milestone: 6.3.1
Component: default | Version: 6.3.0
Resolution: | Keywords: sqlite db
---------------------------+------------------------------------------------
Comment (by ferrouswheel):

The output above is from d.what.vect

--
Ticket URL: <http://trac.osgeo.org/grass/ticket/180#comment:1&gt;
GRASS GIS <http://grass.osgeo.org>

3

#180: Sqlite driver doesn't handle blank numeric entries
---------------------------+------------------------------------------------
  Reporter: ferrouswheel | Owner: grass-dev@lists.osgeo.org
      Type: defect | Status: new
  Priority: minor | Milestone: 6.3.1
Component: default | Version: 6.3.0
Resolution: | Keywords: sqlite db
---------------------------+------------------------------------------------
Comment (by 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...

--
Ticket URL: <http://trac.osgeo.org/grass/ticket/180#comment:2&gt;
GRASS GIS <http://grass.osgeo.org>

4

#180: Sqlite driver doesn't handle blank numeric entries
---------------------------+------------------------------------------------
  Reporter: ferrouswheel | Owner: grass-dev@lists.osgeo.org
      Type: defect | Status: new
  Priority: major | Milestone: 6.3.1
Component: default | Version: 6.3.0
Resolution: | Keywords: sqlite db
---------------------------+------------------------------------------------
Changes (by neteler):

  * priority: minor => major

Comment:

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
[source: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

--
Ticket URL: <http://trac.osgeo.org/grass/ticket/180#comment:3&gt;
GRASS GIS <http://grass.osgeo.org>

5

#180: Sqlite driver doesn't handle blank numeric entries
---------------------------+------------------------------------------------
  Reporter: ferrouswheel | Owner: grass-dev@lists.osgeo.org
      Type: defect | Status: closed
  Priority: major | Milestone: 6.4.0
Component: default | Version: 6.3.0
Resolution: fixed | Keywords: sqlite db
---------------------------+------------------------------------------------
Changes (by neteler):

  * status: new => closed
  * resolution: => fixed
  * milestone: 6.3.1 => 6.4.0

Comment:

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

--
Ticket URL: <http://trac.osgeo.org/grass/ticket/180#comment:4&gt;
GRASS GIS <http://grass.osgeo.org>

6

#180: Sqlite driver doesn't handle blank numeric entries
---------------------------+------------------------------------------------
  Reporter: ferrouswheel | Owner: grass-dev@lists.osgeo.org
      Type: defect | Status: closed
  Priority: major | Milestone: 6.4.0
Component: default | Version: 6.3.0
Resolution: fixed | Keywords: sqlite db
---------------------------+------------------------------------------------
Comment (by neteler):

Also fixed in 6.3.relbranch for 6.3.1.

Markus

--
Ticket URL: <http://trac.osgeo.org/grass/ticket/180#comment:5&gt;
GRASS GIS <http://grass.osgeo.org>