[GRASS-user] SQL Error using "v.color"

[Hamish]

Roy wrote:
>  I am using the December 19 (2010) build of
> GRASS 6.4 for Windows. I have loaded a vector map (for a country with
> approx 20 province boundaries)

(if you haven't discovered the Color Brewer website yet, it's a really
nice resource:  http://colorbrewer2.org  I've been meaning to build that
into an addon script but haven't figured out the program flow yet)

> with an attribute table that contains a continuous variable at the
> province level (name of column is `y2009'). Trying to put the `y2009'
> variable on the map, i begin with defining the color grading to be used
...
>  Doing so I get the following error:
...
> DBMI-SQLite driver error:
> Error in sqlite3_prepare():
> near "SET": syntax error
>  ERROR: Error while executing: 'UPDATE SET hc2 = '0:0:255' WHERE y2009 =
>       0.094713'
> ERROR: Processing SQL transaction

it's missing the table name (often the same as the map name). the
correct statement would be like:
 UPDATE pse SET hc2 = ...

>  Any idea what is creating the error -- or
> what i am doing wrong? Is there a way to get around this/fix it?

Micha:
> I have a suggestion that might fix this problem. The v.colors script on
> line 209 uses the expression:
>   grep "^$GIS_OPT_LAYER;"
> to isolate the TABLE name, and I think that the windows version of grep
> is confused by the semicolon ';'.

is that a guess or have you seen that before? I'd never heard of it.
Actually I wonder if that's needed at all, as the layer number is
explicitly given to the v.db.connect command.

ah, it is. even with layer=1, v.db.connect still shows results for all
layers.  bug?!


Roy, what output does the command:
  v.db.connect -g map=pse layer=1 fs=";"
give?

It should looks something like:
  1;pse;cat;C:\some path\....\mapset\sqlite.db;sqlite


one option is to change that fs=";" to fs="|" (and the grep to match),
but the main problem is that v.db.connect is outputting for all layers
when we told it to just report for one of them. solve that and the grep
isn't needed. why the grep might not like ';' on Windows is a mystery to
me.


Micha:
> Please locate the script (should be under %GISBASE\scripts)  and open
> it in an editor. Then replace line 209 as follows:
> (original)
> TABLE=`v.db.connect -g map="$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" fs=";"
| grep "^$GIS_OPT_LAYER;" | cut -f2 -d';'`
>
> (change to:)
> TABLE=`v.db.connect -g map="$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" fs=";"
| grep "^$GIS_OPT_LAYER" | cut -f2 -d';'`
>
> (just remove the ; from "^$GIS_OPT_LAYER" after grep)
> Let us know if this works.

beware that if v.db.connect was reporting a layer more than 10, then ^1
without the field separator would catch all layers 1, 10, 11, 150, etc,
ie anything starting with a 1.   (for those reading along at home the "^"
in there means "at the start-of-line")


Hamish