-
Notifications
You must be signed in to change notification settings - Fork 0
/
odbc.js
329 lines (283 loc) · 9.66 KB
/
odbc.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
// Copyright 2020 IBM
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
// http://www.apache.org/licenses/LICENSE-2.0
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
module.exports = function(RED) {
const odbc = require('odbc');
const process = require('process');
function odbcPool(config) {
RED.nodes.createNode(this, config);
// Pass a poolConfig object to the odbc.pool function. If the values are not
// set on the config object, they will get set to `undefined`, in which case
// odbc.pool will set them to the defaults during its execution.
this.poolConfig = config;
this.pool = null;
this.connecting = false;
this.connect = async () => {
let connection;
if (this.pool == null) {
try {
this.pool = await odbc.pool(this.poolConfig);
this.connecting = false;
} catch (error) {
throw(error);
}
}
try {
connection = await this.pool.connect();
} catch (error) {
throw(error);
}
return connection;
}
}
RED.nodes.registerType('ODBC pool', odbcPool);
function odbcQuery(config) {
RED.nodes.createNode(this, config);
this.poolNode = RED.nodes.getNode(config.connection);
this.queryString = config.query;
this.outfield = config.outField;
this.name = config.name;
this.runQuery = async function(message, send, done) {
let connection;
try {
connection = await this.poolNode.connect();
} catch (error) {
if (error) {
this.error(error);
this.status({fill: "red", shape: "ring", text: error.message});
if (done) {
// Node-RED 1.0 compatible
done(error);
} else {
// Node-RED 0.x compatible
node.error(error, message);
}
}
}
this.status({
fill:"blue",
shape:"dot",
text:"querying..."
});
let parameters = undefined;
let result;
// Check if there is a payload.
// If yes, obtain the query and/or parameters from the payload
// If no, use the node's predefined query
if (message.payload) {
// If the payload is a string, convert to JSON object and get the query
// and/or parameters
if (typeof message.payload == 'string')
{
let payloadJSON;
try {
// string MUST be valid JSON, else fill with error.
// TODO: throw error?
payloadJSON = JSON.parse(message.payload);
} catch (error) {
this.status({fill: "red", shape: "ring", text: error.message});
connection.close();
if (done) {
// Node-RED 1.0 compatible
done(error);
} else {
// Node-RED 0.x compatible
node.error(error, message);
}
}
parameters = payloadJSON.parameters || undefined;
this.queryString = payloadJSON.query || this.queryString;
}
// If the payload is an object, get the query and/or parameters directly
// from the object
else if (typeof message.payload == 'object') {
parameters = message.payload.parameters || undefined;
this.queryString = message.payload.query || this.queryString;
}
}
try {
result = await connection.query(this.queryString, parameters);
} catch (error) {
this.error(error);
this.status({fill: "red", shape: "ring", text: error.message});
connection.close();
if (done) {
// Node-RED 1.0 compatible
done(error);
} else {
// Node-RED 0.x compatible
node.error(error, message);
}
}
connection.close();
message.payload = result;
send(message);
connection.close();
this.status({fill:'green',shape:'dot',text:'ready'});
if (done) {
done();
}
}
this.checkPool = async function(message, send, done) {
if (this.poolNode.connecting) {
setTimeout(() => {
this.checkPool(message, send, done);
}, 1000);
return;
}
// On initialization, pool will be null. Set connecting to true so that
// other nodes are immediately blocked, then call runQuery (which will
// actually do the pool initialization)
if (this.poolNode.pool == null) {
this.poolNode.connecting = true;
}
await this.runQuery(message, send, done);
}
this.on('input', this.checkPool);
this.status({fill:'green',shape:'dot',text:'ready'});
}
RED.nodes.registerType("ODBC query", odbcQuery);
function odbcProcedure(config) {
RED.nodes.createNode(this, config);
this.poolNode = RED.nodes.getNode(config.connection);
this.catalog = config.catalog;
this.schema = config.schema;
this.procedure = config.procedure;
this.parameters = config.parameters;
this.outfield = config.outField;
// If parameters were passed in through the config, they are a string.
// Need to convert the string to an actual JavaScript array
if (this.parameters) {
try {
this.parameters = JSON.parse(this.parameters);
} catch (error) {
this.status({fill:'red',shape:'ring',text: error.message});
return;
}
}
// If catalog evaluates to false, convert the value to null, as the
// odbc connector expects
if (!this.catalog) {
this.catalog = null;
}
// If schema evaluates to false, convert the value to null, as the
// odbc connector expects
if (!this.schema) {
this.schema = null;
}
this.runProcedure = async function(message, send, done) {
let connection;
let catalog = this.catalog;
let schema = this.schema;
let procedure = this.procedure;
let parameters = this.parameters;
try {
connection = await this.poolNode.connect();
} catch (error) {
if (error) {
this.error(error);
this.status({fill: "red", shape: "ring", text: error.message});
if (done) {
// Node-RED 1.0 compatible
done(error);
} else {
// Node-RED 0.x compatible
node.error(error, message);
}
}
}
this.status({
fill:"blue",
shape:"dot",
text:"running procedure..."
});
let result;
const payload = message.payload;
// Check if there is a payload.
// If yes, obtain the catalog, schema, table and/or parameters from the
// payload
// If no, use the node's predefined values
if (payload) {
// If the payload is a string, convert to JSON object and get the
// catalog, schema, table, and/or parameters.
if (typeof payload == 'string')
{
let payloadJSON;
try {
// string MUST be valid JSON, else fill with error.
// TODO: throw error?
payloadJSON = JSON.parse(payload);
} catch (error) {
this.status({fill: "red", shape: "ring", text: error.message});
connection.close();
if (done) {
// Node-RED 1.0 compatible
done(error);
} else {
// Node-RED 0.x compatible
node.error(error, message);
}
}
catalog = payloadJSON.catalog || catalog;
schema = payloadJSON.schema || schema;
proceudre = payloadJSON.procedure || procedure;
parameters = payloadJSON.parameters || parameters;
}
// If the payload is an object, get the catalog, schema, table, and/or
// parameters directly from the object
else if (typeof payload == 'object') {
catalog = payload.catalog || catalog;
schema = payload.schema || schema;
procedure = payload.procedure || procedure;
parameters = payload.parameters || parameters;
}
}
try {
result = await connection.callProcedure(catalog, schema, procedure, parameters);
} catch (error) {
this.error(error);
this.status({fill: "red", shape: "ring", text: error.odbcErrors[0].message});
connection.close();
if (done) {
// Node-RED 1.0 compatible
done(error);
} else {
// Node-RED 0.x compatible
node.error(error, message);
}
}
connection.close();
message.payload = result;
send(message);
connection.close();
this.status({fill:'green',shape:'dot',text:'ready'});
if (done) {
done();
}
}
this.checkPool = async function(message, send, done) {
if (this.poolNode.connecting) {
setTimeout(() => {
this.checkPool(message, send, done);
}, 1000);
return;
}
// On initialization, pool will be null. Set connecting to true so that
// other nodes are immediately blocked, then call runProcedure (which will
// actually do the pool initialization)
if (this.poolNode.pool == null) {
this.poolNode.connecting = true;
}
await this.runProcedure(message, send, done);
}
this.on('input', this.runProcedure);
}
RED.nodes.registerType("ODBC procedure", odbcProcedure);
}