SQLite help
Posted: Monday 28th January 2019 4:06am
I've been aware that Gambas has SQLite functionality. But its something I've always shied away from because I don't know SQL and I don't understand it. But lately I've set my mind to learning it at least enough to use it, because I really need it for my coding. I've been hardcore researching, and trying to figure it out the best I can.
Gambas-related SQLite examples are sparse. But the best example I've been able to find is:
https://kalaharix.wordpress.com/gambas/ ... om-gambas/
Using that code as a tutorial, I've built on it and expanded on it as best as I could figure out. I've come up with a proof-of-concept test project which I'll attach to this message.
I've primarily focused on only the basic SQLite functionality that I need for my code, most of which it appears I've been able to make work. But at the same time, I built this project through a lot of guesswork and not really knowing what I'm doing.
I'm requesting two points of help:
1)
One function I need in SQLite which I can't figure out how to implement with Gambas is: for my main project, new data (dozens of rows) will periodically come in and will automatically be added to the database. Easy enough. However, with some of those rows, the primary key will already exist in the database. We can ignore those rows. BUT, some of those incoming rows will contain updated information in one or more fields, which in such instances those rows will need to be updated in the database.
I need to determine, amongst the already-existing primary keys in the database, if any data in the incoming rows are different. And if so, update only those rows needing updated.
My understanding that if I were to blindly update all existing rows where no data has changed (when dealing with a dB with tens of thousands, maybe hundreds of thousands of rows) would cause big I/O operation bottlenecks and adversely affect performance.
2)
I build this test project using guesswork and not really knowing what I'm doing. Could someone who's experienced with SQLite look over the code and tell me what needs to be changed? Or if I'm doing things incorrectly or redundantly or something, could you tell me the proper method of doing it?
The attachment was coded using Gambas 3.12.2, in Linux Mint Mate 18.3, 64 bit.
To anyone else also trying to learn SQLite, please use this guesswork code with caution until someone can verify how correctly its been written. It has potential to eventually be a great tutorial for the Gambas community.
Gambas-related SQLite examples are sparse. But the best example I've been able to find is:
https://kalaharix.wordpress.com/gambas/ ... om-gambas/
Using that code as a tutorial, I've built on it and expanded on it as best as I could figure out. I've come up with a proof-of-concept test project which I'll attach to this message.
I've primarily focused on only the basic SQLite functionality that I need for my code, most of which it appears I've been able to make work. But at the same time, I built this project through a lot of guesswork and not really knowing what I'm doing.
I'm requesting two points of help:
1)
One function I need in SQLite which I can't figure out how to implement with Gambas is: for my main project, new data (dozens of rows) will periodically come in and will automatically be added to the database. Easy enough. However, with some of those rows, the primary key will already exist in the database. We can ignore those rows. BUT, some of those incoming rows will contain updated information in one or more fields, which in such instances those rows will need to be updated in the database.
I need to determine, amongst the already-existing primary keys in the database, if any data in the incoming rows are different. And if so, update only those rows needing updated.
My understanding that if I were to blindly update all existing rows where no data has changed (when dealing with a dB with tens of thousands, maybe hundreds of thousands of rows) would cause big I/O operation bottlenecks and adversely affect performance.
2)
I build this test project using guesswork and not really knowing what I'm doing. Could someone who's experienced with SQLite look over the code and tell me what needs to be changed? Or if I'm doing things incorrectly or redundantly or something, could you tell me the proper method of doing it?
The attachment was coded using Gambas 3.12.2, in Linux Mint Mate 18.3, 64 bit.
To anyone else also trying to learn SQLite, please use this guesswork code with caution until someone can verify how correctly its been written. It has potential to eventually be a great tutorial for the Gambas community.