SQLite edit-update

Databases
1

I have the following code that read a table, which has some field added, which need to be changed on the first run.

[code] dim sql as String = “Select * FROM service”
// run the query
dim rs as RecordSet = gDB.SQLSelect(sql)
if gDB.error then — whatever

while rs <> nil and rs.eof <> true
‘’ ******* fix cost ***** This should only update records once

if db = 0 then
  if val(rs.field("ST_cost").StringValue) <> 0 then
 
   
    rs.edit
    if gDB.error then  --- whatever

    rs.field("ST_cost_parts").StringValue = rs.field("ST_cost").StringValue
    
    rs.Update//check for error
    if gDB.error then  --- whatever
    
    // Commit changes to the database
    gDB.Commit//check for error
    if gDB.Error then
      MsgBox "Error Commit service list: "+gdb.ErrorMessage [/code]

I get SQLite error" cannot commit - no transaction is active"

this ran ok on RealSQL

If I add "Select rowid, * FROM service"

result are the same, error.

if I add gDB.sqlExecute "begin transaction"//check for error
just before the rs.edit I do NOT get the error.

NOTE NOTE: In all cases the update WORKED! The database was updated with the change.

My question: Do I need the “commit” statement. My understanding was that for the edit/update needed it to actually do the update.

2

Hello James,

After working with SQLite for a while, the commit statement is needed when using transactions. Tranactions look something like:

db.SQLExecute("BEGIN TRANSACTION") 'SQL code past this point can be reversed (rolled back) db.SQLExecute("do some SQL code here") If db.Error then db.Rollback 'if there is an error, rollback or reverse the changes Else db.Commit 'no errors so make the SQL code permanent End If

If the code is not a transaction then it would look something like:

db.SQLExecute("do some SQL code here") 'Note: no commit is required

A transaction allows the SQL code request to be reversed.

A Link to Helpful Xojo Docs

If there is an update to a transaction, I am sure I will be corrected :wink:

3

Hi Eugene
Thanks. That just what I needed to know.