Scala Doobie. Creating and Inserting into a Temp Table

Question

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)

Answer 1

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            )

Answer 2

I'm also banging my head against my laptop because of it.

It seems that Doobie does something strange with the sessions underneath. Temp tables are shared per session. In theory, they should have the same session so they should share temp tables but somehow they don't in Doobie.

[Edit2: It turned out that the session is the same for each query and the error is related to a known bug in JDBC for SQL Server related to metadata and some procedure sp_describe_undeclared_parameters that throws this exception]

I have a partial, hacky solution:

It would work if you used a global temp table (double ## instead of single #).

However, I'm not sure if you can use global temp tables in your use case. They are shared per connection.

You could make sure that the table name is random with global tables to avoid any issues with it:

val tableId = "e0c0544d"
Fragment.const(s"CREATE TABLE ##temp_${tableId} (tmp CHAR(20))").update.run.void

But it doesn't solve the original problem that Doobie does something weird with sessions and hence local temp tables don't work as they should. I'm also not sure if you don't need to drop this table explicitly to be safe so you might as well create a normal table...

Your solution is to glue 3 queries into one - unfortunately, it is not applicable in all cases. More often we would like to create a temp table in one sql"...", fill it from another sql"..." and do something it in another sql"..." and execute/transact together as one ConnectionIO using single .transact.

[Edit: Maybe it was a design choice. You can use local temp tables in the same query, use global temp tables in the one .transact.]

[Edit2: I found the solution by experimenting with it in Java. Seems that this error is thrown when you use PreparedStatement but it is all right for Statement. Probably doobie uses by default prepared statements for security (SQL injection). To have working local temp tables in Doobie you can use Doobie's high-level API to convert code to SQL Statement:]

val create: _root_.doobie.hi.ConnectionIO[Int] = 
   HC.createStatement(HS.executeUpdate(s"CREATE TABLE #temp (tmp CHAR(20));"))

With this, it will work.