CODEWITHSUNDEEP
javascriptsqlsqliteasynchronouslawnchair
user1389968
user1389968

Reputation: 23

Asynchronous SQL insert in loop

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>

Upvotes: 2

Views: 1200

Answers (3)

Robert Young
Robert Young

Reputation: 313

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.

Upvotes: 1

Juan Leung
Juan Leung

Reputation: 356

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!

Upvotes: 0

cmather
cmather

Reputation: 1950

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.

Upvotes: 2

Related Questions