Recent

Author Topic: MYSQL: class helper for TSQLConnection and TSQLQuery  (Read 315 times)

kinlion

  • Jr. Member
  • **
  • Posts: 69
  • I Love Lazarus
MYSQL: class helper for TSQLConnection and TSQLQuery
« on: June 12, 2020, 09:23:01 am »
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 :)
« Last Edit: June 12, 2020, 09:25:24 am by kinlion »
Lazarus 2.0.8 / FPC 3.0.4 / SVN 62944 / Win10x64

 

TinyPortal © 2005-2018