MYSQL: class helper for TSQLConnection and TSQLQuery
lazarus / 16-Databases

----------------------------------------------------------------------
kinlion · 2020-06-12 07:23

Two purposes:
1. To simplify code of Start/Commit/Rollback transaction
2. To solve the error 'mysql has gone away!'

There are 2 class helpers for TSQLConnection and TSQLQuery.

For TSQLConnection, add 4 procedures just for simplify coding:
    TSQLConnection.startTran
        equals to TSQLConnection.Transaction.StartTransaction.
        if 'mysql has gone away!' occurred, close connection and
reopen it,
        then re-do TSQLConnection.Transaction.StartTransaction

    TSQLConnection.commit
        equals to TSQLConnection.Transaction.Commit

    TSQLConnection.rollback
        equals to TSQLConnection.Transaction.Rollback

    TSQLConnection.newQuery
        create a TSQLQuery, set its Database and Transaction
properties, return it as result

For TSQLQuery, add 2 procedures to suppress error 'mysql has gone
away' if in a implicity transaction:
    TSQLQuery.tryOpen
    TSQLQuery.tryExec
        Both procedures first call the native procedure,
        if 'mysql has gone away' occurred, close its Database forcely,
the re-call the procedure.
        if any other error occurred, just raise it.

Note:
    By default, TSQLConnection always start a transaction implicity if
there is no its transaction not active.
    If you run a SQL command thru a TSQLQuery or by calling
TSQLConnection.ExecuteDirect,
    the transaction will keep active, until you end it explicity,
that's resonable.
    So keep in mind that always call Commit/Rollback explicity after
your task done, or the transaction
    will end only before the connection closed with the default
action, commit or rollback. That may not be deserved result.

     To use these class helpers, you are responsible to be sure that a
transaction can end before 'mysql has gone away'

Hope helpful and any advice is welcome