More examples

[larsandersson-ifpi]

I have problems understanding how i clean up after execute an that am done fetching the result. I see in documentation that there are: Connection.close() Connection.disconn() Statement.close() Statement.closeCursor() But i would like some examples using them.

Currently the SQL chrashes on my system, when i run scripts that is heavy in sql calls, and i am not able go get a new connection. So i need to better know how to clean up. currently i just use the Connection.close()

[abmusse]

Can you please provide an example script that is heavy in sql calls that causes the crash?

[larsandersson-ifpi]

Here is some sample code, then the function xxxx is called 2-5000 times or more from the main script, processing a excelfile row by row. Sometimes getStatement starts failing little before 2000 calls, and i get a SQL service dump in the machine.

async function getStatement( TTL = 3){ try{ const connection = new Connection(c.get('dbConfig')); connection.setConnAttr(SQL_ATTR_HEX_LITERALS, SQL_HEX_IS_CHAR )

const statement = new Statement(connection);
statement.enableNumericTypeConversion = function(flag) {
const { stmt } = this;
    if (typeof flag === 'undefined') {
      return stmt.asNumber();
    }
    return stmt.asNumber(flag);
 }
  await statement.enableNumericTypeConversion(true);
  return statement;

} catch (err) { console.log('getStatement error TTL=', TTL, err.stack); TTL--; if (TTL>0) { return getStatement(TTL); } return } }

async function sqlExecute (stmt, sql, parms, filters){ try{ await stmt.prepare(sql); await stmt.bind(parms); await stmt.execute(); let results = await stmt.fetchAll(); if (filters){ return u.filterArrayOr(results, filters); } else { return results; } } catch (err){ console.log(err.stack); return } }

async function sqlExecuteNoFetch (stmt, sql, parms){ try{ await stmt.prepare(sql); await stmt.bind(parms); return await stmt.execute(); } catch (err){ console.log(err.stack); return } }

async function sqlCloseStatement (stmt){ try{ await stmt.close(); return true; } catch (err){ console.log(err.stack); return } }

async function xxxx (p1, p2, p3){ try { const statement = await getStatement(); var parm2 = Number.parseInt(p2); var parm3 = (p3 ? p3.toUpperCase() : "") var parm1 = (p1 ? p1 : “1”) ;
const sqlt=CALL ABC.XX0009C('1', '${process.env.ENV1}'); //CL program that set the librarylist and other parameters for the job. No data is returned const sql= CALL ABC.XX0444I( cast ('4' as char (1)), cast(? as char (1)), cast (? as decimal(7)), cast(? as char (20)) ); let results = await sqlExecuteNoFetch (statement, sqlt, []); results = await sqlExecute (statement, sql, [[parm1, IN, CHAR], [parm2, IN, INT], [parm3.padEnd(20), IN, CHAR]] ); var closed = await sqlCloseStatement (statement); return result; } catch (err) { console.log(err.stack); return } };

[abmusse]

Formatted the code sample above to make it more readable:

async function getStatement(TTL = 3) {
    try {
        const connection = new Connection(c.get('dbConfig'));
        connection.setConnAttr(SQL_ATTR_HEX_LITERALS, SQL_HEX_IS_CHAR)

        const statement = new Statement(connection);
        statement.enableNumericTypeConversion = function(flag) {
            const {
                stmt
            } = this;
            if (typeof flag === 'undefined') {
                return stmt.asNumber();
            }
            return stmt.asNumber(flag);
        }
        await statement.enableNumericTypeConversion(true);
        return statement;

    } catch (err) {
        console.log('getStatement error TTL=', TTL, err.stack);
        TTL--;
        if (TTL > 0) {
            return getStatement(TTL);
        }
        return
    }
}

async function sqlExecute(stmt, sql, parms, filters) {
    try {
        await stmt.prepare(sql);
        await stmt.bind(parms);
        await stmt.execute();
        let results = await stmt.fetchAll();
        if (filters) {
            return u.filterArrayOr(results, filters);
        } else {
            return results;
        }
    } catch (err) {
        console.log(err.stack);
        return
    }
}

async function sqlExecuteNoFetch(stmt, sql, parms) {
    try {
        await stmt.prepare(sql);
        await stmt.bind(parms);
        return await stmt.execute();
    } catch (err) {
        console.log(err.stack);
        return
    }
}

async function sqlCloseStatement(stmt) {
    try {
        await stmt.close();
        return true;
    } catch (err) {
        console.log(err.stack);
        return
    }
}

async function xxxx(p1, p2, p3) {
    try {
        const statement = await getStatement();
        var parm2 = Number.parseInt(p2);
        var parm3 = (p3 ? p3.toUpperCase() : "")
        var parm1 = (p1 ? p1 : "1");
        const sqlt = "CALL ABC.XX0009C('1', '${process.env.ENV1}')"; // CL program that set the librarylist and other parameters for the job. No data is returned
        const sql = "CALL ABC.XX0444I( cast ('4' as char (1)), cast(? as char (1)), cast (? as decimal(7)), cast(? as char (20)) )";
        let results = await sqlExecuteNoFetch(statement, sqlt, []);
        results = await sqlExecute(statement, sql, [
            [parm1, IN, CHAR],
            [parm2, IN, INT],
            [parm3.padEnd(20), IN, CHAR]
        ]);
        var closed = await sqlCloseStatement(statement);
        return result;
    } catch (err) {
        console.log(err.stack);
        return
    }
};

[abmusse]

async function getStatement(TTL = 3) {
    try {
        const connection = new Connection(c.get('dbConfig'));
        connection.setConnAttr(SQL_ATTR_HEX_LITERALS, SQL_HEX_IS_CHAR)

        const statement = new Statement(connection);
        statement.enableNumericTypeConversion = function(flag) {
            const {
                stmt
            } = this;
            if (typeof flag === 'undefined') {
                return stmt.asNumber();
            }
            return stmt.asNumber(flag);
        }
        await statement.enableNumericTypeConversion(true);
        return statement;

    } catch (err) {
        console.log('getStatement error TTL=', TTL, err.stack);
        TTL--;
        if (TTL > 0) {
            return getStatement(TTL);
        }
        return
    }
}

async function xxxx(p1, p2, p3) {
    try {
        const statement = await getStatement();
        var parm2 = Number.parseInt(p2);
        var parm3 = (p3 ? p3.toUpperCase() : "")
        var parm1 = (p1 ? p1 : "1");
        const sqlt = "CALL ABC.XX0009C('1', '${process.env.ENV1}')"; // CL program that set the librarylist and other parameters for the job. No data is returned
        const sql = "CALL ABC.XX0444I( cast ('4' as char (1)), cast(? as char (1)), cast (? as decimal(7)), cast(? as char (20)) )";
        let results = await sqlExecuteNoFetch(statement, sqlt, []);
        results = await sqlExecute(statement, sql, [
            [parm1, IN, CHAR],
            [parm2, IN, INT],
            [parm3.padEnd(20), IN, CHAR]
        ]);
        var closed = await sqlCloseStatement(statement);
        return result;
    } catch (err) {
        console.log(err.stack);
        return
    }
};

Here is some sample code, then the function xxxx is called 2-5000 times or more from the main script, processing a excelfile row by row. Sometimes getStatement starts failing little before 2000 calls, and i get a SQL service dump in the machine.

@larsandersson-ifpi

Calling getStatement will generate a new connection each time. At the end of xxxx the statement is closed out with sqlCloseStatement but the underlying connection is left dangling. The next time we call xxxx a new connection and statement pair will be created. I think what you want to do here is use connection pooling. We have DBPool class that will establish preset # of connections and reuse the existing connections. New connections will only be created if the existing connections are not available.

[abmusse]

@larsandersson-ifpi

I think you are right to point out the examples in README do not explicitly close out the statement and connections. I will add the clean up steps to the examples!