SAP BTP - HANA Client를 이용하여 Node 인터페이스에 연결해보자.
경로를 하나 만들어주고 pacakge.json를 init
경로 구성
npm init -y
dlgn hana client를 npm으로 설치해준다
npm install @sap/hana-client
설치 확인
npm list
node 폴더로 이동하여 nodeQuery.js 생성
'use strict';
const { PerformanceObserver, performance } = require('perf_hooks');
var util = require('util');
var hana = require('@sap/hana-client');
var connOptions = {
//Option 1, retrieve the connection parameters from the hdbuserstore
serverNode: '@Nuruhee', //host, port, uid, and pwd retrieved from hdbuserstore
//Option 2, specify the connection parameters
// serverNode: '00e63a27-dda7-4603-9b32-8f894fc5c2f1.hana.trial-us10.hanacloud.ondemand.com:443',
// UID: 'USER1',
// PWD: 'Password1',
//Additional parameters
//As of 2.7 trace info can be directed to stdout or stderr
//traceFile: 'stdout',
//traceOptions: 'sql=warning',
//As of SAP HANA client 2.6, connections on port 443 enable encryption by default (HANA Cloud).
//encrypt: 'true', //Must be set to true when connecting to HANA as a Service
sslValidateCertificate: 'false', //Must be set to false when connecting to an SAP HANA, express edition instance that uses a self-signed certificate.
//For encrypted connections, the default crypto provider is mscrypto on Windows or openSSL on Linux or macos
//To use the SAP crypto provider, uncomment the below line.
//sslCryptoProvider: 'commoncrypto',
//As of SAP HANA client 2.6 for OpenSSL connections, the following settings can be ignored as root certificates are read from the default OS location.
//ssltruststore: '/home/dan/.ssl/trust.pem', //Used to specify where the trust store is located
//Alternatively provide the contents of the certificate directly (DigiCertGlobalRootCA.pem)
//DigiCert Global Root CA: https://cacerts.digicert.com/DigiCertGlobalRootCA.crt.pem used for SAP HANA cloud
//on-premise cert can be retrieved using openssl s_client -connect localhost:39015
//This option is not supported with the mscrypto provider (the default provider on Windows)
//ssltruststore: '-----BEGIN CERTIFICATE-----MIIDrzCCApegAwIBAgIQCDvgVpBCRrGhdWrJWZHHSjANBgkqhkiG9w0BAQUFADBhMQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQuY29tMSAwHgYDVQQDExdEaWdpQ2VydCBHbG9iYWwgUm9vdCBDQTAeFw0wNjExMTAwMDAwMDBaFw0zMTExMTAwMDAwMDBaMGExCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xIDAeBgNVBAMTF0RpZ2lDZXJ0IEdsb2JhbCBSb290IENBMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA4jvhEXLeqKTTo1eqUKKPC3eQyaKl7hLOllsBCSDMAZOnTjC3U/dDxGkAV53ijSLdhwZAAIEJzs4bg7/fzTtxRuLWZscFs3YnFo97nh6Vfe63SKMI2tavegw5BmV/Sl0fvBf4q77uKNd0f3p4mVmFaG5cIzJLv07A6Fpt43C/dxC//AH2hdmoRBBYMql1GNXRor5H4idq9Joz+EkIYIvUX7Q6hL+hqkpMfT7PT19sdl6gSzeRntwi5m3OFBqOasv+zbMUZBfHWymeMr/y7vrTC0LUq7dBMtoM1O/4gdW7jVg/tRvoSSiicNoxBN33shbyTApOB6jtSj1etX+jkMOvJwIDAQABo2MwYTAOBgNVHQ8BAf8EBAMCAYYwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUA95QNVbRTLtm8KPiGxvDl7I90VUwHwYDVR0jBBgwFoAUA95QNVbRTLtm8KPiGxvDl7I90VUwDQYJKoZIhvcNAQEFBQADggEBAMucN6pIExIK+t1EnE9SsPTfrgT1eXkIoyQY/EsrhMAtudXH/vTBH1jLuG2cenTnmCmrEbXjcKChzUyImZOMkXDiqw8cvpOp/2PV5Adg06O/nVsJ8dWO41P0jmP6P6fbtGbfYmbW0W5BjfIttep3Sp+dWOIrWcBAI+0tKIJFPnlUkiaY4IBIqDfv8NZ5YBberOgOzW6sRBc4L0na4UU+Krk2U886UAb3LujEV0lsYSEY1QSteDwsOoBrp+uvFRTp2InBuThs4pFsiv9kuXclVzDAGySj4dzp30d8tbQkCAUw7C29C79Fv1C5qfPrmAESrciIxpg0X40KPMbp1ZWVbd4=-----END CERTIFICATE-----'
};
//Synchronous example querying a table
var connection = hana.createConnection();
//As of 2.9, tracing can be directed to a callback
/*
var traceCB = function (buf) {
console.log(buf);
};
connection.onTrace("sql=error,api=debug,OutBufferSize=64k", traceCB);
*/
connection.connect(connOptions);
//connection.onTrace("", null); //disables callback tracing for the rest of the program
var sql = 'SELECT TITLE, FIRSTNAME, NAME FROM BJSYSTEMS.CUSTOMER;';
var t0 = performance.now();
var result = connection.exec(sql);
console.log(util.inspect(result, { colors: false }));
var t1 = performance.now();
console.log("time in ms " + (t1 - t0));
connection.disconnect();
이전에 만든 키로 변경해주어야 한다.
쿼리 부분도 만들었던 스키마로 변경해준다.
js 파일을 실행시켜보자.
node nodeQuery.js
DEBUG를 활성화 시켜서 실행시켜본다. (VS Code에서는 제대로 활성화 되지 않지만 cmd에서는 정상적으로 동작)
Connetion Pool을 사용하여 Synchronous APP을 만들어보자. node폴더 아래에 nodeQueryConnectionPool.js를 작성한다.
'use strict';
const { PerformanceObserver, performance } = require('perf_hooks');
var util = require('util');
var hana = require('@sap/hana-client');
var connOptions = {
//Option 1
serverNode: '@Nuruhee'
//Option 2
// serverNode: '00e63a27-dda7-4603-9b32-8f894fc5c2f1.hana.trial-us10.hanacloud.ondemand.com:443',
// UID: 'USER1',
// PWD: 'Password1',
};
var poolProperties = {
poolCapacity: 10,
maxConnectedOrPooled: 20,
pingCheck: false,
allowSwitchUser: true,
maxPooledIdleTime: 3600,
}
var pool = null;
queryTable(false, "1st Run");
queryTable(false, "2nd Run");
queryTable(true, "1st Run");
queryTable(true, "2nd Run");
queryTable(true, "3rd Run", true); // change user
console.log("Connections in use : " + pool.getInUseCount());
console.log("Connections in the pool : " + pool.getPooledCount());
function queryTable(usePool, run, user2) {
var t0 = performance.now();
var connection = null;
if (usePool) {
var t0 = performance.now();
if (pool === null) {
pool = hana.createPool(connOptions, poolProperties);
}
if (user2) {
connection = pool.getConnection('USER2', 'Password2');
}
else {
connection = pool.getConnection();
}
var t1 = performance.now();
}
else {
connection = hana.createConnection();
connection.connect(connOptions);
var t1 = performance.now();
}
var t2 = performance.now();
var sql = 'SELECT CURRENT_USER FROM DUMMY;';
var result = connection.exec(sql);
var t3 = performance.now();
var t4 = performance.now();
console.log(util.inspect(result, { colors: false }));
var t5 = performance.now();
var t6 = performance.now();
connection.disconnect();
var t7 = performance.now();
console.log("Connection Pool Enable : " + usePool + " " + run);
console.log("===============================================");
console.log("Connection time in ms : " + (t1 - t0));
console.log("Query time in ms : " + (t3 - t2));
console.log("Display time in ms : " + (t5 - t4));
console.log("Disconnect time in ms : " + (t7 - t6));
console.log("Total time in ms : " + (t7 - t0) + "\n");
}
명령어를 실행시켜보면
node nodeQueryConnectionPool.js
아래와 같은 결과가 나온다.
[ { CURRENT_USER: 'USER1' } ]
Connection Pool Enable : false 1st Run
===============================================
Connection time in ms : 2217.7769000530243
Query time in ms : 202.65929996967316
Display time in ms : 4.33050000667572
Disconnect time in ms : 0.4077000617980957
Total time in ms : 2425.1993000507355
[ { CURRENT_USER: 'USER1' } ]
Connection Pool Enable : false 2nd Run
===============================================
Connection time in ms : 2185.944999933243
Query time in ms : 190.20969998836517
Display time in ms : 1.0742000341415405
Disconnect time in ms : 0.5641000270843506
Total time in ms : 2377.8408999443054
[ { CURRENT_USER: 'USER1' } ]
Connection Pool Enable : true 1st Run
===============================================
Connection time in ms : 2213.4205000400543
Query time in ms : 189.6405999660492
Display time in ms : 0.2539999485015869
Disconnect time in ms : 192.43669998645782
Total time in ms : 2595.7734999656677
[ { CURRENT_USER: 'USER1' } ]
Connection Pool Enable : true 2nd Run
===============================================
Connection time in ms : 0.06330001354217529
Query time in ms : 200.4617999792099
Display time in ms : 0.5311000347137451
Disconnect time in ms : 195.1325000524521
Total time in ms : 396.19840002059937
[ { CURRENT_USER: 'USER1' } ]
Connection Pool Enable : true 3rd Run
===============================================
Connection time in ms : 2434.7738000154495
Query time in ms : 190.13090002536774
Display time in ms : 0.6014000177383423
Disconnect time in ms : 193.97010004520416
Total time in ms : 2819.4958000183105
Connections in use : 0
Connections in the pool : 1
Callback을 사용하여 Synchronous APP을 만들어보자. node 폴더 아래 nodeQueryCallback.js 파일 생성 및 실행
'use strict'
var util = require('util');
var hana = require('@sap/hana-client');
var connOptions = {
//Option 1
serverNode: '@Nuruhee',
//Option 2
// serverNode: '00e63a27-dda7-4603-9b32-8f894fc5c2f1.hana.trial-us10.hanacloud.ondemand.com:443',
// UID: 'USER1',
// PWD: 'Password1',
sslValidateCertificate: 'false',
};
var connection = hana.createConnection();
connection.connect(connOptions, function (err) {
if (err) {
return console.error(err);
}
const statement = connection.prepare('CALL BJSYSTEMS.SHOW_RESERVATIONS(?,?)');
const parameters = [11, '2020-12-24'];
var results = statement.execQuery(parameters, function (err, results) {
if (err) {
return console.error(err);
}
processResults(results, function (err) {
if (err) {
return console.error(err);
}
results.close(function (err) {
if (err) {
return console.error(err);
}
statement.drop(function (err) {
if (err) {
return console.error(err);
}
return connection.disconnect(function (err) {
if (err) {
return console.error(err);
}
});
});
});
});
});
});
function processResults(results, cb) {
results.next(function (err, hasValues) {
if (err) {
return console.error(err)
}
if (hasValues) {
results.getValues(function (err, row) {
console.log(util.inspect(row, { colors: false }));
processResults(results, cb);
})
}
else {
return cb();
}
});
}
결과
다음으로 Promise를 사용하여 Asynchronous APP을 만들어보자 node 폴더아래에 nodeQueryPromise.js 파일을 만들고 실행시켜보자.
'use strict';
var util = require('util');
var hana = require('@sap/hana-client');
var PromiseModule = require('@sap/hana-client/extension/Promise.js');
const { resolve } = require('path');
var connOptions = {
//Option 1
// serverNode: '@Nuruhee',
//Option 2
serverNode: '1e48300b-3b35-4003-8340-aa8f2ffb3cc1.hana.trial-us10.hanacloud.ondemand.com:443',
UID: 'USER1',
PWD: 'Password1',
sslValidateCertificate: 'false',
};
var connection = hana.createConnection();
var statement;
PromiseModule.connect(connection, connOptions)
.then(() => {
return PromiseModule.prepare(connection, 'CALL BJSYSTEMS.SHOW_RESERVATIONS(?,?)');
})
.then((stmt) => {
statement = stmt;
const parameters = [11, '2020-12-24'];
return PromiseModule.executeQuery(stmt, parameters);
})
.then((results) => {
return processResults(results);
})
.then((results) => {
return PromiseModule.close(results);
})
.then(() => {
PromiseModule.drop(statement);
})
.then(() => {
PromiseModule.disconnect(connection);
})
.catch(err => {
console.error(err);
});
function processResults(results) {
return new Promise((resolve, reject) => {
var done = false;
PromiseModule.next(results)
.then((hasValues) => {
if (hasValues) {
return PromiseModule.getValues(results);
}
else {
done = true;
}
})
.then((values) => {
if (done) {
resolve(results);
}
else {
console.log(util.inspect(values, { colors: true }));
processResults(results)
.then((results) => {
resolve(results);
});
}
})
.catch(err => {
reject(err);
});
});
}
결과
다음으로 TypeScript를 사용해보자
설치했던 hanaclient의 경로로 이동하여 node/lib에 보면 index.d.ts파일이 있다.
파일을 열어 setClientInfo가 있는지 확인.
기존에 작업하던 프로젝트 폴더의 node로 이동하여 nodeQueryTS.ts 파일을 만든 후 실행시켜준다.
"use strict";
import * as hana from '@sap/hana-client';
var connection: hana.Connection = hana.createConnection();
var connOptions: hana.ConnectionOptions = {
serverNode: '@Nuruhee', //host, port, uid, and pwd retrieved from hdbuserstore
//serverNode: '123456-7890-400a-8bbd-41097dfd15ae.hana0.prod-us10.hanacloud.ondemand.com:443',
//UID: 'USER1',
//PWD: 'Password1'
};
connection.connect(connOptions);
connection.setClientInfo("CURRENT_YEAR", "2023"); //should be "2023"
console.log("Year session value is " + connection.getClientInfo("CURRENT_YEAR"));
var sql1: string = "SELECT TITLE, FIRSTNAME, NAME FROM BJSYSTEMS.CUSTOMER WHERE FIRSTNAME LIKE ?";
var statement: hana.Statement = connection.prepare(sql1);
var result1: hana.ResultSet = statement.executeQuery(['M%']);
var moreData: boolean = result1.next();
while (moreData) {
var row : {[key: string]: string} = result1.getValues();
console.log(row);
moreData = result1.next();
}
connection.disconnect();
TypeScript의 버전을 확인하는 명령어는 아래와 같다. (설치가 되어 있지 않다면 설치를 해줘야 한다.)
tsc -version
npm을 이용하여 TypeScript를 설치하고, 모듈을 추가해준다.
npm install -g typescript
npm info @types/node
typescript를 실행시켜주면, typescript가 js로 변환되어 만들어진다.
tsc nodeQueryTs.ts