Freebasic & Sqlite. Is there a need for a seperate thread?

[Paul Squires]

4. By the way, get to know transactions. If you do a whole lot of updates to the database at one time, they go a lot faster if you wrap them in a transaction.

Good point. I always wrap my updates/inserts in transactions (especially when adding bulk entries). The speed difference is considerable and the safety of using transactions gives you peace of mind that your data will fully committed to the database.

[Petrus Vorster]

What do you consider 'large' volumes?
The tool I write will probably have 20 rows at the most on any query.
Would that need to be wrapped in a transaction?

It will auto-delete anything older than 6 months.
Using José's code in the example, how would you do that?
Updating a Listview from a large volume of data?

Sorry for all the questions, but I have started ROCK-BOTTOM again, and find this very interesting and with a new bit of zest for trying something new.

Regards, Peter

[Paul Squires]

I don't think that Jose's class has specific Start/End transaction methods but it is super simple to simulate this by calling the sql using his Exec method.

pDbc.Exec("BEGIN IMMEDIATE TRANSACTION;")
'.... do your adding, updating, inserting, etc
pDbc.Exec("COMMIT TRANSACTION;")

It doesn't hurt to wrap all your calls in transactions. You will see speed improvements when adding bulk data to a table, say, in the hundreds or thousands. If you only have 20 records then you won't see much of a speed improvement. In your case, SQLite will implicitly create a transaction behind the scenes for EVERY ONE of your 20 additions. Therefore, the database is being locked and unlocked 20 times. If you wrap the whole code in BEGIN/COMMIT TRANSACTION then the database only has to do it once. It is the implicit transaction locking and unlocking that causes slow down on bulk data operations.... that is why I always wrap everything regardless.

[Paul Squires]

Read through this page:  https://www.sqlite.org/lang_transaction.html

[Petrus Vorster]

That makes a lot of sense.
Will immediately start to do it that way.

-Regards, Peter

[Petrus Vorster]

Now I understand what you meant with get to know transactions.
I have created a few infinite loops, crashes and spoke some undesirable language until I got the AHA! Moment from your example.
I also learned that Freebasic is not as forgiving as Powerbasic.

But serious progress had been made. Should have done this 15 years ago.

-Peter

[Petrus Vorster]

A great deal of progress were made with your advice.
Tabs with multiple controls gets updated from a single Database call.

Something odd I noticed is your code on top of a new form.

Code Select Expand

'Application.Run(Frm Settings) Remove the following Application.Run code if it used elsewhere in your application.
Today, the application complained about this line as already included elsewhere. I could not find anywhere that it was mentioned before.

Also the dot syntax acts odd. When I type e.g.

Code Select Expand

Settings.Listview2.Columns.AddThere would be a combobox pop-up with all the option I can choose. [Very Handy]
Just the next line it simply doesnt happen even if i repeat the previous line word for word.
After a 'save project' it works again.

I am enjoying myself again as things are starting to make sense again.

-Peter

[Paul Squires]

'Application.Run(Frm Settings) Remove the following Application.Run code if it used elsewhere in your application.

There can be only one of these lines in the application because that is the code that starts the application. It is automatically added to every new form that you create because WinFBE does not know what form you will use as the startup form. You can even move that code a .bas file that you might be using as your startup code file. Make sure to check ALL of your form code files (even the tab control child forms) to see if that code exists elsewhere.

"combobox popup" codetips.
This is a known problem. It will be fixed when I do the work on the visual designer. The dot "." syntax is inconsistent in triggering the codetips.