node.jstypescriptsql-updatenode-oracledb

oracle sql server connection pool not updating the table column


I'm working in node.js with oracledb.
I have create connection pool to connection database.

When I fetch any data from database it's working perfectly. But when I'm trying to update table column it's not updating oracle sql server table.

I have an EmpInfo table with columns empno (primary key) and notes (varchar(200)). I'm tring to update notes column w.r.t empno.

This is my sample code as follows:

oracleSqlDb.ts

import * as oracledb from 'oracledb';

const oracleDB_connectionConfig = {
    connectString   : "host:port/schema",
    user            : "username",
    password        : "********",
    poolIncrement   : 0,
    poolMax         : 10,
    poolMin         : 10,
    poolTimeout     : 60,
    poolAlias       : 'default'
};

export default class OracleSQL {
    private static isConnected: boolean = false;
    private static poolConn: any;

    static async connect() {
        return await oracledb.createPool(
            oracleDB_connectionConfig, 
            (err, pool) => {
                if (err) {
                    console.error('Connectivity Error: ', {code : err.errorNum, msg: err.message});
                    this.close();
                }
                console.info('Connection Pool Established ...');
                this.isConnected = true;
            });
    }

    static async query(queryStr: string) {
        if (!this.isConnected) {
            console.info('Reconnecting ....');
            await this.connect();
        }
        console.debug(`Query: ${queryStr}`);
        try {
            this.poolConn = await oracledb.getConnection('default');
            const result = await this.poolConn.execute(queryStr);
            if (result) {
                await this.poolConn.close();
                console.debug('Query result: ', result);
                return result;
            }
        } catch(err) {
            console.error('Query execution error: ', {code : err.errorNum, msg: err.message});
            this.close();
            return null;
        }
    }

    static async close() {
        console.debug("Disconnecting Connection Oracle SQL DB .... ");
        if (this.isConnected && this.poolConn && this.poolConn.isHealthy()) {
            await this.poolConn.close().then(() => {
                this.poolConn = null;
                console.info("DB disconnected ...");
            });
        }
        await oracledb.getPool().close(0);
        this.poolConn = null;
        this.isConnected = false;
    }
}

Model.ts

export interface UpdateQueryModel {
    empno : number
    notes : string | ''
};
export interface UpdateDataResponseModel {
    updateRecordId      : number | string,
    updateRecordType    : string,
    updateRecordCount   : number
};

AppRepository.ts

import { UpdateQueryModel, UpdateDataResponseModel } from "./models";
import { OracleSQL } from "./oracleSqlDb";

export class AppRepository {
    public async updateTableColumn(query: UpdateQueryModel): Promise<any> {
        console.debug(`QueryModel: ${JSON.stringify(query)}`);

        const resModel: UpdateDataResponseModel = {
            updateRecordId: query.empno,
            updateRecordType: 'Failure Message',
            updateRecordCount: 0
        };
        try {
            const dbQuery: string = `UPDATE  empinfo
                        SET     notes     =   '${query.notes}'
                        WHERE   empno     =   ${Number(query.empno)}`;

            const result = await OracleSQL.query(dbQuery);
            if (result && result.rowsAffected) {
                return {
                    ...resModel, 
                    updateRecordCount: dbData.rowsAffected,
                    updateRecordType : 'Update Record succesfully'
                };
            } else return resModel;
        } catch(err) {
            return resModel;
        }
    }
}

AppController.ts

import { Request, Response } from "express";
import { UpdateQueryModel } from "./models";
import { AppRepository } from "./AppRepository"

export class AppController {
    public async updateUserNotes(req: Request, res: Response) {
        console.debug(`Request body info  : ${JSON.stringify(req.body)}`);
        const appRepos: AppRepository = new AppRepository();

        let resultList: UpdateDataResponseModel;
        let queryModel: UpdateQueryModel = req.body;
        console.info(`API Url: '${req.originalUrl}' with config: ${JSON.stringify(req.body)}`);

        if(queryModel && Object.keys(queryModel).length && queryModel.empno) {
            resultList = await appRepos.updateTableColumn(queryModel);
            console.silly(`Output Data: ${JSON.stringify(resultList, null, 2)}`);

            return res.status(200).send({
                msg     :   "Succesful Response",
                data    :   resultList
            });
        } else {
            return res.status(200).send({
                msg: `Invalid Query: ${JSON.stringify(queryModel)}`
            });
        }
    }
}

Notes column update is not working my end.

is that issue of db connection pool issue or some other issue? Please give some suggestion.


Solution

  • You aren't explicitly committing after you perform the update, so these changes won't be visible from outside the transaction, and will be rolled back when the connection is closed.

    You could either set the connection to auto commit, or, probably more robustly, explicitly commit after performing an update:

    await this.poolConn.commit();