Using certain Cordova SQLite plugin

I have this code: StartJS var db = window.sqlitePlugin.openDatabase({ name: 'pts.db3', location: 'default', androidDatabaseProvider: 'system' }); db.transaction(function(tx){ args = [window.App.RootScope.user_id, window.App.RootScope.user_name] tx.executeSql('INSERT INTO users (id, user_name) VALUES(?,?);', args); }, function(tx, error) { alert('Transaction ERROR: ' + error.message); }, function() { alert('Transaction Success: User inserted'); }); db.close(function () { console.log("DB closed!"); }, function (error) { alert("Error closing DB:" + error.message); }); EndJS

For some reason in any of the Javascript code I write in AB using StartJS/EndJS do not activate any of the alert(...); lines that I would like to see. Am I using it wrong?

---

In fact, something is failing inside that JS and I not getting an alert from the error or success functions.

---

Hello Ryan,

Since, if I am not wrong, we depend on an Apache Cordova plugin... are you sure that the above code is executed after the app Ready event? I can't see anything except that maybe the "args" variable must be preceded with the "var" reserved word... :-(

---

Hi David

I have this custom plugin referenced in the app settings Cordova area: cordova-sqlite-storage

I did end up wrapping the JS in

document.addEventListener('deviceready', function() { ... });

That did not seem to make any difference.

---

Hello Ryan,

The problem is probably the database or the name or the location. I did not try this plugin, but, asume that the database name must refer to some existing database file. If so, probably we can't simply use a database name like "pts.db3", but something like "app/files/pts.db3", suposing we include the "pts.db3" file using the app files manager and from the Others (files) tab.

About the device ready, probably the code no need to be wrapper inside that event handler: for example, if you execute the code in certain Push button Click event, once the app has started (of course), maybe in other app view than the main one, then the device ready event has been already fired, and therefore we can use the code "as is" without problems. But if the code is placed in the main app view Show event... then is possible that that code are executed before the device ready event is fired.

---

So I added the SQLite database to the files located in the project file manager

var db = window.sqlitePlugin.openDatabase({ name: 'pts.db3', location: 'app/files/', androidDatabaseProvider: 'system' });

Would the location parameter in the openDatabase function be: location: 'app/files/'?

---

Hello Ryan,

I can't answer at this moment... you must refer to the plugin's documentation: maybe the "name" must be the database's file path, maybe just the database file name, and then use the "location" to place the database's directory... I can't help you right now on this, but you must take a look at the documentation, since there is not the same to set one thing or another...

---

How can I tell if my database was deployed to 'app/files/'?

---

Hello Ryan,

If you use the app files manager and the Others tab, then choose the "pts.db3" file from your computer, when the app is compiled, that file is copied into the "app/files/" directory, so their path is "app/files/pts.db3". Be sure that you use the files manager and be sure that you can find that file in "app/files/".

---

Hi David

I have the database pts.db3 loaded in the file manager. On the emulator I have navigated to Files --> Android SDK built for x86 --> Android -->Data --> com.ptsapp.pts --> files -->

That location is empty

---

Hello Ryan,

I am not understand the Android SDK -> Android... if you add a file from the Other tab in the app files manager, that file is copied with the other app files (HTML, CSS, JS, etc.) in the "app/files/" path. So, if you add a "MyFile.db" from that place, the final path for that file is "app/files/MyFile.db". Are you tried with that? How you try? Can place here the paths that you are trying to use and in what variable are you set it?

---

David

The path C:\Sync\Ryan\GitHub Repos\PTS Mobile\PTS_Compiled\www\app\files on my laptop contains the ptd.db3 file but it does not appear on the Android emulator I am testing with.

This is the path I see on the Android emulator: "com.ptsapp.pts/files/" but that folder is empty.

I dont think SQLite database files can be placed in that directory

---

Hello Ryan,

For sure we can't use the absolute path of your computer: we can choose that file when using the app files manager. To me "com.ptsapp.pts/files/" is rare, because I am not sure where you view this path, or if that is the path in which the plugin expect the database files.

In the best case, the path can be changed with the provided one, so, we can add the database file using the app files manager and therefore can use a path like "app/files/myDatabase.db". Exactly in the same way that you can use another file, like a JSON file, for example.

In the worst case, the plugin expect the files to be in the path that you referred... so we must manually copy the database file in that path, which must be inside some of the "platforms\android\" folders... the point is that the plugin's documentation must be clear about all of this...

---

According to the plugin documentation using this code will open the database, but also create a new database if one is not present.

var db = null; document.addEventListener('deviceready', function() { db = window.sqlitePlugin.openDatabase({ name: 'my.db', location: 'default', }); });

The location called 'default' in the code snippet refers to this location: "//data/data//databases/"

This location is not visible to browsing. My code in AB is using the "openDatabase" method, but I don't know if it is working. The code that creates the database, also executes some SQL to create tables if they do not exist.

Using Javascript as documented in the plugin, should produce expected results in AB since it compiles as JS.

---

Hello Ryan,

I can read here that the method that you refers open a database, but, don't mention that can create a database if not exists. That's a good point... because if the plugin need to create a file in "//data/data//databases/"... maybe we need some special permissions or something like that? Are you try by provide an already existing database file, even when it's empty? So use the files manager to pick a database file in order to be use... and try to open it... in this way at least we avoid the creation, that maybe it's causing the problem. Because you are right... we are talking about Javascript at the end... must works...

---

David

My project requirement is to build an app on Android tablets that can go offline for an extended period of time and still handle CRUD to a local data source. Once I detect a network connection, I update data to a web SQL Server database via API functions that I wrote.

I am open to any ideas to achieve this requirement. I have put alot of time into this AB project and really want to keep using it.

I have considered using JSON as an offline data store but I am not sure how that would handle new and updated records. In addition, some of my database queries from the API will return at minimum 8500 rows of relational data.

---

Hello Ryan,

Of course maybe you can consider other possibles ways, for example, JSON: you can use the FileWrite and FileRead actions to read and write JSON files, for example. Deal with JSON is quite easy, in principle. JSON is a Javascript notation by itself, that is, if we save an Array, what we get when load the JSON is an Array, if we save objects, what we get are objects ready to use. But anyway the point is why the SQLite plugin don't work... are you try with an empty (or not empty) existing file database? Maybe this can work... so you can get the SQLite plugin working...

---

Thanks David, I really appreciate your exceptional support. I will mess with a few settings and post back here.

---

Hello Ryan,

Please, no problem at all. If I was not as busy as I am (and you will know in what in the near future... and I hope that you enjoy it..) I will try the plugin by myself... maybe I can finally do it... there are various samples about JSON (see JSON, JSON2, JSON3 samples apps)... it's quite easy to work with JSON, and, since the FileWrite and FileRead works with text files... and we can serialize any JSON and unserialize it... can be a possible to store complex data offline, that is, something more complex than simply plain text. But the SQLite plugin must work... for sure that must work... or at least we must know what is happen... and if what happen can be solved or what...

---