MySql START TRANSACTION

Databases
1

Hi: I`m working with MySql and Xojo… I need to insert data in several tables from one form and I’m using the following structure:

db.SQLExecute(“SET autocommit=0;”)
db.SQLExecute(“START TRANSACTION”)
db.SQLExecute(sql1) //insert data in table 1
db.SQLExecute(sql2) //insert data in table 2
db.SQLExecute(sql3) //insert data in table 3
db.SQLExecute(sql4) //insert data in table 4

if d.error then
MsgBox db.ErrorMessage
db.Rollback
Return
else
db.commit
end

My question is If I’m doing it in the correct way or not, and if not… how should I insert data in different tables and avoid insert the previus data if I get an error in the last table (in this example table Nº4)

Regards

2

Are you currently getting an error?
The code above looks like it would work. If your sql statements are all terminated (end with “;”) you can concatenate them for readability and fun.

db.SQLExecute(sql1 + sql2 + sql3 + sql4)
3

You really need to check for errors after every SQLExecute. Including the Start Transaction.

4

Thanks Tim… I do not have errors but I was not sure if all the data were been saved…

Bob… So I should do something like this:

db.SQLExecute(“SET autocommit=0;”)
if db.error then
MsgBox db.ErrorMessage
end

db.SQLExecute(“START TRANSACTION”)
if db.error then
MsgBox db.ErrorMessage
end

db.SQLExecute(sql1 + sql2 + sql3 + sql4)

if db.error then
MsgBox db.ErrorMessage
db.Rollback
Return
else
db.commit
end

isn`t it?

Thanks again!!!

5

Well, I hate all the extra coding so I created my own function called SQLExecuteRaiseOnError

Sub SQLExecuteRaiseOnError(extends db as Database, sql as String) db.SQLExecute( sql ) if db.Error then raise new BKS_Database.DatabaseException( db.ErrorMessage, sql ) //Subclass of RuntimeException end if End Sub

I have an equivalent method for SQLSelect. Very handy to use because it autocompletes and error checking is automatic. :slight_smile:

6

Thanks!!!

7

Bob you’re so clever! I really like that idea!

8

I’m lazy. What can I say? :slight_smile:

If you ever find yourself writing the same code over, and over, and over again you might want to think about doing stuff like this. It will save you time and heartache if you ever need to change it.