1. Is the left-fold the best/only interface to expose? I think yes, but that doesn't allow fine-grained control over cursors i.e. being able to open many cursors at once and interleave fetches from them. Or does it?
I'd like to remark first that many real databases let us avoid opening many cursors at once. It seems to be more efficient to do as much as possible within the engine -- using the full power of SQL or Stored Procedures -- and relay only the final results through the boundary. We can use SELECT ... UNION, correlated subqueries, SELECT rowid, self (outer) joins, etc. There are cases where SQL is powerless -- a transitive closure comes to mind. Stored procedures may sometimes help. A stored procedure may return a 'row' and thus can be used as a "generator" in Icon or Ruby's sense of the word. Still, opening several cursors may be unavoidable. The left fold approach may still help -- we _can_ mechanically invert a left fold combinator (to be precise, a half-baked version of it) into a lazy list. Please see a separate message "how to invert the left fold"
This approach is quite awkward (especially w.r.t. writing extract functions), and it's hard for me to see how to build a better interface. Hard, because of the memory management requirements.
I believe the extract functions can be constructed automatically -- similar to the way Quickcheck constructs test cases. I believe that instead of
results <- doQuery dbconn sqltext [] \row results -> do name <- stringv row 1 address <- stringv row 2 return (name,address):results
we can do results <- doQuery dbconn sqltext [] process where process:: String -> String -> [(String,String)] -> [(String,String)] process name address results = (name,address):results doQuery should be able to figure our that the function 'process' takes two arguments of type string. Therefore, doQuery needs to extract two columns from the current row, both of type string. Note, 'process' is a pure function. There is no reason for it to be monadic (if it doesn't have to be). Because doQuery is a left-fold iterator, it can keep reusing buffers until the iteration finishes. We can keep the pointers to column buffers in a polytipic list, and we can keep the corresponding deallocation actions in a list of IO (). When we prepare a statement, we create both lists. When we finish doQuery, we scan the list of destructor actions and execute them to free all the buffers. It's hard for me to write the corresponding code because I don't have Oracle handy (besides, I like Informix better). Is it possible to come up with a "stub" that uses flat files? We are only interested in fetching rows. It doesn't matter if these rows come from a file or from a database. That would make prototyping the interface quite easier.