CODEWITHSUNDEEP
scalaplayframework-2.0slicktypesafe-stack
Abhishek Pande
Abhishek Pande

Reputation: 463

[SlickException: Read NULL value for column (USERS /670412212).LOGIN_ID]

I am using Slick 1.0.0 with play framework 2.1.0. I am getting the following error when I query my Users table. The value of LOGIN_ID is null in DB. The query I am executing is: 

    val user = { for { u <- Users if u.providerId === id.id } yield u}.first

This results in the following error:

play.api.Application$$anon$1: Execution exception[[SlickException: Read NULL value for column (USERS /670412212).LOGIN_ID]]
    at play.api.Application$class.handleError(Application.scala:289) ~[play_2.10.jar:2.1.0]
    at play.api.DefaultApplication.handleError(Application.scala:383) [play_2.10.jar:2.1.0]
    at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$12$$anonfun$apply$24.apply(PlayDefaultUpstreamHandler.scala:314) [play_2.10.jar:2.1.0]
    at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$12$$anonfun$apply$24.apply(PlayDefaultUpstreamHandler.scala:312) [play_2.10.jar:2.1.0]
    at play.api.libs.concurrent.PlayPromise$$anonfun$extend1$1.apply(Promise.scala:113) [play_2.10.jar:2.1.0]
    at play.api.libs.concurrent.PlayPromise$$anonfun$extend1$1.apply(Promise.scala:113) [play_2.10.jar:2.1.0]
scala.slick.SlickException: Read NULL value for column (USERS /670412212).LOGIN_ID
    at scala.slick.lifted.Column$$anonfun$getResult$1.apply(ColumnBase.scala:29) ~[slick_2.10-1.0.0.jar:1.0.0]
    at scala.slick.lifted.TypeMapperDelegate$class.nextValueOrElse(TypeMapper.scala:158) ~[slick_2.10-1.0.0.jar:1.0.0]
    at scala.slick.driver.BasicTypeMapperDelegatesComponent$TypeMapperDelegates$StringTypeMapperDelegate.nextValueOrElse(BasicTypeMapperDelegatesComponent.scala:146) ~[slick_2.10-1.0.0.jar:1.0.0]
    at scala.slick.lifted.Column.getResult(ColumnBase.scala:28) ~[slick_2.10-1.0.0.jar:1.0.0]
    at scala.slick.lifted.Projection15.getResult(Projection.scala:627) ~[slick_2.10-1.0.0.jar:1.0.0]
    at scala.slick.lifted.Projection15.getResult(Projection.scala:604) ~[slick_2.10-1.0.0.jar:1.0.0]

My User table is defined as :

package models

import scala.slick.driver.MySQLDriver.simple._

case class User(userId:String,email:String,loginId:String,fullName:String,firstName:String,lastName:String,location:String,homeTown:String,providerId:String,provider:String,state:String,zip:String,accessKey:String,refreshKey:String,avatarUrl:String)

object Users extends Table[User]("USERS") {
  def userId = column[String]("USER_ID", O.PrimaryKey) // This is the primary key column
  def email =  column[String]("EMAIL",O.NotNull)
  def loginId = column[String]("LOGIN_ID",O.Nullable)
  def fullName = column[String]("FULL_NAME",O.NotNull)
  def firstName = column[String]("FIRST_NAME",O.Nullable)
  def lastName = column[String]("LAST_NAME",O.Nullable)
  def location = column[String]("LOCATION",O.Nullable)
  def homeTown = column[String]("HOME_TOWN",O.Nullable)
  def providerId = column[String]("PROVIDER_ID",O.Nullable)
  def provider = column[String]("PROVIDER",O.Nullable)
  def state = column[String]("STATE",O.Nullable)
  def zip = column[String]("ZIP",O.Nullable)
  def accessKey = column[String]("ACCESS_KEY",O.Nullable)
  def refreshKey = column[String]("REFRESH_KEY",O.Nullable)
  def avatarUrl = column[String]("AVATAR_URL",O.Nullable)

  // Every table needs a * projection with the same type as the table's type parameter
  def * = userId ~ email ~ loginId ~ fullName ~ firstName ~ lastName ~ location ~ homeTown ~ providerId ~ provider ~ state ~ zip ~ accessKey ~ refreshKey ~ avatarUrl <> (User,User.unapply _)
}

Please help. It looks like Slick can not handle Null values from DB?

Upvotes: 19

Views: 13837

Answers (5)

kosgeinsky
kosgeinsky

Reputation: 538

Building on @ForeverLearner answer, I found that this also arises when you have a nullable field denoted as Option[Foo] with a custom column mapper; say that converts a class to JSON field.

If the mapper returns MappedColumnType.base[Option[Foo], String] e.g

    MappedColumnType.base[Optionp[Foo], String](
      maybeFoo => maybeFoo.map(Json.toJson(_).toString()).getOrElse("{}"),
      json => Try(Json.parse(json).as[Foo]).toOption
    )

You will get the exception if the column contains null value. There are two solutions:

  1. Use a default value in the column e.g {} for the JSON above
  2. Do not try to convert value to Option[Foo] in the mapper; instead DO NOT use Option[Foo] in the column AND ensure to use .? when constructing the case class

Upvotes: 0

Bertie92
Bertie92

Reputation: 707

What happened to me was that I had anomalies in the DB and some values were accidentally nulls - those that I didn't expect to be. So do not forget to check your data too :)

Upvotes: 0

ForeverLearner
ForeverLearner

Reputation: 2103

This problem arises if a column contains a null value and at runtime it gets a null in the column response. If you see in the code below, my cust_id is nullable, but it has no null values. Since, there is a job that makes sure that is is never null. So, the below mapping works. However, it is the best practice to look at your table structure and create the class accordingly. This avoids nasty runtime exception.

If the table definition on database is like:

CREATE TABLE public.perf_test (
    dwh_id serial NOT NULL,
    cust_id int4 NULL,
    cust_address varchar(30) NULL,
    partner_id int4 NULL,
    CONSTRAINT perf_test_new_dwh_id_key UNIQUE (dwh_id)
);

The corresponding class definition can be as below. But, it will be advised to have the cust_id also as Option[Int]. However, as long as it has values and no nulls, you will not encounter error.

import slick.jdbc.PostgresProfile.api._

class PerfTest(tag: Tag) extends Table[(Int, Int, Option[String], Option[Int])](tag, "perf_test") {
  def dwhId = column[Int]("dwh_id")
  def custId = column[Int]("cust_id")
  def custAddress = column[Option[String]]("cust_address")
  def partnerId = column[Option[Int]]("partner_id")
  def * = (dwhId, custId, custAddress,partnerId)
}

Upvotes: 4

iwein
iwein

Reputation: 26161

If you get this error, you'll have to either make the properties O.Nullable, or you have to specify that your query returns an option.

For example let's say you do a rightJoin you might not want to make the properties of the right record optional. In that case you can customize the way you yield your results using .?

val results = (for {
  (left, right) <- rightRecord.table rightJoin leftRecord.table on (_.xId === _.id)
  } yield (rightRecord.id, leftRecord.name.?)).list
results map (r => SomeJoinedRecord(Some(r._1), r._2.getOrElse(default)))

Upvotes: 7

i.am.michiel
i.am.michiel

Reputation: 10404

Your case class is not ok. If you use O.Nullable, all your properties have to be Option[String].

Upvotes: 31

Related Questions