CodeProject, Scala

SCALA mocking

 

Last time we looked at writing unit tests for our code, where we looked at using ScalaTest. This time we will be looking at mocking.

In .NET there are several choices available that I like (and a couple that I don’t), such as :

  • Moq
  • FakeItEasy
  • RhinoMocks (this is one I am not keen on)

I personally am most familiar with Moq, so when I started looking at JVM based mocking frameworks I kind of wanted one that used roughly the same syntax as the ones that I had used in .NET land.

There are several choices available that I think are quite nicely, namely :

  • ScalaMock
  • EasyMock
  • JMock
  • Mockito

Which all play nicely with ScalaTest (which I am sure you are all very pleased to here).

So with that list what did I decide upon. I personally opted for Mockito, as I liked the syntax the best, that is not to say the others are not fine and dandy, it is just that I personally liked Mockito and it seemed to have good documentation and favorable Google search results, so Mockito it is.

So for the rest of this post I will talk about how to use Mockito to write our mocks. I will be used Mockito along side ScalaTest which we looked at last time.

SBT Requirements

As with most of the previous posts you will need to grab the libraries using SBT. As such your SBT file will need to use the following:

libraryDependencies ++= Seq(
  "org.mockito" % "mockito-core" % "1.8.5",
  "org.scalatest" %% "scalatest" % "2.2.5" % "test"
)

 

Our First Example

So with all that stated above. Lets have a look at a simple example. This trivial example mocks out a java.util.ArrayList[String]. And also sets up a few verifications

class FlatSpec_Mocking_Tests extends FlatSpec with Matchers with MockitoSugar {


  "Testing using Mockito " should "be easy" in {


    //mock creation
    val mockedList = mock[java.util.ArrayList[String]]

    //using mock object
    mockedList.add("one");
    mockedList.clear

    //verification
    verify(mockedList).add("one")
    verify(mockedList).clear

  }
}

One thing you may notice straight away is how the F*k am I able to mock a ArrayList[T], which is a class which is not abstract by the way. This is pretty cool.

 

Stubbing

Using Mockito we can also stub out things just as you would expect with any 1/2 decent mocking framework. Here is an example where we try and mock out a simple trait.

import java.util.Date
import org.scalatest._
import org.scalatest.mock._
import org.mockito.Mockito._


trait DumbFormatter {

  def formatWithDataTimePrefix(inputString : String, date : Date) : String = {
    s"date : $date : $inputString"
  }

  def getDate() : String = {
    new Date().toString
  }
}



class FlatSpec_Mocking_Tests extends FlatSpec with Matchers with MockitoSugar {

  "Stubbing using Mockito " should "be easy" in {

    var mockDumbFormatter = mock[DumbFormatter]
    when(mockDumbFormatter.getDate()).thenReturn("01/01/2015")
    assert("01/01/2015" === mockDumbFormatter.getDate())
  }
}

It can be seen above that it is quite easy to mock a trait. You can also see how we stub the mock out using the  Mockito functions

  • when
  • thenReturn

 

Return Values

We just saw an example above of how to use the “thenReturn” Mockito function, which is what you would use to setup your return value. If you want a dynamic return value this could quite easily call some other function which deals with creating the return values. Kind of a return value factory method.

 

Argument Matching

Mockito comes with something that allows you to match against any argument value. It also comes with regex matchers, and allows you to write custom matchers if the ones out of the box don’t quite fit your needs.

Here is an example of writing a mock where we use the standard argument matchers:

import java.util.Date
import org.scalatest._
import org.scalatest.mock._
import org.mockito.Mockito._
import org.mockito.Matchers._


trait DumbFormatter {

  def formatWithDataTimePrefix(inputString : String, date : Date) : String = {
    s"date : $date : $inputString"
  }

  def getDate() : String = {
    new Date().toString
  }
}



class FlatSpec_Mocking_Tests extends FlatSpec with Matchers with MockitoSugar {

  "Stubbing using Mockito " should "be easy" in {

    var mockDumbFormatter = mock[DumbFormatter]
    when(mockDumbFormatter.formatWithDataTimePrefix(anyString(),any[Date]())).thenReturn("01/01/2015 Something")
    assert("01/01/2015 Something" === mockDumbFormatter.formatWithDataTimePrefix("blah blah blah", new Date()))
  }
}

Exceptions

To throw exceptions with Mockito we simply need to use the “thenThrow(….) function. Here is how.

