Slick / SQL Server

[Simon Parten]

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 what would have been my preference;

com.typesafe.slick.driver.ms.SQLServerDriver

My Build.sbt

"com.typesafe.slick" %% "slick" % "3.1.0",
"com.typesafe.slick" %% "slick-extensions" % "3.0.0",
"com.typesafe.play" %% "play-slick" % "1.1.0",
"net.sourceforge.jtds" % "jtds" % "1.3.1",
"com.h2database" % "h2" % "1.4.187",

Which appears to have been made deliberately difficult to use. I have not found a successful configuration with it. I always get ClassNotFound Exceptions. 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?

A similar question exists on stackoverflow ; not really sure where the right place is, but it's here in the interests of transparency. http://stackoverflow.com/questions/33564197/play-slick-config

[Simon Parten]

Turns out that Slick creates string columns as varchar(1) in SQL Server. Nice.

That causes the data truncation problems. If you don't use Slick to create the table and DIY it , things appear to work closer to planned.