[GRASS-user] Error in sqlite3_step()

[Craig Leat]

Dylan:
> Yeah. I have recently noticed this type of behavior after switching to
> sqlite. I was unable to patch two vectors (and att tables) after
> defining new columns of type varchar(20). I re-made the columns as
> 'text' and v.patch worked as expected. Maybe we need some special,
> internal conversions (varchar ---> text) when working with sqlite db.

v.info -c SBC2
Displaying column types/names for database connection of layer 1:
INTEGER|cat
INTEGER|WARD
TEXT|LM
TEXT|CLASS_NAME

db.describe -c table=SBC2 driver=sqlite
database=/home/craig/GIS/grassdata/uMDM_ll/craig/sqlite.db
ncols: 4
nrows: 7961
Column 1: cat:INTEGER:20
Column 2: WARD:INTEGER:20
Column 3: LM:TEXT:1000
Column 4: CLASS_NAME:TEXT:1000

v.info -c SBC1
Displaying column types/names for database connection of layer 1:
INTEGER|cat
INTEGER|WARD
TEXT|LM
TEXT|CLASS_NAME

db.describe -c table=SBC1 driver=sqlite
database=/home/craig/GIS/grassdata/uMDM_ll/craig/sqlite.db
ncols: 4
nrows: 197842
Column 1: cat:INTEGER:20
Column 2: WARD:INTEGER:20
Column 3: LM:TEXT:1000
Column 4: CLASS_NAME:TEXT:1000

Ps. I didn't specify the lengths of columns 3 and 4. 1000 must be the
default length for text.

v.patch -e in=SBC1,SBC2 out=SBC_test
Patching vector map <SBC1 at craig>...
DBMI-SQLite driver error:
Error in sqlite3_step():
SQL logic error or missing database

ERROR: Cannot insert new record: 'insert into SBC_test values ( 64510, 37,
       'The Msunduzi', 'Dwelling')'

So GRASS (6.5 r35892) rejects both CHARACTER:1 (see earlier in the
thread) and TEXT:1000.

Let's try with debugging on:

g.gisenv set=DEBUG=3
v.patch -e in=SBC1,SBC2 out=SBC_test2

...
D3/3: fetch row = 64506
D3/3: col 0, litetype 1, sqltype 3: val = '64508'
D3/3: col 1, litetype 1, sqltype 3: val = '3'
D3/3: col 2, litetype 3, sqltype 13: val = 'Mkhambathini'
D3/3: col 3, litetype 3, sqltype 13: val = 'Dwelling'
D3/3: Row fetched
D2/3: SQL: insert into SBC_test2 values ( 64509, 3, 'Mkhambathini', 'Dwelling')
D3/3: execute: insert into SBC_test2 values ( 64509, 3,
'Mkhambathini', 'Dwelling')
D3/3: fetch row = 64507
D3/3: col 0, litetype 1, sqltype 3: val = '64509'
D3/3: col 1, litetype 1, sqltype 3: val = '37'
D3/3: col 2, litetype 3, sqltype 13: val = 'The Msunduzi'
D3/3: col 3, litetype 3, sqltype 13: val = 'Dwelling'
D3/3: Row fetched
D2/3: SQL: insert into SBC_test2 values ( 64510, 37, 'The Msunduzi', 'Dwelling')
D3/3: execute: insert into SBC_test2 values ( 64510, 37, 'The
Msunduzi', 'Dwelling')
DBMI-SQLite driver error:
Error in sqlite3_step():
SQL logic error or missing database

D2/3: G__home home = /home/craig
ERROR: Cannot insert new record: 'insert into SBC_test2 values ( 64510, 37,
       'The Msunduzi', 'Dwelling')'

Might the space in 'The Msunduzi' cause the problem?

db.describe -c table=test driver=sqlite
database=/home/craig/GIS/grassdata/uMDM_ll/craig/sqlite.db
ncols: 4
nrows: 7963
Column 1: cat:INTEGER:20
Column 2: WARD:INTEGER:20
Column 3: LM:TEXT:1000
Column 4: CLASS_NAME:TEXT:1000

echo "INSERT INTO test (cat,WARD,LM,CLASS_NAME) values (8000,1,'test
phrase','test')" | db.execute
v.db.select test | tail
7953|26|The Msunduzi|Dwelling
7954|19|The Msunduzi|Dwelling
7955|1|Mpofana|Dwelling
7956|1|Mpofana|Dwelling
7957|1|Mpofana|Dwelling
7958|1|Mpofana|Dwelling
7959|1|Mpofana|Dwelling
7960|1|Mpofana|Dwelling
7961|1|Mpofana|Dwelling
8000|1|test phrase|test

So the above db.execute command worked. This also works:
echo "insert into test values ( 8002, 37, 'The Msunduzi', 'Dwelling')"
| db.execute

BTW the same error message is given if I try to insert into a cat
that's already used. I checked SBC1 around cat 64510 and didn't see
any duplicate cats.

Any more ideas from anyone?

Craig