I modified the example below to (batch) import Records read from a text file as is:
While Not TIS.EOF
db.ExexuteSQL("INSERT
db.CommitTransaction
Wend
The code crashed systematically at the db.CommitTransaction line.
Fortunately, I had the idea to read the error.Message and noticed Xojo told me there is no TRANSACTION.
So I moved the line db.ExecuteSQL("BEGIN TRANSACTION") in the While loop and the project import correctly my 12 lines (it imported 2 lines before the crash )
I was thinking the TRANSACTION needs to be opened once (and the docs let me think that too).
Question: is is normal or this is new with Xojo 2019r3.1 ?
Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("Employees.sqlite")
Try
db.Connect
db.ExecuteSQL("BEGIN TRANSACTION")
db.ExecuteSQL("INSERT INTO Employees (Name,Job,YearJoined) VALUES " _
+ "('Dr.Strangelove','Advisor',1962)")
db.CommitTransaction
Now as a side note, you do not need to create a transaction for a single command because it is done automatically. But the thing is that the number of transactions per second is limited which can be a problem if you want to execute a large number of statements (each statement would create its new transaction).
In such a case, you should open a new transaction, execute a number of commands then commit your changes to speed up operations.
the current code is in a while …/… wrnd block where it reads a line of a text file, NthField it in an INSERT line, then Commit and go to the next line. (read code above)
The original code with only one TRANSACTION crashed after two lines.
The final code with the TRANSACTION inside the While / Wend code import 13 lines (I think).
BTW: with API 1 (2015r1), I imported more than 600 Records (using that amount of images; the text comes from large file names I gave to the images) with only one TRANSACTION.
Thus my question.
Now, if this is how things have to be done now, who am I to complain (excepted for the waste of time, around 1 hour, and the nerves I have to shut down cause I was at McDonalds and have to say nothing, think kids around).
Now, if this is how things have to be done now, maybe this have to be said in the docs (SQLiteDataBase).
@Emile Schwarz, I think there is a slight misunderstanding on using transactions. Using your example from the original posting
While Not TIS.EOF
db.ExexuteSQL("INSERT
db.CommitTransaction
Wend
There is no beginning of a transaction so the CommitTransaction fails. Your observation that the BeginTransaction needs to be opened once (per transaction) is correct. Note that a SQL statement like Select or Execute is NOT a transaction. Unless they are bracketed in a transaction, they will auto-commit.
Since the ExecuteSQL statement is not a transaction, you can put as many of them inside the transaction as you wish. So, since you are operating inside a While-Wend loop the transaction statements should be outside of the loop.
db.BeginTransaction
While Not TIS.EOF
db.ExexuteSQL("INSERT
Wend
db.CommitTransaction
This way the SQL statements will all be done within the transaction and they all will be committed once they all are done.
Of course, you could put both the BeginTransaction and CommitTransaction statements inside the While-Wend loop but, trust me, if there a sizable number of iterations of the loop, doing so will impact performance.
Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("Employees.sqlite")
Try
db.Connect
db.ExecuteSQL("BEGIN TRANSACTION")
db.ExecuteSQL("INSERT INTO Employees (Name,Job,YearJoined) VALUES " _
+ "('Dr.Strangelove','Advisor',1962)")
db.CommitTransaction
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
db.RollbackTransaction
End Try
As I wrote above, I added a While Wend block after the BEGIN TRANSACTION line and the Wend is after the db.CommitTransaction live.
Once I moved the line db.ExecuteSQL(“BEGIN TRANSACTION”) after the While (in the Loop), the code worsk fine.
In short: I had to set BEGIN TRANSACTION for each INSERT INTO line, unlike what I wrote last week of so with 2015r1 (BEGIN TRANSACTION and INSERT 600 Records).
I understand, Emile. Look at the differences in the code blocks below. Your code as I think you are presenting it
While Not TIS.EOF
db.BeginTransaction
db.ExexuteSQL("INSERT
db.CommitTransaction
Wend
My revision of the code.
db.BeginTransaction
While Not TIS.EOF
db.ExexuteSQL("INSERT
Wend
db.CommitTransaction
Yours has the transaction created and committed for every Insert statement while mine has all of the Inserts in a single transaction. To expand on the example, assuming you already have the rowset from a previous Select statrment
Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("Employees.sqlite")
Try
db.Connect
db.BeginTransaction
While Not TIS.EOF
db.ExexuteSQL("INSERT
Wend
db.CommitTransaction
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
db.RollbackTransaction
End Try
[quote=476975:@Dale Arends]db.BeginTransaction
While Not TIS.EOF
db.ExexuteSQL("INSERT
Wend
db.CommitTransaction[/quote]
This does not worked (the error was no connection available), thus the new code.
I had in the INSERT line, but I finally found what was wrong (I forgot; no I remember: a typo in the TABLE name ).
That is what I was thinking. But, I am open and then I do womething and it worked (to my surprise).
After checking my API 1 project, I do the same (BEGIN TRANSACTION in the Loop: > 600 Records were added to the SQLite DB).
Maybe this time I wrote the stuff in a different time flow: starting with the TextInputStream data, then adding the INSERT line
while in the API1 code, I iterate a folder contents, reading both the images names and NthField it into the Records fields plus load the image into the Records (too) and into the screen (both text and image)
If you put begin transaction inside the loop, then you are not gaining any benefit from using a transaction. Use Dale’s code (and figure out why it didn’t work when you tried it). Begin and Commit both should be outside the loop.
What should a transaction do?
A transaction moves the data from one consistent state to the next consistent state.
The entire scope of the data to be changed should be bracketed in one transaction.
So it is like Tim has written: “Begin and Commit both should be outside the loop”.
Otherwise you may got a lot of work to restore your data.
).