Hi Stéphane
2013/5/25 Stéphane Cl <[email protected]> > Hello, > It is quite hard to find information on what JDBC drivers are actually > doing different behind the scenes when working with updatable resultset. > Yes, there's not a lot of documentation and the specs are somewhat "fuzzy", i.e. the Javadoc doesn't always properly specify the behaviour of update methods. One big mismatch that I have found, is the ResultSet.deleteRow() method: http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#deleteRow() It is not specified on the method Javadoc itself, whether the row should also be deleted from the ResultSet (and thus, if all subsequent rows should be shifted), or if the row should be kept in the ResultSet. And as expected, I have seen both behaviours... However, jOOQ may be able to abstract this and provide a uniform behaviour for Result and Record. > Compared to a forward-only query followed by a batch-update, is there any > gain? Because it's probably increasing data traffic between the app and the > database. > I think that JDBC drivers have complete liberty with respect to implementation, here. Looking into ojdbc's or postgres JDBC's byte-code (and some others), you can see that they execute new SELECT / UPDATE / DELETE / INSERT statements based on ROWID values. So with ojdbc, you don't gain much compared to jOOQ's pre-existing functionality, which operates on primary keys. jtds on the other hand, does some trickery with cursors using "sp_cursor" and "sp_cursorfetch". The SQL Server JDBC driver uses some internal RPC API to send data to the server. So, yes there might be some gain if users choose to explicitly use CONCUR_UPDATABLE over explicit new updates. If a database provides extensive support for cursors as re-usable resources, then CONCUR_UPDATABLE might be a better choice in some situations... However, as I have only little experience with this, I'd like to reach out to the community. Cheers Lukas Regards > > Le mercredi 22 mai 2013 21:08:28 UTC+2, Lukas Eder a écrit : > >> Dear group, >> >> I'm reaching out for feedback again, for a feature that I have been >> experimenting recently in jOOQ 3.1. This feature is tracked as #1846 [1] >> and #2265 [2]. >> >> A short feature summary: >> ------------------------------**------------------------------** >> ------------------------------ >> #1846 allows for specifying that the JDBC ResultSet is kept around in >> resulting org.jooq.Result and/or org.jooq.Record objects. For this, jOOQ >> will apply ResultSet.TYPE_SCROLL_**SENSITIVE and possibly >> ResultSet.CONCUR_UPDATABLE. >> >> #2265 will pull up store(), refresh(), delete() and other CRUD methods >> from UpdatableRecord to Record, virtually making all records updatable if >> fetched accordingly. If a ResultSet is available in a Record, these >> operations will directly... >> - refresh() themselves using the ResultSet, positioning the cursor at the >> right index and re-reading the values >> - update() themselves using the ResultSet (if it is CONCUR_UPDATABLE), >> positioning the cursor at the right index and calling ResultSet.updateXXX() >> methods >> - delete(), store() and insert() might be a bit harder as the record >> might need to be removed from / added to the Result >> - Result's inherited List operations might have a new meaning, too >> >> #2265 will also allow for storing / refreshing / deleting the whole >> Result at once, through these JDBC methods. >> >> Some criticism: >> ------------------------------**------------------------------** >> ------------------------------ >> These features look very tempting. They have been first encountered on >> this group in a thread [3] comparing org.jooq.Result with ADO.NETDataTables >> [4]. This sounds nice, but it will introduce a lot of complexity >> into jOOQ. I feel that few users might actually use this feature, which >> depends on something that is poorly implemented by a couple of JDBC drivers. >> >> I have also checked back with Igor Polevoy, the creator of ActiveJDBC [5] >> to see what he thinks about this idea. >> >> Personally, I feel that this would be a great feature for jOOQ, >> completely along the lines of jOOQ's mission. But I also feel that the high >> complexity might not pull its own weight, as few users might use it. >> >> As always, your thoughts are very welcome >> >> Cheers >> Lukas >> >> [1]: >> https://github.com/jOOQ/**jOOQ/issues/1846<https://github.com/jOOQ/jOOQ/issues/1846> >> [2]: >> https://github.com/jOOQ/**jOOQ/issues/2265<https://github.com/jOOQ/jOOQ/issues/2265> >> [3]: https://groups.google.**com/d/msg/jooq-user/** >> 0F1YSZB2xDk/Vwq1wbAiG8cJ<https://groups.google.com/d/msg/jooq-user/0F1YSZB2xDk/Vwq1wbAiG8cJ> >> [4]: http://msdn.microsoft.**com/en-us/library/system.data.** >> datatable.aspx<http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx> >> [5]: https://groups.google.**com/forum/?fromgroups#!topic/** >> activejdbc-group/MV8ZgfwoI7E<https://groups.google.com/forum/?fromgroups#!topic/activejdbc-group/MV8ZgfwoI7E> >> > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
