Inserting multiple values in Mysql using Nodejs and notifying user with a response

Question

I am making a new webservice where i send a curl command with JSON and the JSON contains a array as

[{tempid:1,email:abc@123,address:asd},{tempid:2,email:abc@12345,address:asd45},{tempid:3,email:abc@1234,address:asd4}]

Now when i pass and insert the array in a mysql table tempid is just to show a mapping to the user to the contact id generated in the database as tempid:1 is now inserted and in database it has cid 120 , like this for tempid2 and 3 ,

But when i am trying to show the client the updated values it shows only one value , last last change not the whole updated Array. Its becuase of the async nature of the connection.querry function , so i need help in this , here is my webservice

contactadd webservice -->

for(var i=0;i<=request.body.contact.length-1;i++)
            {
             if(request.body.contact[i].tempid)
                { var ardata=new Array();
                    var o=request.body.contact[i];
                    pair=Object.keys(o).map(function(a){ return [a, o[a]] });
                    AM.addcontact(pair,request.session.user,request.body.contact.length,function(e,o){
                        if(!o)
                        {
                             response.send('something went wrong'+e);

                        }
                        else
                        {
                           //response.send(o);
                        }

                    });


                }
    }

Here is the update function in the database.js script -->

//ContactSync-addcontact module for database 
exports.addcontact=function (arr,email,addnum,callback)
{ 
  var counter=0;
  var uid;
  var data=new Array();
  var showinsert=new Array();
  var values=new Array();
  var datatable=new Array();
  var inserting=new Array();

  var tempid=0;
   connection.query('SELECT UID FROM user where email1="'+email.email+'"',function(err,rows,fields){
    if(err)
    {
     throw err;
    }
    else
    {
    if(rows[0]!=undefined)
    {
     uid=rows[0]['UID'];
    }
    else
    {
    uid="no id in database";
    }
    }
    });// get the UID of the inserting user

    // make array of user provided data
    for(var j=0;j<=arr.length-1;j++)
    {
     if(arr[j][0]!='tempid')
     {
      data.push(arr[j][0]);
     }
     else
     {
      tempid=arr[j][1];
     }
    }

    connection.query('SELECT column_name FROM information_schema.columns where table_schema="webservice" AND table_name="usercontacts"',function(err,rows,fields){
    if(err)
    {
     throw err;
    }
    else
    {

    for(var i=0;i<=rows.length-1;i++)
    {
      datatable.push(rows[i]['column_name']);

    }
    }
       for(var k=0;k<=datatable.length-1;k++)
        {
          if(inArray(data[k],datatable))
          {
            inserting.push(data[k]);
          }
        }
        if(inserting.length>0)
        {


            for(var z=0;z<=arr.length-1;z++)
            {
              if(inArray(arr[z][0],inserting))
               { 

                 values.push('"'+arr[z][1]+'"');

               }
            }


            // Insert tempid values and data in the usercontacts table with inserting and values

            connection.query('INSERT INTO usercontacts (cid,uid,'+inserting+') VALUES("","'+uid+'",'+values+')',function(err,rows,fields){
            if(err)
            {
            throw err;
            }
            else
            {  
                connection.query('SELECT * FROM usercontacts WHERE uid="'+uid+'" ORDER BY cid DESC LIMIT 0,'+addnum+'',function(err,rows,fields){
                if(err)
                {
                    throw err;
                }
                else

                {   showinsert.push('temp-id: '+tempid+',cid:'+rows[0].cid+',uid:'+uid);
                    //for(var i=0;i<=inserting.length-1;i++)
                    forEach(inserting,function(row,index)
                    {
                      showinsert.push(inserting[index]+":"+values[index]);
                      counter+=1;
                    });

                    callback(null,showinsert);

                }

                });
            }

            });

            //insertion finished 


        }
        else
        {
          callback("Please Provide atleast one field to enter with tempid");
        }

  });


}

I just need to insert all the callback in a array which has been inserted and show user that array ,please help , completely stuck and then only i am trying StackOverflow.

Thank you for reading till the end BTW

