"prepared" statements (Re: Limit vs setMaxRows issue)

Oliver Jowett <oliver(at)opencloud(dot)com> writes:

> I am a bit confused about what this discussion is actually about .. do
> you have a point to make here? What is it that you want to do that the
> current driver doesn't do well? A fair amount of work has gone into
> getting query execution working smoothly and efficiently within the
> constraints of the API already.. Vague high-level handwaving,
> especially without a clear target, doesn't get us anywhere. For
> example, it's largely irrelevant to an application whether the query
> gets parsed by the server at statement creation or statement execution
> .. at worst, it means you see parse errors at a different point.

- By the way:

<http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html#getMetaData()>

Retrieves a ResultSetMetaData object that contains information about
the columns of the ResultSet object that will be returned when this
PreparedStatement object is executed.

Because a PreparedStatement object is precompiled, it is possible to
know about the ResultSet object that it will return without having to
execute it. Consequently, it is possible to invoke the method
getMetaData on a PreparedStatement object rather than waiting to
execute it and then invoking the ResultSet.getMetaData method on the
ResultSet object that is returned.

Similar thing for #getParameterMetadata()

- However, in the 3rd edition of the JDBC book:

23.1.3 Using parameter metadata wisely

The purpose of a prepared statement is to allow the data source to
"prepare" an SQL statement, that is, to compile an SQL statement
before it is executed. [..] However, not all data sources prepare a
statement the same way, and some do not prepare them at all. If a
data source does not precompile SQL statements, the
ParameterMetaData methods will not be able to return results until
execution [this is a lie because it misses "lazy" implementations,
see below].

According to my (limited) understanding of the code, pgjdbc sends a
special "describe" request to the server for #getMetadata() in case the
statement has not been executed yet, and systematically for
#getParameterMetadata(). Neither one seems to cache results, at least
not on the driver-side.

As you said above, all this looks very well-crafted to be functionally
irrelevant to the application. It definitely looks relevant and good
to know concerning performance.