Asynchronous SQL insert in loop

Question

I'm having trouble inserting rows into an SQL database. I want to turn an array of objects into a SQL table in javascript.

The following code only adds the first object of the array. I've tried everything I could find on stackoverflow and elsewhere and can't get it to work.

Any help would be appreciated. Thanks.

        for (var i = 0; i < arr.length; i++) {
            db.save({key:i+"", value:arr[i]}, function(e){

            });
        }

UPDATE 1: I've altered it to mathec's example and narrowed down the problem a little.

The number of rows that get inserted depends on the size of the object being inserted. So it has something to do with the time it takes to process each object.

How do I get around this problem? Thanks.

Update 2:

I've taken Robert Young's suggestion below and included a self contained example.

The example below only inserts the first 5 elements. If I remove some of the word text in the test key so it only says 'word' once, then 10 elements are inserted. So now i'm sure it has something to do with the time it takes to process each object.

<html>
    <head>
        <script src="jquery.js"></script>
        <script src="lawnchair.js"></script>
        <script type='text/javascript'>


            var db = "";
            var arr = [];

            arr.push({name:"a1", test:"word word word word word word word word word word word word word word "});
            arr.push({name:"a2", test:"word word word word word word word word word word word word word word "});
            arr.push({name:"a3", test:"word word word word word word word word word word word word word word "});
            arr.push({name:"a4", test:"word word word word word word word word word word word word word word "});
            arr.push({name:"a5", test:"word word word word word word word word word word word word word word "});
            arr.push({name:"a6", test:"word word word word word word word word word word word word word word "});
            arr.push({name:"a7", test:"word word word word word word word word word word word word word word "});
            arr.push({name:"a8", test:"word word word word word word word word word word word word word word "});
            arr.push({name:"a9", test:"word word word word word word word word word word word word word word "});
            arr.push({name:"a10", test:"word word word word word word word word word word word word word word "});
            arr.push({name:"a11", test:"word word word word word word word word word word word word word word "});

            $(function() {
                db = new Lawnchair({table:'t50'}, function(e){
                    for ( i = 0; i < arr.length; i++) {
                        (function(i) {
                            add_row(i);
                        }(i));
                    }
                });
            });

            function add_row(i) {
                db.save({key:i+"", value:arr[i]}, function(e){

                });
            }

        </script>
    </head>
    <body>

    </body>
</html>

UPDATE 3: I used Robert's suggested code and came up with the following which worked with three small elements. So I altered the first element, making it larger than the others to test it. The first element was not added and the last two were. Is there some time limit for processing the array?

<html>
    <head>
        <script src="jquery.js"></script>
        <script src="lawnchair.js"></script>
        <script type='text/javascript'>

            var arr = [];
            var db = "";

            $(function() {
                db = new Lawnchair({table:'t51'}, function(e){
                    arr=[{key:"k1", value:"v1. Because the contents of this element are larger than the others it will not be added for some reason. Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. "}
                    ,{key:"k2", value:"v2"}
                    ,{key:"k3", value:"v3"}]

                    db.batch(arr, function () {
                        db.all(function (recs) { for (r in recs) {console.log(recs[r].key +"| "+ recs[r].value) } });
                    });
                });

            });

        </script>
    </head>
    <body>
    </body>
</html>

I don't see any problems with the first snippet of code you posted, except that the callback argument isn't necessary if you aren't using it. It seems like something strange is going on. Could you post a [short, self-contained example](http://sscce.org/) of the problem? — Robert Young, May 12 '12 at 00:22
Hi Robert, I've added the complete example you suggested. Any ideas? Thanks. — user1389968, May 12 '12 at 03:24

score 2 · Answer 1 · answered May 11 '12 at 17:28

2

Tricky mistake that I've made myself a few times :-). If a callback function is called inside the loop it will be called asynchronously. So, your loop will continue to execute all the way to the end. A way to solve this problem is to form a closure around the current i value so that when the callback function executes it's bound to the right i. Something like this:

