Opened 7 years ago

Closed 7 years ago

#1901 closed defect (fixed)

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

Reported by: pertusus Owned by: grass-dev@…
Priority: normal Milestone: 7.0.0
Component: Vector Version: svn-trunk
Keywords: v.mkgrid Cc:
CPU: Unspecified Platform: Linux

Description

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.

Change History (4)

comment:1 Changed 7 years ago by marisn

Keywords: v.mkgrid added

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.

comment:2 Changed 7 years ago by 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!

comment:3 in reply to:  2 Changed 7 years ago by 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

comment:4 Changed 7 years ago by pertusus

Resolution: fixed
Status: newclosed

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

Note: See TracTickets for help on using tickets.