Cannot use UUID in Plain SQL Queries

[unoexperto]

I cannot use UUID value in Plain SQL Queries when I construct query using +?. I get following error

could not find implicit value for parameter p: scala.slick.jdbc.SetParameter[java.util.UUID]
      val qqq = Q.u + "UPDATE " + AbilitiesTable.tableName + " SET is_can=" +? true + " WHERE id=" +? ability_id
                                                                                                            ^

How can I do it ? Or could you provide example of converter for UUID ? It's available in Lifted Embedding so perhaps it can be reused.

Thanks.

[stig]

I've also run into this issue:

[error] [...]Foo.scala:51: could not find implicit value for parameter pconv: scala.slick.jdbc.SetParameter[java.util.UUID]
[error]             Q.query[UUID, Int]("select count(*) from user where user_id = ?").first(user.userID) must be (1)

[urcadox]

I just experienced the same issue and I found a workaround in PostgreSQL:

You can convert your UUID to a string and then cast it back to a uuid in your query by adding '::uuid' after your parameter.

Not pretty, but it works.

[ljwagerfield]

Also getting this with MySQL when using sqlu string interpolation.

[adamgordonbell]

I think that to fix this next and set methods would be needed for UUID and Option[UUID] like is done in this commit to the postgres slick project for special postgres types.

As a work around you can just add these yourself and then just write an implicit GetResult for your type:

  //mapping for select for UUID
  implicit class PgPositionedResult(val r: PositionedResult) extends AnyVal{
    def nextUUID : UUID =  UUID.fromString(r.nextString)
    def nextUUIDOption : Option[UUID] = r.nextStringOption().map(UUID.fromString(_))
  }

// Some entity which uses UUID that I want to work with plain sql
implicit val getUUIDContent = GetResult(r => UUIDContent(r.nextUUIDOption, r.<<, ... ))

At the very least this has worked for me

[bbatarelo]

@agbell did you change the slick source or did you somehow manage to extend the mappings without touching the source?

[unoexperto]

@bbatarelo I patched MySql driver to support UUIDs. https://github.com/cppexpert/mysql-connector-java-with-uuid-support

[adamgordonbell]

@bbatarelo I am extending without touching the source. I am extending PositionedResult to have nextUUID which just uses UUID.FromString to do the conversion.

Re-reading @cppexpert 's question though, it is more about UUID parameters. My proposed workaround is for getting and setting objects with UUID columns using slick plan sql.

[bbatarelo]

@agbell Can you please tell me where you are placing the implicit class? If I place it in the actual class where the job is getting done (in my repository) compiler throws exception: value class may not be a member of another class. Putting it within the method is also not supported by compiler, and implicit classes can not be the top level objects.

[bbatarelo]

@agbell never mind - I figured it out :)

[adamgordonbell]

I have a object like this

object PositionResultExtensions {

  implicit val getWebsiteContent = GetResult(r => WebsiteContent(r.nextLTree,r.nextString,r.nextLTree, r.<<, r.<<, r.nextHStore,r.nextZonedDateTimeOption,r.<<, None, r.<<, r.nextUUIDOption))

  implicit class PgPositionedResult(val r: PositionedResult) extends AnyVal {
    def nextUUID: UUID = UUID.fromString(r.nextString)

    def nextUUIDOption: Option[UUID] = r.nextStringOption().map(UUID.fromString(_))

    def nextUUIDList() = nextArray[UUID].getOrElse(Nil)

    def nextUUIDListOption() = nextArray[UUID]

    def nextDateTimeRangeList() = nextOptionDateTimeRangeList.getOrElse(Nil)

    def nextDateTimeRange() = nextRangeDateTime

    def nextAssetsType() : WebsiteAssetType = WebsiteAssetTypes.withName(r.rs.getString(r.skip.currentPos))

    def nextEnumType[T <: Enumeration](eNum:T) = eNum.withName(r.rs.getString(r.skip.currentPos))
 }

}

And then somewhere else, where I want get my websitecontent object I import that object and do this.

 val content = Q.queryNA[WebsiteContent]("some query").list(session)

[bbatarelo]

Exactly - thanks! :)

[bbatarelo]

Btw, is there a similar solution but for the opposite route when it comes to interpolating parameters into plain sql? Currently I am working off a solution by @urcadox by casting a string value with ::uuid.

[adamgordonbell]

@bbatarelo You can write set methods similarly. However I haven't done it. It may be that would work for inserts but not for parameters. give it a try.

[bbatarelo]

Got it working: implicit object SetUUID extends SetParameter[UUID] { def apply(v: UUID, pp: PositionedParameters) { pp.setObject(v, JDBCType.BINARY.getVendorTypeNumber) } } Now we have both routes covered :)

[adamgordonbell]

Awesome

[szeiger]

Proper integration blocked on #1263

[veegee]

This is what I did to get around the problem (note that I'm also using slick-pg, which is a wonderful library by @tminglei)

import java.sql.JDBCType
import java.util.UUID

import com.github.tminglei.slickpg._
import slick.jdbc.{PositionedResult, PositionedParameters, SetParameter}

object ExPostgresDriver extends ExPostgresDriver

trait UUIDPlainImplicits {

  implicit class PgPositionedResult(val r: PositionedResult) {
    def nextUUID: UUID = UUID.fromString(r.nextString)

    def nextUUIDOption: Option[UUID] = r.nextStringOption().map(UUID.fromString)
  }

  implicit object SetUUID extends SetParameter[UUID] {
    def apply(v: UUID, pp: PositionedParameters) {
      pp.setObject(v, JDBCType.BINARY.getVendorTypeNumber)
    }
  }

}

trait MyPostgresDriver
  extends ExPostgresDriver
  with PgArraySupport
  with PgDate2Support
  with PgPostGISSupport {

  object MyAPI
    extends API
    with ArrayImplicits
    with DateTimeImplicits
    with PostGISImplicits
    with PostGISAssistants {
    implicit val strListTypeMapper = new SimpleArrayJdbcType[String]("varchar").to(_.toList)
  }

  object PlainAPI extends API
  with SimpleArrayPlainImplicits
  with Date2DateTimePlainImplicits
  with PostGISPlainImplicits
  with UUIDPlainImplicits {
  }

  override val api = MyAPI
  val plainAPI = PlainAPI
}

object MyPostgresDriver extends MyPostgresDriver

[dvtomas]

I ran into the same problem and have used your solution. A small enhancement: I think it's better to retrieve the UUID via

r.nextObject().asInstanceOf[UUID]

instead of your proposed

UUID.fromString(r.nextString)

Once transfer goes into binary mode, you'll save one UUID bytes -> string -> UUID roundtrip as the UUID will get instantiated directly from the bytes. See PgResultSet.internalGetObject for implementation details. UUID.fromString is slow.

[drobert]

I was looking into implementing this as well from the above comments, on top of Postgres. The code snippets above, which reference PgPositionedResult (https://github.com/slick/slick/issues/161#issuecomment-169373730) and using the direct r.nextObject().asInstanceOf[UUID] approach mentioned https://github.com/slick/slick/issues/161#issuecomment-329168647 mostly work for me, but GetResult errors.

At least in postgres, using JDBCType.BINARY results in errors, namely:

column "SOME_COLUMN" is of type uuid but expression is of type bytea at character OFFSET

It works correctly using JDBCType.OTHER, however. I have a gist up here with working code https://gist.github.com/drobert/9974355296c95029cce9528526e9a1ec

[akumartiwari]

r.nextObject().asInstanceOf[UUID Approach seem much prominent way as cast is fast, reliable and doesn't involved round trip as mentioned above .@dvtomas

Saviour