Get Node.js oracledb result as JSON-CodePudding

I am using the oracledb package to retrieve some data and output the data as json and I have successfully done that but I want the values to be in quotes for example this is the output i get.

{
   "postage_charge":0,
   "shipping_charge":52.28,
   "order_processing":9.82,
   "receiving_charge":0,
   "storage_charge":21.36,
   "product_charge":2.65,
   "sup_charge":0,
   "hold_charge":0
}

I want it too look like

{
   "postage_charge":"0",
   "shipping_charge":"52.28",
   "order_processing":"9.82",
   "receiving_charge":"0",
   "storage_charge":"21.36",
   "product_charge":"2.65",
   "sup_charge":"0",
   "hold_charge":"0"
}

here is my code

async function selectInvoiceDetail(req, res, invoice) {

    try {
      connection = await oracledb.getConnection({
        user: "",
        password: "",
        connectString: "192.168.1.5:1521/test"
      });
      
      postageCharge = await connection.execute(`select sum(item_total) as postal_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and charge_type='SHIPPING' and charge_subtype='POSTAGE'`);
      shippingCharge = await connection.execute(`select sum(item_total) as shipping_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and (charge_type='SHIPPING' and charge_subtype='SHIPPING' or  charge_type='SUP' and charge_subtype='DUTAX' or  charge_type='SUP' and charge_subtype='SHIPPING' or  charge_type='SUP' and charge_subtype='VASSHIP')`);
      orderProcessing = await connection.execute(`select sum(item_total) as order_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and (charge_type='ORDERS' and charge_subtype='BASE' or  charge_type='ORDERS' and charge_subtype='INTNAT' or  charge_type='ORDERS' and charge_subtype='LTLCP' or  charge_type='ORDERS' and charge_subtype='PACKAGING' or  charge_type='ORDERS' and charge_subtype='PIECE' or  charge_type='ORDERS' and charge_subtype='PIECEC' or  charge_type='ORDERS' and charge_subtype='SERIAL' or  charge_type='SUP' and charge_subtype='ORDERS')`);
      receivingCharge = await connection.execute(`select sum(item_total) as receiving_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and (charge_type='RECEIPT' and charge_subtype='PIECE' or  charge_type='RECEIPT' and charge_subtype='ITEM' or  charge_type='RECEIPT' and charge_subtype='PIECEC' or  charge_type='SUP' and charge_subtype='RECEIVE' or  charge_type='RECEIPT' and charge_subtype='LEVEL')`);
      storageCharge = await connection.execute(`select sum(item_total) as storage_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and (charge_type='STORAGE' and charge_subtype='FLAT' or  charge_type='STORAGE' and charge_subtype='LOCATION' or  charge_type='STORAGE' and charge_subtype='VOLUME')`);
      productCharge = await connection.execute(`select sum(item_total) as product_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and charge_type='PRODUCT' and charge_subtype='SETUP'`);
      supCharge = await connection.execute(`select sum(item_total) as sup_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and (charge_type='SUP' and charge_subtype='RECEIPT' or  charge_type='SUP' and charge_subtype='CRETURN' or  charge_type='SUP' and charge_subtype='LABEL' or  charge_type='SUP' and charge_subtype='RETURN' or  charge_type='SUP' and charge_subtype='VAS')`);
      holdCharge = await connection.execute(`select sum(item_total) as hold_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and charge_type='OHOLD' and charge_subtype='PERCENT'`);
      
    } catch (err) {
      return res.send(err.message);
    } finally {
      if (connection) {
        try {
          await connection.close();
        } catch (err) {
          console.error(err.message);
        }
      }
        res.setHeader('Content-Type', 'application/json');

        var result = {
        "postage_charge": postageCharge.rows[0].POSTAL_CHARGE ?? 0,
        "shipping_charge": shippingCharge.rows[0].SHIPPING_CHARGE ?? 0,
        "order_processing": orderProcessing.rows[0].ORDER_CHARGE ?? 0,
        "receiving_charge": receivingCharge.rows[0].RECEIVING_CHARGE ?? 0,
        "storage_charge": storageCharge.rows[0].STORAGE_CHARGE ?? 0,
        "product_charge": productCharge.rows[0].PRODUCT_CHARGE ?? 0,
        "sup_charge": supCharge.rows[0].SUP_CHARGE ?? 0,
        "hold_charge": holdCharge.rows[0].HOLD_CHARGE ?? 0
        };
        
        return res.send(result);
    }
  }

any help is appreciated

thank you.

CodePudding user response：

Just convert number (coming from DB) to string .toString(). In you results :(postageCharge.rows[0].POSTAL_CHARGE ?? 0).toString() and the rest of them which type is number.

<!DOCTYPE html>
<html>
<body>

<h2>convert numbers coming from server to string to get quotes in JSON - toString() Method</h2>
<p>toString() returns the content of a string:</p>
<p> your example woudl be : (postageCharge.rows[0].POSTAL_CHARGE ?? 0).toString(),</p>
<p id="demo"></p>

<script>
//your example woudl be 
// (postageCharge.rows[0].POSTAL_CHARGE ?? 0).toString(),
let fromServer  = 123;
let result = fromServer.toString();
document.getElementById("demo").innerHTML = result; 
</script>

</body>
</html>

number toString Mozilla documentation

CodePudding user response：

Read the node-oracledb manual on Fetching Numbers and Dates as String.

Try something like:

sql = `SELECT 12.23 AS D FROM DUAL WHERE 1 = :i`;
binds = [1];
options = { fetchInfo: { 'D': { type:oracledb.STRING }} };

result = await connection.execute(sql, binds, options);
console.dir(result, { depth: null });

The advantage of this method over converting with toString() is that it circumvents Oracle's internal decimal format being converted to Node.js floating point type and removes any subsequent user confusion about the numbers not matching.

I included a bind variable in the WHERE clause as an example since you must change your usage of ... where INVOICE_ID='${invoice}' ... to use binding. This is important for scalability and security.

An alternative to fetchInfo is to do the conversion in the SQL statement e.g. like

SELECT TO_CHAR(12.23) AS D FROM DUAL WHERE 1 = :i

Since you have a fixed number of SELECT statements and they return simple types, you should consider wrapping them all in a single PL/SQL block. This will reduce Database Round-trips because you only need one execute() and it will make the overall system more efficient. You can do something like:

const oracledb = require('oracledb');
const dbConfig = { user: 'cj', password: process.env.NODE_ORACLEDB_PASSWORD, connectString: 'localhost/orclpdb1' };

if (process.platform === 'darwin') {
  oracledb.initOracleClient({libDir: process.env.HOME   '/Downloads/instantclient_19_8'});
}

const plsql = `begin
                 select sum(sal) into :salsum from emp where empno > :en;
                 select sum(comm) into :comsum from emp where empno > :en;
               end;`;

const binds = {
  en: 7600,
  salsum: {dir: oracledb.BIND_OUT, type: oracledb.STRING},
  comsum: {dir: oracledb.BIND_OUT, type: oracledb.STRING}
};

const options = { outFormat: oracledb.OUT_FORMAT_OBJECT };

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection(dbConfig);

    const result = await connection.execute(plsql, binds, options);
    console.dir(result.outBinds, { depth: null });

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();