Db file

// Query
/*
*
*   @param jdbc Database object
*   @param id Id to be compared on
*   @param cb Callback function
*/
function (jdbc, id, cb){
    var query = 'SELECT "query", "description", "type" FROM TEST WHERE "t_id" = ?';

    jdbc.connection(function(err, con){
        if(err)
            logger.logError('There was an error when connecting');
        jdbc.prepare(con, query, true, function(err, ps){
            setInt(ps, id);
            ps.executeQuery(function(err, resultSet){
                resultSet.toObject(function(err, obj){
                    cb(err, obj.rows);
                });
            });
       });
    });
}


// DB FILE

var path = require('path');
var fs = require('fs');
var errors = require('../../utilities/errors');
var logger = require('../../utilities/clog');
//object for making & consolidating a db connection

var JDBC = require('jdbc');
var jinst = require('jdbc/lib/jinst')
var _ = require('underscore');
var dbConneInit = false;

function dbConnection(db, cb, minPoolSize, maxPoolSize){

    var dbConn = this;

    var prefix = '';

    var jarPath = db['*JDBC_driver_path'];
    var connOpts = {
        libpath: jarPath,
        drivername: '',
        url: ''
    };
    fs.exists(jarPath, function(exists){
        if(!exists){
            jarPath = path.join(__dirname,'..', '..', '..','jdbc', jarPath);
            fs.exists(jarPath, function(exists){
                if(!exists){
                    console.log(jarPath + ' is not a valid path/file!');
                }else{
                    initJDBC();
                }
            });
        }else{
            initJDBC();
        }
    });


    function initJDBC()
    {
        connOpts.libpath = jarPath;
        var file = path.basename(jarPath);
        var useOverride = db.use_override_url;

        if(file == 'ojdbc6.jar'){ //oracle db
            connOpts.drivername = 'oracle.jdbc.OracleDriver';

            if(useOverride) {
                connOpts.url = db.override_url;
                console.log("[********** USE OVERRIDE ENABLED : " + useOverride);
                console.log("[********** DB CONNECTION STRING : " + db.override_url);
            }
            else
                connOpts.url = 'jdbc:oracle:thin:' + db.username + '/' + db.password + '@' + db.hostname + ':' + db.port +':'+ db.sid_or_db_name;

            dbConn.getVersionInfo = function getVersionInfo(cb){
                doQuery('SELECT * FROM v$version', function(err, data){
                    if(err) return cb(err);

                    var info = [];
                    data.forEach(function(val){
                        info.push(val.BANNER);
                    });
                    cb(err, info);
                });
            };

        }else if(file.match('mysql-connector')){ //else MySQL db
            connOpts.drivername = 'com.mysql.jdbc.Driver';

            if(useOverride)
                connOpts.url = db.override_url;
            else
                connOpts.url = 'jdbc:mysql://' + db.hostname + ':' + db.port +'/' + db.sid_or_db_name + '?user=' + db.username + '&password=' + db.password;



            dbConn.getVersionInfo = function getVersionInfo(cb){
                doQuery('SHOW VARIABLES LIKE "%version%"', function(err, data){
                    if(err) return cb(err);

                    var info = [];
                    data.forEach(function(val){
                        info.push(val.VARIABLE_NAME +' : ' + val.VARIABLE_VALUE);
                    });
                    cb(err, info);
                });
            };

        }else if(file.match('db2jcc')){ //else DB2
            connOpts.drivername = 'com.ibm.db2.jcc.DB2Driver';


            if(useOverride)
                connOpts.url = db.override_url;
            else
                connOpts.url = 'jdbc:db2://' + db.hostname + ':' + db.port + '/' + db.sid_or_db_name +
                ':user=' + db.username + ';password=' + db.password +';';

            if(db.default_schema)
                connOpts.url += 'currentSchema=' + db.default_schema + ';';
            prefix = db.default_schema? db.default_schema + '.': '';


            dbConn.getVersionInfo = function getVersionInfo(cb){
                doQuery('SELECT * FROM SYSIBMADM.ENV_INST_INFO', function(err, data){
                    if(err) return cb(err);

                    var info = [];
                    data = data[0];
                    for(var key in data)
                        info.push(key + ': ' + data[key]);
                    cb(err, info);
                });

            };

        }else if(file.match('sqljdbc')){ //else SQL server db
            connOpts.drivername = 'com.microsoft.sqlserver.jdbc.SQLServerDriver';


            if(useOverride)
                connOpts.url = db.override_url;
            else
                connOpts.url = 'jdbc:sqlserver://' + db.hostname + ':' + db.port + ';' +
                'databaseName=' + db.sid_or_db_name + ';user=' + db.username + ';password=' + db.password + ';';
            prefix = db.default_schema? db.default_schema + '.': '';


            dbConn.getVersionInfo = function getVersionInfo(cb){
                doQuery('SELECT @@VERSION', function(err, data){
                    if(err) return cb(err);
                    var info = [];
                    data = data[0];
                    for(var key in data)
                        info.push(data[key]);
                    cb(err, info);
                });
            };

        }else{
            console.log('ERROR: Unsupported JDBC driver: ' + connOpts.libpath + '\nExternal database access is impossible!');
        }

        if (!jinst.isJvmCreated())
        {
            jinst.addOption("-Xrs");
            jinst.setupClasspath([connOpts.libpath]);
        }

        dbConn.JDBC = new JDBC({
            url: connOpts.url,
            minpoolsize : minPoolSize,
            maxpoolsize: maxPoolSize,
            drivername : connOpts.drivername,

            });


        if(cb) cb();
    }

    // prints error if theres an error
    function errCatch(err, query){
        if(err) console.log('\n' + query + '\n\n' + err);
    }


    /**
    * Convenience 'from' sql query clause.
    * MS sql server needs schema to prefix tables. This method
    * will know and add the prefix when needed.
    *
    * from('yourtable', 'someothertable') //as many as you want
    *
    * @params variable length of table strings
    * @return formatted FROM clause string
    */
    this.from = function from(){
        var rtn = ' FROM ';
        for(var i=0; i<arguments.length; i++)
            rtn += prefix + arguments[i] + ' ';
        return rtn.length > 6? rtn: '';
    };



    this.doQuery = function(query, cb){

        this.connection(function(err, con){

            errCatch(err, query);

            executeQuery(con, query, function(err, data){

                errCatch(err, query)

                data.toObject(function(err, result){

                    errCatch(err, query);
                    cb(null, result.rows);
                });

            });
        });
    }

    this.doUpdate = function(query, cb){
        this.connection(function(err, con){

            errCatch(err, query);

            executeUpdate(con, query, function(err, data){

                errCatch(err, query);
                cb(err, data);
            });
        });
    }


    this.tableexists = function(db, catalog, schema, name, callback) {
    reserve(db, function(err, connobj, conn) {
        conn.getMetaData(function(err, metadata) {
        if (err) {
            release(db, connobj, err, null, callback);
        } else {
            metadata.getTables(catalog, schema, name, null, function(err, resultset) {
            if (err) {
                release(db, connobj, err, null, callback);
            } else {
                resultset.toObjArray(function(err, results) {
                    release(db, connobj, err, results.length > 0, callback);
                });
            }
            });
        }
        });
    });
    }

    this.executePreparedStatement = function(sql, generatedKeys, callback)
    {
        var that = this;

        that.connection(function(err, con){

            if(err)
                logger.logError('THERE WAS AN ERROR CONNECTING TO DB: ' + err);

            that.prepare(con, sql, generatedKeys, callback) ;

        });

    }


    this.prepare = function(db, sql, generatedKeys, callback) {
        reserve(db, function(err, connobj, conn)
        {
            if(!generatedKeys)
            {
                conn.prepareStatement(sql, function(err, preparedstatement) {
                    release(db, connobj, err, preparedstatement, callback);
                });
            }
            else
            {
                conn.prepareStatement(sql, 1, function(err, preparedstatement) {

                release(db, connobj, err, preparedstatement, callback);

                });
            }
        });
    };

    this.prepareCall = function(db, sql, callback) {
        reserve(db, function(err, connobj, conn) {
            conn.prepareCall(sql, function(err, callablestatement) {
                release(db, connobj, err, callablestatement, callback);
            });
        });
    }



    this.metadata = function(db, callback) {
    reserve(db, function(err, connobj, conn) {
        conn.getMetaData(function(err, metadata) {
        release(db, connobj, err, metadata, callback);
        });
    });
    }

    this.connection = function (callback)
    {
        if (!dbConneInit) {
            dbConn.JDBC.initialize(function(err) {
            if (err) {
                return callback(err);
            } else {
                dbConneInit = true;
                return callback(null, dbConn.JDBC);
            }
            });
        } else {
            return callback(null, dbConn.JDBC);
        }
    }
}


