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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