import java.util.Date
import org.scalatest._
import org.scalatest.mock._
import org.mockito.Mockito._
import org.mockito.Matchers._


trait DumbFormatter {

  def formatWithDataTimePrefix(inputString : String, date : Date) : String = {
    s"date : $date : $inputString"
  }

  def getDate() : String = {
    new Date().toString
  }
}



class FlatSpec_Mocking_Tests extends FlatSpec with Matchers with MockitoSugar {

  "Stubbing using Mockito " should "be easy" in {

    var mockDumbFormatter = mock[DumbFormatter]
    when(mockDumbFormatter.formatWithDataTimePrefix(anyString(),any[Date]()))
	.thenThrow(new RuntimeException())

    //use the ScalaTest intercept to test for exceptions
    intercept[RuntimeException] {
      mockDumbFormatter.formatWithDataTimePrefix("blah blah blah", new Date())
    }
  }
}

See how we also have to use the ScalaTest “intercept” for the actually testing

 

CallBacks

Callbacks are useful when you want to see what a method was called with and then you can make informed decisions about what you could possibly return.

Here is how you do callbacks in Mockito, note the use of the “thenAnswer” function, and how we use an anonymous Answer object.

import java.util.Date
import org.mockito.invocation.InvocationOnMock
import org.mockito.stubbing.Answer
import org.scalatest._
import org.scalatest.mock._
import org.mockito.Mockito._
import org.mockito.Matchers._


trait DumbFormatter {

  def formatWithDataTimePrefix(inputString : String, date : Date) : String = {
    s"date : $date : $inputString"
  }

  def getDate() : String = {
    new Date().toString
  }
}



class FlatSpec_Mocking_Tests extends FlatSpec with Matchers with MockitoSugar {

  "Stubbing using Mockito " should "be easy" in {

    var mockDumbFormatter = mock[DumbFormatter]
    when(mockDumbFormatter.formatWithDataTimePrefix(anyString(),any[Date]()))
      .thenAnswer(new Answer[String] {
        override def answer(invocation: InvocationOnMock): String = {
          val result = "called back nicely sir"
          println(result)
          result
        }
      })

    assert("called back nicely sir" === mockDumbFormatter.formatWithDataTimePrefix("blah blah blah", new Date()))



  }
}

 

Verification

The last thing I wanted to talk about was verification. Which may include verifying functions got called, and were called the right number of times.

Here is a simple example of this:

import java.util.Date
import org.mockito.invocation.InvocationOnMock
import org.mockito.stubbing.Answer
import org.scalatest._
import org.scalatest.mock._
import org.mockito.Mockito._
import org.mockito.Matchers._


trait DumbFormatter {

  def formatWithDataTimePrefix(inputString : String, date : Date) : String = {
    s"date : $date : $inputString"
  }

  def getDate() : String = {
    new Date().toString
  }
}



class FlatSpec_Mocking_Tests extends FlatSpec with Matchers with MockitoSugar {

  "Stubbing using Mockito " should "be easy" in {

    var mockDumbFormatter = mock[DumbFormatter]
    when(mockDumbFormatter.formatWithDataTimePrefix(anyString(),any[Date]()))
      .thenReturn("someString")

    val theDate = new Date()
    val theResult = mockDumbFormatter.formatWithDataTimePrefix("blah blah blah", theDate)
    val theResult2 = mockDumbFormatter.formatWithDataTimePrefix("no no no", theDate)

    verify(mockDumbFormatter, atLeastOnce()).formatWithDataTimePrefix("blah blah blah", theDate)
    verify(mockDumbFormatter, times(1)).formatWithDataTimePrefix("no no no", theDate)


  }
}

 

 

Further Reading

You can read more about how to use Mockito from the docs : https://docs.google.com/document/d/15mJ2Qrldx-J14ubTEnBj7nYN2FB8ap7xOn8GRAi24_A/edit

 

 

End Of The Line

Personally my quest goes on, I am going to keep going until I consider myself  good at Scala (which probably means I know nothing).

Anyway behind the scenes I will be studying more and more stuff about how to get myself to that point. As such I guess it is only natural that I may post some more stuff about Scala in the future.

But for now this it it, this is the end of the line for this brief series of posts on Scala. I hope you have all enjoyed the posts, and if you have please feel free to leave a comment, they are always appreciated.

 

Advertisements
CodeProject, Scala

SCALA : TESTING OUR CODE

 

So last time we looked at how to use Slick to connect to a SQL server database.

