Blog

22
January 2015

Gavin Pickin

Using WebSQL in my Mobile App - Lessons learned - The hard way

Javascript, Mobile Development, WebSQL

I have been working on my Multiple Platform App, using WebSQL for bigger storage needs, instead of simple Locale Storage, in which my App started with. My last blog post walked us through opening a db connection with WebSQL, creating tables, inserting data, inserting data and protecting your app from SQL Injection using placeholders, and the 2 types of transactions, .transaction() which locks the db, and .readTransaction() which does not lock the whole db, and can only be used for ready only sql… as the name suggests.

Of course, the more you dig into something, the more you learned, and I think I learned a few important things to share.
And Yes, that title is beyond a little dramatic.

Primary Keys

SQLite, which is what WebSQL is based off of, allows you some pretty nice primary key functions… and one of my tables has an id with the following flags.

ID INTEGER PRIMARY KEY ASC AUTOINCREMENT

The interesting thing with primary keys though… it seems as if they are REQUIRED fields in your inserts. Maybe I’m just spoiled with mySql, to be honest its been so long since I’ve done much dev with Postgres, MS Sql and Oracle to know how they behave, but in MySql, you don’t need the primary key if its auto increment, it just handles it under the covers.

So, lesson, even for a simple insert, you need something like this

INSERT INTO myTable ( ID ) values ( null );


Different Insert Syntax

In the last post, we shows a simple insert, and we showed a simple insert with placeholders, and followed them with an array of values for placeholders. Most DB tools give you a lot of flexibility, and you can insert many rows at a time, or change the insert syntax. WebSQL SQLite is a little more strict.

'INSERT INTO myTable ( ID ) values ( null )' - works

'INSERT INTO myTable ( ID ) values ( ? )’, [ null ]- works

‘INSERT INTO myTable set ID = null’ - DOES NOT WORK

Now, I have seen online some people mentioning you cannot insert multiple lines at once, but in Safari I have got the following to work

'INSERT INTO myTable ( ID ) values ( ? ), ( ? )’, [ null, null ]

But in Chrome, it didn’t support it, so I’m not sure if iOS, Android, or Windows8 will support it not either… I will have to test it… we’ll leave that for another day.


Our Friend Async

Now, for those developers new to Javascript, the world of sync and async can be a little strange at first… and dealing with Async is more Reactive Programming than a lot of Procedural Server Side languages that most of come from. I know with ColdFusion, unless you say so, its all Procedural, synchronous code, and it blocks.

The trick with Javascript is, what is Async and what is not Async. General rule is… if you reach outside Javascript VM… then it is Async, and you have to deal with it, using Callbacks, or Deferreds/Promises.

Local Storage is synchronous, because its in memory, essentially part of the VM, so all my code for Local Storage was written in sync. Now I am changing code over to WebSQL, calls are not in sync, they are async, and therefore something simple like the following, gets a lot messier.

userData = userDAO.getUser();
$(‘#firstName’).text(userData.firstName);

If you had this code in a method, the call would go out for getUser() and since you are expecting it to return data to act on, and it wouldn’t return in sync, userData.firstName would not be populated.

Its not that this Async is hard, its just something else to consider when you’re making the switch from Local Storage to WebSQL or NoSQL.

So how do you code for Async?
You’ll have to wait for the next post.

Leave me feedback if I’m off base, remember, I’m learning about this too.

Thanks for reading.

Blog Search