Node.js
SQLServer
Azure
AzureFunctions

SQL Server の一時テーブルをNode.jsから使う時の注意点

More than 1 year has passed since last update.

SQL Server にある一時テーブルは、CSVファイルなど外部にある情報を一時的に取り込んでSQLとしてDB内にあるデータと合わせて処理したい時に便利です。

Azure SQL Databaseでも利用可能であり、Blobストレージにあるファイルの取り込み先としても利用できます。

Node.jsでAzure Functionsの関数を実装している時に、SQL Serverのクライアントmssqlでハマったことについて書きます。


TLTR :clock10:

一時テーブルを作る時はCREATE文をqueryじゃなくて、batchを使う。

以上。


サンプルコード


素直に書くと動かない

SQL文の実行はqueryメソッドを使う。

だから、一時テーブルの作成もqueryメソッドで実行する。


sample.js

const sql = require('mssql');

(async () => {
try {
var connection = await sql.connect({
user: '**',
password: '**',
server: '**',
database: '**'
})
var request = connection.request();
// Create a temporary table #tmp_users
await request.query('CREATE TABLE #tmp_users (name NVARCHAR(100) NOT NULL)')

const names = ['Su', 'Yui', 'Moa']
names.forEach(async (name) => {
await request.query(`INSERT INTO #tmp_users VALUES ('${name}')`)
})

const result = await request.query('SELECT * FROM #tmp_users')
console.log(result)
} catch(err) {
console.error(err)
}
})();


しかし、素直にqueryでSQL文を送ってみるも、期待どおりに動かない。どわーっとエラーが出て来るのだけど、肝心なところだけ見ると、どうやら作ったはずの一時テーブル#tmp_usersが無いようだ。

$ node sample.js

...
RequestError: Invalid object name '#tmp_users'.
...


動く


sample.js

const sql = require('mssql');

(async () => {
try {
var connection = await sql.connect({
user: '**',
password: '**',
server: '**',
database: '**'
})
var request = connection.request();
// Create a temporary table #tmp_users
await request.batch('CREATE TABLE #tmp_users (name NVARCHAR(100) NOT NULL)')

const names = ['Su', 'Yui', 'Moa']
names.forEach(async (name) => {
await request.query(`INSERT INTO #tmp_users VALUES ('${name}')`)
})

const result = await request.query('SELECT * FROM #tmp_users')
console.log(result)
} catch(err) {
console.error(err)
}
})();


queryではなく、batchという別のメソッドを使うと期待した通りに動く。


何が起きているのか

色々調べてみたので、まとめてみる。


そもそも一時テーブルとは :clock1:

一時的な用途で使うためのテーブルのことで、作成時のセッションの中でだけ存在できる。

セッションを切った時に、SQL Serverによって自動的にDROP TABLEが実行される。

ローカル一時テーブルは、接続しているセッションの中でだけ参照できる。一方で、グローバルは他のセッションからでも参照ができるが、ローカルと同様に、その一時テーブルを作成したセッションを切った時に削除される。


で、Node.jsのクライアントmssqlquerybatchって何だ :question:

mssqlREADMEにbatchの記述があるので、そこを見ると。


Execute the SQL command. Unlike query, it doesn't use sp_executesql, so is not likely that SQL Server will reuse the execution plan it generates for the SQL.


とある。queryは内部でsp_executesqlという動的にSQLを実行するための、システムが予め持っているストアドプロシージャを使っているようだ。


sp_executesql に渡されるSQLは別セッションで実行される。

sp_executesqlと一時テーブルに関するこの記事によると、


Any dynamic SQL execution with either exec() or sp_executeSQL is ran in

a child session/scope of the current one.


ということで、異なるセッションで実行されるそうだ。


まとめ


  • ローカル一時テーブルはセッション内でしか存在できず、かつそのセッション内でしか参照できない


  • queryで実行されるSQLはそこまでのセッションとは異なるセッションで実行される。

  • すなわち、queryでローカル一時テーブルを作成しても、即座に消えてしまう。

ということがわかりました。つまり、ローカル一時テーブルを作成する時はbatchで実行しましょうということです :smile:


参考