function doSomething(i) {
  console.log(i)
}

var i, len;

for ( i = 0; i < 10; i++) {
  (function(i) {
    doSomething(i);
  }(i));
}

The function inside the loop will be executed immediately, but the i value will be kept in scope.

answered May 11 '12 at 17:28

cmather

1,950
14
17

You need to narrow down the possibilities at this point. Maybe try logging the array value to make sure the iteration is working. Then once that's confirmed, check the db log to make sure the row isn't being rejected for some reason. – cmather May 11 '12 at 17:44
Hi mathec, I narrowed down the problem. The larger the object the less rows get inserted. So if has something to do with the time it takes to process the object. But can't figure out what. – user1389968 May 11 '12 at 18:23

score 1 · Accepted Answer · answered May 12 '12 at 05:51

First, since this is persistent storage, if you run this over and over, you might get different results each time because you don't initialize the persistent store. It might be good to add in a db.nuke() to make sure that you start with a blank slate, at least until you work out any bugs.

The main thing to know about asynchronous storage is that just because the function returns doesn't mean that the data is actually stored yet. So if you run

db = new Lawnchair(function(db){
    db.save({key:"value"})
    db.get("foo", function (rec) { console.log(rec.value) });
});

it may or may not work, because when you call db.get, the value might or might not be stored.

For this to work, we need to be sure that the value is stored before we call db.get. The way to do this is by using callbacks -- if you pass two arguments to db.save, then it will call the second argument once the value is stored. So

db = new Lawnchair(function(db){
    db.save({key:"foo", value:"bar"}, function () {
        db.get("foo", function (rec) { console.log(rec.value) });
    });
});

works fine.

This gets difficult when you're trying to store multiple values, because you want to be sure that all of them are stored. Fortunately, there's a function for this, batch:

db = new Lawnchair(function(db){
    arr=[{key:"k1", value:"v1"},{key:"k2", value:"v2"},{key:"k3", value:"v3"}];
    db.batch(arr, function () {
        db.all(function (recs) { for (r in recs) {console.log(recs[r].key) } });
    });
});

should output

k1
k2
k3

Try it and see what happens.

Hi Robert, I tried your example and just like mine it only works with very small amounts of data. If you replace 'k1' in your example with more text (say a paragraph) only the last two elements get added. — user1389968, May 12 '12 at 18:11
Another interesting thing is when you increase the size of text in the key or value in the first element the last two elements get added to the database, but console.debug never gets called. This is a very odd problem. — user1389968, May 12 '12 at 18:38
This may be a browser-specific problem, because I haven't had problems like that. What browser (version, OS) and what Lawnchair adapter are you using? Is it possible that the browser you're using won't allow you to store large amounts of data? — Robert Young, May 12 '12 at 20:16

score 0 · Answer 3 · answered May 12 '12 at 04:35

Is hard to narrow the solution of the problem because we don't know how you're saving the data to the DB, a AJAX request maybe?, but I think the problem is in the whole logic of saving each row of the array asynchronously, because the code don't wait for one row to be saved to start another, so some kind of race condition is occurring and some rows are saved in the same "id" (autoincrement field) and that explain why only some rows are saved.

My suggestion is send the whole array to the server and then you save each row.

Of course you could have done for each row, first lock the id and then save the data, but is a bad idea because you need a roundtrip to the server and DB for each row, doubling the amount of request to the server.

Hope it helps!

P.D. +1 to @mathec answer for point out the looping variable with callback issue!

Hi Juan, its being stored locally, so there's no need to worry about it being sent to the server. I'll try and test your ID hypothesis but even when I just add one slightly large row (just a paragraph of text in a key) by itself, nothing gets added. So I think its something to do with the size of the element. — user1389968, May 12 '12 at 18:25

Asynchronous SQL insert in loop

3 Answers3