[GRASS-user] Error in sqlite3_step()

[Moritz Lennert]

On 03/04/09 10:03, Craig Leat wrote:
> Moritz,
> 
> Thanks for the suggestions. I think I've got something:
> 
> Craig Leat wrote:
>> I am receiving the following error when patching two point vector maps:
>> v.patch -e in=SBC1,SBC2 out=SBC_0607
>> 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_0607 values ( 64510,
>>       'Dwelling', 'The Msunduzi', 37)'
> 
> Notice that the second and third columns contain strings.
> 
> 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: CLASS_NAME:CHARACTER:1
> Column 3: LM:CHARACTER:1
> Column 4: WARD:INTEGER:20
> 
> Second and third columns are type CHARACTER with length=1 ???
> 
> How did this happen, as SBC1 was generated by:
> g.copy v=SBC_0506,SBC1
> 
> and then modified with a few runs of v.db.dropcol map=SBC1 layer=1 col=
> 
> Columns CLASS_NAME and LM are correctly specified in the original
> vector (SBC_0506):
> db.describe -c table=SBC_0506 driver=sqlite
> database=/home/craig/GIS/grassdata/uMDM_ll/craig/sqlite.db
> ncols: 21
> nrows: 197842
> Column 1: cat:INTEGER:20
> Column 2: CLASS:DOUBLE PRECISION:20
> Column 3: CLASS_NAME:CHARACTER:20
> Column 4: SOURCE:CHARACTER:15
> Column 5: PROV:CHARACTER:25
> Column 6: MUNIC:CHARACTER:50
> Column 7: ESKOM_FSA:CHARACTER:25
> Column 8: TRIBAL_AUT:CHARACTER:50
> Column 9: PROCLAIMED:CHARACTER:5
> Column 10: INT_ERVEN:CHARACTER:5
> Column 11: INT_FARM:CHARACTER:5
> Column 12: INT_FARMPN:CHARACTER:5
> Column 13: INT_AGRHLD:CHARACTER:5
> Column 14: INT_PARK:CHARACTER:5
> Column 15: INT_TA:CHARACTER:5
> Column 16: SUB_HV_DES:CHARACTER:25
> Column 17: SUB_MV_DES:CHARACTER:25
> Column 18: TRNSFMR_NM:CHARACTER:25
> Column 19: SOURCE_IMG:CHARACTER:20
> Column 20: LM:CHARACTER:20
> Column 21: WARD:INTEGER:20
> 
> It seems to me that either g.copy or v.db.dropcol has messed up
> columns 3 and 20 (in the original vector). 

If you can reproduce this, especially with the GRASS demo data, then 
please file a bug report.

> Can I change the length of
> columns 2 and 3 in SBC1 and SBC2 or maybe I can copy the columns to
> new columns with the correct length?

AFAIK, sqlite does not support alter column statements, but I think you 
can change column types in sqlitebrowser (don't know how they implement 
this). But you can obviously do it via copying.

SQLite does not really enforce column types (unless you tell it 
explicitely to do so), so normally you can put whatever you want into 
whatever column. This does sometimes create problems with GRASS' 
internal SQL type checks.

Moritz