Multiple SQLite Databases


#1

My app needs to store data that the user enters, in an SQLite database, and read data from a second SQLite database containing reference information that will need to be periodically updated.

Is this possible and if so, are there specific things I need to know/do?

Thank you.


#2

I believe the answer is no based on similar questions in the past. Here’s one from earlier this year.

Kind regards,
Doug


#3

Doug,

Have you tried the SQLSample1 project?

I made these small changes to the sample and it creates and opens 2 SQL DB’s.

This is JavaScript:

Global variables:

 var DB; //the database itself
 var DB2; //the second database

Then in the initDB() function:

DB = SqlOpenDatabase("customer.db", "1.0", "My Customer Database");
DB2 = SqlOpenDatabase("information.db", "1.0", "My Information Database");

When I inspect in Chrome, they are both created no problem. Once you create the two DB’s you can then create whatever tables and queries you want in each of them.

This is not a complete solution, but if you follow the code in the references example, there is no reason why you can’t accomplish what you are asking.

Buck


#4

Do you really need two databases? Usually multiple tables in one database does the job.


#5

The “Reference” part of the database needs to be updated periodically…If there is only one database I need to be certain that the user-entered data does not get corrupted when the app is upgraded or the Reference section updated… My concern is probably more from a lack of knowledge than anything.

I may need to have your group do some consulting to come up with the best approach…


#6

Ok Buck, that’s good news, I stand corrected. Thank you. So it’s just the attaching of the DBs together for joined queries that’s not possible then.

Warren, another option might be to save the user-entered data in LocalStorage since that data persists after the app is closed or updated. JSON.parse() and JSON.stringify() can be used to convert data types back and forth for use within your app (with Objects or Arrays, for example).

Kind regards,
Doug


#7

If you need to do a join query in SQL, then using two tables form a single database is easier.

As for updating data in a table, that is something that SQL can easily handle and you shouldn’t really have to worry about corrupting the user data (providing you are not deleting the tables and installing a new file).

In any strategy you choose, you should also be thinking about how you can synchronize the data with a server in case the app crashes or the user uninstalls the app to fix a bug (or by accident) - then if the user re-installs your app, they should be able to pull in all their data from the server (cloud).

Good luck with your project.


#8

I appreciate all your suggestions, they are VERY helpful!

Thank you.