This time we look at how to use one of the 2 popular Scala testing frameworks.

The 2 big names when it comes to Scala testing are

  • ScalaTest
  • Specs2

I have chosen to use ScalaTest as it seems slightly more popular, when you do a Google search, and I quite liked the syntax. That said Specs2 is also very good. so if you fancy having a look at that you should.

SBT for ScalaTest

So what do we need to get started with ScalaTest. As always we need to grab the JAR, which we do using SBT.

At time of writing this was accomplished using this SBT entry:

name := "ClassesDemo"

version := "1.0"

scalaVersion := "2.11.7"

libraryDependencies ++= Seq(
  "org.scalatest" %% "scalatest" % "2.2.5" % "test"
)

With that in place, SBT should pull down the JAR from Maven Central for you. So once you are happy that you have the ScalaTest JAR installed, we can not proceed to write some tests.

 

Writing Some Tests

I come from a .NET background, and as such I am using to working with tools such as

  • NUnit
    • TestFixture
    • Setup : To setup the test
    • TearDown : To teardown the test
  • Moq / FakeItEasy : Mocking frameworks

As such I wanted to make sure I could do everything that I was used to in .NET using ScalaTest.

This article will concentrate on the testing side of things, while the next post will be on the mocking side of things.

So let’s carry on for now shall we.

Choosing You Test Style

ScalaTest allows you to use 2 different styles of writing tests.

  • FunSuite : This is more in line with what you get with NUnit say. We would write something like Test(“testing should be easy”)
  • FlatSpec : This is more of a BDD style test declaration, where we would write something like this: “Testing” should “be easy”
     

We will see an examples of both of these styles in just a minute, but before that lets carry on and looks at some of the common things you may want to do with your tests

Setup / TearDown

You may want to run some startup/teardown code that is run. Typically startup would be used to setup mocks for your test cases, and that sort of thing.

In things like NUnit this would simply be done by creating a method and attributing it to say it is the Setup/TearDown methods.

In ScalaTest things are slightly different in that we need to mixin the “BeforeAndAfter”  trait to do this. Lets see an example:

import org.scalatest.{FunSuite, BeforeAndAfter}
import scala.collection.mutable.ListBuffer

class FunSuite_Example_Tests extends FunSuite with BeforeAndAfter {

  val builder = new StringBuilder
  val buffer = new ListBuffer[String]

  before {
    builder.append("ScalaTest is ")
  }

  after {
    builder.clear()
    buffer.clear()
  }
}

It can be seen in this example that the BeforeAndAfter trait, gives you 2 additional functions

  • before
  • after

You can use these to perform your startup/teardown logic.

This example uses the FunSuite style, but the “BeforeAndAfter”  trait mixin is done exactly the same for the FlatSpec style testing.

 

Writing A Test Using FunSuite

I think if you have come from a NUnit / XUnit type of background you will probably identify more with the FunSuite style of testing.

Here is an example of a set of FunSuite tests.

import org.scalatest.{FunSuite, BeforeAndAfter}

import scala.collection.mutable.ListBuffer

class FunSuite_Example_Tests extends FunSuite with BeforeAndAfter {

  val builder = new StringBuilder
  val buffer = new ListBuffer[String]

  before {
    builder.append("ScalaTest is ")
  }

  after {
    builder.clear()
    buffer.clear()
  }

  test("Testing should be easy") {
    builder.append("easy!")
    assert(builder.toString === "ScalaTest is easy!")
    assert(buffer.isEmpty)
    buffer += "sweet"
  }

  test("Testing should be fun") {
    builder.append("fun!")
    assert(builder.toString === "ScalaTest is fun!")
    assert(buffer.isEmpty)
  }
}

It can be see that they follow the very tried and tested approach of tools like NUnit, where you have a test(…) function, where “…” is the text that describes your testcase.

Nothing much more to say there apart from to make sure you mixin the FunSuite trait.

 

Writing A Test Using FlatSpec

ScalaTest also supports another way of writing your tests, which is to use the FlatSpec trait, which you would mixin instead of the FunSuite trait.

When you use FlatSpec you would be writing your tests more like this:

  • “Testing” should “be easy” in {…}
  • it should “be fun” in {…}

Its more of a BDD style way of creating your test cases.

Here is the exact same test suite that we saw above but this time written using the FlatSpec instead of FunSuite.

import scala.collection.mutable.ListBuffer
 
class FlatSpec_Example_Tests extends FlatSpec with BeforeAndAfter {
 
