Wednesday, December 28, 2011

On the Fourth Day of PhoneGapping: Creating a Database from a SQL Dump

On a slightly different track than Day 2 we are going to create a SQLiteDB from scratch using only JavaScript on startup of our application. In order to do this we are going to use the handy JavaScript library HTML5SQL.js by Ken Corbett Jr.

First we'll do a check to see if we've already created our database. If we haven't we'll do a XHR to get the SQL dump file containing all the statements we need to create and populate our tables. Finally, upon success we'll set a flag so this doesn't run every time we start our application.

and here is our sql file:


The html5sql.js lib makes working with the Web SQL specification much easier than hard coding it yourself. You owe it to yourself to look into this library as it will save you some time and hair.
at

17 comments:

  1. PhilipDecember 31, 2011 at 3:45 AM

    Hi Simon
    Thanks for sharing your experimenting with phonegap. I just installed it a few days myself and so I'll be following your progress. Actually, I'm probably only going to need phonegap for packaging my application. Most of the functionality I need I should be able to handle with Sencha Touch. Anyways, exciting times ahead!

    ReplyDelete
  2. Xander DumaineJanuary 24, 2012 at 9:51 AM

    This works great for reading a single file, but I need to get a listing of the files in a directory in the assets folder. Is there a way to get a directory listing of a folder in the assets with XHR?

    Creating a text file manually before hand and reading that doesn't solve my problem, because if that's what I must do, I may as well not use XHR at all, and list the files in the source.

    The purpose of this (for context) is to be able to drop some images in the folder, and the javascript can create an image element for each one, without altering the source.

    ReplyDelete
  3. Simon MacDonaldJanuary 25, 2012 at 12:21 PM

    @Xander

    Currently there is no way to get a listing of the asset directory in PhoneGap short of creating a plugin to do it. It would be a useful plugin and not to difficult. If you want to take it on I'll do coaching.

    ReplyDelete
  4. Mobile MagicMarch 22, 2012 at 9:21 AM

    Hi Simon, thanks for this code, i used it but i get a problem at process method as given below.

    html5sql.process(res, sucCall, failC);
    there is no data in .db file .
    plz help me.

    ReplyDelete
  5. Simon MacDonaldMarch 22, 2012 at 2:27 PM

    @Mobile Magic

    Well it is hard to tell without seeing your code but when you are calling process with "res" is that "responseText"?

    ReplyDelete
  6. Mobile MagicMarch 23, 2012 at 5:52 AM

    Hi Simon
    I got the problem. It was my mistake, there were some html tag remaining in .sql file.

    Thanks again

    ReplyDelete
  7. Sergi KolesnikApril 16, 2012 at 1:11 PM

    @Xander,

    Since you 'drop' images beforehand, you surely know their names. So you just create a csv file (or anyfile for that matter) that will list all names you need to read.

    If you are downloading images dynamically over net connection, that save them to permanent storage. And from there you can request what files are stored in the directory.

    So, is plugin to read files in the asset directory really needed? Because, once again, you know beforehand what files are listed there.

    ReplyDelete
  8. rJune 25, 2012 at 4:22 PM

    hello.

    is there any way to got it work with ios?

    ReplyDelete
  9. Simon MacDonaldJune 26, 2012 at 11:56 AM

    @r

    I can't see why this wouldn't work in iOS.

    ReplyDelete
  10. AnonymousJuly 24, 2012 at 12:24 AM

    Thanks to Simon and Ken.

    Question: anyone able to run this in Chrome without getting a security error?

    SECURITY_ERR: DOM Exception 18

    The error is happening when I attempt to get the sql file.

    request.open("GET", "mydb.sql", true);

    The sql is in the same location as index, and I see the error if I use the full path "file:///users/me/myfolder/mydb.sql"

    I've run Chrome with a flag to allow-file-access-from-files.

    Also in my mobileinit function I have:
    $.support.cors = true;
    $.mobile.allowCrossDomainPages = true;

    Sure would be nice to debug this thing in Chrome!

    ReplyDelete
  11. Simon MacDonaldJuly 24, 2012 at 1:36 PM

    @Annette

    You need to set:

    -disable-web-security

    as well.

    http://joshuamcginnis.com/2011/02/28/how-to-disable-same-origin-policy-in-chrome/

    ReplyDelete
  12. UnknownFebruary 19, 2013 at 8:08 AM

    Hi simon,
    I have developed android app using phonegap. I have used sqllite and local storage in that app. The application gets closed suddenly while using. I am facing this problem in android device only not in iPhone.

    Used phonegap version 2.3.0

    Tested android device 4.0.4

    I am getting this error in logcat

    02-19 15:41:31.795: E/dalvikvm(20055): JNI ERROR (app bug): local reference table overflow (max=512)
    02-19 15:41:31.795: W/dalvikvm(20055): JNI local reference table (0x12bada0) dump:
    02-19 15:41:31.795: W/dalvikvm(20055): Last 10 entries (of 512):
    02-19 15:41:31.795: W/dalvikvm(20055): 511: 0x2bb95f70 android.content.res.AssetManager
    02-19 15:41:31.795: W/dalvikvm(20055): 510: 0x2ce0b078 byte
    02-19 15:41:31.795: W/dalvikvm(20055): 509: 0x2ce03060 byte
    02-19 15:41:31.795: W/dalvikvm(20055): 508: 0x2cdfb048 byte
    02-19 15:41:31.795: W/dalvikvm(20055): 507: 0x2cdf3030 byte
    02-19 15:41:31.795: W/dalvikvm(20055): 506: 0x2cdeb018 byte
    02-19 15:41:31.795: W/dalvikvm(20055): 505: 0x2cde3000 byte
    02-19 15:41:31.795: W/dalvikvm(20055): 504: 0x2cddafe8 byte
    02-19 15:41:31.795: W/dalvikvm(20055): 503: 0x2cdd2fd0 byte
    02-19 15:41:31.795: W/dalvikvm(20055): 502: 0x2cdcafb8 byte
    02-19 15:41:31.795: W/dalvikvm(20055): Summary:
    02-19 15:41:31.795: W/dalvikvm(20055): 1 of java.lang.Class
    02-19 15:41:31.795: W/dalvikvm(20055): 510 of byte (510 unique instances)
    02-19 15:41:31.795: W/dalvikvm(20055): 1 of android.content.res.AssetManager
    02-19 15:41:31.795: E/dalvikvm(20055): Failed adding to JNI local ref table (has 512 entries)
    02-19 15:41:31.795: I/dalvikvm(20055): "Thread-2487" prio=5 tid=16 RUNNABLE
    02-19 15:41:31.795: I/dalvikvm(20055): | group="main" sCount=0 dsCount=0 obj=0x2bbe6160 self=0x1c34b0
    02-19 15:41:31.795: I/dalvikvm(20055): | sysTid=20078 nice=0 sched=0/0 cgrp=[fopen-error:2] handle=2394304
    02-19 15:41:31.795: I/dalvikvm(20055): | schedstat=( 0 0 0 ) utm=109 stm=59 core=0
    02-19 15:41:31.795: I/dalvikvm(20055): at android.content.res.AssetManager.readAsset(Native Method)
    02-19 15:41:31.795: I/dalvikvm(20055): at android.content.res.AssetManager.access$700(AssetManager.java:35)
    02-19 15:41:31.795: I/dalvikvm(20055): at android.content.res.AssetManager$AssetInputStream.read(AssetManager.java:573)
    02-19 15:41:31.795: I/dalvikvm(20055): at dalvik.system.NativeStart.run(Native Method)
    02-19 15:41:31.795: E/dalvikvm(20055): VM aborting
    02-19 15:41:31.795: A/libc(20055): Fatal signal 11 (SIGSEGV) at 0xdeadd00d (code=1)

    Thanks

    ReplyDelete
  13. Simon MacDonaldFebruary 19, 2013 at 11:23 AM

    @niranjana devi

    What is in your assets folder? This is a signal 11 problem coming from the OS. What is in assets that it is trying to read?

    ReplyDelete
  14. UnknownFebruary 20, 2013 at 2:04 AM

    Hi Simon,

    I am having normal html,js, and css files in my assets. Normally i m getting this error when my app size goes beyond 50 mb. I searched in google about this.. i found few links like

    http://stackoverflow.com/questions/12803309/android-webview-asset-reference-memory-leak

    http://stackoverflow.com/questions/9278149/android-webview-memory-leak-when-using-assets

    that says to load html file from internal or external storage instead of assets. But i don't know this is correct way or not.

    Do you have any idea to resolve this? Is there any way to clear cache?

    Thanks,

    ReplyDelete
  15. Simon MacDonaldFebruary 20, 2013 at 10:01 AM

    @niranjana devi

    50mb is the usual maximum file size for an apk. You will probably need to use an expansion file.

    https://developer.android.com/google/play/expansion-files.html

    ReplyDelete
  16. lse123August 5, 2013 at 8:42 AM

    I got fail when run localhost on desktop... well required mobile device? phonegap library must also included? With other way can run on desktop but without phonegap.js and html5sql.js?

    ReplyDelete
  17. Simon MacDonaldAugust 5, 2013 at 9:31 AM

    @lse123

    Yes, you can do this from a desktop but the xhr call is probably failing for you with a CORS issue. Look at http://html5sql.com/ for more info on the library.

    ReplyDelete