Question regarding await/async and code examples

[tyrann]

Answer the following questions:

1. What is your Node.js version: use console.log(process.version)? Is it 64-bit or 32-bit: use console.log(process.arch)?

v10.13.0 x64

2. What is your node-oracledb version: use console.log(oracledb.versionString)?

3.1.2

3. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?

I am trying to make a simple example work with the await/async syntax. I have managed to make the select1.js example work.

However I cannot manage to make such simple example work:

    const oracledb = require('oracledb');
    const dbConfig = {
    user : 'user',
    password : 'pass',
    connectString : 'somestring'
    };

    (async function() {
    let conn; // Declared here for scoping purposes

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

        console.log('Connected to database');

        let result = await conn.execute(
            'select * from some_table',
            [], // no binds
            {
                outFormat: oracledb.OBJECT
            }
    );

        console.log('Query executed');
        console.log(result.rows);
    } catch (err) {
        console.log('Error in processing', err);
    } finally {
        if (conn) { // conn assignment worked, need to close
            try {
                await conn.close();

                console.log('Connection closed');
            } catch (err) {
                console.log('Error closing connection', err);
            }
        }
    }
    })();

This code juste prints:

Connected to database

The

await conn.execute

line never resolves. I have tried other async example I could find and it seems all of them show this behavior. I am fairly new to both javascript and oracle... Is there something I am missing here?

[cjbj]

What is 'never resolves'? Is there an error?

It works for me :)

 const oracledb = require('oracledb');
    const dbConfig = {
    user : process.env.NODE_ORACLEDB_USER,
    password : process.env.NODE_ORACLEDB_PASSWORD,
    connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING
    };

    (async function() {
    let conn; // Declared here for scoping purposes

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

        console.log('Connected to database');

        let result = await conn.execute(
            'select * from dual',
            [], // no binds
            {
                outFormat: oracledb.OBJECT
            }
    );

        console.log('Query executed');
        console.log(result.rows);
    } catch (err) {
        console.log('Error in processing', err);
    } finally {
        if (conn) { // conn assignment worked, need to close
            try {
                await conn.close();

                console.log('Connection closed');
            } catch (err) {
                console.log('Error closing connection', err);
            }
        }
    }
    })();

Gives:

cjones@cjones-mac2:~/n$ node --version
v10.16.0
cjones@cjones-mac2:~/n$ node t.js
Connected to database
Query executed
[ { DUMMY: 'X' } ]
Connection closed

Google for query hangs and see if any of the tips e.g. about latches help.

FWIW, the dev-4.0 branch has most examples converted to async/await, see https://github.com/oracle/node-oracledb/tree/dev-4.0/examples

[tyrann]

Thanks for the reply, sorry for not being clear. 'Never resolves', means that the promise is pending forever, there is no error message.

I tried the new examples you suggested and I get the expected result. However I still don't understand the difference here:

async function not_working() {
    let conn; // Declared here for scoping purposes

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

        console.log('Connected to database');

        let result = await conn.execute(
            'select * from some_table',
            [], // no binds
            {
                outFormat: oracledb.OBJECT
            }
        );

        console.log('Query executed');
        console.log(result.rows);
    } catch (err) {
        console.log('Error in processing', err);
    } finally {
        if (conn) { // conn assignment worked, need to close
            try {
                await conn.close();

                console.log('Connection closed');
            } catch (err) {
                console.log('Error closing connection', err);
            }
        }
    }
}

async function working() {

    let connection;

    try {
        // Get a non-pooled connection

        connection = await oracledb.getConnection(  {
            user         : dbConfig.user,
            password     : dbConfig.password,
            connectString: dbConfig.connectString
        });

        const result = await connection.execute(

            `SELECT * FROM some_table`,

            [],

            {
                maxRows: 1

            });

        console.log(result.metaData); 
        console.log(result.rows);  

    } catch (err) {
        console.error(err);
    } finally {
        if (connection) {
            try {
                // Note: connections should always be released when not needed
                await connection.close();
            } catch (err) {
                console.error(err);
            }
        }
    }
}

not_working() only prints Connected to database

working() prints a record from the database

[tyrann]

After further investigation. It seems that the problem comes from the absence of this parameter

{
maxRows: 1
}

When I add it to my not_working() example, it seems to achieve the desired result.

Is it the expected behavior?

[cjbj]

Give us the SQL to create your schema and the SELECT statement you actually use.

Are you using a PIPELINED table? https://github.com/oracle/odpi/issues/73

(Update: node-oracledb sits on top of ODPI-C)

[tyrann]

I do not know anything about the database... I just know how to reach it and that there is a table with a known name. But as you describe it, it seems it is more a problem of the database settings than with with node-oracledb.

[cjbj]

@tyrann if you can give a runnable testcase that hangs, we can help. https://stackoverflow.com/help/minimal-reproducible-example

[tyrann]

ok thanks. It seems that it is really related to Oracle rather than node-oracledb. Ok to close for me.

[dmcghan]

@tyrann What happens if you change the query to select count(*) from some_table?

Also, how long did you wait before giving up?

[tyrann]

@dmcghan

There are 47'000 records in that table. And yes, you are right, I was not waiting long enough before giving up.

But then that raises the question whether it is normal to have to wait several minutes to get the records. Even by reducing the number of records with maxRows, I still have to wait several seconds for maxRows > 300. To be precise , Execution time: 19311 ms for 300 rows. And this time is increasing, the more I increase maxRows.

I tried on Toad, the result is instantaneous (123 ms), even for the whole table. Is this waiting time expected ?

[dmcghan]

@tyrann Did you get the row count from executing the query with Node.js, Toad, or another way?

Are both Toad and Node.js on the same machine?

What do you get when you execute: desc some_table?

Most tools don't fetch all the rows, they fetch fewer rows to improve performance. In Toad, what do see in "Options > Oracle > General" for OCI Array Buffer Size?

[tyrann]

@dmcghan I ran count on both Node and Toad. Same output

Are both Toad and Node.js on the same machine?

yes

What do you get when you execute: desc some_table?

Name                                      Null?    Type
----------------------------------------- -------- ---------------------

JOBNAME NOT NULL VARCHAR2(64) JOBNO NOT NULL NUMBER(10) DESCRIPT VARCHAR2(4000) APPLIC VARCHAR2(64) APPLGROUP VARCHAR2(64) SCHEDTAB NOT NULL VARCHAR2(770) AUTHOR VARCHAR2(64) OWNER VARCHAR2(30) PRIORITY NUMBER(10) CRITICAL CHAR(2) CYCLIC CHAR(2) RETRO CHAR(2) AUTOARCH CHAR(2) TASKCLASS CHAR(3) CYCLICINT VARCHAR2(6) TASKTYPE CHAR(2) DATEMEM VARCHAR2(30) NODEGRP VARCHAR2(50) PLATFORM VARCHAR2(20) NODEID VARCHAR2(50) DOCLIB VARCHAR2(255) DOCMEM VARCHAR2(64) MEMLIB VARCHAR2(255) MEMNAME VARCHAR2(64) OVERLIB VARCHAR2(255) CMDLINE VARCHAR2(4000) MAXRERUN NUMBER(10) MAXDAYS NUMBER(10) MAXRUNS NUMBER(10) FROMTIME CHAR(4) UNTIL CHAR(4) MAXWAIT NUMBER(10) DAYSTR VARCHAR2(160) WDAYSTR VARCHAR2(50) MONTHSTR CHAR(12) AJFSONSTR CHAR(32) CONF CHAR(2) UNKNOWNTIM NUMBER(10) DAYSCAL VARCHAR2(30) WEEKCAL VARCHAR2(30) CONFCAL VARCHAR2(30) CAL_ANDOR CHAR(2) SHIFT CHAR(4) ADJUST_COND CHAR(2) STARTENDCYCIND CHAR(2) CREATIONUSERID VARCHAR2(64) CREATIONDATETIME VARCHAR2(14) CHANGEUSERID VARCHAR2(64) CHANGEDATETIME VARCHAR2(14) RELATIONSHIP CHAR(2) GROUPID NUMBER(10) TABROWNO NUMBER(10) MULTIAGENT CHAR(2) APPLTYPE VARCHAR2(10) TIMEZONE VARCHAR2(9) STATEMSK CHAR(9) APPLVER VARCHAR2(10) TIMEREF CHAR(2) ACTFROM VARCHAR2(8) ACTTILL VARCHAR2(8) CMVER VARCHAR2(10) APPLFORM VARCHAR2(30) INSTREAM_IND CHAR(2) INSTREAM_SCRIPT CLOB RUN_TIMES VARCHAR2(4000) INTERVAL_SEQUENCE VARCHAR2(4000) TOLERANCE NUMBER(10) CYCLIC_TYPE CHAR(2) REMOVEATONCE CHAR(2) DAYSKEEPINNOTOK NUMBER(10) DELAY NUMBER(10)

> OCI Array Buffer Size?

500

[dmcghan]

@tyrann And what does this query give you?

select segment_name, segment_type, bytes/1024/1024 MB
from user_segments
where segment_type='TABLE' 
  and segment_name='SOME_TABLE'

[tyrann]

24 MB

[dmcghan]

@tyrann Yeah, that's not a very big table - you'd think it would transfer much faster. What is your database version and what version of the Instant Client are you using?

What do you get with this test?

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

async function runTest() {
  let conn;

  try {
    conn = await oracledb.getConnection(config);

    const result = await conn.execute(
      'select * from some_table',
      [],
      {
        resultSet: true
      }
    );

    const resultSet = result.resultSet;
    let row;
    let idx = 0;

    while (row = await resultSet.getRow()) {
      idx += 1;
    }

    // always close the ResultSet
    await resultSet.close();

    console.log(idx);
  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

runTest();

[tyrann]

What is your database version and what version of the Instant Client are you using?

From SQLplus :

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)

What do you get with this test?

It takes forever, if I limit the select to 20 ROWS I get 20. maybe I can let it run all night, see what happens

[dmcghan]

@tyrann What do you get if you log the value of oracledb.oracleClientVersionString to the console?

[tyrann]

What do you get if you log the value of oracledb.oracleClientVersionString to the console?

12.1.0.2.0

[dmcghan]

@tyrann Could you update the Instant Client to the latest version supported on your platform? This will help to eliminate the possibility that this is a bug that's been fixed in a later version.

By the way, what is your platform? Windows, Linux, Mac?

[tyrann]

Windows, I'll update the client and report back. Thanks

[dmcghan]

@tyrann Awesome, thanks. Another test that would be very helpful is if you could run the query via SQL*Plus on the same machine. If it's not already installed, you can get it while downloading the files for the Instant Client.

[cjbj]

• try without selecting the CLOB column
• try a result set

[tyrann]

@dmcghan I did a new test:

Python with cx_Oracle:

import cx_Oracle
import time
con = cx_Oracle.connect('user/pass@mydatabase/sid')
print(con.version)
cur = con.cursor()

start = time.time()
cur.execute('select * from some_table')
end = time.time()
print("Query executed in ", end - start)

con.close() 

This prints:

12.1.0.2.0
Query executed in  0.02300238609313965 (ms)

SQLPlus seems as fast as that.

@cjbj OK I think this is the problem.

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

async function runTest() {
    let conn;

    try {
        conn = await oracledb.getConnection(config);
        var start = new Date().getTime();
        const result = await conn.execute(
            'select JOBNAME from some_table',
            [],
            {
                resultSet: true
            }
        );
        var end = new Date().getTime();
        var time = end - start;
        console.log('Query Execution time: ' + time);

        const resultSet = result.resultSet;

        let row;
        let idx = 0;

        while (row = await resultSet.getRow()) {
            idx += 1;
        }
        var end = new Date().getTime();
        var time = end - start;
        console.log('Set Execution time: ' + time);

        // always close the ResultSet
        await resultSet.close();

        console.log(idx);
    } catch (err) {
        console.error(err);
    } finally {
        if (conn) {
            try {
                await conn.close();
            } catch (err) {
                console.error(err);
            }
        }
    }
}

runTest();

This prints:

Query Execution time: 24
Set Execution time: 9719
47455

And if I add the CLOB column, it takes forever.

[cjbj]

The Python example isn't fully comparable to the given node-oracledb examples since it only executes the SQL statement but doesn't actually fetch rows. Also, isn't time.time() returning seconds, not ms? This would make the Python execution time about the same as in the latest Node.js testcase.

LOBs can be slow to fetch, but are you actually fetching them? https://oracle.github.io/node-oracledb/doc/api.html#lobhandling

Also, try tuning https://oracle.github.io/node-oracledb/doc/api.html#propdbfetcharraysize

[tyrann]

@cjbj You're right I just realized that. The return is indeed in seconds, not ms, which makes sense. I ran another test and it's true that share the same execution time.

Also, try tuning https://oracle.github.io/node-oracledb/doc/api.html#propdbfetcharraysize

This changed a lot. I have set it to 10000 and I got the following in JS:

Query Execution time: 24
Set Execution time: 646
47455

So I guess that the problem was the roundtrips to the database...

[tyrann]

To be honest I don't even know if this execution time is reasonable. To me it is ok to close, thanks a lot for the help & info.

[dmcghan]

@tyrann I'm not sure what you plan on doing with the data you're fetching, but if you need the CLOB column and the size of the CLOBs is relatively small, consider using fetchInfo to fetch that column out as a string. This is generally more performant than using LOBs but it requires more memory.

[tyrann]

@dmcghan Ok, thanks for the tip, I'll check it out