B4J Question Easiest way to create database and run queries on it

kostefar · May 14, 2024

Dear All,

I have a dataset which in itself is not very large nor complicated, 2 rows with each 1500 items, but I realized that the queries that I need to carry out on this would be a lot easier if using SQL or something similar, rather than trying to code it manually with multiple iterations that I cannot get my head around for now.
I would need to create the database each time the program is started. Does anybody have a recommendation of what the simplest solution would be here? JSQL perhaps?

Thanks in advance!

MicroDrie · May 22, 2024

It all started with a simple:

not very large nor complicated, 2 rows with each 1500 items

Followed by

The data may get updated from time to time

Expended by

lets me view a db live

Perhaps he can use database logging as an acceptable solution for this? Or use a added time stamp to see the difference with which previous version (and how is the previous version remembered)?

This is a typical question that seems simple at first glance, but quickly becomes a lot more complex during implementation because more and more functionality is required. Whether you see an opportunity to initially start with a simple solution as a basis, the challenge is can it also scale up to provide extra functionality in that simple solution and how likely will that solution also offer good performance in an working environment with all kinds of other programs.

RichardN · May 23, 2024

@kostefar

A fundamental question you need to ask yourself..... Is this data going to be modified by the program? Or is it simply reference material that you will not be changing?

Either way SQLite is the more flexible solution. If this is simply reference material it is simpler to construct the database only once outside of code. I use SQLite Expert Pro.

- You can import/export a variety of file structures to a database
- You can have confidence by testing SQL queries in the visual interface before pasting them into code.
- You can create SQLite Views which are SQL queries stored within the database you can call externall by a simple name
- Copy and paste data between tables or separate databases

Remember if you include an externally created SQLite database in your package it will be copied to FileDir.Assets which is read-only. You will need to copy it to FileDir.Internal (same as XUI.DefaultFolder) as even a simple read operation requires write access to the database. Good luck.

kostefar · May 25, 2024

RichardN said:
@kostefar

A fundamental question you need to ask yourself..... Is this data going to be modified by the program? Or is it simply reference material that you will not be changing?

Either way SQLite is the more flexible solution. If this is simply reference material it is simpler to construct the database only once outside of code. I use SQLite Expert Pro.

- You can import/export a variety of file structures to a database
- You can have confidence by testing SQL queries in the visual interface before pasting them into code.
- You can create SQLite Views which are SQL queries stored within the database you can call externall by a simple name
- Copy and paste data between tables or separate databases

Remember if you include an externally created SQLite database in your package it will be copied to FileDir.Assets which is read-only. You will need to copy it to FileDir.Internal (same as XUI.DefaultFolder) as even a simple read operation requires write access to the database. Good luck.

In fact, I decided to have some of the entries, which are always the same but requires some computation to be generated, stored in a file, and then insert additional data into the same db when running the code, but where the entries, that are the same, have been copied into another db, which is initialized in the memory. My system was having some hiccups because of a lot of things were going on when using a file, which was resolved by using memory instead.
For the file location, I just used File.Dirapp, which lets you read and write as you please. It´s in the objects directory in the project folder. Probably not the most logical place to have it, but it works.

kostefar · May 25, 2024

MicroDrie said:
It all started with a simple:

Followed by

Expended by

Perhaps he can use database logging as an acceptable solution for this? Or use a added time stamp to see the difference with which previous version (and how is the previous version remembered)?

This is a typical question that seems simple at first glance, but quickly becomes a lot more complex during implementation because more and more functionality is required. Whether you see an opportunity to initially start with a simple solution as a basis, the challenge is can it also scale up to provide extra functionality in that simple solution and how likely will that solution also offer good performance in an working environment with all kinds of other programs.

Thanks, I guess that I´ll just create it myself then.

B4J Question Easiest way to create database and run queries on it

kostefar

Active Member

MicroDrie

Well-Known Member

RichardN

Well-Known Member

kostefar

Active Member

kostefar

Active Member

Similar Threads

B4J Question Easiest way to create database and run queries on it

kostefar

Active Member

MicroDrie

Well-Known Member

RichardN

Well-Known Member

kostefar

Active Member

kostefar

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency