oracledb node for node-js (several queries one after the other). The other take result as binds parameter

Question

I am stuck at my problem because I am really new to oracle-db for node-js and JavaScript in general. I wrote API-endpoints with JavaScript and each endpoint does an oracledb “select query”. However, my colleagues need only one endpoint which gives all results of all oracle select-queries as one JSON object. All queries are dependent on the first query, i.e. the result of the first query has to be used as a bind-parameter for the second query and so forth. In this js-file I tried to execute two oracle-sql queries (one after the other) and use one value of the result-JSON-object of the first query as a parameter for the second query and give all results as response for the API client. But that does not work. What am I doing wrong here? With only one sql and therefore only one result it works. Thanks in advance!

Here is my code I wrote:

const oracledb = require('oracledb');
const config = require('../config/config')

oracledb.autoCommit = true
oracledb.fetchAsString = [ oracledb.DATE, oracledb.NUMBER ]


module.exports= async (req, res) => {

   
   let connection;

 try {

   var sql, binds, options, result, result2, time

   connection = await oracledb.getConnection(config.oracledbconnection)

   // Query the data 


   sql = `SELECT FIRSTNr, SECONDNR,STARTTIME,MACHINE FROM MACHINELOGS WHERE Machine=:Machine AND ENDTIME > CURRENT_DATE -1 ORDER BY RUNNO ASC`;
   
   binds = {Machine:req.params.machine}

   options = {
     outFormat: oracledb.OUT_FORMAT_OBJECT // query result format 
     //outFormat: oracledb.OBJECT // is the same as above // extendedMetaData: true, // get extra metadata 
     // prefetchRows: 100, // internal buffer allocation size for tuning 
     // fetchArraySize: 100 //internal buffer allocation size for tuning 
   };
   
   result = await connection.execute(sql, binds, options);

   // console.log("Metadata: ");
   // console.dir(result.metaData, { depth: null });
   // console.log("Query results: ");
   // console.dir(result.rows, { depth: null });
   


   // 
   // Show the date. The value of ORA_SDZT affects the output

   sql = `SELECT TO_CHAR(CURRENT_DATE, 'DD-Mon-YYYY HH24:MI') AS CD FROM DUAL`;
   time = await connection.execute(sql, binds, options);


   // console.log("Current date query results: ");
   // console.log(result2.rows[0]['CD']);

   
   sql = `SELECT GRADE FROM NOTES WHERE SECONDNR=:Secondnr`
   binds = {Secondnr:result.rows[0]['SECONDNR']}
   result2 = await connection.execute(sql,binds,options);
   options = {
     outFormat: oracledb.OUT_FORMAT_OBJECT // 
   };

   

 } 
 
 catch (err) 
 {
   console.error(err);
 } 
 
 finally 
 {
   if (connection) 
   {
     try {
       await connection.close();
     } 
     catch (err) {
       console.error(err);
     }
   }
 }
 res.send(result,result2) 
 
   
}

Answer 1

Take it back to basics. This works:

'use strict';

process.env.ORA_SDTZ = 'UTC';

const oracledb = require('oracledb');
const dbConfig = require('./dbconfig.js');

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

let sql, options, result1, result2;
options = { outFormat: oracledb.OUT_FORMAT_OBJECT };

async function run() {
  let connection;

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

    result1 = await connection.execute(
      `select 1 as myval from dual`,
      [], options);
    console.dir(result1.rows, { depth: null });

    result2 = await connection.execute(
      `select sysdate from dual where 1 = :bv`,
      {bv: result1.rows[0].MYVAL},
      options);
    console.dir(result2, { depth: null });

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

run();

Then try with your datatypes.

I'm not sure what you want to do with returning 'all results'. You can build up any JS object and convert to and from JSON easily with JSON.stringify and JSON.parse.

For efficiency, you could review whether you can do all your 'queries' in a single SQL statement.