Web SQL Grow Database for iOS

Question

I need to create an HTML5 Web SQL database (I realize that it's deprecated) for use on the iPad. It used to be with iOS 6 that you could create a 50 MB database right off the bat. But now iOS 7 has introduced a bug (see this article) that prevents a 50 MB database from being created. I understand that the workaround for this bug is to create a small database and grow it as needed. Safari will prompt the user to grow at 10 MB, 25 MB and 50 MB. However, when the user is prompted to increase the size of the database, my JavaScript code that does the inserting of data just stops running. What am I doing wrong? (This happens on iOS 6 and 7.) There's no error thrown from what I can tell. How can I proceed with the inserts after the user prompt to grow the database? My Web SQL insert code is below (some code left out for brevity):

db.transaction(function (tx) {
   var insertSQL = 'insert into ...';

   for (var i = 0; i < len; i++) {
        var items = [{}]; // the items to insert

        tx.executeSql(insertSQL, items, function (tx, results) {
             // When all items are inserted, fire the callback method if there is one
             if ((++itemsInserted === itemsToInsert) && $.isFunction(callback)) {
                  callback();
             }
        }, function (tx, error) {
             if (console && $.isFunction(console.warn)) {
                  console.warn(error.message);
             }
        });
     }
});

The above code works fine on iOS 6 if the database was created initially with 50 MB. However, when the database starts out small, this code is no longer working. Thanks for your time and help on this!

Thanks,

Andy

Answer 1

OK I figured out one way to do this. It's a little sloppy but it works. In the code below, I'm testing for iOS 7, and starting a timer via setInterval. If the data hasn't made it into the local table after a delay of 15 seconds, then I assume it didn't work because the user was stopped by the prompt to increase the DB size. Then it re-tries the insert and should succeed on the 2nd attempt.

// loadData hits a server endpoint to retrieve data and inserts that data into the Web SQL database
function loadData(config) {
    $.ajax({
        url: 'the url',
        //...other properties for the ajax request omitted...
        success: function (data) {
            // Kick off the insert of data into the local DB
            insertLocalData();

            // workaround for iOS 7 - verify whether the insert method's success callback was called
            if (/iphone|ipad/i.test(navigator.userAgent) && navigator.userAgent.indexOf('Version/7') !== -1) {
                config.timerId = setInterval(function () {
                    if (!config.dataInserted) {
                        insertLocalData();
                    }
                    else {
                        clearInterval(config.timerId);
                    }
                }, 15000);
            }

            function insertLocalData() {
                // db is a reference to the local DB and insert is the custom method whose guts can be found in the original question above
                config.db.insert(config.TableName, data.Data, function () {
                    config.dataInserted = true;
                    // Execute callback function if one was given
                    if ($.isFunction(config.successCallback)) {
                        config.successCallback(config);
                    }
                });
            }
        }
    });
}

I hope that Apple fixes this Web SQL bug, but in the meantime perhaps this can help somebody else.

Thanks,

Andy

Answer 2

You should be able to use the error callback of the transaction method as defined in the spec.

database.transaction(
    function (tx) {
        tx.executeSql('INSERT INTO mytable (x, y) VALUES (?, ?)', [x, y], function() {}, function() {});
    }, 
    function (error) {
        if (error.code === 4) {
            // Quota exceeded
        }
    }
);

In iOS 6 and 7, I'm seeing this error callback called with the QUOTA_ERR code always after the user is prompted to increase database size. You will also see this same error if a database write exceeds the hard limit of 50 MB.

I'm uncertain if there is a nice way to determine if you are seeing the database hit the hard limit or if the user was prompted to increase the DB size. If the user increases the DB size or does not allow for it to increase, you will see this same error.