How to add table of values of html to mysql using node .js

Question

I'm doing a project on automatic time table generation system.I'm new to node .js .This is the program to insert table of values from html to mysql. When I run this program ,it is leading to a deadlock condition.

This is my index .js file.

var express=require('express');
 var app=express();
 app.set('view engine','ejs');
 app.use(express.static('./public'));
 app.use(express.static("."));
 var Array=require('node-array');
 var mysql = require('mysql');
 var s=require('string');

 //Connection pool
 var pool=mysql.createPool({
 connectionLimit:10,
 host: "localhost",
 user: "root",
 password: "user@1235",
 database:"time_table"
 });

app.post('/expt2',urlencodedParser,function(req,res){
  var table=req.body.table1;
  console.log(req.body);

  var data=[];
  for(var key in (req.body)){
    data.push(req.body[key]);
  }
  for(i=0;i<(data.length-1);i++)
    console.log(data[i]);

  var i=0;
  while(i<(data.length-1))
  {
    lg=data[i];
    console.log(lg.substr(2,1));
    if(lg.substr(2,1)=="4")
    {   
      sem=4  ;  
      room="lh1"; 
    }
   else if(lg.substr(2,1)=="6")
   {
     sem=6;
     room="lh2";
   }
  else if(lg.substr(2,1)=="8")
  {
     sem=8;
     room="lh3";
  }
  else
  {
     sem=5;
     room="lh2";
  }

  day="Thrusday";
  console.log(room);
  console.log(sem);
  pool.getConnection(function(err,con){
    if (err) throw err;
    console.log("Connected!");
    console.log(lg);

    pool.query("select fid from course where 
    cid='"+lg+"'",function(err,result)
    {

     if(err)throw err;
     else 
     {
        console.log("okay");
        console.log(result);

        Object.keys(result).forEach(function(key){
        row=result[key];
        console.log(row.fid);
        ob=row.fid;
        console.log(ob);
        var sql="INSERT INTO classroom(sem,day,timings,fid,cid,room_name) 
        VALUES('"+sem+"','"+day+"','12:00:00','"+ob+"','"+cid+"','"+room+ 
        "');"

        con.query(sql,function(err,result)
        {

          if(err)throw err;
          console.log("one row inserted");
          //con.release();
        });
       });
   }
  });
  });
  i++;}
 });

This is expt2.html file.

<html>
    <head>
      <title>Master-Timetable</title>
      <link rel="stylesheet"           
      href="/public/asset/css/bootstrap.min.css" type="text/css"/>
      <script type="text/javascript" src="/public/asset/popper.min.js"> 
      </script>
      <script src="/public/asset/js/bootstrap.min.js" 
      type="text/javascript"></script>
      <script src="/public/asset/jquery-3.2.1.min.js" 
      type="text/javascript"></script>

    </head>

    <body>
     <form id="contacth" method="POST" action="/expt2"></form>
      <table id="table1">
       <tr class="head">
        <th>Time->day|</th>
        <th>7:30-8:30</th>
        <th>8:30-9:30</th>
        <th>9:30-10:30</th>
        <th rowspan="19">BREAK</th>
        <th>11:00-12:00</th>
        <th>12:00-1:00</th>
        <th rowspan="19">LUNCH BREAK</th>
        <th>2:30-3:30</th>
        <th>3:30-4:30</th>
        <th>4:30-5:30</th>
      </tr>
      <tr>
       <th rowspan="3" class="parent" value="monday">Monday</th>
        <td class="child"><input type="text" maxlength="5" name="am1" 
        form="contacth"></td>
        <td ><input type="text" maxlength="5" name="am2" form="contacth"/> 
        </td>
        <td><input type="text" maxlength="5" name="am3" form="contacth"/> 
        </td>
        <td><input type="text" maxlength="5" name="am4" form="contacth"/> 
        </td>
        <td><input type="text" maxlength="5" name="am5" form="contacth"/> 
        </td>
        <td><input type="text" maxlength="5" name="am6" form="contacth"/> 
        </td>
        <td><input type="text" maxlength="5" name="am7" form="contacth"/> 
        </td>
        <td><input type="text" maxlength="5" name="am8" form="contacth"/> 
        </td>
      </tr>
      <tr>
       <td>
        <input type="submit" name="t_save" value="save" class="btn btn- 
        success" form="contacth"/><br/>
      </td>
     </tr>
   </table>
  </body>
 </html>

I got a suggestion to use "async" and "await".But I'm not really getting where to use "async" and await in my code.

you can put your business logic inside a function by making it modular and you can use async await their or you can use promise to handle async flows.Use pool.getConnection() method and get a connection and then query using the connection and then close the connection — Biswadev, May 01 '18 at 11:00

score 0 · Answer 1 · answered May 01 '18 at 10:17

0

Try not querying the pool directly. Replace the first pool.query with con.query.

See this question: Difference between using getConnection() and using pool directly in node.js with node-mysql module?

answered May 01 '18 at 10:17

MarCPlusPlus

366
1
5

Error: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect integer value: ',,,,,,,,102' for column 'fid' at row 1 at Query.Sequence._packetToError (C:\sh\node_modules\mysql\lib\protocol\sequences\Sequence.js:52:14) at Query.ErrorPacket (C:\sh\node_modules\mysql\lib\protocol\sequences\Query.js:77:18) at Protocol._parsePacket (C:\sh\node_modules\mysql\lib\protocol\Protocol.js:279:23) – Sharanya K G May 01 '18 at 14:03
Try removing the double quotes from both sides of +ob+ – MarCPlusPlus May 01 '18 at 14:46
It looks like the variable ob is a string and fid is expecting an integer. Make sure you are passing appropriate data types and values for the given columns. – MarCPlusPlus May 01 '18 at 15:00
Ob is an integer variable. – Sharanya K G May 01 '18 at 15:21
New error - ER_LOCK_DEADLOCK: Deadlock found when trying to get lock ; try restarting transaction at Query . sequence._packetToError – Sharanya K G May 01 '18 at 15:24
What if you uncomment con.release() – MarCPlusPlus May 01 '18 at 17:35
It has no effect. It is again leading to a same problem ( deadlock ) – Sharanya K G May 01 '18 at 18:15

How to add table of values of html to mysql using node .js

1 Answers1