Scala Doobie. Creating and Inserting into a Temp Table-CodePudding

I am relatively new to Scala and also new to Doobie. I am connecting to SQL Server 2014 and need to create a temp table and subsequently insert into that temp table. In SQL Server, when you create a temp table, and the connection is severed, the temp table is automatically deleted.

In the following snippet, I am getting this exception:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException:  Invalid object name '#temp'

The snippet:

  val create: doobie.ConnectionIO[Int] = sql"CREATE TABLE #temp (tmp CHAR(20))".update.run

  val insert: doobie.ConnectionIO[Int] = sql"INSERT INTO #temp values ('abc'), ('def')".update.run

  val query: doobie.ConnectionIO[List[String]] = sql"select * from #temp  ".query[String].to[List]

  def wrapper(): ConnectionIO[List[String]] = {
    for {
      c <- create
      i <- insert
      q <- query
    } yield q
  }

wrapper().transact(xa).debug.as(ExitCode.Success)

I believe this is telling me that Doobie is dropping the connection between the create and insert statements?

The expected/desired behavior is that it will return a List("abc","def").

Thanks in advance for any help!

Update:

Here's a small example of what I know is in fact working:

  val create = sql"CREATE TABLE #temp (tmp CHAR(20))"

  val insert: doobie.ConnectionIO[Int] = sql"INSERT INTO #temp values ('abc'), ('def')"


(create    insert).update.run.transact(xa).debug.as(ExitCode.Success)

(Note that it only works with the create and insert part and not the query part)

CodePudding user response：

After 1 week of banging my head against my laptop...I finally figured it out. Doobie will actually perform "update" commands when doing .query:

  val create: Fragment = sql"CREATE TABLE #temp (tmp CHAR(20))"

  val insert: Fragment = sql"INSERT INTO #temp values ('abc'), ('def')"

  val query: Fragment = sql"select * from #temp  "

  (create    insert    query).query[String].to[List].transact(xa).debug.as(ExitCode.Success)

Outputs:

List(abc             ,def            )