Derby supports updatable cursor and may be you could use that as an
for now until updatable resultsets support is added to Derby. In other words,
you could use SELECT ... FROM... FOR UPDATE. Keep in mind though
that there are several restrictions on the exact form of SELECT query. You
can find the details in the documentation.
Steven Buschman wrote:
Thanks for the update on "update". In the short term, should I just delete the record, then do an insert? It's no biggie (but watch out for those cascading deletes ...)
Mamta Satoor wrote:Hi Steve, Let me jump in and answer the res.updateXXX issue since I am working on providing support for that in Derby. Derby at this point in time doesn't support updatable resultsets and that is why you get the exception. I have send in a patch for support of delete using updatable resulset APIs and am working on the support of update using updatable resultset APIs. You can find some other threads on this same issue on the list. Mamta Steven Buschman wrote:Hello, First let me apologize in advance if I'm not following protocol - this is the first time I've accessed an Apache project, so newbie I am. I downloaded the compiled 56458 release successfully. My intention was to port a MySQL client application to Derby, primarily because of the near zero admin issues with Derby. After a bit of grief (there are quite a few differences), I'm down to just one problem that I can't figure out worked w/MySQL 4.1 and fails with Derby: Statement state = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet res = state.executeQuery("select * from xxx where ..."); if (!res.next()) throw new Exception(...); // I'm sure the select will succeed! res.updateString(...); // SQLException occurs !!!! SHOULD NOT HAPPEN!!! res.updateInt(...); res.updateRow(); res.close(); state.close(); On a separate note - a few issues that I observed: 1. Using ij, INSERT into table values (..), (...), (...) generates a stack overflow if there are too many inserts. I wanted to import 12,000 rows in Derby and it failed after a dozen rows or so. I was stuck doing 12,000 separate inserts, which took close to 2 minutes on a very fast machine (10K WD drive with 1 GB ram and AMD 64 939). 2. After doing a "select * from table" with 6000 rows and 47 columns, it took 10 seconds to load the table if I used res.getXXX("text") but only 1.5 seconds if I used res.getXXX(integer value). 3. Derby: var INT NOT NULL GENERATED ALWAYS AS IDENTITY vs. MySQL: var INT NOT NULL AUTO_INCREMENT My problem is that Derby won't let me assign a value to var even if it's unique. The problem here is that if you're importing data from elsewhere and "var" is being used as a foreign key in another table, then it's a major pain to make sure everything is in sync. All in all Derby is fantastic - never have I been able to download and port (99%) in just 1 day. I see tremendous opportunities for it for small scale applications. Thanks. -- Steve Buschman 22 Blackburnian Rd. Lincoln, MA 01773 781-259-0295-- Steve Buschman 22 Blackburnian Rd. Lincoln, MA 01773 781-259-0295