Question posted in Json
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Get Node.js oracledb result as JSON

Justin
January 26, 2023
182 views
1 vote
2 Answers

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.

Answers

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

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();

Please signup or login to give your own answer.

Click here to cancel reply.