Hi guys (a lot of mails to catch up with)
Garret, you're running into this issue here: https://github.com/jOOQ/jOOQ/issues/2374 We're hoping to finally fix this for jOOQ 3.4 (and then merge to 3.3 and 3.2), but we cannot promise this, as identifying an IDENTITY column at runtime without relevant information from generated tables is a bit tricky. Everyone, I really need an answer to this quickly. > If time is of the essence, then let me suggest our support services, which we're offering at competitive rates to customers running projects on tight schedules: http://www.jooq.org/support Do note also that a basic support package is included in the jOOQ Professional Edition and jOOQ Enterprise Edition licenses. Both licenses can also be purchased if you're using jOOQ with an Open Source database. > In another table I had a workaround in that I would query the URI value, > which was unique, and it would return me the ID. Very inefficient, but > workable. > > But now I have another table that does not have any unique values other > than the generated ID. So if RETURNING doesn't work, how can I find out the > ID that was generated? > > I should be able to use CURRVAL<http://stackoverflow.com/a/2944481/421049>, > and indeed > DSLContext<http://www.jooq.org/javadoc/3.3.x/org/jooq/DSLContext.html> seems > to have a currval(), but it requires a Sequence. Where do I get a sequence? > I don't seem to be able to do this: > > ...sequenceByName("entity_id") > You're right, this method should be added for API completeness. I have added a feature request for this: https://github.com/jOOQ/jOOQ/issues/3189 As a workaround, you can instanciate the internal (but public) type org.jooq.impl.SequenceImpl instead. > It seems there is also a LASTVAL, but DSLContext doesn't have a lastVal(). > It has a lastID(), but the documentation says it is not supported on > PostgreSQL. > I wasn't aware of LASTVAL(), but you can probably use currval() on your sequence. The org.jooq.Sequence type provides a currval() method. I try to manually execute CURRVAL after an insert: > > dslContext.resultQuery("select CURRVAL(uriid)").fetch() > > That gives me: *ERROR: column "uriid" does not exist* > CURRVAL() takes a sequence name as a string (or more precisely as a regclass type): http://www.postgresql.org/docs/9.3/static/functions-sequence.html If uriid is your sequence name, try CURRVAL('uriid') instead, if you want to do this yourself with plain SQL. > So I try to indicate a table: > > dslContext.resultQuery("select CURRVAL(uriid) from uris").fetch() > > I get: *ERROR: could not open relation with OID 1* > > Maybe it's because jOOQ automatically closed the connection after the > previous INSERT. > jOOQ doesn't automatically close any connections. > I think I'm going to have to throw in the towel for the day. I need some > direction here. > > I just need the generated sequence value. I'm getting the feeling that > with normal SQL this would be a piece of cake. *Why is jOOQ making it > harder than SQL?* > Garret, I can understand that you may have been frustrated with the issues that you have encountered. Yes, there is an open issue #2374 preventing you from fetching IDs. Yes, there are some peculiarities in PostgreSQL syntax. Yes, we may have made a bad decision in early product stages regarding dependency management (jOOU, etc.). This is software. It has issues, and we're working very hard to not only fix these issues but to make the jOOQ experience a most enjoyable one for all of our users and customers. We strongly believe that there is currently no other SQL API on the market that: - Adds as much value to customers as jOOQ does - Takes SQL as seriously as jOOQ does - Has as many features as jOOQ does I'm more than happy to point out some of the many benefits you will get down the line of your project, once that project matures, *because* you are using an internal DSL that builds an AST representation of your SQL statement in memory. These benefits include things like multi-tenancy, row-level security, optimistic locking, SQL AST transformation, and much more. But these benefits might only be noticeable *down the line*, which means that I hope you will stay with us and that you will be patient with us. Looking forward to hearing from you, Lukas -- 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/d/optout.
