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.

Waiting for the response till 1Hr. No helpful hands yet. – Lekhnath Sep 16 '13 at 17:01 — Lekhnath, Sep 16 '13 at 17:01

Alexandre Mendes · Answer 1 · 2013-09-16T18:23:29.043

1

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!");

});

edited Sep 16 '13 at 18:23

answered Sep 16 '13 at 17:07

Alexandre Mendes

119
1
1
11

I am trying to use pre-populdated database where I would have the `database` , `table` and some records in it. I can't use cordova for creating the `table` and inserting the records because I have 100000+ records. I have tried 'test' instead of 'test.db' but getting the same error. – Lekhnath Sep 16 '13 at 17:16
1

I mean try to pre-populate to check if you are using the database you want. Use just a fill datas to check it. – Alexandre Mendes Sep 16 '13 at 17:19
I am getting the same error with and without the `.db` extension, and the `error.message` is also `undefined` which is making it hard to debug, do you have any idea please – Lekhnath Sep 16 '13 at 17:27
Try the code that I add in the answer, also try to remove the parameter size of database. – Alexandre Mendes Sep 16 '13 at 18:27
1

`check if you are using the database you want` was really helpful. I changed the `databases` to `app_database` and seems the error `no such table` no longer exists. Thank you for your useful comment. And yes `test.db` is not needed now `test` is doing the job. It's 12 : 30 AM here , going to bed, good nite. – Lekhnath Sep 16 '13 at 18:39
When you wake, if you got a problme just comment here! – Alexandre Mendes Sep 16 '13 at 18:55

score 0 · Answer 2 · answered Oct 11 '13 at 17:27

I completely test your code. I solved writing the complete path in js code:

var db = window.openDatabase(
        '/data/data/com.example.myapp/databases/test.db',
        '1.0.0',
        'Test Database',
        64 * 1024
    );

..and a project clean before re-run the project.

Hope this help you.

Regards

Pre-populated sqlite database issue in cordova android app

2 Answers2