3

How can I explicitly close/release the result of an ExecuteSQL statement when querying a Spatialite database with OGR via Python? I have a simple query to return the SRID of a dataset. This works as expected. However, subsequent calls to ds.ExecuteSQL fail with the error 'SQL logic error or missing database' unless I iterate all the way through the result rows.

For example:

ogr.UseExceptions()

# This query returns a single row
sql = 'select distinct srid(geometry) from foo'
result = ds.ExecuteSQL(sql)
row = result.next()
epsg = row.GetField(0)

# This call fails
ds.ExecuteSQL('drop table bar')

Completing the iteration avoids the error:

_ = [r for r in result]

This is fine for simple cases, but not very explicit. I've tried calling result.Dereference(), row=None; result=None but these do not help. What is it that I'm missing here?

UPDATE The exception is only raised when I enable ogr.UseExceptions(). Otherwise the error passes silently and the drop table statement has no affect.

  • hmmm. i was able to run a test case without issue - using 2 different select statements. Is it because the second statement in your example is a drop table command? – fluidmotion Sep 17 '14 at 13:33
  • @fluidmotion You may be on to something. I suspect OGR is expecting a return value that is not present for the drop table. Note that I am explicitly requesting OGR Exceptions to be raised. – tharen Sep 17 '14 at 15:40
  • at risk of asking a simple question - is the datasource capable of deleting the table? it can be tested with print ds.TestCapability('oDsCDeleteLayer'). In my test case, this returned false (see gdal.org/ogr_sql.html 'The OGR SQL DROP TABLE command can be used to delete a table. This is only supported on datasources that declare the ODsCDeleteLayer capability.') – fluidmotion Sep 17 '14 at 16:14
  • TestCapability returned False. However, if I execute the drop table before the select everything works. My understanding from the SQLite/Spatialite OGR driver docs is that all SQL statements are passed directly to the SQLite engine unless the dialect is specified as OGR_SQL. Also, in this case the table being dropped is a simple table with no geometry columns. – tharen Sep 17 '14 at 16:29
  • ok - not to grasp at straws, but the ogr_sql page also mentions '[after ExecuteSQL] The returned temporary layer should be released with OGRDataSource::ReleaseResultsSet() method when no longer needed. Failure to release it before the datasource is destroyed may result in a crash.' Sorry - i haven't had a chance to test. – fluidmotion Sep 17 '14 at 17:08
3

As @fluidmotion suggested the result layer object returned by result=ds.ExecuteSQL(sql), with a select statement that returns rows, must be released by calling ds.ReleaseResultSet(result) to properly destroy the object. I overlooked this in the documentation, API and OGR_SQL.

ogr.UseExceptions()

# This query returns a single row
sql = 'select distinct srid(geometry) from foo'
result = ds.ExecuteSQL(sql)
row = result.next()
epsg = row.GetField(0)

# Destroy the result set
ds.ReleaseResultSet(result)

# This call should now succeed
ds.ExecuteSQL('drop table bar')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.