OK, thanks for the feedback. I suggest you use LIMIT in addition to fetchOne() if you want to fetch only the first result. fetchAny() will still make the database materialise all the rows but then fetches only the first... With LIMIT 1, the database actually knows you're interested in only one row.
2015-12-10 9:51 GMT+01:00 Daniel Roziecki <[email protected]>: > Ehh, > > you are absolutly right. I should use LIMIT or fetchAny. I thought > fetchOne works like sql with LIMIT 1. > > Regards. > > DR > > W dniu czwartek, 10 grudnia 2015 09:37:10 UTC+1 użytkownik Lukas Eder > napisał: >> >> Indeed, only one record is logged by the call to Cursor.fetchOne(), but >> the Cursor does seem to have more records. >> >> Are you sure you're selecting only one record? Your query looks like >> you're selecting pretty much every record in the LT table (for which there >> exists a corresponding record in the LK table). What are you getting when >> you run the query in a SQL tool? >> >> 2015-12-10 9:21 GMT+01:00 <[email protected]>: >> >>> Hello, >>> >>> jooqVersion := "3.7.0" >>> PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu >>> Scala >>> >>> this: >>> >>> def get_existing_record()(implicit sql_conn: EmsSQLConnection) = { >>> val LT = Tables.TBL_LOANS_TEMP >>> val LK = Tables.TBL_LINKAGE >>> jooq.dsl >>> .select() >>> .from(LT) >>> .whereExists( >>> jooq.dsl >>> .select(LK.FLD_LINKAGE_ID) >>> .from(LK) >>> .where(LK.FLD_CAT_ID === LT.FLD_CAT_ID and LK.FLD_DB_ID === >>> LT.FLD_DB_ID) >>> ) >>> .fetchOne() >>> } >>> >>> give me this exception (cat_id + db_id) are a primary key. >>> >>> 09:06:42.145 [DEBUG] Query executed : Total: 1.437ms >>> 09:06:42.146 [DEBUG] Fetched result : >>> +---------------------------------+---------+----------+---------+-------------+-----------+-----------+-------------+---------------+ >>> 09:06:42.146 [DEBUG] : |fld_cat_id >>> >>> |fld_db_id|fld_agenda|fld_total|fld_available|fld_on_site|fld_unknown|fld_queue_len|fld_next_return| >>> 09:06:42.146 [DEBUG] : >>> +---------------------------------+---------+----------+---------+-------------+-----------+-----------+-------------+---------------+ >>> 09:06:42.146 [DEBUG] : | >>> [email protected]|U70 |66 | 1| >>> 1| 0| 0| 0| | >>> 09:06:42.146 [DEBUG] : >>> +---------------------------------+---------+----------+---------+-------------+-----------+-----------+-------------+---------------+ >>> 09:06:42.146 [DEBUG] Finishing : Total: 2.224ms, +0.786ms >>> 09:06:42.165 [ERROR] Cursor returned more than one result >>> org.jooq.exception.TooManyRowsException: Cursor returned more than one >>> result >>> at org.jooq.impl.Utils.fetchOne(Utils.java:1220) >>> ~[jooq-3.7.0.jar:na] >>> at >>> org.jooq.impl.AbstractResultQuery.fetchOne(AbstractResultQuery.java:493) >>> ~[jooq-3.7.0.jar:na] >>> at org.jooq.impl.SelectImpl.fetchOne(SelectImpl.java:2735) >>> ~[jooq-3.7.0.jar:na] >>> >>> >>> I see only one data row here. >>> >>> Any ideas how fix this? >>> >>> Regards. >>> >>> Daniel Roziecki >>> >>> -- >>> 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. >>> >> >> -- > 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. > -- 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.
