Progress bar update during sqlite database writing

Solar2D General Questions/Discussion
#1

Hello,

I have a problem with updating the progress bar when writing the SQLite table.

With the attached code, I create an SQLite database, I create a table and I insert about 2200 records each with one of the words present in array words.

At the beginning of the insertion, I want to display a progress bar and, during the table writing cycle, update the progress bar.

The code seems to be correct, in the Corona Simulator console (print instruction) the progress value of the progress bar is incremented and the records are written in the table.

The problem is that the progress bar is not displayed and updated until the end of the writing cycle.

How can I update the progress bar?

P.S.:To re-execute this code, first delete the database.

Thank you for your answer

Regards

Benedetto

#2

If you want to show progress, you’ll probably have to split up your inserts into chunks that run each frame, giving the UI a chance to update. 

Also when doing this many transactions, you can use db:exec(“BEGIN TRANSACTION”) before generating the inserts and db:exec(“COMMIT”) afterwards to vastly speed up insertion. This may render a progress bar unnecessary.

I assume this list of words is just a test of concept, but if not wouldn’t it just be easier to populate the DB beforehand using a CSV file  or something and include it in the project?

#3

Hi Nick,

many thanks for your quick and effective suggestions.

I solved my problem by simply adding the two instructions (in bold) in my code before and after the insertion cycle:

db:exec(“BEGIN TRANSACTION”)

for i = 1, table.maxn(words) do

tablefill = [[INSERT INTO word VALUES (]] … “’” … words[i] … “’” … [[);]]

db:exec( tablefill )

rec = rec + 1

– Refresh progression bar each 100 words writed

      if rec > recPrec + 100 then

        recPrec = rec

        progr = progr + progrValue

        progressView:setProgress( progr )

– Write progression value in console

        print (“progress bar”, progr, “word writed”, recPrec)

      end

end

db:exec(“COMMIT”) 

…

In fact, the progress bar is not necessary.

Yes, this is an example, I populate the DB tables using a CSV file.

Regards

Benedetto