Blog

26
May 2015

Gavin Pickin

A quirk of Cross Platform WebSQL in Hybrid Mobile Development for iOS Android and Windows

Android, Cordova / Phonegap, Ionic, Javascript, Mobile Development, Techie Gotchas, WebSQL

When is WebSQL not compatible across devices? This is really a quick question, as if you recall reading in previous blog posts ( if you haven’t read them, I urge you to ) I am using WebSQL for my hydrib mobile app development with Phonegap and Cordova… and although WebSQL is supported on Android and iOS, it is not natively supported on Windows Phones / Devices. That is where the lovely people at MS OpenTech come in, Microsofts Open Source company, which wrote a WebSQL wrapper for SQLite to allow you to work with SQLite like you would WebSQL. FANTASTIC, until you hit those issues… so lets look at a couple of them.

I have not tested every SQL syntax, I am sure there are plenty of other issues, and gotchas, like the “Loading or Duping Large Amounts of Data into WebSQL - Options with Performance Numbers” where there are differences with multiple queries, vs single query multi value. 

Today, I am reminded that sometimes the simplest of SQL is not available. While I am working on my app, I quite often change the DB structure, adding new tables to my app. In that case, I usually use:
CREATE TABLE IF NOT EXISTS test

This obviously, creates a table called test, if it does not exist. Quite handy, when you’re adding  tables to your app, if it doesn’t exist, it adds it. If you try and add a table, that does exist, if gives you an error, so this is a painless way to do that. 

If you want to change the data structure of a table, and you do not want to deal with migration scripts… in development, you simple drop the table, and then create it, so you might just use:
DROP TABLE test

That will of course  error, if the table doesn’t exist… so I usually use:
DROP TABLE IF EXISTS test.

You might say: 'Thanks Gavin for the advice, but whats you’re point?'

Even thought SQLite documentation seems to support the IF EXISTS and IF NOT EXISTS, this wrapper written around the SQLite library, by MSOpenTech in the Cordova plugin, does not actually support this. 

So instead of using:
DROP TABLE IF EXISTS test 

I simple use:
DROP TABLE test

And then I have to catch the error it throws when it tries to drop a table that does not exist.
Of course, if I drop the table, all the time, then I can just CREATE the table.

Its not a complicated incompatibility, but when you develop and test on iOS... and then convert to Windows, which uses a wrapper, you need to remember.

KISS - Keep it simple stupid.

I wonder if the code using joins in updates work as expected... I guess we'll find out soon.

 

Thanks for reading.
 

Blog Search