function reserve(db, callback)
{
  db.reserve(function(err, connobj)
  {
    //logger.logInfo('reserveing connection');
    if (err) {
      return callback(err + ' error accquiring connection');
    } else
    {
      return callback(null, connobj, connobj.conn);
    }
  });
}

function release(db, connobj, err, result, callback)
{

  db.release(connobj, function(e)
  {
    //logger.logInfo('releasing connection');
    if (err) {
      return callback(err + ' error releaseing connection');
    } else
    {
      return callback(null, result);
    }
  });
}

var dbs = {};
exports.dbs = dbs;
/**
* Loads the database configurations & makes new dbConnections.
* Will not overwrite unless force is true.
*
* @param force boolean to tru if you want to force a load even if there's already databases.
*/
function loadQaDatabase(force){
    if(force){
        for(var key in dbs)
            delete dbs[key];
    }
    var db = require('../../../config.js').jdbc;

    //If database is set to not active, make sure it's not being used.
    if(!db.platform.active) {
        errors.missingFeature('Platform Database Is Not Active');
    }
    else if(db.qa.active)
    {
        if(!dbs.qa) dbs.qa = new dbConnection(db.qa, null, 200, 600);
    }
}

exports.load = loadQaDatabase;


function executeQuery (db, sql, callback)
{
    reserve(db, function(err, connobj, conn) {
        conn.createStatement(function(err, statement) {
        if (err) {
            release(db, connobj, err, null, callback);
        } else {
            statement.executeQuery(sql, function(err, result) {
            release(db, connobj, err, result, callback);
            });
        }
        });
    });
}

function executeUpdate(db, sql, callback) {
        reserve(db, function(err, connobj, conn) {
            conn.createStatement(function(err, statement) {
            if (err) {
                release(db, connobj, err, null, callback);
            } else {
                statement.executeUpdate(sql, function(err, result) {
                release(db, connobj, err, result, callback);
                });
            }
            });
        });
    };