370 lines
11 KiB
JavaScript
370 lines
11 KiB
JavaScript
/* Copyright (c) 2015, 2023, Oracle and/or its affiliates. */
|
|
|
|
/******************************************************************************
|
|
*
|
|
* This software is dual-licensed to you under the Universal Permissive License
|
|
* (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
|
|
* 2.0 as shown at https://www.apache.org/licenses/LICENSE-2.0. You may choose
|
|
* either license.
|
|
*
|
|
* If you elect to accept the software under the Apache License, Version 2.0,
|
|
* the following applies:
|
|
*
|
|
* 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
|
|
*
|
|
* https://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.
|
|
*
|
|
* NAME
|
|
* 56. fetchAs.js
|
|
*
|
|
* DESCRIPTION
|
|
* Testing driver fetchAs feature.
|
|
*
|
|
*****************************************************************************/
|
|
'use strict';
|
|
|
|
const oracledb = require ('oracledb');
|
|
const assert = require('assert');
|
|
const dbConfig = require ('./dbconfig.js');
|
|
const assist = require ('./dataTypeAssist.js');
|
|
|
|
|
|
describe('56. fetchAs.js', function() {
|
|
|
|
let connection = null;
|
|
beforeEach('get one connection', async function() {
|
|
connection = await oracledb.getConnection(dbConfig);
|
|
});
|
|
|
|
afterEach('release connection, reset fetchAsString property', async function() {
|
|
oracledb.fetchAsString = [];
|
|
await connection.close();
|
|
});
|
|
|
|
it('56.1 property value check', function() {
|
|
|
|
assert.deepStrictEqual(oracledb.fetchAsString, []);
|
|
|
|
oracledb.fetchAsString = [oracledb.DATE];
|
|
assert.deepStrictEqual(oracledb.fetchAsString, [ oracledb.DATE ]);
|
|
|
|
oracledb.fetchAsString = [ oracledb.NUMBER ];
|
|
assert.deepStrictEqual(oracledb.fetchAsString, [ oracledb.NUMBER ]);
|
|
|
|
oracledb.fetchAsString = [ oracledb.DATE, oracledb.NUMBER ];
|
|
assert.deepStrictEqual(oracledb.fetchAsString, [ oracledb.DATE, oracledb.NUMBER ]);
|
|
|
|
oracledb.fetchAsString = [ oracledb.DB_TYPE_JSON ];
|
|
assert.deepStrictEqual(oracledb.fetchAsString, [ oracledb.DB_TYPE_JSON ]);
|
|
|
|
oracledb.fetchAsString = [ oracledb.DB_TYPE_JSON, oracledb.DATE, oracledb.NUMBER ];
|
|
assert.deepStrictEqual(oracledb.fetchAsString, [ oracledb.DB_TYPE_JSON, oracledb.DATE, oracledb.NUMBER ]);
|
|
});
|
|
|
|
it('56.2 Fetch DATE column values as STRING - by-Column name', async function() {
|
|
const result = await connection.execute(
|
|
"SELECT TO_DATE('2005-01-06', 'YYYY-DD-MM') AS TS_DATE FROM DUAL",
|
|
[],
|
|
{
|
|
outFormat: oracledb.OUT_FORMAT_OBJECT,
|
|
fetchInfo: { "TS_DATE": { type: oracledb.STRING } }
|
|
}
|
|
);
|
|
assert.strictEqual(typeof result.rows[0].TS_DATE, "string");
|
|
});
|
|
|
|
it('56.3 Fetch DATE, NUMBER column values STRING - by Column-name', async function() {
|
|
const result = await connection.execute(
|
|
"SELECT 1234567 AS TS_NUM, TO_TIMESTAMP('1999-12-01 11:10:01.00123', 'YYYY-MM-DD HH:MI:SS.FF') AS TS_DATE FROM DUAL",
|
|
[],
|
|
{
|
|
outFormat: oracledb.OUT_FORMAT_OBJECT,
|
|
fetchInfo:
|
|
{
|
|
"TS_DATE": { type: oracledb.STRING },
|
|
"TS_NUM": { type: oracledb.STRING }
|
|
}
|
|
}
|
|
);
|
|
assert.strictEqual(typeof result.rows[0].TS_DATE, "string");
|
|
assert.strictEqual(typeof result.rows[0].TS_NUM, "string");
|
|
assert.strictEqual(Number(result.rows[0].TS_NUM), 1234567);
|
|
});
|
|
|
|
it('56.4 Fetch DATE, NUMBER as STRING by-time configuration and by-name', async function() {
|
|
oracledb.fetchAsString = [ oracledb.DATE, oracledb.NUMBER ];
|
|
|
|
const result = await connection.execute(
|
|
"SELECT 1234567 AS TS_NUM, TO_TIMESTAMP('1999-12-01 11:10:01.00123', 'YYYY-MM-DD HH:MI:SS.FF') AS TS_DATE FROM DUAL",
|
|
[],
|
|
{
|
|
outFormat: oracledb.OUT_FORMAT_OBJECT,
|
|
fetchInfo:
|
|
{
|
|
"TS_DATE": { type: oracledb.STRING },
|
|
"TS_NUM": { type: oracledb.STRING }
|
|
}
|
|
}
|
|
);
|
|
assert.strictEqual(typeof result.rows[0].TS_DATE, "string");
|
|
assert.strictEqual(typeof result.rows[0].TS_NUM, "string");
|
|
assert.strictEqual(Number(result.rows[0].TS_NUM), 1234567);
|
|
});
|
|
|
|
it('56.5 Fetch DATE, NUMBER column as STRING by-type and override at execute time', async function() {
|
|
oracledb.fetchAsString = [ oracledb.DATE, oracledb.NUMBER ];
|
|
|
|
const result = await connection.execute(
|
|
"SELECT 1234567 AS TS_NUM, TO_TIMESTAMP('1999-12-01 11:10:01.00123', 'YYYY-MM-DD HH:MI:SS.FF') AS TS_DATE FROM DUAL",
|
|
[],
|
|
{
|
|
outFormat: oracledb.OUT_FORMAT_OBJECT,
|
|
fetchInfo:
|
|
{
|
|
"TS_DATE": { type: oracledb.DEFAULT },
|
|
"TS_NUM": { type: oracledb.STRING }
|
|
}
|
|
}
|
|
);
|
|
assert.strictEqual(typeof result.rows[0].TS_DATE, "object");
|
|
assert.strictEqual(typeof result.rows[0].TS_NUM, "string");
|
|
assert.strictEqual(Number(result.rows[0].TS_NUM), 1234567);
|
|
});
|
|
|
|
it('56.6 Fetch ROWID column values STRING - non-ResultSet', async function() {
|
|
const result = await connection.execute(
|
|
"SELECT ROWID from DUAL",
|
|
[],
|
|
{
|
|
outFormat: oracledb.OUT_FORMAT_OBJECT,
|
|
fetchInfo:
|
|
{
|
|
"ROWID": { type: oracledb.STRING }
|
|
}
|
|
}
|
|
);
|
|
assert.strictEqual(typeof result.rows[0].ROWID, "string");
|
|
});
|
|
|
|
it('56.7 Fetch ROWID column values STRING - ResultSet', async function() {
|
|
const result = await connection.execute(
|
|
"SELECT ROWID from DUAL",
|
|
[],
|
|
{
|
|
outFormat: oracledb.OUT_FORMAT_OBJECT,
|
|
resultSet: true,
|
|
fetchInfo:
|
|
{
|
|
"ROWID": { type: oracledb.STRING }
|
|
}
|
|
}
|
|
);
|
|
const row = await result.resultSet.getRow();
|
|
assert.strictEqual(typeof row.ROWID, "string");
|
|
await result.resultSet.close();
|
|
});
|
|
|
|
/*
|
|
* The maximum safe integer in JavaScript is (2^53 - 1).
|
|
* The minimum safe integer in JavaScript is (-(2^53 - 1)).
|
|
* Numbers out of above range will be rounded.
|
|
* The last element is out of Oracle database standard Number range. It will be rounded by database.
|
|
*/
|
|
const numStrs =
|
|
[
|
|
'17249138680355831',
|
|
'-17249138680355831',
|
|
'0.17249138680355831',
|
|
'-0.17249138680355831',
|
|
'0.1724913868035583123456789123456789123456'
|
|
];
|
|
|
|
const numResults =
|
|
[
|
|
'17249138680355831',
|
|
'-17249138680355831',
|
|
'.17249138680355831',
|
|
'-.17249138680355831',
|
|
'.172491386803558312345678912345678912346'
|
|
];
|
|
|
|
it('56.8 large numbers with fetchInfo', async function() {
|
|
for (const element of numStrs) {
|
|
const result = await connection.execute(
|
|
"SELECT TO_NUMBER( " + element + " ) AS TS_NUM FROM DUAL",
|
|
[],
|
|
{
|
|
outFormat: oracledb.OUT_FORMAT_OBJECT,
|
|
fetchInfo:
|
|
{
|
|
"TS_NUM": { type: oracledb.STRING }
|
|
}
|
|
}
|
|
);
|
|
assert.strictEqual(typeof result.rows[0].TS_NUM, "string");
|
|
assert.strictEqual(result.rows[0].TS_NUM, numResults[numStrs.indexOf(element)]);
|
|
}
|
|
});
|
|
|
|
it('56.9 large numbers with setting fetchAsString property', async function() {
|
|
oracledb.fetchAsString = [ oracledb.NUMBER ];
|
|
|
|
for (const element of numStrs) {
|
|
const result = await connection.execute(
|
|
"SELECT TO_NUMBER( " + element + " ) AS TS_NUM FROM DUAL",
|
|
[],
|
|
{ outFormat: oracledb.OUT_FORMAT_OBJECT }
|
|
);
|
|
assert.strictEqual(typeof result.rows[0].TS_NUM, "string");
|
|
assert.strictEqual(result.rows[0].TS_NUM, numResults[numStrs.indexOf(element)]);
|
|
}
|
|
});
|
|
|
|
// FetchInfo format should <columName> : {type : oracledb.<type> }
|
|
it('56.10 invalid syntax for type should result in error', async function() {
|
|
await assert.rejects(
|
|
async () => {
|
|
await connection.execute(
|
|
"SELECT SYSDATE AS THE_DATE FROM DUAL",
|
|
{ },
|
|
{ fetchInfo: { "THE_DATE": oracledb.STRING }}
|
|
);
|
|
},
|
|
// NJS-015: type was not specified for conversion
|
|
/ NJS-015:/
|
|
);
|
|
});
|
|
|
|
it('56.11 assigns an empty array to fetchAsString', function() {
|
|
oracledb.fetchAsString = [];
|
|
assert.deepStrictEqual(oracledb.fetchAsString, []);
|
|
});
|
|
|
|
it('56.12 Negative - empty string', function() {
|
|
assert.throws(
|
|
function() {
|
|
oracledb.fetchAsString = '';
|
|
},
|
|
/NJS-004:/
|
|
);
|
|
});
|
|
|
|
it('56.13 Negative - null', function() {
|
|
assert.throws(
|
|
function() {
|
|
oracledb.fetchAsString = null;
|
|
},
|
|
/NJS-004:/
|
|
);
|
|
});
|
|
|
|
it('56.14 Negative - undefined', function() {
|
|
assert.throws(
|
|
function() {
|
|
oracledb.fetchAsString = undefined;
|
|
},
|
|
/NJS-004:/
|
|
);
|
|
});
|
|
|
|
it('56.15 Negative - NaN', function() {
|
|
assert.throws(
|
|
function() {
|
|
oracledb.fetchAsString = NaN;
|
|
},
|
|
/NJS-004:/
|
|
);
|
|
});
|
|
|
|
it('56.16 Negative - invalid type of value, number', function() {
|
|
assert.throws(
|
|
function() {
|
|
oracledb.fetchAsString = 10;
|
|
},
|
|
/NJS-004:/
|
|
);
|
|
});
|
|
|
|
it('56.17 Negative - invalid type of value, string', function() {
|
|
assert.throws(
|
|
function() {
|
|
oracledb.fetchAsString = 'abc';
|
|
},
|
|
/NJS-004:/
|
|
);
|
|
});
|
|
|
|
it('56.18 Negative - passing oracledb.DATE type to fetchInfo', async function() {
|
|
await assert.rejects(
|
|
async () => {
|
|
await connection.execute(
|
|
"select sysdate as ts_date from dual",
|
|
{ },
|
|
{
|
|
fetchInfo: { ts_date: { type: oracledb.DATE } }
|
|
}
|
|
);
|
|
},
|
|
// NJS-021: invalid type for conversion specified
|
|
/NJS-021:/
|
|
);
|
|
});
|
|
|
|
it('56.19 Negative - passing empty JSON to fetchInfo', async function() {
|
|
const result = await connection.execute(
|
|
"select sysdate as ts_date from dual",
|
|
{ },
|
|
{
|
|
fetchInfo: {}
|
|
}
|
|
);
|
|
assert(result);
|
|
assert.strictEqual(result.rows[0][0] instanceof Date, true);
|
|
});
|
|
|
|
it('56.20 Negative - passing oracledb.NUMBER type to fetchInfo', async function() {
|
|
await assert.rejects(
|
|
async () => {
|
|
await connection.execute(
|
|
"select sysdate as ts_date from dual",
|
|
{ },
|
|
{
|
|
fetchInfo: { ts_date: { type: oracledb.NUMBER } }
|
|
}
|
|
);
|
|
},
|
|
// NJS-021: invalid type for conversion specified
|
|
/NJS-021:/
|
|
);
|
|
});
|
|
|
|
it('56.21 Negative - invalid type of value, Date', function() {
|
|
assert.throws(
|
|
function() {
|
|
const dt = new Date ();
|
|
oracledb.fetchAsString = dt;
|
|
},
|
|
/NJS-004:/
|
|
);
|
|
});
|
|
|
|
it('56.22 Negative - invalid type of value, Buffer', function() {
|
|
assert.throws(
|
|
function() {
|
|
const buf = assist.createBuffer (10) ; // arbitary sized buffer
|
|
oracledb.fetchAsString = buf;
|
|
},
|
|
/NJS-004:/
|
|
);
|
|
});
|
|
|
|
});
|