Pre-populated sqlite database issue in cordova android app

Question

I am trying to create 'Offline Dictionary Android App Using Cordova. I have created sqlite database using SQLite database browser. The database file is test.db and it has one table test with two fields '_id' which is INTEGER PRIMARY KEY and 'value' which is TEXT. Some records are also inserted.

I have tried these with no luck :

I have been able to collect the following code. I want my third attempt to be successful so I am here to ask for a help with experts.

WHAT I HAVE DONE

I put the test.db sqlite database file in the assets directory of my android project. I have MainActivity.java File which has the following code :

MainActivity.java

package com.example.sqlite;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.os.Bundle;
import android.util.Log;

import org.apache.cordova.*;

public class MainActivity extends DroidGap {

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        super.loadUrl("file:///android_asset/www/index.html");

        try{
            System.out.println("----------Copying Database -----------");
                copyDataBase("test.db");

        } catch (IOException ioe){
            System.out.println("----------Error Copying Database -----------");
            ioe.printStackTrace();

        }


    }

    private void copyDataBase(String dbname) throws IOException {
        // Open your local db as the input stream
        String pkg = this.getApplicationContext().getPackageName();
        InputStream myInput = this.getAssets().open(dbname);

        // Path to the just created empty db
        String outFileName = "/data/data/"+ pkg + "/databases/" + dbname;

        // Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(outFileName);


        // transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }
        // Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();

    }

}

and script.js file with the following script :

script.js

(function($, undefined){

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

    function onDeviceReady(){

        var db = window.openDatabase(
            'test.db',
            '1.0.0',
            'Test Database',
            64 * 1024
        );

        db.transaction(function(tx){

            tx.executeSql(
                'SELECT * FROM test', [], function(tx, result){

                    console.log("Query Success");
                    console.log('Total Rows :' + result.rows.length);

                },function(tx, error) {
                    console.log('An Error Occured (Qry) : ' + error.message);
                }
            )

        }, function(error){

            console.log('An Error Occured : ' + error.message);

        }, function(){
            console.log("Transaction Success");
        })

    }

})(jQuery);

I have launched my app in both emulator and the physical device and got the same error response that says 'no such table : test'. I found the test.db insdie data/data/MY_PACKAGE/databases/. I pulled that database file using eclipse and opened in SQLite Database browser and there is test table with some records that I have populated.

I am using cordova 2.6.0 and testing my app on android 4.2 . My device is not rooted (ignore if it does not affect).

The screen shot which visualizes the scenario : enter image description here

Why am I getting the 'no such table' error even there is a table ?

Please Help.

Alexandre Mendes Alexandre Mendes · Accepted Answer · 2013-09-16T17:07:42

Just check if you have done the connect in the database correctly, then create a table, insert some data, then select what you did put on the table, from Cordova and the Database Browser.

Maybe you dont need to put .db, try this code first

var db = window.openDatabase(
        'test',
        '1.0.0',
        'Test Database',
        64 * 1024
    );

db.transaction(function populateDB(tx) {

    tx.executeSql('DROP TABLE IF EXISTS DEMO');
    tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');
    tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');
    tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');

},function errorCB(tx, err) {

    alert("Error processing SQL: "+err);

},function successCB() {

    alert("success!");

});

Pre-populated sqlite database issue in cordova android app

2 Answers