Re: LuaSQL Question

[Tomas Guisasola Gorham <tomas@...>]

	Hi Terry

> I've been using the luaSQL module a lot lately.  The documentation for
> luaSQL suggests the use of an iterator to 'walk' over the result set:
>
> function rows (connection, sql_statement)
>     local cursor = assert (connection:execute (sql_statement))
>     return function ()
>       return cursor:fetch()
>     end
>   end
>
> This works, but often causes an "there are open cursors" error when
> closing the connection.
>
> My naive attempt to solve this doesn't appear to work:
>
> function rows(connection, sql_statement)
>  local cursor = assert (connection:execute (sql_statement))
>  return function ()
> 	local rc = cursor:fetch()
> 	if (rc == nil) then
> 		cursor:close()
> 		cursor = nil
> 	end
>    return rc
>  end
> end
	Why is it not working?	There are two problems that may occur:
the cursor remains alive after traversing the entire result set, and the
cursor remains alive because you haven't traversed the entire result set.
	The first problem could be solved with the code above and a
minor correction.  Since cursor:fetch() can return more than one value,
you have to store it in a table:

function rows(connection, sql_statement)
  local cursor = assert (connection:execute (sql_statement))
  return function ()
    local result = { cursor:fetch() }
    if result[1] == nil then
      cursor:close()
      --cursor = nil -- this is useless
    end
    return unpack(result)
  end
end

	This might not work very well because unpack doesn't know exactly
how many elements are there in table result.  For that to work we have
to call a function as Philipp Janda already posted.
	The other problem is more difficult to solve, since the iterator
cannot know when it would not be called anymore :-)  For that, I use to
return the cursor just after the true iterator as in:

function rows(connection, sql_statement)
  local cursor = assert (connection:execute (sql_statement))
  return function ()
    local result = { cursor:fetch() }
    if result[1] == nil then
      cursor:close()
    end
    return unpack(result)
  end, cursor
end

-- naive testing
local iter, cur = rows(connection, "select id from table order by birth_date")
for id in iter do
	-- do anything with id
	if some_condition() then
		break
	end
end
cur:close()

	Regards,
		Tomás