Re: Roadmap for FE/BE protocol redesign

Dave Page wrote:
> I don't know about JDBC, but ODBC could use it, and it would save a heck
> of a lot of pain in apps like pgAdmin that need to figure out if a column
> in an arbitrary resultset might be updateable.
> At the moment there is some nasty code in pgAdmin II that attempts to
> parse the SQL statement to figure out if the the resultset is updateable
> by trying to figure out the number of relations in the query, whether any
> of them is a view or sequence, whether there are any function calls or
> expressions in the attribute list and so on. It then has to try to figure
> out if there is a complete pkey in the resultset that can be used for the
> update, or whether it should attempt an update based on all existing
> values. That code is just plain nasty in VB. In pgAdmin III we've already
> mentioned stealing bits of the PostgreSQL parser.

I will just add a "me to" here. This would be very useful for JDBC as
well. We go through the same hoops to support the jdbc spec that Dave
does. The jdbc spec has two features that require this level of
information:

1) For every result set you can ask for a ResultSetMetaData object.
This object provides you with the following methods:

getColumnCount()
isAutoIncrement(int column)
isCaseSensitive(int column)
isSearchable(int column)
isNullable(int column)
getColumnDisplaySize(int column)
getColumnLabel(int column)
getColumnName(int column)
getSchemaName(int column)
getPrecision(int column)
getScale(int column)
getTableName(int column)
getColumnTypeName(int column)
isReadOnly(int column)
isWritable(int column)
isDefinitelyWritable(int column)

Now one can state the spec is broken and it doesn't make sense to ask
this type of information about a query (and frankly I would agree with
you), but that doesn't mean that I don't need to support it anyway. So
anything that the server can do to make this easier is greatly
appreciated. And I believe ODBC has almost the exact same issue since
in general the JDBC spec was copied from the ODBC spec.

2) Updateable result sets. The jdbc spec allows the user to declare any
select statement to be updateable. This means that as you scroll
through the result (the ResultSet object) you can issue modify the data
and expect the jdbc driver to reflect that change back to the base
tables. The following if from the JDBC API doc:

* A set of updater methods were added to this interface
* in the JDBC 2.0 API (JavaTM 2 SDK,
* Standard Edition, version 1.2). The comments regarding parameters
* to the getter methods also apply to parameters to the
* updater methods.
*
* The updater methods may be used in two ways:
*
* to update a column value in the current row. In a scrollable
* ResultSet object, the cursor can be moved backwards
* and forwards, to an absolute position, or to a position
* relative to the current row.
* The following code fragment updates the NAME column
* in the fifth row of the ResultSet object
* rs and then uses the method updateRow
* to update the data source table from which rs was derived.
*
*
* rs.absolute(5); // moves the cursor to the fifth row of rs
* rs.updateString("NAME", "AINSWORTH"); // updates the
* // NAME column of row 5 to be AINSWORTH
* rs.updateRow(); // updates the row in the data source
*
*
* to insert column values into the insert row. An updatable
* ResultSet object has a special row associated with
* it that serves as a staging area for building a row to be inserted.
* The following code fragment moves the cursor to the insert row,
builds
* a three-column row, and inserts it into rs and into
* the data source table using the method insertRow.
*
*
* rs.moveToInsertRow(); // moves cursor to the insert row
* rs.updateString(1, "AINSWORTH"); // updates the
* // first column of the insert row to be AINSWORTH
* rs.updateInt(2,35); // updates the second column to be 35
* rs.updateBoolean(3, true); // updates the third row to true
* rs.insertRow();
* rs.moveToCurrentRow();
*

Now application developers love this functionality. It allows them to
implement fairly complex apps with very little sql knowledge. They only
need to know how to do a simple select statement and that is it. The
jdbc driver handles the rest for them automatically (updates, inserts,
deletes). As a jdbc maintainer I personally hate this functionality as
it is a real pain to implement, and can't work in any but the most
simple select statements. But is is part of the spec and needs to be
supported in the best manner possible.

thanks,
--Barry