[GRASS-dev] SQLite 3 "SQL logic error"

[Benjamin Ducke]

Dear all,

I have made some progress on this and think that I have actually
found the reason for this problem.

The error message is issued by this block of C++ code
in gdal-1.6.1/ogr/ogrsf_frmts/sqlite/ogrsqlitetablelayer.cpp 
(ca. line 826):

/* -------------------------------------------------------------------- */
/*      Execute the insert.                                             */
/* -------------------------------------------------------------------- */
    rc = sqlite3_step( hInsertStmt );    

    if( rc != SQLITE_OK && rc != SQLITE_DONE )
    {
        CPLError( CE_Failure, CPLE_AppDefined, 
                  "sqlite3_step() failed:\n  %s", 
                  sqlite3_errmsg(hDB) );

        return OGRERR_FAILURE;
    }

I have added some debugging code to the OGR SQLite driver to see
what the SQL statement looks like that actually gets processed
by sqlite3_step().

The output of "v.out.ogr" then was:

Exporting 2898 points/lines...
SQL: 'INSERT INTO 'dem' (WKT_GEOMETRY,'cat','cat_','flt1') VALUES (?,'1','1','75.5')'

SQL: 'INSERT INTO 'dem' (OGC_FID,WKT_GEOMETRY,'cat','cat_','flt1') VALUES (1,?,'2','2','73.57')'
ERROR 1: sqlite3_step() failed:
  SQL logic error or missing database

SQL: 'INSERT INTO 'dem' (OGC_FID,WKT_GEOMETRY,'cat','cat_','flt1') VALUES (1,?,'3','3','75.41')'
ERROR 1: sqlite3_step() failed:
  SQL logic error or missing database
[...]

Interestingly, the SQL code for the first feature (which gets stored
OK in the DB) differs from that of all the following in that it does
not insert a value into the "OGC_FID" column!

After that, the statements look OK but they are not: "OGC_FID" is
a primary key field, but the same value "1" is generated for all
rows.

Actually, the first statement is the right one! Since it does not
touch the "OGC_FID" field, but that field is properly declared
as an auto-incrementing primary key, the SQLite3 driver takes care
of inserting a proper value by itself.

So it seems to me the fix would be to stop v.out.ogr from creating
an "OGC_FID" value as part of the attribute record for the OGR
feature (after the first record, which is for some reason OK!). 

Unfortunately, I know very little about the OGR C API and don't quite
understand how to change the code in v.out.ogr. Could someone more
familiar with that module help out, please?

Thanks,

Ben


P.S.: Why this all seems to be no problem for a PostGIS DBMS completely
eludes me -- perhaps it just ignores wrong values for a PK field and
silently replaces them with correct values...


Benjamin Ducke wrote:
> Dear all,
> 
> I have been trying in vain to store some very simple GRASS vector
> map in an SQLite3 DBMS using v.out.ogr. The data consists of
> only 93 3D points with attached integer and double attributes.
> No complex shapes, timestamps, text or blobs.
> 
> Using v.out.ogr, I get:
> 
> ERROR 1: sqlite3_step() failed:
>   SQL logic error or missing database
> 
> for all except the first record, which gets stored correctly
> in the (new) database. It is not possible to test this with
> an existing SQLite database file, because v.out.ogr in RC5
> does not support the OGR "update" action.
> 
> I have searched the Trac system for clues and found one ticket
> that may be related: http://trac.osgeo.org/grass/ticket/548
> 
> I have also tried this with different versions of SQLite and
> on different Linux systems, but always with the same result.
> 
> My setup:
> 
> 32Bit Gentoo Linux
> 
> GRASS 6.4RC5
> 
> SQLite 3.6.16
> 
> GDAL 1.6.1 (SQLite driver using native code, not linked
> to spatialite)
> 
> 
> Is there anybody here experiencing the same problems?
> Any idea where to start looking? I am very interested in
> making external SQLite support via OGR work, but have no
> clue where to start looking to resolve the problem, so
> any ideas are more than welcome!
> 
> Thanks,
> 
> Ben
> 
> 
> ------
> Files attached to this email may be in ISO 26300 format (OASIS Open Document Format). If you have difficulty opening them, please visit http://iso26300.info for more information.
> 
> _______________________________________________
> grass-dev mailing list
> grass-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/grass-dev
> 
> 



------
Files attached to this email may be in ISO 26300 format (OASIS Open Document Format). If you have difficulty opening them, please visit http://iso26300.info for more information.