4

I have an optional insert query:

val q = sql"insert into some_table (some_field) select 42 where ...(some condition)"

Running this query with:

q.update.withUniqueGeneratedKeys[Option[Long]]("id")

fails with

Result set exhausted: more rows expected

then condition is false.

How to get Optional[Long] result from insert statements with Doobie?

UPD

.withGeneratedKeys[Long]("id") gives just Long in for comprehension

val q = sql"insert into some_table (some_field) select 42 where ...(some condition)"
for {
  id <- q.update.withGeneratedKeys[Long]("id")   // id is long
  _ <- if (<id is present>) <some other inserts> else <nothing>
} yield id

How to check id?

Oleg
  • 899
  • 1
  • 8
  • 22
  • 2
    The documentation says `withUniqueGeneratedKeys` expects exactly one row. Maybe `withGeneratedKeys` (which returns a Stream of all of them) works better here? – Thilo May 28 '19 at 12:58

1 Answers1

1

As @Thilo commented, you can use the use withGeneratedKeys which gives you back a Stream[F, Long] (where F is your effect type)

val result = q.update.withGeneratedKeys[Long]("id")

Here is the doc.

Valy Dia
  • 2,781
  • 2
  • 12
  • 32
  • `.withGeneratedKeys[Long]("id")` gives just `Long` in for comprehension. See UPD. How to handle this problem? – Oleg May 28 '19 at 14:34
  • This is a `for-comprehension` on a `Stream`, so indeed it's a `Long`, if the `Stream` happens to be empty nothing get done, so you are safe to remove the `if` statement – Valy Dia May 28 '19 at 15:58