LC and transactions in MySQL
Posted: Thu Nov 21, 2019 5:47 pm
Hi
I would like to understand something more about the transactions and use them to avoid anomalies in case of multiple concurrent operations.
This LC script example, used with MySQL database, presents two problems:
global gDataBaseID
.........
put "START TRANSACTION; " into tSQL
put "UPDATE Corsi " after tSQL
put "SET PostiLiberi = 34 , PostiRiserva = 0 “ & \
"WHERE IDCorso = 257 “ & \
"AND IDAnnoIscrizione = 5; " after tSQL
put "COMMIT" after tSQL /* senza il “;” */
eseguiComandoSQL tSQL
.........
on eseguiComandoSQL pSQL
revExecuteSQL gDataBaseID, pSQL
if the result is not a number or the result < 0 then error the result
end eseguiComandoSQL
on error pError
answer error "Si è verificato un errore: " & pError as sheet
exit to top
end error
Problem (1): written in this way, using the custom command 'eseguiComandoSQL tSQL', the routine provides database ERROR and stops.
Problem (2): If instead I simply use 'revExecuteSQL gDataBaseID, tSQL', instead of the custom one, the routine DOES NOT report an error but the database is NOT updated as it should happen!
Without the use of 'START TRANSACTION' command by writing only the simple LC code:
put "UPDATE Corsi " & \
"SET PostiLiberi = 34, " & \
"PostiRiserva = 0 “ & \
"WHERE IDCorso = 257 “ & \
"AND IDAnnoIscrizione = 5 “ into tSQL
it is indifferent to use one (custom) or the other command (LC std) to execute the query and everything works regularly, including database updating, but in this way I am not repaired by any problems of concomitance of operations: this is the reason why I want to use the transactions mode when necessary.
How is it possible that the database, when executing a transaction, is not updated? Should "COMMIT" not immediately register the query result in the database?
Excuse my ignorance, but don't understand.
Sandro
I would like to understand something more about the transactions and use them to avoid anomalies in case of multiple concurrent operations.
This LC script example, used with MySQL database, presents two problems:
global gDataBaseID
.........
put "START TRANSACTION; " into tSQL
put "UPDATE Corsi " after tSQL
put "SET PostiLiberi = 34 , PostiRiserva = 0 “ & \
"WHERE IDCorso = 257 “ & \
"AND IDAnnoIscrizione = 5; " after tSQL
put "COMMIT" after tSQL /* senza il “;” */
eseguiComandoSQL tSQL
.........
on eseguiComandoSQL pSQL
revExecuteSQL gDataBaseID, pSQL
if the result is not a number or the result < 0 then error the result
end eseguiComandoSQL
on error pError
answer error "Si è verificato un errore: " & pError as sheet
exit to top
end error
Problem (1): written in this way, using the custom command 'eseguiComandoSQL tSQL', the routine provides database ERROR and stops.
Problem (2): If instead I simply use 'revExecuteSQL gDataBaseID, tSQL', instead of the custom one, the routine DOES NOT report an error but the database is NOT updated as it should happen!
Without the use of 'START TRANSACTION' command by writing only the simple LC code:
put "UPDATE Corsi " & \
"SET PostiLiberi = 34, " & \
"PostiRiserva = 0 “ & \
"WHERE IDCorso = 257 “ & \
"AND IDAnnoIscrizione = 5 “ into tSQL
it is indifferent to use one (custom) or the other command (LC std) to execute the query and everything works regularly, including database updating, but in this way I am not repaired by any problems of concomitance of operations: this is the reason why I want to use the transactions mode when necessary.
How is it possible that the database, when executing a transaction, is not updated? Should "COMMIT" not immediately register the query result in the database?
Excuse my ignorance, but don't understand.
Sandro