SQLITE DATABASE

[Abhinav Jaiswal]

Hey hii Kerri,

I have one specific doubt related to PhoneGal. I'm using SQLite plugin for storing and retrieving data.

Currently what I have understand through its documentation on git hub or their site is after installing the application when you open the app for the first time then only databases and table is getting created and we can execute all our SQL statements trough transaction statements.

However, I have a pre-populated DB which I have created through SQLite Browser. This will contain a list of records which I need to fetch in a drop down in my app. Now, where I can include this pre-populated DB in the app bundle. So that this DB is only getting used.

My target devices are both iOS and Android.

Please help me on this.

[Zoltan Birner]

Hi,

you should use  https://github.com/litehelpers/cordova-sqlite-ext  plugin.

Put the database file in the www directory and open the database like (both database location and createFromLocationitems are required):

var db = window.sqlitePlugin.openDatabase({name: "my.db", location: 'default', createFromLocation: 1});

Zoltan

[Abhinav Jaiswal]

Thanks Zoltan,

I have used this plugin and followed the steps given in the documentation. However, I'm not able to point to my pre-populated DB in bundle. 

What I did is -

1. I created a new project and installed the "https://github.com/litehelpers/cordova-sqlite-ext" plugin.

2. I created my pre-populated db with SQLite Browser and placed it under the www folder.

3. Through my javascript  I wrote the below code to access the DB.

  

    document.addEventListener("deviceready",onDeviceReady, false);

        

        var db = null;

        function onDeviceReady(){

          db = window.sqlitePlugin.openDatabase({name: "Master.db", location: 'default', createFromLocation: 1});

          db.transaction(populateDB, errorDB, successDB);

          alert("DB is created");

        }

        function populateDB(tx){

          alert("prepopulated is created");

        }

        function errorDB(err){

          navigator.notification.alert("Error : "+err, null, 'Error', 'OK');

        }

        function successDB(){

          navigator.notification.alert('Database created Successfully',null, 'Information', 'OK');

        }

       document.addEventListener("deviceready",GetRecords, false);

        function GetRecords(){

          alert("GetRecords Executed");

          db = window.sqlitePlugin.openDatabase({name:'Master.db',location:'default',createFromLocation: 1});

          db.transaction(viewData, errorMsg, viewSuccess);

          alert("GetRecords Executed later");

        }

        function errorMsg(err){

          alert("Error msg is coming from GetRecords function !");

          navigator.notification.alert('Error :'+err,null, 'Information', 'OK');

        }

        function viewSuccess(success){

          navigator.notification.alert('Retrieve Successfull',null, 'Information', 'OK');

        }

        function viewData(tx){

          alert("View Data Executed");

          tx.executeSql("SELECT * FROM SchoolDetails",[], resultSuccess, resultError );

          alert("View Data Executed later");

        }

        function resultSuccess(tx, response){

          alert("Response:"+ response);

          var listdiv = document.getElementById("responseDiv");

          // var temp = "<div class=\"list-block media-list\"><ul>";

          var temp = "";

          alert(response.rows.length);

          for(i=0; i<response.rows.length; i++){

            temp += "<li><div class=\"item-link item-content\"><div class=\"item-media\"><img src=\"img/"+ response.rows.item(i).ImgPath +"\" height=\"75\"></div><div class=\"item-inner\"><div onclick=\"LaunchInApp('" + response.rows.item(i).url +"')\"><div class=\"item-title-row\"><div class=\"item-title\">" + response.rows.item(i).name + "</div><div class=\"item-after\"></div></div><div class=\"item-subtitle\">" + response.rows.item(i).address + "</div><div class=\"item-text\">" + response.rows.item(i).city + "</div></div><div class=\"row theme-orange\"><div class=\"col-50\"></div><div class=\"col-50\"><a href=\"#\" class=\"button\">Unsubscribe</a></div></div></div></div></li>";

            listdiv.innerHTML = temp;

          }

          alert("Successfully Retrieved");

        }

        function resultError(err){

          navigator.notification.alert("Error : "+err,null, 'Error', 'OK');

        }

I don't Know why still I'm not able to interact with my pre-populated DB. 

Please correct me If I'm doing wrong.

Waiting for your response. :)