SQLite: about TRANSACTION

Databases
21

The code below awaits two FolderItem: one is a Reference to the .sqlite file, the second is a Reference to the txt file to import data from (a \Tab is used as Field separator, an EndOfLine is for Records separation).

When the line () is run once, only two Records are imported to the data base until the crash, if I move it into the Loop, everything is imported correctly.

[code]Public Sub Import_Records(Import_SQL_FI As FolderItem, Import_TXT_FI As FolderItem)
// ====================================
//
// Name: Import_Records
// Params:
// Import_Txt_FI As FolderItem
// Import_SQL_FI As FolderItem
// Returns:
//
// ====================================
Var Import_TXT_TIS As TextInputStream
Var aLine As String

Import_TXT_TIS = TextInputStream.Open(Import_TXT_FI)

Import_TXT_TIS.Encoding = Encodings.UTF8

gRecords_db.DatabaseFile = mSQL.gRecords_FI

Try
gRecords_db.Connect
gRecords_db.ExecuteSQL(“BEGIN TRANSACTION”)

While Not Import_TXT_TIS.EOF
  
  // Get a line
  aLine = Import_TXT_TIS.ReadLine

  // Insert a Line
  gRecords_db.ExecuteSQL("INSERT INTO Records('Groupe', Title, 'Part_Number', Company, Released_On, Duration) VALUES "+_
  "('" + NthField(aLine, Chr(9), 1) + "'," + _
  " '" + NthField(aLine, Chr(9), 2) + "'," + _
  " '" + NthField(aLine, Chr(9), 3) + "'," + _
  " '" + NthField(aLine, Chr(9), 4) + "'," + _
  " '" + NthField(aLine, Chr(9), 5) + "'," + _
  " '" + NthField(aLine, Chr(9), 6) + "')")
  
  // Display the data
  wRecords.TF_Group_Name.Text  = NthField(aLine, Chr(9), 1)
  wRecords.TF_LP_Title.Text    = NthField(aLine, Chr(9), 2)
  wRecords.TF_Part_Number.Text = NthField(aLine, Chr(9), 3)
  wRecords.TF_Company.Text     = NthField(aLine, Chr(9), 4)
  wRecords.TF_Released_On.Text = NthField(aLine, Chr(9), 5)
  wRecords.TF_Duration.Text    = NthField(aLine, Chr(9), 6)
  wRecords.Invalidate
  
  // Validate the added Record
  gRecords_db.CommitTransaction // Crash here: Cannot commit: no transaction is active
Wend

Catch error As DatabaseException
MessageBox("Error: " + error.Message)
gRecords_db.RollbackTransaction
End Try

// Close the OS Reference to the .txt file
Import_TXT_TIS.Close
End Sub[/code]

22

[quote]// Validate the added Record
gRecords_db.CommitTransaction // Crash here: Cannot commit: no transaction is active[/quote]
You are stopping the ‘BEGIN TRANSACTION’ inside the loop, and the next time around the loop, there is no transaction to ‘commit’. Try moving the commit to after the (Wend) loop.

23

Thanks John.

It takes two Records to the code to understand that (The crash occured after the second Record was written).

I will test that right now…

and: it works.

To be clear: I have gRecords_db.ExecuteSQL("BEGIN TRANSACTION") in the loop and gRecords_db.CommitTransaction out of the loop (after the Wend).

24

Both BEGIN and COMMIT should either be removed or should BOTH be not in the loop.

25

@Tim:

Try the code instead of answering what you feel it have to do.

If I ask, it is because I found something. If I put BEGIN inside the loop it is because Xojo told me there is no TRANSACTION after writing the second record.

And that is something I said since the beginning.

Looks strange to you ? Yes, why not, but it is what Xojo 2019r3.1 do.

26

You can either:

  1. do a sequence of INSERTs without any BEGIN/COMMIT. This will work because if there is no explicit transaction started, SQLite (not XOJO) will wrap each of your statements in a transaction.

  2. Start a transaction with one BEGIN, do a sequence of INSERTs, and then one COMMIT. This will work exactly as (1) but faster. If however it fails in the middle, NONE of the inserts will have been done.

In my code I usually do (1) but there are several places in my app where the speed increase in (2) is very important, and others where it is important to me that either ALL or NONE of a sequence of statements is done.

27

If all you want to do is inserting multiple records you don’t have to use a transaction at all, just use this syntax:

INSERT INTO tableName (columnName)
VALUES
(“Buddy Rich”),
(“Candido”),
(“Charlie Byrd”);

This will either succeed and insert all records or fail and not insert any record.