CentOS 7
Sponsored Link

SQL Server 2017 : Node.js で利用する
2017/10/30
 
Node.js からの SQL Server 利用例です。
[1] 必要なモジュールをインストールしておきます。
[cent@dlp ~]$
node -v

v6.11.3
[cent@dlp ~]$
mkdir MssqlTest

[cent@dlp ~]$
cd MssqlTest

[cent@dlp MssqlTest]$
npm init -y

[cent@dlp MssqlTest]$
npm install tedious async

[2] テスト用のデータベース接続用ユーザーとデータベースを作成しておきます。
[cent@dlp ~]$
sqlcmd -S localhost -U SA

Password:
# ログインユーザー作成
1> create login cent with PASSWORD= N'password';
2> go

# [SampleDB] 作成
1> create database SampleDB;
2> go

1> use SampleDB;
2> go
Changed database context to 'SampleDB'.

# ログインユーザー [cent] と関連付けて DBユーザー作成
1> create user cent for login cent;
2> go

# [cent] にDBオーナーロール付与
1> exec sp_addrolemember 'db_owner', 'cent';
2> go

# テストテーブル作成
1> create table SampleTable (
2> ID int identity(1,1) not null primary key, First_Name NVARCHAR(50), Last_Name NVARCHAR(50)
3> );
4> insert into SampleTable (
5> First_Name, Last_Name) values (N'CentOS', N'Linux'), (N'RedHat', N'Linux'), (N'Fedora', N'Linux'
6> );
7> go
[3] 基本的な利用例です。データベースや接続ユーザーは上記で作成したものを使用します。
[cent@dlp ~]$
cd MssqlTest

[cent@dlp MssqlTest]$
vi use_mssql.js
var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
var async = require('async');

var config = {
  userName: 'cent',
  password: 'password',
  server: '127.0.0.1',
  options: {
    database: 'SampleDB'
  }
}

var connection = new Connection(config);

function Start(callback) {
    callback(null);
}

// SampleTable を Select
function Read(callback) {
    console.log('Reading from SampleTable...');

    request = new Request(
    'select * from SampleTable;',
    function(err, rowCount, rows) {
    if (err) {
        callback(err);
    } else {
        console.log(' \n');
        callback(null, 'Ubuntu', 'Linux');
    }
    });

    var result = "";
    request.on('row', function(columns) {
        columns.forEach(function(column) {
            if (column.value === null) {
                console.log('NULL');
            } else {
                result += column.value + " ";
            }
        });
        console.log(result);
        result = "";
    });

    connection.execSql(request);
}

// SampleTable に Insert
function Insert(first_name, last_name, callback) {
    console.log("Inserting '" + first_name + "' into SampleTable...");

    request = new Request(
        'insert into SampleTable (First_Name, Last_Name) output inserted.ID values (@First_Name, @Last_Name);',
        function(err, rowCount, rows) {
        if (err) {
            callback(err);
        } else {
            console.log(rowCount + ' row(s) inserted\n');
            callback(null, 'Redhat', 'Maipo');
        }
        });
    request.addParameter('First_Name', TYPES.NVarChar, first_name);
    request.addParameter('Last_Name', TYPES.NVarChar, last_name);

    connection.execSql(request);
}

// 特定の行を Update
function Update(first_name, last_name, callback) {
    console.log("Updating Last_Name to '" + last_name + "' for '" + first_name + "'...");

    request = new Request(
    'update SampleTable set Last_Name=@Last_Name where First_Name = @First_Name;',
    function(err, rowCount, rows) {
        if (err) {
        callback(err);
        } else {
        console.log(rowCount + ' row(s) updated\n');
        callback(null, 'Ubuntu');
        }
    });
    request.addParameter('First_Name', TYPES.NVarChar, first_name);
    request.addParameter('Last_Name', TYPES.NVarChar, last_name);

    connection.execSql(request);
}

// 特定の行を Delete
function Delete(first_name, callback) {
    console.log("Deleting '" + first_name + "' from Table...");

    request = new Request(
        'delete from SampleTable where First_Name = @First_Name;',
        function(err, rowCount, rows) {
        if (err) {
            callback(err);
        } else {
            console.log(rowCount + ' row(s) deleted\n');
            callback(null);
        }
        });
    request.addParameter('First_Name', TYPES.NVarChar, first_name);

    connection.execSql(request);
}

function Complete(err, result) {
    if (err) {
        callback(err);
    } else {
        console.log("Done!");
    }
}

connection.on('connect', function(err) {
  if (err) {
    console.log(err);
  } else {
    async.waterfall([
        Start,
        Read,
        Insert,
        Update,
        Delete,
        Read
    ], Complete)
  }
});

[cent@dlp MssqlTest]$
node use_mssql.js

Reading from SampleTable...
1 CentOS Linux
2 RedHat Linux
3 Fedora Linux

Inserting 'Ubuntu' into SampleTable...
1 row(s) inserted

Updating Last_Name to 'Maipo' for 'Redhat'...
1 row(s) updated

Deleting 'Ubuntu' from Table...
1 row(s) deleted

Reading from SampleTable...
1 CentOS Linux
2 RedHat Maipo
3 Fedora Linux

Done!
 
Tweet