Wednesday, January 11, 2012

Nodejs with database access

Now, it is the time for database (mysql) access. Yes, now we will access database and show it to pages. In my previous tutorial, I already explained page creation and handling requests. Here, to access database we need driver and a connection string. Driver will do all the work for you and make the thing very easy and smooth. 
See the code-

var http = require('http');
var sys = require('util');
var hash=require('./hashish');    //lib can be downloaded [here ‘./’ part is showing location of the library.
//I stored the lib in application directory.]
var qs = require('querystring');
var mysql = require('./mysql');  //lib can be down loadded
//----Create mysql client using mysql lib
//===========================================================
var client = mysql.createClient({
  user: 'root', //-------will change as per mysql server user and password
  password: 'root'
});
//----------Connect to Database
//===============================================================
function  db (client)
{// use myDB database from mysql
    client.query('USE myDB, function(error, results) {
        if(error) {
            console.log('Error: ' + error.message);
            client.end();
            return;
        }
    });
}
 //----------------------set data to  title_tbl
function SetData (client)
{
  var values = ['1', '2', 'technobelities'];
  client.query('INSERT INTO title_tbl SET company_id = ?, title_id = ? , description = ?', values,
    function(error, results) {
      if(error) {
        console.log("Error: " + error.message);
        client.end();
        return;
      }
      console.log(' Inserted  Data: ' + results.affectedRows + ' row.');
      console.log(' inserted Id: ' + results.insertId);
    }
  );
  GetData(client);
}
//---------------------get data  from db
function  GetData (client,res)
{
var data="";
  client.query(
    'SELECT * FROM title_tbl',
    function selectCb(error, results, fields) {
      if (error) {
          console.log(' Error: ' + error.message);
          client.end();
          return;
      }

      if(results.length > 0)
      {
        var firstResult = results[0];
        data= (' Company id: ' + firstResult['company_id'] + ';') +dataContent;
        data=('Description: ' + firstResult['description']+ ';') +dataContent;
res.end(data);
      }
                  console.log(data);
  });

  client.end();
  console.log('Connection closed');
  return data;
}
var server = http.createServer(function(req, res) {
db(client);
GetData(client);
});
server.listen(8000);


Its …done. Try to customize code as par requirement.  Feel free to ask any query.
Post a Comment