Support for #temp tables across requests with same connection

[jeremyrsellars]

I can only use temporary tables inside the same request that creates the table. This works:

con.execSql new (tedious.Request) "CREATE TABLE #Tags (TagName varchar(50) NOT NULL)
   SELECT * FROM #Tags", (err, rowCount) =>

But this fails with Invalid object name '#Tags'.:

con.execSql new (tedious.Request) "CREATE TABLE #Tags (TagName varchar(50) NOT NULL)", (err, rowCount) =>
   con.execSql new (tedious.Request) "SELECT * FROM #Tags", (err, rowCount) =>

Full sample code:

tedious = require 'tedious'

console.logInGold = (message) -> console.log '\u001b[33m', message, '\u001b[0m'

connectOptions =
   userName: 'sa'
   password: 'sa'
   server:'localhost'

printCountText = "
   DECLARE @ct int
   SELECT @ct = count(*) FROM #Tags
   PRINT 'Count of #Tags: ' + Convert(VarChar(10), @ct)
   "
createTempTableText = "
   CREATE TABLE #Tags (TagName varchar(50) NOT NULL)
   PRINT '#Tags table created'
   " + printCountText
insertRowText = "
   INSERT #Tags VALUES ('tag1')
   " + printCountText

con = new(tedious.Connection) connectOptions
con.on 'connect', (err) => 
   con.on 'infoMessage', (msg) -> console.log msg.message
   console.logInGold err if err?
   con.execSql new (tedious.Request) createTempTableText, (err, rowCount) =>
      console.logInGold err if err?
      con.execSql new (tedious.Request) insertRowText, (err, rowCount) => 
         console.logInGold err if err?
         con.close()

[pekim]

I'm quite surprised about this. It should just work. Some of the integration tests use temporary stored procedures, which should be connection-scoped in the same way.

Thanks for the code. I'll look in to this.

[jeremyrsellars]

I sure appreciate your work on Tedious and this issue. I hope I'm not sending you down a rabbit trail, but it's not working on my machine.

In case this matters, I wasn't able to use NPM to install it, so I downloaded the source as a .zip from github and copied it to my node_modules directory... a few days after you release v4.

Thanks,
Jeremy

-----Original Message-----
From: Mike D Pilsbury [mailto:reply@reply.github.com]
Sent: Monday, April 23, 2012 10:52 PM
To: Jeremy R. Sellars
Subject: Re: [tedious] Support for #temp tables across requests with same connection (#24)

I'm quite surprised about this. It should just work. Some of the integration tests use temporary stored procedures, which should be connection-scoped in the same way.

Thanks for the code. I'll look in to this.

---

Reply to this email directly or view it on GitHub:
#24 (comment)

[pekim]

The problem relates to the scoping rules used for temporary tables (http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx). Starting with tedious v0.0.4, the execSql function executes sp_executesql to execute the sql. This is to provide support for parameters.

If execSql is replaced with execSqlBatch then your code works. Because of the scoping rules, only the table creation needs to be changed, although it would do no harm if both calls were changed.

I need to make the documentation for these two function clearer. I also wonder whether the function names are appropriate.

[jeremyrsellars]

Great that works in my 'real' code, as well. Thanks for your help with this.

• Jeremy

[pekim]

Documentation updated.