1

I am writing some non-web app helper, and came across a need for a synchronous query call.

Basically, within a loop I need to check the database to see if the value exists. If it doesn't then insert the value. Currently, with node-mysql I can only get it to work with a callback. Because of that, node.js treats the call as asynchronous and keeps processing my request before the query is finished. This is a big issue because in the end it could be inserting duplicates because they were in the queue.

Ideal Solution - doesn't work. Results is actually the object of client, and I can't find the actual results within. However this does make it synchronous.

results = client.query('SELECT COUNT(md5) as md5Count FROM table WHERE md5 = "' + md5 + '"')

The following does not work. Node.js treats it as asynchronous, and outerResult is still the object of client.

outerResult = client.query('SELECT COUNT(md5) as md5Count FROM board WHERE md5 = "' + md5 + '"',  function  selectCb(err, results, fields) {console.log(results);});

Any help is appreciated.

user761974
  • 13
  • 3
  • As a side note, do not form your queries like this as you introduce the possibility of SQL injection into your application. See http://stackoverflow.com/a/15779796/253594 – Vetsin Jul 10 '13 at 03:10

2 Answers2

1

Basically, within a loop I need to check the database to see if the value exists. If it doesn't then insert the value. Currently, with node-mysql I can only get it to work with a callback. Because of that, node.js treats the call as asynchronous and keeps processing my request before the query is finished. This is a big issue because in the end it could be inserting duplicates because they were in the queue.

There is an asynchronous solution, there always is.

basically your worried that duplicate entries could be entered.

I presume you have an array of data to loop through. Your problem is solved with _.uniq or some other filter solution.

So you simply call _.uniq(md5s).forEach(function() { })

Raynos
  • 166,823
  • 56
  • 351
  • 396
1

Basically, within a loop I need to check the database to see if the value exists. If it doesn't then insert the value.

This is a problem best served with SQL. You don't solve this problem by talking to the database repeatedly, you solve this problem by having SQL only insert where the index value doesn't already exist.

INSERT INTO mytable ( name, address ) 
SELECT @name, @address FROM DUAL 
WHERE NOT EXISTS (SELECT * FROM mytable WHERE name = @name, address = @address)

This is a super simplified example, and not the most optimized. You can do the same thing here with sets of data, instead of record by record, if you like.

Raynos
  • 166,823
  • 56
  • 351
  • 396
jcolebrand
  • 15,889
  • 12
  • 75
  • 121
  • I should mention that the operation is much faster if you can insert many records at once, by building a temptable and joining... – jcolebrand May 20 '11 at 13:28
  • I have done more research on my end, and the creator of node-mysql stated that it can't be made synchronous. Because of that, I see the doing the filter within SQL as the best overall option. I will definitely try out the temp table. With further fooling around I was able to "trick" node js into waiting. However, I would not utilize this solution for anything that required extreme speeds. I basically used an EventEmitter within the query callback and had the parent script wait for the call. – user761974 May 22 '11 at 22:54