SQLite files incomplete

[darkblueB]

Some minimal KML files, coverted to SQLite, generate an incomplete SQLite file

ex. ogr2ogr -f SQLite t.sqlite Polygon.kml

ogrinfo will read the file as containing a polygon, but opening the file with SQLite3 will show "Incomplete SQL: SQLite format 3xxx", where xxx is the table defintion. This occurs for many minimal files converted to SQLite.

[darkblueB]

... looking at the error string from the sqlite3 command line monitor, this curious file [ca_places_sqlite_ERR.txt] tells perhaps a little more about what is going on.. looks like a badly termintated string, or unitialized buffer, for the CREATE string

[darkblueB]

... added a memset/NULL to init the command buffer in OGRSQLiteDataSource::CreateLayer(). That does not appear to have solved the problem. However, closer inspection of the file build sequence shows not less than 6 calls to CREATE TABLE when making a trivial conversion of Polygon.kml [poly_KML_SQLite_ERR.txt]

[darkblueB]

thinking of a fix which is just 'CREATE TABLE IF NOT EXISTS ... ', but, foiled by SQLIte versions... not a good fix as many (like me) would not have the latest and greatest SQLite3 installed...

---

Subject: Re: Create table IF NOT EXISTS Group: Sqlite-users From: Thomas Chust Date: 2 Mar 2006

On Thu, 2 Mar 2006, Sripathi Raj wrote:

[...] Is the IF NOT EXISTS constraint supported? When I try to use it, I get an error message saying that there is a syntax error at 'NOT'? I'm using Sqlite 3 on Windows and I'm trying to connect to it from Perl. [...]

Hello,

the information that you are using version 3 is not precise enough to answer this question with yes or no ;)

I know the IF [NOT] EXISTS syntax is *not* supported in SQLite 3.1.3 but it *is* supported in SQLite 3.3.4. A quick glance at the changelog says the syntax was introduced with 3.3.0 alpha.

However I recently ran into the pitfall, that with SQLite 3.3.4 the compilation of an IF [NOT] EXISTS qualified statement may result in a NULL statement handle being returned in case the IF [NOT] EXISTS clause does not apply. If this is a problem for you, you should use the current CVS version of SQLite3 which has that behaviour fixed.

cu, Thomas

[Even Rouault]

Works fine with FWTools which uses a pretty ancient sqlite3 (3.0.2 apparently)