    val builder = new StringBuilder
    val buffer = new ListBuffer[String]
 
     before {
         builder.append("ScalaTest is ")
       }
 
     after {
         builder.clear()
         buffer.clear()
       }
 
    "Testing" should "be easy" in {
         builder.append("easy!")
         assert(builder.toString === "ScalaTest is easy!")
         assert(buffer.isEmpty)
         buffer += "sweet"
       }
 
     it should "be fun" in {
         builder.append("fun!")
         assert(builder.toString === "ScalaTest is fun!")
         assert(buffer.isEmpty)
       }
}

I don’t mind either, I guess it’s down to personal choice/taste at the end of the day.

Using Matchers

Matchers are ScalaTest’s way of providing additonal constraints to assert against. In some testing frameworks we would just use the Assert class for that along with things like

  • Assert.AreEqual(..)
  • Assert.IsNotNull(..)

In ScalaTest you can still use the assert(..) function, but matchers are also a good way of expressing your test conditional.

So what exactly are matchers?

In the words of the ScalaTest creators:

ScalaTest provides a domain specific language (DSL) for expressing assertions in tests using the word should.

So what do we need to do to use these ScalaTest matchers? Well quite simply we need to just mix in Matchers, like this:

import org.scalatest._

class ExampleSpec extends FlatSpec with Matchers { ...}

You can alternatively import the members of the trait, a technique particularly useful when you want to try out matcher expressions in the Scala interpeter. Here’s an example where the members of Matchers are imported:

import org.scalatest._
import Matchers._

class ExampleSpec extends FlatSpec { // Can use matchers here ...

So that give us the ability to use the ScalaTest matchers DSL. So what do these things look like. Lets see a couple of examples:

import org.scalatest._


class FlatSpec_Example_Tests extends FlatSpec with Matchers {

    "Testing" should "probably use some matchers" in {

          //equality examples
          Array(1, 2) should equal (Array(1, 2))
          val resultInt = 23
          resultInt should equal (3) // can customize equality
          resultInt should === (3)   // can customize equality and enforce type constraints
          resultInt should be (3)    // cannot customize equality, so fastest to compile
          resultInt shouldEqual 3    // can customize equality, no parentheses required
          resultInt shouldBe 3       // cannot customize equality, so fastest to compile, no parentheses required

          //length examples
          List(1,2) should have length 2
          "cat" should have length 3

          //string examples
          val helloWorld = "Hello worlld"
          helloWorld should startWith ("Hello")
          helloWorld should endWith ("world")

          val sevenString ="six seven eight"
          sevenString should include ("seven")

          //greater than / less than
          val one = 1
          val zero = 0
          val seven = 7
          one should be < seven
          one should be > zero
          one should be <= seven
          one should be >= zero

          //emptiness
          List() shouldBe empty
          List(1,2) should not be empty
       }

}

 

 

For more information on using matchers, you should consult this documentation, which you can find here:

http://www.scalatest.org/user_guide/using_matchers

 

 

Scala

SCALA : Connecting to a database

 

This time we will proceed to look at using Scala to connect to SQL server.

In .NET we have quite a few ORM choices available, as well as standard ADO.NET. For example we could use any of the following quite easily

  • Linq to SQL
  • Entity Framework
  • Dapper
  • NHibernate
  • ADO .NET

In Scala things are a bit more tame on the ORM front. We basically only have one player, which is called “Slick”. The rest of this post will be about how to use Slick.

 

Slick

The good thing about Slick is that it works with a wide range of SQL dialects. For this post I will be using what I know which is MS SQL server. As such I will be using a MS SQL server driver, and there may be differences between the driver I use and other Slick drivers, but hopefully you will get the idea.

 

Notes on MS SQL Server

The following notes assume you are install

I found that I had to do the following to get Slick to work with MS SQL Server

  • Turn on the TCP/IP
  • Insure that the full set of SQL server services were running for the Slick Extension SQL Server driver to work.

Demo IntelliJ IDEA Project

As this one is quite a lot bigger than the previous Scala posts. I have decided to upload this one to GitHub.

You can grab the project from here :

https://github.com/sachabarber/ScalaSlickTest

But before you try and run it you should make sure you have done the following :

  • Created a MS SQL Server DB
  • Run  the schema creation scripts included in the IntelliJ IDEA project
  • Changed the “application.conf” file to point to YOUR SQL Server installation

 

The rest of this post will deal with how to do various things using Slick such as:

