[GIS] Explicitly close a OGR result object from a call to ExecuteSQL

ogrpythonspatialite

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.

Best Answer

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')
Related Question