|
I attached a new test case, DerbyTest142.java, based on db-derby-10.1.1.0-bin/demo/simple/SimpleApp.java that demonstrates the problem with the Derby
Network Client (Derby 10.1.1.0). You can compile the app to run in embedded mode or in Derby Network Client mode. The output for the embedded run shows that the ID and NAME columns are writeable: [jta@gertie3 DERBY-142]$ java -Dderby.system.home=$DERBY_SYSTEM_HOME DerbyTest142 DerbyTest142 starting in embedded mode. Loaded the appropriate driver. Connected to and created database test Created table readonlytest ID column is writeable Name column is writeable Dropped table readonlytest Closed result set and statement Committed transaction and closed connection Database shut down normally DerbyTest142 finished The output for the Derby Network Client run shows that the columns are readonly: [jta@gertie3 DERBY-142]$ java -Dderby.system.home=$DERBY_SYSTEM_HOME DerbyTest142 DerbyTest142 starting in Derby Network Client mode. Loaded the appropriate driver. Connected to and created database test Created table readonlytest ID column is readonly Name column is readonly Dropped table readonlytest Closed result set and statement Committed transaction and closed connection DerbyTest142 finished Thomas Fischer explained in http://mail-archives.apache.org/mod_mbox/db-torque-dev/200510.mbox/%3cOFD3D00A51.B30FADD0-ONC1257093.0025F17E-C1257093.0026E38E@seitenbau.net%3e that this problem prevents Torque from working with Derby and the network server: > The problem was that the network driver in the meta-info > declared all columns to be read-only, which causes a library used > internally in Torque (village) to refuse to write any datasets into the > table. The problem is described in the Bug DERBY-142 in Derby's Jira. > > However, in future releases of Torque, it is quite probable that village is > removed, so this will not be problematic any longer. There is a question about whether isReadOnly() should return read-only (or definitely not writable) value for the resultSet column returned or for the base table column. Since a ResultSetMetaData should describe the ResultSet itself, not the base table, I think both Derby Client and embedded drivers should return FALSE, for the following:
connection = DriverManager.getConnection(URL, USER, PASSWORD); statement = connection.createStatement(); resultset = statement.executeQuery( "select id,name from " + TABLE); ResultSetMetaData metaData = resultset.getMetaData(); System.out.println("ID column is: ' metaData.isReadOnly(1)); System.out.println("Name column is: '+metaData.isReadOnly(2)); For this case, the resultset is forward-only read-only resultset. So, it seems isReadOnly() should return TRUE. Embedded driver doesn't, so I think we should change embed driver to match Derby Client here. However, if resultset is retrived as: resultset = statement.executeQuery( "select id,name from " + TABLE + " FOR UPDATE"); Derby Client returns FALSE for isReadOnly(), which is correct. isReadOnly() and possibly isWriteOnly() don't match behavior between DerbyClient and embedded.
So Torque uses Village ( http://www.softwareforge.de/releases/village/ ) to insert/modify datasets. Here are a few more details about what Village does under the hood after a (very quick) look at it. (Torque lurkers should feel free to correct these details.)
Village obtains the schema for a given table with this query: String sql = "SELECT " + columnsAttribute + " FROM " + tableName + " WHERE 1 = -1"; This query will work in any database, so this much makes sense. And it also makes sense to me that they wouldn't include a "FOR UPDATE" clause on it. At this point, they're just building internal information about the schema. Information for each column is then obtained with the ResultSetMetaData methods, including whether or not the column is writeable with that isReadOnly method call. The Village method that inserts a new row only does so if the columns are writeable. Hence the current problem. I'm looking at the jdbc docs at http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSetMetaData.html and I'm not seeing clearly whether these metadata calls are intended to just describe columns in the result set or columns in the underlying table: - isReadOnly: "Indicates whether the designated column is definitely not writable." - isWritable: "Indicates whether it is possible for a write on the designated column to succeed.". What is the "designated column"? Just the result set? Or could it also refer to the underlying table? I'm looking at the "JDBC API Tutorial and Reference, Third Edition", and section 27.1.19 on "Queries That Produce Updateable Result Sets" suggests to me a strong association with the permissions on the underlying table. --I'm left wondering what it might mean to update a result set outside the context of an underlying table. Looks like the Derby requirement of "FOR UPDATE" on updatable resultsets is causing quite a bit of headache. There is improvement "Derby-231 "FOR UPDATE" required for updatable result set to work" filed for the very same issue and it might be worth the efforts for someone to look into possibly not requiring it.
The description Jean gave above of how village works is correct.
Regarding the question how to interpret the term "designated column", all the databases Torque is working with (I can confirm this personally for mysql, postgresql, oracle, firebird, hsqldb, others report that db2, sybase... are also working) either interpret this as "the table of the underlying column", or produce updateable result sets without adding the "for update" clause. Village using the following code: String sql = "SELECT " + columnsAttribute + " FROM " + tableName + " WHERE 1 = -1"; stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); From the java API spec: public Statement createStatement() throws SQLException Creates a Statement object for sending SQL statements to the database. .... Result sets created using the returned Statement object will by default be type TYPE_FORWARD_ONLY and have a concurrency level of CONCUR_READ_ONLY. So I would suspect that most of the databases will not return an updateable result set upon execution of the code above (because the concurrency level is explitly set to CONCUR_READ_ONLY), though I know it only for oracle for sure (oracle also uses the "for update" clause). Thus, most of the other database vendors interpret the term "designated column" as "the table of the underlying column". Thanks, Thomas for your detailed comment. I tested on DB2, that I have access to. Using their Universal JCC driver against 8.15 server, it seems DB2 interprets "designated column" to mean the result column, not the base table column. Without FOR UPDATE, DB2 server returns isReadOnly() to TRUE. With FOR UPDATE clause, it returns FALSE.
I haven't tried against other database vendors. Just wanted to add this info. I am willing to address the issue eitherway once we reach a consesus here. Satheesh, just to be clear in your last test what was the updateability of the JDBC ResultSet? Can you repeat the test with a non-updateable result set? I thought with DB2 (and the SQL standard) that a SELECT statement was always updatable with a positioned update, thus FOR UPDATE would not make a difference. Might be interesting to try the SELECT statement with FOR READ ONLY.
Correcting the previous comment. The test was run on 8.1.5 DB2 server using bundled JCC driver.
It seems FOR UPDATE/FOR READ ONLY specification overwrites statement concurrency. DerbyTest142 starting ... Default resultSetType and default concurrancy Query: SELECT id, name FROM readonlytest ID column isReadOnly = true Name column isReadOnly = true Query: SELECT id, name FROM readonlytest FOR UPDATE ID column isReadOnly = false Name column isReadOnly = false Query: SELECT id, name FROM readonlytest FOR READ ONLY ID column isReadOnly = true Name column isReadOnly = true Forward-only resultSetType and CONCUR_UPDATABLE Query: SELECT id, name FROM readonlytest ID column isReadOnly = false Name column isReadOnly = false Query: SELECT id, name FROM readonlytest FOR UPDATE ID column isReadOnly = false Name column isReadOnly = false Query: SELECT id, name FROM readonlytest FOR READ ONLY ID column isReadOnly = true Name column isReadOnly = true Forward-only resultSetType and CONCUR_READONLY Query: SELECT id, name FROM readonlytest ID column isReadOnly = true Name column isReadOnly = true Query: SELECT id, name FROM readonlytest FOR UPDATE ID column isReadOnly = false Name column isReadOnly = false Query: SELECT id, name FROM readonlytest FOR READ ONLY ID column isReadOnly = true Name column isReadOnly = true Here is the program: public class DB2Test { public String protocol = "jdbc:db2://localhost:50000/"; public static void main(String[] args) throws Exception { System.out.println("DerbyTest142 starting ..."); Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); Connection conn = DriverManager.getConnection("jdbc:db2://localhost:50000/tdb", "USER", "PASSWORD"); System.out.println("Default resultSetType and default concurrancy"); Statement s = conn.createStatement(); printQueryInfo("SELECT id, name FROM readonlytest", s); printQueryInfo("SELECT id, name FROM readonlytest FOR UPDATE", s); printQueryInfo("SELECT id, name FROM readonlytest FOR READ ONLY", s); s.close(); System.out.println("\nForward-only resultSetType and CONCUR_UPDATABLE"); s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); printQueryInfo("SELECT id, name FROM readonlytest", s); printQueryInfo("SELECT id, name FROM readonlytest FOR UPDATE", s); printQueryInfo("SELECT id, name FROM readonlytest FOR READ ONLY", s); s.close(); System.out.println("\nForward-only resultSetType and CONCUR_READONLY"); s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); printQueryInfo("SELECT id, name FROM readonlytest", s); printQueryInfo("SELECT id, name FROM readonlytest FOR UPDATE", s); printQueryInfo("SELECT id, name FROM readonlytest FOR READ ONLY", s); s.close(); conn.close(); } public static void printQueryInfo(String query, Statement s) throws SQLException { ResultSet rs = s.executeQuery(query); ResultSetMetaData metaData = rs.getMetaData(); System.out.println("\tQuery: "+query); System.out.println("\t\tID column isReadOnly = " +metaData.isReadOnly(1)); System.out.println("\t\tName column isReadOnly = " +metaData.isReadOnly(2)); rs.close(); } } The TorqueTutorial.txt attachment summarizes output for runs with the Derby embedded driver, which succeeds, and the Derby Network Client and DB2 JCC drivers, which both fail with syntax errors.
My previous comment regarding the TorqueTutorial.txt attachment should have included details about the Torque Tutorial. It's here:
http://db.apache.org/torque/releases/torque-3.2-rc2/runtime/tutorial/index.html Derby instructions for doing the Torque Tutorial with Derby and the embedded driver are here: http://db.apache.org/derby/integrate/db_torque.html Hm, let's not get too Torque-centric here. If the derby developers decide that the current behaviour of the network driver is correct, that is ok with me, I can see the reasons for it now. I was probably a bit naive when I reported this as a 'bug'.
Another thing, if you think it would be helpful to know how other databases interpret the isReadOnly function, I can offer to run Satheesh's test program against some other databases (mysql, postgresql, oracle, firebird, hsqldb). Just let me know. In a discussion on derby-dev, Lance (JDBC expert) said
"i would recommend returning false as this returned result is not tied to an updatable ResultSet but to whether you can definitively determine the column cannot be modified. This is a JDBC 1.0 method." So if noone objects the correst resolution of this issue is to change client to match embedded. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Sub-task
Open
Major
The db2 universal Driver libraries should be in the classpath; a Derby network server should be running on localhost and a database named test should exist on that server in order to be able to run the test case