Batch insertion to client side database(WEB SQL) in HTML 5/Javascript

Question

Is it possible to use Batch operation in client side database in HTML 5 ?

Answer 1

I assume you're referring to WebSQL?

At this time, WebSQL is basically SQLite in a wrapper. SQLite does not support inserting multiple rows in one INSERT statement. But you can make one query with placeholders and execute it multiple times in a single transaction to improve performance and ensure integrity.

If that does not answer your question, you should clarify what exact problem you are having.

Answer 2

I'm a couple of years late, but i'd like to supplement DCoder's answer with some (additional) suggestions and code!

First, if you're reading this, you probably shouldn't be using WebSQL. It has been deprecated in favor of IndexedDB, which at this point is the only database on the W3C standards track.

If, for whatever reason, you're intent on staying with WebSQL, you're probably aware that the asynchrosity of its default API means that multi-statement transaction code has a good chance of being complex and unwieldy.

Check out BakedGoods if you want to save yourself the trouble of writing such code. Using the library, inserting one or more items in to a table, for example, is as simple as:

bakedGoodsd.set({
    data: [{key: "key1", value: "value1"}, {key: "key2", value: "value2"}]
    storageTypes: ["webSQL"],
    complete: function(byStorageTypeStoredItemRangeDataObj, byStorageTypeErrorObj){}
});

... which is a lot more concise than what you'd have to write to do the same using the raw API:

function insertData(transaction)
{
    var dataInsertStatement = "INSERT INTO Main(key, value) VALUES (?, ?)";
    var dataArray = [{key: "key1", value: "value1"}, {key: "key2", value: "value2"}];

    int i = 0;

    function advance()
    {
        if(++i < dataArray.length)
            insertDataItem();
    }

    function insertDataItem()
    {
        transaction.executeSql(
            dataInsertStatement, 
            [dataArray[i].key, dataArray[i].value], 
            advance
        );
    }

    insertDataItem();
}


function conductDataInsertTransac(database)
{
    database.transaction(insertData);
}

window.openDatabase("Baked_Goods", "", "Baked Goods", 1024*1024, conductDataInsertTransac);

The simple interface and unmatched storage facility support of BakedGoods comes at the cost of lack of support for some storage facility-specific configurations. For instance, it does not support the conduction of storage operations in WebSQL tables with multi-column primary keys.

So if you make heavy use of those types of features, you may want to look elsewhere.

Oh, and for the sake of complete transparency, BakedGoods is maintained by yours truly :) .