Re: Issue with QUERY and no-result queries

Chris Capel wrote:
> Running an insert statement like so:
> [...]
> against a postgresql-socket backend throws the error "missing-result / Didn't
> receive result cursor for query."  It might do similar things for other
> backends.

Yes, that error checking is protect against a database backend failure
where the backend had trouble returning an answer to the query. It's
also a guard if a query contained a syntax error causing the backend
not to create a result set.

> Reading the documentation, I find that I'm supposed to use
> EXECUTE-COMMAND to execute a query that returns no values. However,
> I think this is a bug. There's no reason QUERY shouldn't let you run
> a command that doesn't return anything if you want to. The /simple/
> conceptual image of QUERY is that it takes some SQL, either in
> string form or object form or special-reader-syntax form, and it
> runs it against the database, and returns any values that the query
> happens to produce. By causing it to throw an error when the query
> returns a value, you complicate the concept of the function
> considerably, and I see no advantage in the behavior to offset that.

Some might argue that a /simple/ concept of query is that is an
invocation where an answer is requested and that overloading that
concept to include imperatives complicates the simple concept of a
query.  I'm not arguing one way or another, I'm just pointing out that
one's view of /simple/ is prejudiced by their previous experience and
existing conceptual framework. 


> Similarly, by limiting QUERY to only one resultset (at least the
> postgresql-socket backend does this), you complicate what the
> developer has to keep in mind while using the function. It's logical
> that running a query that returns multiple values would return
> multiple values back to lisp.

I'm not sure what you mean by multiple values. By default, two values
are returned from the query: the tuple results of the query and a list
of field names. The tuple results is a list of lists which contains
the "rows" and "columns" of the query.  This is in accordance with the
CommonSQL specification of which CLSQL strives to be compatible.

> So, immediately, I think one should use QUERY for both valued and
> non-valued SQL statements, and EXECUTE-COMMAND should be
> deprecated. In the future, I think support for multiple return
> values from QUERY should be considered.

Execute-command will not be deprecated in CLSQL unless Xanalys decides
to deprecate it in their specification and they expand the role of
QUERY to accept commands in addition to queries.

> Even if you don't agree with the above, the error message should probably
> suggest EXECUTE-COMMAND.

Something along the lines of "Error: the query failed to produce a
result set. Either the database backend had a failure or you supplied
a SQL command which was not a query. If the latter is the case, you
should use EXECUTE-COMMAND instead."?

-- 
Kevin Rosenberg
kevin-HJRc7zDS/[email protected]