Please note, this is a STATIC archive of website www.tutorialspoint.com from 11 May 2019, cach3.com does not collect or store any user information, there is no "phishing" involved.
/* to connect with db first we need to import that module*/
var sql= require("./mysql");
var client = sql.createConnection({host:"localhost", user:"root"}); //at this point we can also send the password for db connecttion, ALL CAN PASS DATABASE name.
client.connect();
/*
CLIENT.CONNECT(FUNCTION(ERR){
IF (ERR){
RETURN CONSOLE.LOG("CONNECTION FAILED");
}
CONSOLE.LOG("CONNECTION SUCCEED");
}
*/
// if db is already created then call
client.query("use bookdb");
// if want to create a new database then call
client.query("create database bookdb", function(err){
if (err){
client.end()
console.log(err);
}else{
console.log("Database is created");
}
});
client.query("use bookdb");
// NOW CREATE DATABSE tables-----
var sql="CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))";
var sql1= "CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))";
// sql1 is query with primary key
var sql2 = "INSERT INTO customers (name, address) VALUES ('Company Inc', 'Highway 37')";
// sql2 is query for insert data.
var sql3 = "INSERT INTO customers (name, address) VALUES ?";
var values = [
['John', 'Highway 71'],
['Peter', 'Lowstreet 4'],
['Amy', 'Apple st 652'],
['Hannah', 'Mountain 21'],
['Michael', 'Valley 345'],
['Sandy', 'Ocean blvd 2'],
['Betty', 'Green Grass 1'],
['Richard', 'Sky st 331'],
['Susan', 'One way 98'],
['Vicky', 'Yellow Garden 2'],
['Ben', 'Park Lane 38'],
['William', 'Central st 954'],
['Chuck', 'Main Road 989'],
['Viola', 'Sideway 1633']
];
// sql3 is query for insert data.
client.query(sql, function(err,result){
if (err){
return console.log("table is not created");
}
console.log("table has created");
});
//for sql3
client.query(sql, [values], function(err,result){
if (err){
return console.log("table is not created");
}
console.log("table has created with no of rows"+ result.affectedRows);
});
/* THESE ALL THING RESULTS CONTAINS AFTER EXECUTING QUERY
{
fieldCount: 0,
affectedRows: 14,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '\'Records:14 Duplicated: 0 Warnings: 0',
protocol41: true,
changedRows: 0
}
*/
/*===============================================*/
/*WHEN WE USE SELECT QUERY THEN WE NEED TO TAKE ONE MORE PARAMETER "fields" IN FUNCTION INSIDE CLIENT.QUERY()
this fiels give all other details of fetch data
*/
/*===============================================
// 1
var adr = 'Mountain 21';
//Escape the address value:
var sql = 'SELECT * FROM customers WHERE address = ' + mysql.escape(adr);
// 2
var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE address = ?';
con.query(sql, [adr], function (err, result)
// 3
var name = 'Amy';
var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE name = ? OR address = ?';
con.query(sql, [name, adr], function (err, result)
*/
/*
DROP TABLE customers
DROP TABLE IF EXISTS customers
============================================================================
SELECT * FROM customers LIMIT 5 = starting 5 rows
SELECT * FROM customers LIMIT 5 OFFSET 2= 5 rows but start from 3rd row.s
*/
exports.authenticateUser=function(username, password, callback){
var strQuery="select * from users where username='"+username+"' and password='"+password+"';";
client.query(strQuery,function(err,rows){
if (err || !rows){
return callback(err);
}else if (rows.lenth===0){
return callback(err);
}else{
return callback(null, "success");
}
});
}
exports.addUser=function(username,password,address,res,callback){
var strQuery="insert into users values('"+username+"','"+password+"','"+address+"');";
client.query(strQuery,function(err, saved){
if (err || !saved){
return callback(err);
}
else{
callback(null,saved);
}
});
}
/*=======================================CONNECT WITH MONGODB====================================================*/
var databasrUrl = "localhost/bookdb";
var collections = ["users","books"] // collections are like tables present in mongoDB.
var db = require("./mongojs");
db.connect(databasrUrl, collections); // this use when already database is created.
db.connect(databasrUrl,function(err,db){
if(err){
return console.log("error occured");
}
console.log("database is created");
db.close();
});
var dbo = db.db("mydb"); // name of database
/*
Important: In MongoDB, a database is not created until it gets content!
MongoDB waits until you have created a collection (table), with at least one document (record) before it actually creates the database (and collection).
*/
db.createCollctions("customers", function(err,result){
if(err){
return console.log("error occured");
}
console.log("Collctions is created");
db.close();}
);
db.collection("customers").insertOne({name : "avinash", company : "infosys", address : "Maharamau"},function(err, result){
if(err){
return console.log("row not inserted");
}
console.log("data inserted");
db.close();
});
/* If you try to insert documents in a collection that do not exist, MongoDB will create the collection automatically. */
dbo.collection("customers").find({}).toArray(function(err, result) {
if (err) throw err; // in this no parameter given in find function
console.log(result);
db.close();
});
//updateCommands
var myquery = { address: "Valley 345" };
var newvalues = { $set: {name: "Mickey", address: "Canyon 123" } };
dbo.collection("customers").updateOne(myquery, newvalues, function(err, res) {
if (err) throw err;
console.log("1 document updated");
db.close();
});
function printHello() {
console.log( "Hello, World!");
}
// Now call above function after 2 seconds
var t = setTimeout(printHello, 2000);
// Now clear the timer
clearTimeout(t);