  • Use direct SQL commands (sql strings)
  • Use the slick ORM for CRUD
  • Use a store procedure with Slick

But before we get on to any of that lets just outline the schema we will be working with. The one and only table we will be using is this one :

image

So now that we know what the single (I know lame we should have had more, but meh) table looks like lets crack on

NOTE : In the examples shown in this post I am using the Scala Async Library that I have talked about before.

 

Using Direct SQL Commands

In this section we will see how we can use Slick to run arbitrary SQL commands. Lets see some examples

Return a Scalar value

Say we only want 1 value back. Perhaps count of the rows. We can just do this:

def selectScalarObject(db:Database) : Unit = {

  val action = sql"""Select count(*) as 'sysobjectsCount'  from sysobjects""".as[Int]
  val futureDB : Future[Vector[Int]] = db.run(action)

  async {
    val sqlData = await(futureDB)
    val count = sqlData.head
    println(s"PlainSQLHelper.selectScalarObject() sysobjectsCount: $count")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

Return more than 1 value

We may of course want a couple of values, but we are not quite ready to return a brand new entity. So we can use a Tuple.

Here is an example:

def selectTupleObject(db: Database) : Unit = {

  val action = sql"""Select count(*)  as 'sysobjectsCount', count(*)/10  as 'sysobjectsCountDiv10' from sysobjects""".as[(Int,Int)]
  val futureDB : Future[Vector[(Int,Int)]] = db.run(action)

  async {
    val sqlData = await(futureDB)
    val (x,y) = sqlData.head
    println(s"PlainSQLHelper.selectTupleObject() sysobjectsCount: $x, sysobjectsCountDiv10: $y")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

Return a case class

We can obviously make things more formal, and be nice and return  a nice case class. Here is an example of that:

def selectRawTableObject(db: Database) : Unit = {

  val action = sql"""Select * from Items""".as[(Int,String, Double, Int)]
  val futureDB : Future[Vector[(Int,String, Double, Int)]] = db.run(action)

  async {
    val sqlData = await(futureDB)
    val (id,desc, cost, location) = sqlData.head
    val item = RawSQLItem(id,desc, cost, location)
    println(s"PlainSQLHelper.selectRawTableObject() Id: ${item.id}, Description: ${item.description}, Cost: ${item.cost}, WarehouseLocation: ${item.warehouseLocationId}")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}


case class RawSQLItem(id: Int, description: String, cost: Double,  warehouseLocationId: Int)

 

 

Using The Slick ORM For CRUD

These examples show how you can do the basic CRUD operations with Slick.

However before we start to look at the CRUD operations, lets just see a bit of basic Slick code. Slick uses a trait called Table which you MUST mixin. It is also common practice that we use a companion object to create a TableQuery[T]. Here is the one for the CRUD operations we will be looking at next

package org.com.barbers.slicktest

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

object Items {
  val items = TableQuery[Items]
}

case class DBItem(id: Int, description: String, cost: Double,  warehouseLocationId: Int)

class Items(tag: Tag) extends Table[DBItem](tag, "Products") {
  def id = column[Int]("Id", O.PrimaryKey, O.AutoInc)
  def description = column[String]("Description")
  def cost = column[Double]("Cost")
  def warehouseLocationId = column[Int]("WarehouseLocationId")
  def * = (id, description, cost, warehouseLocationId) <> (DBItem.tupled, DBItem.unapply)
}

Create

Ok so now we have seen that Slick uses a Table mixin, and that there is a TableQuery[T] at play. Let’s move on to see how we can create some data.

This is quite weird to do. Normally what we want from a INSERT is an Id. How Slick does that is a bit strange. We need to use the Slick DSL to say what we would like returned (the “Id”), which we do using the “returning” followed by the map of the Items table. This may sound weird but the example below may help to illustrate this a bit. Here is how we do it:

def saveItem(db: Database, item: DBItem) = {

  val action =(Items.items returning Items.items.map(_.id)) +=
    DBItem(-1, item.description, item.cost, item.warehouseLocationId)
  val futureDB : Future[Int] = db.run(action)

  async {
    val savedItemId = await(futureDB)
    println(s"TableResultRunner.saveItem() savedItem.Id ${savedItemId}")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

And here is how we store several items.For a bulk insert, we can’t really get the inserted Ids. But we can add all Items in on go using the standard Scala collection operator ++=, which appends a new collection to the current collection.

Again an example will make this clearer

def insertSeveralItems(db: Database, items : List[DBItem]) : Unit = {

  implicit val session: Session = db.createSession()
  val insertActions = DBIO.seq(
    (Items.items ++= items.toSeq).transactionally
  )
  val sql = Items.items.insertStatement
  val futureDB : Future[Unit] = db.run(insertActions)

  async {
    await(futureDB)
    println(s"TableResultRunner.insertSeveralItems() DONE")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

 

Retrieve

So we now have some Items, so how do we get them back from the DB?

There are many ways to do this with Slick. Let’s use a simple Take(2) operation to start with

def selectTwoItems(db: Database) : Unit = {

  implicit val session: Session = db.createSession()
  val q =  Items.items.take(2)
  val futureDB : Future[Seq[DBItem]] = db.run(q.result)

  async {
    val sqlData = await(futureDB)
    val item = sqlData.head
    println(s"TableResultRunner.selectTwoItems()[0] " +
      s"Id: ${item.id}, Description: ${item.description}, " +
      s"Cost: ${item.cost}, WarehouseLocationId: ${item.warehouseLocationId}")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

We can also use Queries to filter out what we want from the DB. Here is an example of using a Query, where we use a filter to get all Items that have a Id that matches a Id

def findItemById(db: Database,id : Int) = {

  async {
    val q = for { p <- Items.items if p.id === id } yield p
    val futureDBQuery : Future[Option[DBItem]] = db.run(q.result.headOption)
    val item : Option[DBItem] = await(futureDBQuery)
    println(s"OPTION ${item}")
    item match {
      case Some(x) =>  println(s"TableResultRunner.findItemById The item is $x")
      case _ => ()
    }
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

 

Update

Update is a stranger on. Where we get out only the attributes we want from the DB using a query, and then use Slicks inbuilt update(..) function to perform the update on the columns we want. This is clearer with an example.

In this example we want to update ONLY the “cost” column of an Item.

def updateItemCost(db: Database, description : String, cost : Double) = {

  async {
    val q = Items.items
      .filter(_.description === description)
      .map(_.cost)
      .update(cost)

    val futureDB = db.run(q)
    val done = await(futureDB)
    println(s"Update cost of ${description}, to ${cost}")

    val q2 = for { p <- Items.items if p.description === description } yield p
    val futureDBQuery : Future[Seq[DBItem]] = db.run(q2.result)
    val items = await(futureDBQuery)
    items.map(item => println(s"TableResultRunner.updateItemCost The item is now $item") )
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

Delete

Lastly we would like to delete an Item. So let’ see how we can do that. Again we use some Slick magic for this, where we use the .delete() function. Here is an example where I delete a random Item from the DB.

def deleteRandomItem(db: Database) = {

  async {
    val q =  Items.items.take(1)
    val futureDB : Future[Seq[DBItem]] = db.run(q.result)
    val sqlData = await(futureDB)
    val item = sqlData.head
    val deleteFuture : Future[Unit] = db.run(
      Items.items.filter(_.id === item.id).delete).map(_ => ())
    await(deleteFuture)
    println(s"TableResultRunner.deleteRandomItem() deleted item.Id ${item.id}")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

 

Calling A Stored Procedure

To call a stored procedure is a as simple as using the db session, and building out the call to the right stored procedure:

Say we have this stored procedure:

USE [SLICKTEST]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_SelectItemsByDescription]
    (
      @description NVARCHAR(MAX)
    )
AS
BEGIN
	SET NOCOUNT ON;

	select * from Items i where i.[Description] LIKE '%' + @description + '%'

END

GO


This is how we would call it using slick

def selectItems(db: Database, description: String): Unit = {

  val sqlStatement = db.source.createConnection().prepareCall(
    "{ call [dbo].[sp_SelectItemsByDescription](?) }",
    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)

  sqlStatement.setFetchDirection(ResultSet.FETCH_FORWARD);
  sqlStatement.setString("@desc", description)

  val rs = sqlStatement.executeQuery()

  while (rs.next()) {
    val item = new DBItem(
      rs.getInt("Id"),
      rs.getString("Description"),
      rs.getDouble("Cost"),
      rs.getInt("WarehouseLocationId"))

    println(s"StoredProcedureHelper.selectProducts " +
      "using description set to ${desc} got this result : " +
      s"Id: ${item.id}, Description: ${item.description}, " +
      s"Cost: ${item.cost}, WarehouseLocationId: ${item.warehouseLocationId}")
  }

  rs.close()
  sqlStatement.close()
}