Three points of contention... First, would suggest wrapping your check/insert into a single SQL statement, that returns the existing, or new uid for a given user matching against an email address... Second, would suggest parameterized queries, not string concatenation... Third, look into npm install async --save the async module will provide methods for iterating against your collection, and returning a completed result set or error. Fourth, you should use a transaction for your entire process so that it can success or fail as a whole. — Tracker1
– Tracker1, Commented Nov 19, 2013 at 21:00
hi thanks for your comment , pastebin.com/V35UZdPw i have pasted my new updated code on the service side , i am running a foreach but the variable ardata remain empty/nothing on response , please help — Divyanshu Negi
– Divyanshu Negi, Commented Nov 19, 2013 at 21:53
ardata is a simlple variable i have tried that , infact i have just used Async.series — Divyanshu Negi
– Divyanshu Negi, Commented Nov 19, 2013 at 23:05

Richard Marr · Accepted Answer · 2013-11-19 23:27:37Z

1

I'm not sure what the specific problem is, but there are some problems with the code you've shared that will bite you sooner or later. One of these may be causing your problem.

Race conditions

If the query SELECT UID FROM user where email1= for any reason takes longer than the SELECT column_name FROM information_schema.columns just below it then you won't have a value for the variable uuid and your logic will fail. Remember that these calls are non-blocking, so you can't rely on one finishing before the other one unless they're nested or use another flow-control mechanism (As @Tracker points out, async is popular).

Catching edge cases

In the line below you're assigning a string value to the uid variable and then continuing to use that variable even though it now contains an error message.

    uid="no id in database";

Doing that means that your code later on will have trouble reacting. Instead use a different variable, leave the uid = undefined or immediately return the callback with an error, e.g.

    return callback(new Error("user not found"));

Reporting errors

Don't throw errors in Node unless you want to kill the process, e.g. dependency problems during server startup. It doesn't work like Java, async errors are not caught by try/catch and will kill your process or leave you in a state that's hard to reason about. Instead make the error object your first parameter to the callback and return it immediately, like this:

    if ( err ) return callback(err);

Then in your client code you can always check the first parameter to see if there was a problem.

Security problem

As @Tracker mentioned, don't ever do the this:

    connection.query('SELECT UID FROM user where email1="'+email.email+'"', ...

If the value of the variable is passed through as "; drop table user; or similar then you're in trouble. Instead you can use node-mysql's build in escaping like this:

    connection.query('SELECT UID FROM user where email1=?', [email.email], ...

Whitelist

You're querying information_schema.columns in order to detect which fields are valid then inserting them into usercontacts. This is a clever trick, but increases a 3 query process to 4 queries, and raises questions if there are any fields that a user shouldn't be inserting data into. Using a column whitelist may seem like more code to maintain, but would actually be simpler than all the code required to match columns dynamically.

Arrays

I don't see the source for the function inArray() but it looks like it does the same as Array.prototype.indexOf() so it may be better to use that. e.g.

    if ( datatable.indexOf(data[k]) > -1 ) inserting.push(data[k]);

Every line of custom code you can delete is a line of code you don't have to maintain.

answered Nov 19, 2013 at 23:27

Richard Marr

3,0741 gold badge24 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Divyanshu Negi Over a year ago

thanks a lot for your valuable time and good lesson , i will always remember this from now. Thanks again.

Divyanshu Negi Over a year ago

I am making it from scratch again , this time i am using nesting of queries , is that a bad practice ?

Richard Marr Over a year ago

Nesting is fine, but can become hard to follow if you go too deep. My suggestion would be to use nesting to begin with until you're comfortable with this style of code, then look into async. I'm not a fan of promises personally but that's an option too. Another tip, if you give your anonymous functions a name, e.g. doSomething(function myUniquelyNamedCallback(){ ... }), then the name will show up in your stack traces instead of 'anonymous function'.

Richard Marr Over a year ago

Another tip... use node-inspector to add breakpoints to important points and you'll be able to walk through your app step by step, looking at the values of variables as you go

Collectives™ on Stack Overflow

Inserting multiple values in Mysql using Nodejs and notifying user with a response

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related