[GRASS-user] Error in sqlite3_step()
I am receiving the following error when patching two point vector maps: v.patch -e in=SBC1,SBC2 out=SBC_0607 Patching vector map ... 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)' The attribute tables look like this : v.info -c SBC1 Displaying column types/names for database connection of layer 1: INTEGER|cat CHARACTER|CLASS_NAME CHARACTER|LM INTEGER|WARD v.info -c SBC2 Displaying column types/names for database connection of layer 1: INTEGER|cat CHARACTER|CLASS_NAME CHARACTER|LM INTEGER|WARD I have checked disk space and file size of the sqlite db - no problems there. Any ideas why sqlite chokes on cat 64510? sqlite 3.4.2 grass 6.5 ubuntu 7.10 Thanks, Craig ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
On 02/04/09 22:22, 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 ... 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)' The attribute tables look like this : v.info -c SBC1 Displaying column types/names for database connection of layer 1: INTEGER|cat CHARACTER|CLASS_NAME CHARACTER|LM INTEGER|WARD v.info -c SBC2 Displaying column types/names for database connection of layer 1: INTEGER|cat CHARACTER|CLASS_NAME CHARACTER|LM INTEGER|WARD I have checked disk space and file size of the sqlite db - no problems there. Any ideas why sqlite chokes on cat 64510? What does v.db.connect -p show for each of the two maps ? Moritz ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
Moritz wrote: > What does v.db.connect -p show for each of the two maps ? v.db.connect -p map=SBC1 Vector map is connected by: layer <1> table in database through driver with key v.db.connect -p map=SBC2 Vector map is connected by: layer <1> table in database through driver with key All looks fine to me. BTW v.patch works if I drop the -e option, but I need the attributes. Craig ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
On 03/04/09 09:10, Craig Leat wrote: Moritz wrote: What does v.db.connect -p show for each of the two maps ? v.db.connect -p map=SBC1 Vector map is connected by: layer <1> table in database through driver with key v.db.connect -p map=SBC2 Vector map is connected by: layer <1> table in database through driver with key And what about v.info -c / v.db.connect -p SBC_0607 ? db.connect -p ? Also what do you see when you run sqlite3 /home/craig/GIS/grassdata/uMDM_ll/craig/sqlite.db ? And then .tables and/or .schema TableName at the command prompt ? Maybe you can also set g.gisenv set=DEBUG=3 and see if you get any useful debug info. Moritz ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
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 ... > 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). 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? Thanks Craig ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
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 ... 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 ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
On Fri, Apr 3, 2009 at 10:59 AM, Moritz Lennert wrote: > On 03/04/09 10:03, Craig Leat wrote: ... >> 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, Right:http://www.sqlite.org/omitted.html See the script v.db.renamecol -> # some tricks how to potentially work around (create new column, transfer content). Markus ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
I have a problem with v.db.dropcol, it's changing the length of columns. db.describe -c table=SBC1 driver=sqlite database=/home/craig/GIS/grassdata/uMDM_ll/craig/sqlite.db ncols: 6 nrows: 197842 Column 1: cat:INTEGER:20 Column 2: CLASS_NAME:CHARACTER:1 Column 3: LM:CHARACTER:1 Column 4: WARD:INTEGER:20 Column 5: building:CHARACTER:20 Column 6: LM_name:CHARACTER:20 v.db.dropcol map=SBC1 col=CLASS_NAME db.describe -c table=SBC1 driver=sqlite database=/home/craig/GIS/grassdata/uMDM_ll/craig/sqlite.db ncols: 5 nrows: 197842 Column 1: cat:INTEGER:20 Column 2: LM:CHARACTER:1 Column 3: WARD:INTEGER:20 Column 4: building:CHARACTER:1 Column 5: LM_name:CHARACTER:1 building and LM_name have changed from length=20 to length=1 I'll test with Spearfish and update GRASS before reporting a bug, but this may take some time. First I have to finish this job with a broken v.db.dropcol. It's time to play with sqlitebrowser... Craig ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
On 03/04/09 11:34, Craig Leat wrote:
I have a problem with v.db.dropcol, it's changing the length of columns.
db.describe -c table=SBC1 driver=sqlite
database=/home/craig/GIS/grassdata/uMDM_ll/craig/sqlite.db
ncols: 6
nrows: 197842
Column 1: cat:INTEGER:20
Column 2: CLASS_NAME:CHARACTER:1
Column 3: LM:CHARACTER:1
Column 4: WARD:INTEGER:20
Column 5: building:CHARACTER:20
Column 6: LM_name:CHARACTER:20
v.db.dropcol map=SBC1 col=CLASS_NAME
db.describe -c table=SBC1 driver=sqlite
database=/home/craig/GIS/grassdata/uMDM_ll/craig/sqlite.db
ncols: 5
nrows: 197842
Column 1: cat:INTEGER:20
Column 2: LM:CHARACTER:1
Column 3: WARD:INTEGER:20
Column 4: building:CHARACTER:1
Column 5: LM_name:CHARACTER:1
building and LM_name have changed from length=20 to length=1
Don't have time to look into this now, but it must be a problem in the
special sqlite implementation in that script:
if [ "$driver" = "sqlite" ] ; then
#echo "Using special trick for SQLite"
# http://www.sqlite.org/faq.html#q13
v.info --q -c map=$GIS_OPT_MAP layer=$GIS_OPT_LAYER | cut -d'|'
-f1,2 | grep -v "|${col}$" > "$TMP.coldesc"
# need to revert order:
cat "$TMP.coldesc" | cut -d'|' -f1 > "$TMP.coltypes"
cat "$TMP.coldesc" | cut -d'|' -f2 > "$TMP.colnames"
COLDEF=`paste -d' ' "$TMP.colnames" "$TMP.coltypes" | tr '\n'
',' | sed 's+,$++g'`
COLNAMES=`cat "$TMP.colnames" | tr '\n' ',' | sed 's+,$++g'`
echo "BEGIN TRANSACTION;
CREATE TEMPORARY TABLE ${table}_backup(${COLDEF});
INSERT INTO ${table}_backup SELECT ${COLNAMES} FROM ${table};
DROP TABLE ${table};
CREATE TABLE ${table}(${COLDEF});
INSERT INTO ${table} SELECT ${COLNAMES} FROM ${table}_backup;
DROP TABLE ${table}_backup;
COMMIT;" > "$TMP"
db.execute input="$TMP"
You could try the individual steps and see where the problem comes from.
I'll test with Spearfish and update GRASS before reporting a bug, but
this may take some time. First I have to finish this job with a broken
v.db.dropcol. It's time to play with sqlitebrowser...
You can also try this:
https://addons.mozilla.org/en-US/firefox/addon/5817
Moritz
___
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
Moritz Lennert wrote:
> Don't have time to look into this now, but it must be a problem in the
> special sqlite implementation in that script:
>
> if [ "$driver" = "sqlite" ] ; then
>#echo "Using special trick for SQLite"
># http://www.sqlite.org/faq.html#q13
>v.info --q -c map=$GIS_OPT_MAP layer=$GIS_OPT_LAYER | cut -d'|' -f1,2
> | grep -v "|${col}$" > "$TMP.coldesc"
># need to revert order:
>cat "$TMP.coldesc" | cut -d'|' -f1 > "$TMP.coltypes"
>cat "$TMP.coldesc" | cut -d'|' -f2 > "$TMP.colnames"
>COLDEF=`paste -d' ' "$TMP.colnames" "$TMP.coltypes" | tr '\n' ',' |
> sed 's+,$++g'`
>COLNAMES=`cat "$TMP.colnames" | tr '\n' ',' | sed 's+,$++g'`
> echo "BEGIN TRANSACTION;
> CREATE TEMPORARY TABLE ${table}_backup(${COLDEF});
> INSERT INTO ${table}_backup SELECT ${COLNAMES} FROM ${table};
> DROP TABLE ${table};
> CREATE TABLE ${table}(${COLDEF});
> INSERT INTO ${table} SELECT ${COLNAMES} FROM ${table}_backup;
> DROP TABLE ${table}_backup;
> COMMIT;" > "$TMP"
> db.execute input="$TMP"
My execute statement looks like this:
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE SBC2_backup(cat INTEGER,LM CHARACTER,WARD
INTEGER,CLASS_NAME CHARACTER);
INSERT INTO SBC2_backup SELECT cat,LM,WARD,CLASS_NAME FROM SBC2;
DROP TABLE SBC2;
CREATE TABLE SBC2(cat INTEGER,LM CHARACTER,WARD INTEGER,CLASS_NAME CHARACTER);
INSERT INTO SBC2 SELECT cat,LM,WARD,CLASS_NAME FROM SBC2_backup;
DROP TABLE SBC2_backup;
COMMIT;
Why does v.db.dropcol define a type CHARACTER when sqlite3 supports
(NULL, INTEGER, REAL, TEXT, BLOB)?
Craig
___
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
On 03/04/09 17:06, Craig Leat wrote:
Moritz Lennert wrote:
Don't have time to look into this now, but it must be a problem in the
special sqlite implementation in that script:
if [ "$driver" = "sqlite" ] ; then
#echo "Using special trick for SQLite"
# http://www.sqlite.org/faq.html#q13
v.info --q -c map=$GIS_OPT_MAP layer=$GIS_OPT_LAYER | cut -d'|' -f1,2
| grep -v "|${col}$" > "$TMP.coldesc"
# need to revert order:
cat "$TMP.coldesc" | cut -d'|' -f1 > "$TMP.coltypes"
cat "$TMP.coldesc" | cut -d'|' -f2 > "$TMP.colnames"
COLDEF=`paste -d' ' "$TMP.colnames" "$TMP.coltypes" | tr '\n' ',' |
sed 's+,$++g'`
COLNAMES=`cat "$TMP.colnames" | tr '\n' ',' | sed 's+,$++g'`
echo "BEGIN TRANSACTION;
CREATE TEMPORARY TABLE ${table}_backup(${COLDEF});
INSERT INTO ${table}_backup SELECT ${COLNAMES} FROM ${table};
DROP TABLE ${table};
CREATE TABLE ${table}(${COLDEF});
INSERT INTO ${table} SELECT ${COLNAMES} FROM ${table}_backup;
DROP TABLE ${table}_backup;
COMMIT;" > "$TMP"
db.execute input="$TMP"
My execute statement looks like this:
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE SBC2_backup(cat INTEGER,LM CHARACTER,WARD
INTEGER,CLASS_NAME CHARACTER);
INSERT INTO SBC2_backup SELECT cat,LM,WARD,CLASS_NAME FROM SBC2;
DROP TABLE SBC2;
CREATE TABLE SBC2(cat INTEGER,LM CHARACTER,WARD INTEGER,CLASS_NAME CHARACTER);
INSERT INTO SBC2 SELECT cat,LM,WARD,CLASS_NAME FROM SBC2_backup;
DROP TABLE SBC2_backup;
COMMIT;
Why does v.db.dropcol define a type CHARACTER when sqlite3 supports
(NULL, INTEGER, REAL, TEXT, BLOB)?
v.db.dropcol just uses the output of v.info -c, and AFAIR, SBC2 has its
columns defined as CHARACTER, or ?
Moritz
___
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
On 06/04/09 11:41, Moritz Lennert wrote:
On 03/04/09 17:06, Craig Leat wrote:
Moritz Lennert wrote:
Don't have time to look into this now, but it must be a problem in the
special sqlite implementation in that script:
if [ "$driver" = "sqlite" ] ; then
#echo "Using special trick for SQLite"
# http://www.sqlite.org/faq.html#q13
v.info --q -c map=$GIS_OPT_MAP layer=$GIS_OPT_LAYER | cut
-d'|' -f1,2
| grep -v "|${col}$" > "$TMP.coldesc"
# need to revert order:
cat "$TMP.coldesc" | cut -d'|' -f1 > "$TMP.coltypes"
cat "$TMP.coldesc" | cut -d'|' -f2 > "$TMP.colnames"
COLDEF=`paste -d' ' "$TMP.colnames" "$TMP.coltypes" | tr '\n'
',' |
sed 's+,$++g'`
COLNAMES=`cat "$TMP.colnames" | tr '\n' ',' | sed 's+,$++g'`
echo "BEGIN TRANSACTION;
CREATE TEMPORARY TABLE ${table}_backup(${COLDEF});
INSERT INTO ${table}_backup SELECT ${COLNAMES} FROM ${table};
DROP TABLE ${table};
CREATE TABLE ${table}(${COLDEF});
INSERT INTO ${table} SELECT ${COLNAMES} FROM ${table}_backup;
DROP TABLE ${table}_backup;
COMMIT;" > "$TMP"
db.execute input="$TMP"
My execute statement looks like this:
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE SBC2_backup(cat INTEGER,LM CHARACTER,WARD
INTEGER,CLASS_NAME CHARACTER);
INSERT INTO SBC2_backup SELECT cat,LM,WARD,CLASS_NAME FROM SBC2;
DROP TABLE SBC2;
CREATE TABLE SBC2(cat INTEGER,LM CHARACTER,WARD INTEGER,CLASS_NAME
CHARACTER);
INSERT INTO SBC2 SELECT cat,LM,WARD,CLASS_NAME FROM SBC2_backup;
DROP TABLE SBC2_backup;
COMMIT;
Why does v.db.dropcol define a type CHARACTER when sqlite3 supports
(NULL, INTEGER, REAL, TEXT, BLOB)?
v.db.dropcol just uses the output of v.info -c, and AFAIR, SBC2 has its
columns defined as CHARACTER, or ?
See also this thread:
http://lists.osgeo.org/pipermail/grass-user/2009-January/048281.html
Moritz
___
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
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.
Cheers,
Dylan
On Mon, Apr 6, 2009 at 2:58 AM, Moritz Lennert
wrote:
> On 06/04/09 11:41, Moritz Lennert wrote:
>>
>> On 03/04/09 17:06, Craig Leat wrote:
>>>
>>> Moritz Lennert wrote:
Don't have time to look into this now, but it must be a problem in the
special sqlite implementation in that script:
if [ "$driver" = "sqlite" ] ; then
#echo "Using special trick for SQLite"
# http://www.sqlite.org/faq.html#q13
v.info --q -c map=$GIS_OPT_MAP layer=$GIS_OPT_LAYER | cut -d'|'
-f1,2
| grep -v "|${col}$" > "$TMP.coldesc"
# need to revert order:
cat "$TMP.coldesc" | cut -d'|' -f1 > "$TMP.coltypes"
cat "$TMP.coldesc" | cut -d'|' -f2 > "$TMP.colnames"
COLDEF=`paste -d' ' "$TMP.colnames" "$TMP.coltypes" | tr '\n' ','
|
sed 's+,$++g'`
COLNAMES=`cat "$TMP.colnames" | tr '\n' ',' | sed 's+,$++g'`
echo "BEGIN TRANSACTION;
CREATE TEMPORARY TABLE ${table}_backup(${COLDEF});
INSERT INTO ${table}_backup SELECT ${COLNAMES} FROM ${table};
DROP TABLE ${table};
CREATE TABLE ${table}(${COLDEF});
INSERT INTO ${table} SELECT ${COLNAMES} FROM ${table}_backup;
DROP TABLE ${table}_backup;
COMMIT;" > "$TMP"
db.execute input="$TMP"
>>>
>>> My execute statement looks like this:
>>> BEGIN TRANSACTION;
>>> CREATE TEMPORARY TABLE SBC2_backup(cat INTEGER,LM CHARACTER,WARD
>>> INTEGER,CLASS_NAME CHARACTER);
>>> INSERT INTO SBC2_backup SELECT cat,LM,WARD,CLASS_NAME FROM SBC2;
>>> DROP TABLE SBC2;
>>> CREATE TABLE SBC2(cat INTEGER,LM CHARACTER,WARD INTEGER,CLASS_NAME
>>> CHARACTER);
>>> INSERT INTO SBC2 SELECT cat,LM,WARD,CLASS_NAME FROM SBC2_backup;
>>> DROP TABLE SBC2_backup;
>>> COMMIT;
>>>
>>> Why does v.db.dropcol define a type CHARACTER when sqlite3 supports
>>> (NULL, INTEGER, REAL, TEXT, BLOB)?
>>
>> v.db.dropcol just uses the output of v.info -c, and AFAIR, SBC2 has its
>> columns defined as CHARACTER, or ?
>
> See also this thread:
>
> http://lists.osgeo.org/pipermail/grass-user/2009-January/048281.html
>
> Moritz
> ___
> grass-user mailing list
> grass-user@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/grass-user
>
___
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
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 ... 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 ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
[...] Count another error here: "Error in sqlite3_step(): database is locked" All I am trying to do is: v.what.rast vector=points raster=FINAL column=test Nikos ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
On Mon, 2009-05-04 at 15:07 +0200, Nikos Alexandris wrote: > [...] > > Count another error here: > "Error in sqlite3_step(): > database is locked" > > All I am trying to do is: > v.what.rast vector=points raster=FINAL column=test WoW :-O All of a sudden, I can't copy vector maps, drop or add columns anymore. I checked dbase connections and all looks fine. The problem started after trying to use v.what.rast and then sqlitebrowser to remove some column. Could it be a sqlitebrowser side-effect? Thanks, Nikos ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
On Mon, May 4, 2009 at 3:20 PM, Nikos Alexandris wrote: > On Mon, 2009-05-04 at 15:07 +0200, Nikos Alexandris wrote: >> [...] >> >> Count another error here: >> "Error in sqlite3_step(): >> database is locked" >> >> All I am trying to do is: >> v.what.rast vector=points raster=FINAL column=test > > > WoW :-O > > All of a sudden, I can't copy vector maps, drop or add columns anymore. > I checked dbase connections and all looks fine. The problem started > after trying to use v.what.rast and then sqlitebrowser to remove some > column. > > Could it be a sqlitebrowser side-effect? Perhaps... I found http://www.sqlite.org/lockingv3.html With "lsof" you can find out who's using a file or a process. Markus ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
On 04/05/09 15:20, Nikos Alexandris wrote: On Mon, 2009-05-04 at 15:07 +0200, Nikos Alexandris wrote: [...] Count another error here: "Error in sqlite3_step(): database is locked" All I am trying to do is: v.what.rast vector=points raster=FINAL column=test WoW :-O All of a sudden, I can't copy vector maps, drop or add columns anymore. I checked dbase connections and all looks fine. The problem started after trying to use v.what.rast and then sqlitebrowser to remove some column. Could it be a sqlitebrowser side-effect? IIRC, if you use sqlitebrowser, you need to save your changes, before being able to access the db from GRASS. BTW, has anyone compared sqlitebrowser to the sqlite manager add-on to firefox [1,2] ? After very rough first trials I have the feeling that the latter actually has a nicer feel to it, but need to do more testing... Moritz [1] https://addons.mozilla.org/fr/firefox/addon/5817 [2] http://code.google.com/p/sqlite-manager/ ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Error in sqlite3_step()
[...] Moritz: > IIRC, if you use sqlitebrowser, you need to save your changes, before > being able to access the db from GRASS. The fact is that I couldn't do any changes to save them! > > BTW, has anyone compared sqlitebrowser to the sqlite manager add-on to > firefox [1,2] ? After very rough first trials I have the feeling that > the latter actually has a nicer feel to it, but need to do more testing... Not a real comparison but I tend to agree that it feels "smoother". Nikos > [1] https://addons.mozilla.org/fr/firefox/addon/5817 > [2] http://code.google.com/p/sqlite-manager/ ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
