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.

Improve this question
5
  • 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, 2014 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, 2014 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, 2014 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, 2014 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, 2014 at 17:08

1 Answer 1

Reset to default
6

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')
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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