Separate NULL from non existence

Question

I having a code which makes select:

val requestId = 123
DB.withConnection { implicit con =>
  SQL("SELECT my_field FROM my_table WHERE some_id = {request_id}").onParams(requestId)()
   .collect {
      case Row(myField: Option[String]) =>
        myField match {
          case Some(x) =>
            // ???   

          case None => //there is no such a record?
         }
      }
  }

my_field can be NULL. Thus there are 3 options:

1. the record WHERE some_id = request_id doesn't exist
2. the record WHERE some_id = request_id exists and has a value
3. the record WHERE some_id = request_id exists and is NULL

How do I separate 1 and 3?

Answer 1

I have only limited experience with Anorm since I have jumped on the Slick bandwagon, but I think you distinguish among your cases this way (in order):

1. You get an empty collection back.
2. You get a Some back.
3. You get a None back.

So I would guess you match like this:

        myField match {
          case Seq() => //the record doesn't exist
          case Some(x) => //unpack x because you found something    
          case None => //the record exists but some_id = NULL
         }

Check the Anorm documentation to see what kind of collection you get back. List does implement the Seq trait though.