CODEWITHSUNDEEP
sql-serverplayframework-2.0slick-3.0
Simon
Simon

Reputation: 431

Play Slick config

I'm trying to get started with play and slick.

Strategy; take hello-slick-3.1 project from the activator tutorials.

If works fine with the H2 in memory database. I want to connect to a sql server. After a battle I have some configuration which appear to connect using jdts.

In application .conf

  driver=net.sourceforge.jtds.jdbc.Driver

url="jdbc:jtds:sqlserver://%%%%:1433;databaseName=%%%%;user=%%%;password=%%%%%"

This is using the jtds driver instead of 

com.typesafe.slick.driver.ms.SQLServerDriver

Which appears to have been made deliberately difficult to use. I have not found a sucessful config with it. JTDS manages to create the 'suppliers' table based on it's schema, but all subsequent requests fall over with a nebulous 'data truncation' message; 

object HelloSlick extends App {
  val db = Database.forConfig("sqlServerLocal")
  try {

    // The query interface for the Suppliers table
    val suppliers: TableQuery[Suppliers] = TableQuery[Suppliers]

    val setupAction: DBIO[Unit] = DBIO.seq(
      // Create the schema by combining the DDLs for the Suppliers and Coffees
      // tables using the query interfaces
      //(suppliers.schema).create,

      // Insert some suppliers
      suppliers += (101, "Acme, Inc.", "99 Market Street", "Groundsville", "CA", "95199"),
      suppliers += ( 49, "Superior Coffee", "1 Party Place", "Mendocino", "CA", "95460"),
      suppliers += (150, "The High Ground", "100 Coffee Lane", "Meadows", "CA", "93966")
    )

Telling me that

background log: info: 10:58:48.465 [sqlServerLocal-1] DEBUG slick.jdbc.JdbcBackend.statement - Preparing statement: insert into "SUPPLIERS" ("SUP_ID","SUP_NAME","STREET","CITY","STATE","ZIP")  values (?,?,?,?,?,?)
background log: error: Exception in thread "main" java.sql.DataTruncation: Data truncation

Does anyone have any ideas? Is connecting to SQL server with slick - 3.1 even a sensible thing to attempt?

EDIT::

@szeiger makes the excellent point that I'm importing the wrong thing into the model classes. I had the old H2 driver import from the Hello Slick example, which should be replaced with this; 

import com.typesafe.slick.driver.ms.SQLServerDriver.api._

In order to work as a SQL server language.

After making this change, the DB configuration which 'did something' no longer works, advertising this

background log: info: Running HelloSlick 
background log: error: Exception in thread "main" java.lang.NoClassDefFoundError: slick/profile/BasicProfile$SimpleQL

I've tried altering the instantiation of the actual db variable to match the 'recommended' strategy suggested by szeiger. 

  val db = Database.forURL("jdbc:sqlserver://%%%:1433;user=%%%%;password=%%%%", driver="com.typesafe.slick.driver.ms.SQLServerDriver",
    executor = AsyncExecutor("test1", numThreads=10, queueSize=1000))

Unfortunately, this fails like so; 

background log: error: Exception in thread "main" java.lang.NoClassDefFoundError: slick/profile/BasicProfile$Implicits

with the jtds driver, it has the same error message with the slick MSSQL driver, and despite my best efforts, I cannot instantiate the MS JDBC driver. It's in a folder called 'lib' in the application , although I suspected that might be naive and the wrong place.

Managing this dependency through play-slick would be excellent. I had thought that I would be doing that via the inclusion of this line in SBT; 

"com.typesafe.play" %% "play-slick" % "1.1.0",

but appears to have no effect in isolation, and I'm unsure how to configure this to reach the 'official' slick MS SQL driver.

EDIT 2:: Finally, this appears to build what I want. 

name := """hello-slick-3.1"""

lazy val root = (project in file(".")).enablePlugins(PlayScala)

resolvers += "Typesafe Releases" at "http://repo.typesafe.com/typesafe/maven-releases/"
resolvers += "Scalaz Bintray Repo" at "https://dl.bintray.com/scalaz/releases"
resolvers += Resolver.url("Typesafe Ivy releases", url("https://repo.typesafe.com/typesafe/ivy-releases"))(Resolver.ivyStylePatterns)


scalaVersion := "2.11.6"

libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick" % "3.1.0",
  "com.typesafe.slick" %% "slick-extensions" % "3.1.0",
  "com.typesafe.play" %% "play-slick" % "1.1.0",
  "org.scalatest" %% "scalatest" % "2.2.4" % "test"
)

I had lots of problems because I forgot to include the correct resolvers.

Don't forget to include the correct resolvers :-).

Upvotes: 1

Views: 830

Answers (2)

szeiger
szeiger

Reputation: 1406

You're confusing Slick drivers and JDBC drivers. Which Slick driver are you using? The imports are missing from your snippet but there should be something like import com.typesafe.slick.driver.ms.SQLServerDriver.api._. Using a wrong driver here could explain String columns being created as VARCHAR(1).

The recommended way of configuring database connections in Slick 3.1 is via DatabaseConfig, which allows you to configure the Slick driver together with the actual connection parameters. When you're writing a Play app, use the play-slick plugin instead to handle database connections. It is also based on the DatabaseConfig syntax.

The alternative to net.sourceforge.jtds.jdbc.Driver would be Microsoft's own JDBC driver, com.microsoft.sqlserver.jdbc.SQLServerDriver which is available as a separate download from Microsoft. You have to manually add sqljdbc4.jar to your build to make this work.

BTW, Slick 3.2 will change terminology and use the word "driver" exclusively for JDBC drivers. Slick drivers will be called "profiles" in 3.2.

Upvotes: 1

Simon
Simon

Reputation: 431

After bonus pain, it looks like slick creates columns in SQL Server as type 'varchar(1)'.

Which can't hold very much data.

Not using Slick to create the tables, and commenting out these lines then means that the intro works as advertised.

Upvotes: 0

Related Questions