v.mkgrid near ",": syntax error, no batch insert

[pertusus]

A v.mkgrid leads to:

v.mkgrid map=grid_map_v grid=$rows,$cols
DBMI-SQLite driver error:
Error in sqlite3_prepare():
near ",": syntax error

DBMI-SQLite driver error:
Error in sqlite3_prepare():
near ",": syntax error

ERREUR :Unable to insert new record: insert into grid_map_v values ( 1, 1,
        1 ), ( 2, 1, 2 ), ( 3, 1, 3 ), ( 4, 1, 4 ), ( 5, 1, 5 ), ( 6, 1, 6
        ), ( 7, 1, 7 ), ( 8, 1, 8 ), ( 9, 1, 9 ), ( 10, 1, 10 ), ( 11, 1,
        11 ), ( 12, 1, 12 ), ( 13, 1, 13 ), ( 14, 1, 14 ), ( 15, 1, 15 ), (
        16, 1, 16 ), ( 17, 1, 17 ), ( 18, 1, 18 ), ( 19, 1, 19 ), ( 20, 1,
        20 )

Trying manually:

db.execute sql=" insert into grid_map_v values ( 3, 1, 3 ), ( 4, 1, 4 )"
DBMI-SQLite driver error:
Error in sqlite3_prepare():
near ",": syntax error

DBMI-SQLite driver error:
Error in sqlite3_prepare():
near ",": syntax error

And trying in sqlite directly:

sqlite> create table grid_map_v ( cat INTEGER, row INTEGER, col INTEGER );
sqlite> insert into grid_map_v values ( 1, 1, 1), ( 2, 1, 2 );
Error: near ",": syntax error
sqlite> insert into grid_map_v values ( 1, 1, 1);

The batch insertion is shown in ​http://www.sqlite.org/lang_insert.html, however I am not sure that it is standard sql. I don't know where to find the standard, but on ​http://troels.arvin.dk/db/rdbms/#insert-multiple it is described as optional.

The commit that introduced that issue is the last commit

r53891 | marisn | 2012-11-18 13:40:34 +0100 (dim. 18 nov. 2012) | 1 ligne

Make some errors more fatal; Write attribute data in batches per 20 to speed up module 16x times

I will set BATCH_SIZE 1 for now but I am not sure it is the correct fix.

[marisn]

Multirow inserts are valid since SQL-92. I would still stay on side, that this feature should be left as is as speed difference on large grids is enormous but non-SQL-92 RDBMSes are disappearing.

According to Wikipedia: "This feature is supported by DB2, SQL Server (since version 10.0 - i.e. 2008), PostgreSQL (since version 8.2), MySQL, sqlite (since version 3.7.11) and H2". Solution - upgrade sqlite.

Second option - add extra logic to switch to slingle row inserts if multirow fails. Still I have no time now to implement it.

[pertusus]

I use a centos 6, by no mean an old platform, but sqlite here is sqlite-3.6.20, thus I think that relying, in the default case on that feature is not a good idea. Updating system sqlite is a very bad idea, using a non system sqlite for grass is very impractical (and prevents updating packages).

Also, I read the standard ​http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt, and it seems to me that the "63)Subclause 13.8" says that for "Entry SQL" there should only be one value, which explains why this feature is that badly implemented in SQL implementations. According to the sqlite news, sqlite 3.7.11 dates from 2012-March-20!

[mmetz]

Replying to pertusus:

I use a centos 6, by no mean an old platform, but sqlite here is sqlite-3.6.20, thus I think that relying, in the default case on that feature is not a good idea. Updating system sqlite is a very bad idea, using a non system sqlite for grass is very impractical (and prevents updating packages).

Also, I read the standard ​http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt, and it seems to me that the "63)Subclause 13.8" says that for "Entry SQL" there should only be one value, which explains why this feature is that badly implemented in SQL implementations. According to the sqlite news, sqlite 3.7.11 dates from 2012-March-20!

I have fixed r53891 in r55271. No more batch insert, but the module is now about 50x faster than the batch insert, using old-fashioned begin/commit transaction.

Markus M

[pertusus]

Thanks, it works now, and it is indeed quite fast.