Skip to content

Instantly share code, notes, and snippets.

@GeorgeNava

GeorgeNava/dataserver.js

Created March 13, 2010 19:28
Download ZIP

Revisions

  1. GeorgeNava revised this gist Mar 13, 2010. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion dataserver.js
    View file
    Original file line number Diff line number Diff line change
    @@ -16,7 +16,6 @@ You can also pass values to the query in placeholders as hash or array:
    rec = sql.getRecord('SELECT * FROM customers WHERE state={0} and country={1}', ['FL','US'])
    Remember to sanitize your queries!
    */

    var MySQL = require('mysql').MySQL;
  2. GeorgeNava revised this gist Mar 13, 2010. 1 changed file with 0 additions and 2 deletions.
    2 changes: 0 additions & 2 deletions dataserver.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,6 @@
    /* dataserver.jss
    USE:
    sql = new SQL();
    sql.connect();
    num = sql.getValue('SELECT count(1) FROM customers')
    @@ -13,7 +12,6 @@ USE:
    sql.disconnect();
    You can also pass values to the query in placeholders as hash or array:
    rec = sql.getRecord('SELECT * FROM customers WHERE id={id}', {id:12345})
    rec = sql.getRecord('SELECT * FROM customers WHERE state={0} and country={1}', ['FL','US'])
  3. GeorgeNava revised this gist Mar 13, 2010. 1 changed file with 1 addition and 8 deletions.
    9 changes: 1 addition & 8 deletions dataserver.js
    View file
    Original file line number Diff line number Diff line change
    @@ -23,13 +23,6 @@ Remember to sanitize your queries!

    var MySQL = require('mysql').MySQL;

    var Config = {
    datahost: 'localhost',
    username: 'root' ,
    password: 'yourpass' ,
    database: 'yourdb'
    };

    function SQL(){
    var db = null;
    var numRows = null;
    @@ -38,7 +31,7 @@ function SQL(){

    SQL.prototype.connect=function(){
    if(!this.db){
    this.db = new MySQL().connect(Config.datahost,Config.username,Config.password,Config.database);
    this.db = new MySQL().connect('localhost','root','pass','database');
    }
    }

  4. GeorgeNava created this gist Mar 13, 2010.
    122 changes: 122 additions & 0 deletions dataserver.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,122 @@
    /* dataserver.jss
    USE:
    sql = new SQL();
    sql.connect();
    num = sql.getValue('SELECT count(1) FROM customers')
    rec = sql.getRecord('SELECT * FROM customers WHERE id=123456')
    recs = sql.getRecords('SELECT * FROM customers')
    list = sql.getList('SELECT DISTINCT name FROM customers')
    table = sql.getTable('SELECT * FROM customers')
    id = sql.execute('INSERT INTO customers(name,phone) VALUES("H4x0r","555-1234")')
    sql.disconnect();
    You can also pass values to the query in placeholders as hash or array:
    rec = sql.getRecord('SELECT * FROM customers WHERE id={id}', {id:12345})
    rec = sql.getRecord('SELECT * FROM customers WHERE state={0} and country={1}', ['FL','US'])
    Remember to sanitize your queries!
    */

    var MySQL = require('mysql').MySQL;

    var Config = {
    datahost: 'localhost',
    username: 'root' ,
    password: 'yourpass' ,
    database: 'yourdb'
    };

    function SQL(){
    var db = null;
    var numRows = null;
    var lastId = null;
    }

    SQL.prototype.connect=function(){
    if(!this.db){
    this.db = new MySQL().connect(Config.datahost,Config.username,Config.password,Config.database);
    }
    }

    SQL.prototype.disconnect=function(){
    if(this.db){ this.db.close(); }
    }

    SQL.prototype.getValue=function(sql,values){
    var query,rows,data=null;
    query = this.query(sql,values);
    rows = query.fetchArrays();
    if(rows.length>0){ data = rows[0][0]; }
    query.close();
    return data;
    }

    SQL.prototype.getRecord=function(sql,values){
    var query,rows,data=null;
    query = this.query(sql,values);
    rows = query.fetchObjects();
    if(rows.length>0){ data = rows[0]; }
    query.close();
    return data;
    }

    SQL.prototype.getRecords=function(sql,values){
    var query,rows,data=null;
    query = this.query(sql,values);
    rows = query.fetchObjects();
    if(rows.length>0){ data = rows; }
    query.close();
    return data;
    }

    SQL.prototype.getList=function(sql,values){
    var query,rows,data=null;
    query = this.query(sql,values);
    rows = query.fetchArrays();
    if(rows.length>0){
    data = [];
    for(i in rows){ data.push(rows[i][0]); }
    }
    query.close();
    return data;
    }

    SQL.prototype.getTable=function(sql,values){
    var query,rows,data=null;
    query = this.query(sql,values);
    rows = query.fetchArrays();
    if(rows.length>0){ data = rows; }
    query.close();
    return data;
    }

    SQL.prototype.execute=function(sql,values){ /* Insert, Update, Delete */
    var query,rows,data=null;
    var command = sql.substr(0,6).toUpperCase();
    this.query(sql,values);
    this.numRows = this.db.affectedRows();
    if (command=="INSERT"){ data = this.lastId = this.db.insertId(); }
    else if(command=="UPDATE"){ data = this.numRows; }
    else if(command=="DELETE"){ data = this.numRows; }
    return data;
    }

    SQL.prototype.query=function(sql,values){
    var query;
    this.connect();
    this.sql = sql = this.parse(sql,values);
    query = this.db.query(sql);
    return query;
    }

    SQL.prototype.parse=function(sql,values){
    if(!values){ return sql; }
    for(var i in values){ sql=sql.replace(new RegExp("{("+i+")}","g"),values[i]); }
    return sql;
    }

    exports.SQL = SQL;