Cordova-sqlite-storage: A local database is never read [Ionic framework]

[GCorbel]

Hi,

I made this simple code :

$.get('map.dump', function(response) {
  if(window.sqlitePlugin) {
    var db = window.sqlitePlugin.openDatabase({name: "map.db", createFromLocation: 1, createFromResource: "1"});
  } else {
    var db = openDatabase('map', '1.0', 'map', 100000000);
    processQuery(db, 2, response.split(';\n'), 'map');
  }

  db.transaction(function(tx) {
    tx.executeSql('SELECT * FROM images', [], function(tx, rs) {
      $('#map_img').attr('src', rs.rows.item(0).tile_data);
    });
  }, function(err) {
    console.log(err.message)
  })
});

And I have always this messages :

SQLitePlugin openargs:    
 {"name":"map.db","createFromLocation":1,"createFromResource":"1","dblocation":"docs"} 
DB opened: map.db
a statement with no error handler failed: no such table: images (code 1): , while compiling: SELECT * FROM images 

I have a file called "map.db" in the "www" directory. The column exist. There is no error in the log of Android.

Is there a solution?

Thanks!

[brodybits]

var db = window.sqlitePlugin.openDatabase({name: "map.db",

createFromLocation: 1, createFromResource: "1"});

According to the readme both createFromLocation and createFromResource
options must be given numeric values. I will make this more explicit.

[GCorbel]

I have the same result with {name:"map.db",createFromLocation:1,createFromResource:1}.

I put a string because of this line.

[brodybits]

createFromResource is internally generated and should not be specified in the open arguments.

If you do NOT use the Cordova CLI, make sure you put the pre-populated db file in the assets/www directory.

The logging from adb logcat should show you if the pre-populated db file was copied. I will add some more logging.

[Mirzanas]

Hi brodybits,

same problem for me, i can't read local database. All times i'm getting "no such table" error. Can you please add an example of pre-populated database?

[GCorbel]

Sorry, I forgot an information which I think it was not useful but it is. I'm using Ionic. I inspected and it search this file /data/data/com.ionicframework.myapp260238/databases/map.db. I don't really now what is the root. I tried to add it in the root folder or in www.

I also tried in assets and www/assets and assets/www.

[Mirzanas]

I'm using Visual Studio Tools for Apache Cordova. @GCorbel same for me, please let me know if you will find any solution. I will be the same.

[Mirzanas]

I think it could be perfect to have working example with DB, plugin and folder structure.

[brodybits]

On Tue, Feb 24, 2015 at 6:58 AM, Mirzanas [email protected] wrote:

I think it could be perfect to have working example with DB, plugin and
folder structure.

I will post a sample sometime later.

[brodybits]

I just uploaded a test sample project to: https://github.com/brodybits/Cordova-pre-populated-db-example-android

This is a version of the example project from Cordova 2.7, with the plugin manually installed, and the test program in assets/www.

In assets/www you will find index.html with the test program and test1.db which is a simple pre-populated database created with the following sql:

CREATE TABLE MYTEST (testentry varchar);
INSERT INTO MYTEST VALUES ("Hello World");

This is in very raw form and the Javascript is not even properly indented, but it should be able to demonstrate a working example.

Please let me know if this helps or not.

[GCorbel]

Many thanks! I will try it tonight.

Le Wed Feb 25 2015 at 12:17:54, Chris Brody [email protected] a
écrit :

I just uploaded a test sample project to:
https://github.com/brodybits/Cordova-pre-populated-db-example-android

This is a version of the example project from Cordova 2.7, with the
plugin manually installed, and the test program in assets/www.

In assets/www you will find index.html with the test program and test1.db
which is a simple pre-populated database created with the following sql:

CREATE TABLE MYTEST (testentry varchar);INSERT INTO MYTEST VALUES ("Hello World");

This is in very raw form and the Javascript is not even properly indented,
but it should be able to demonstrate a working example.

Please let me know if this helps or not.

—
Reply to this email directly or view it on GitHub
https://github.com/brodysoft/Cordova-SQLitePlugin/issues/189#issuecomment-76007526
.

[GCorbel]

I think to behavior with Ionic is really not the same. The architecture of folders are really not the same and I think is can cause some problem. Do you want to support ionic? I probably can debug myself but it will be long because I don't have a big experience.

[brodybits]

Yes if you can help a little. If you tell me where Ionic wants to install the db file, I can add an option to copy from the location (should be a subdirectory under www).

[GCorbel]

It try to install the file here /data/data/com.ionicframework.myapp260238/databases/map.db but I don't know where it is. I have to do some research. I don't have access to my development machine right now. I will do some tests tonight.

[camaleo]

@brodybits please update the docs, this solved an issue where I couldn't use the db anymore after having upgraded the plugin to the actual version (1.0.4)

https://blog.nraboy.com/2014/11/use-sqlite-instead-local-storage-ionic-framework/#comment-1870071773

btw, thanks for the plugin!

[wKoza]

It works for me with ionic 1.0.0-rc.1 and Cordova-sqlite-storage 0.7.9 . GCorbel, are you sure that your map.db is a "binary dump" sqlite ?

[oxydron]

Anyone could comment on this nowdays? Trying to open a pre-generated databse on Ionic last version with this sqlite plugin.

[brodybits]

@GCorbel:

[...]

@jdnichollsc contributed a sample with a pre-populated database at: https://github.com/jdnichollsc/Ionic-Starter-Template

@camaleo:

@brodybits please update the docs, this solved an issue where I couldn't use the db anymore after having upgraded the plugin to the actual version (1.0.4)

https://blog.nraboy.com/2014/11/use-sqlite-instead-local-storage-ionic-framework/#comment-1870071773

That issue has now been sorted, thanks.

btw, thanks for the plugin!

You are welcome, happy to share!

@WilliamKoza:

[...]

Thanks!

@oxydron:

Anyone could comment on this nowdays? Trying to open a pre-generated databse on Ionic last version with this sqlite plugin.

As documented, this is now supported by the cordova-sqlite-ext version. The cordova-sqlite-ext version has some links to samples and tutorials. I will put some here for your reference:

• Ionic starter template with pre-populated SQLite database at: jdnichollsc / Ionic-Starter-Template
• http://redwanhilali.com/ionic-sqlite/
• Tutorial using an-rahulpandey / cordova-plugin-dbcopy (alternative solution) (Android/iOS): https://blog.nraboy.com/2015/01/deploy-ionic-framework-app-pre-filled-sqlite-db/
• https://www.thepolyglotdeveloper.com/2014/11/use-sqlite-instead-local-storage-ionic-framework

I am closing this question since I think it has now been answered. I raised litehelpers/cordova-sqlite-ext#40 to add some more links and samples there. In addition, use with Ionic and other forms of Angular is no longer covered by free support. For commercial support you may contact: [email protected]

[brodybits]

P.S. A couple more links:

• https://www.thepolyglotdeveloper.com/2015/12/use-sqlite-in-ionic-2-instead-of-local-storage/
• https://www.thepolyglotdeveloper.com/2016/08/using-sqlstorage-instead-sqlite-ionic-2-app/

[oxydron]

@brodybits Thank you very much! It helped a lot.