MySql Nativa Transacciones. At. Mr. Rao

[nageswaragunupudi]

After seeing this post, I am considering introducing two switches:
DATA lRaiseErrorDML INIT .f.
DATA lRaiseErrorALL INIT .f.

I can change the name of the variables to lThrowError, etc. if you all suggest

If programmer sets :lRaiseErrorDML := .t., then the Execute() method will raise a runtime error for failure of DML statements only ( DML: INSERT/UPDATE/DELETE ) and will be silent for errors with other SQL queries.


If programmer sets :lRaiseErrorALL := .t., then the Execute() method will raise a runtime error for failure in execution of any query.

Then we can write:

Code: Select all  Expand view

oCn:BeginTransaction()
oCn:lRaseErrorDML := .t.

TRY
    oCn:Insert(...)
    ....
CATCH
    lSaved := .f.
    oCn:RollBack()
END
oCn:lRaiseErrorDML := .f.
if lSaved
   oCn:CommitTransaction()
endif
 

Welcome any suggestions, before I finish the changes

[carlos vargas]

DATA lRaiseErrorDML INIT .f.
DATA lRaiseErrorALL INIT .f.

Ok rao.

But, transaction in mysql is only for insert, update and delete statements?

Umm. Alter table.., etc. Is not supported in transaction?

[Ariel]

Mr. Rao,

A mi modesto parecer deberia funcionar como el :execute()

TRY
oServer:Begintrasaction()
oServer:Execute( "INSERT INTO ... ")
oServer:Execute( "INSERT INTO ... ")
oServer:Execute( "INSERT INTO ... ")
oServer:Execute( "INSERT INTO ... ")
CATCH oErr
oServer:RollBack()
FINALLY
oServer:CommitTransaction()

END

De esta forma SI funciona el break automaticamente por el error.
Saludos.

[nageswaragunupudi]

But, transaction in mysql is only for insert, update and delete statements?

Yes.
Only for DML statements, i.e., INSERT, UPDATE and DELETE statements

Umm. Alter table.., etc. Is not supported in transaction?

Yes. Not supported.

One more important point to remember is that if at all we use any DDL statement inside a transaction, the transaction gets committed and then this DDL statement is executed. So we need to be careful not to use any statements not compatible inside a transaction.

[Ariel]

Mr. Rao,

a mi modesto entender deberia funcionar como cuando usamos :Execute()

TRY
::oServer:BeginTransaction()
::oServer:Execute( "INSERT INTO ... ")
::oServer:Execute( "INSERT INTO ... ")
::oServer:Execute( "INSERT INTO ... ")
::oServer:Execute( "INSERT INTO ... ")
::oServer:Execute( "INSERT INTO ... ")

CATCH oErr
::oServer:RollBack()

FINALLY
::oServer:CommitTransaction()

END

De esta manera salta solo por el catch
Saludos.

[carlos vargas]

from help of xharbour

FINALLY
The finally section is guaranteed to be executed, no matter if an error was handled or not.

in you example

Code: Select all  Expand view

FINALLY
::oServer:CommitTransaction()

END
 

The commit would be executed even when there is an error?
Anyway, I don't know if harbor supports that instruction.


salu2

[nageswaragunupudi]

Anyway, I don't know if harbor supports that instruction.

Harbour does not support "TRY/CATCH" syntax at all. It supports BEGIN SEQUENCE/END SEQUENCE only.
xHarbour supports both.

fivewin.ch translates TRY/CATCH to BEGIN/END SEQUENCE for Harbour users. FINALLY also is translated.

Your observation about FINALLY is right.

[carlos vargas]

yes, work with xhb.hbc

Code: Select all  Expand view

// The example demonstrates program flow for a TRY..CATCH sequence

#ifdef __HARBOUR__  //from fivewin.ch
   #ifndef __XHARBOUR__
   #xcommand TRY  => BEGIN SEQUENCE WITH {| oErr | Break( oErr ) }
   #xcommand CATCH [<!oErr!>] => RECOVER [USING <oErr>] <-oErr->
   #xcommand FINALLY => ALWAYS
   #xtranslate Throw( <oErr> ) => ( Eval( ErrorBlock(), <oErr> ), Break( <oErr> ) )
   #endif
#endif

   PROCEDURE Main()
      LOCAL oErr

      TRY
        ? "Trying"
        oErr := ErrorNew()
        oErr:Args          := { date(), time(), pi() }
        oErr:CanDefault    := FALSE
        oErr:CanRetry      := FALSE
        oErr:CanSubstitute := FALSE
        oErr:GenCode       := EG_SYNTAX
        oErr:Severity      := ES_ERROR
        oErr:SubSystem     := "MYTEST"
        oErr:SubCode       := 1001
        oErr:Operation     := "Prueba de error"
        oErr:Description   := "Descripcion de la prueba de error"    
        Throw( oErr )
      CATCH oErr
        ? "Caught:", oErr:SubCode, oErr:Operation, oErr:Description
        ? "Throwing to outer, should be deferred"
        //Throw( oErr )
      FINALLY
         ? "Finalized"
      END
     
      ? "Oops, should have Re-Thrown, after the FINALLY."
   RETURN

 

[nageswaragunupudi]

Hace años que uso tmysql con estos cambios, y me ha funcionado bien, me gusta mucho la implementación de mysql en fwh pero veo que tengo que hacer muchos cambios

FWH Insert method supports your Insert2 method syntax also.

oCn:Insert( cTable, { { "fld1", "val1" }, ... { "fldN", "valN" } } )
also works.

Soon we are going to implement lThrowError like functionality.

May I know what do you advise to make migration easlier?

[carlos vargas]

The only thing about transactions, the other thing is easy, someone else?

[nageswaragunupudi]

Will be available in the next release.
Applies to execution of DML statements "INSERT,UPDATE,REPLACE,DELETE" directly using oCn:Execute() and to methods:
method Insert(...)
method Upsert(...)
method Update()

Implemented new
DATA lThrowError AS LOGICAL INIT .f.

Recommended usage:

Code: Select all  Expand view

 local lError := .f.
  oCn:BeginTransaction()
  oCn:lThrowError := .t.
  TRY
     oCn:Insert(....)
     oCn:Update(...)
     oCn:Execute( "INSERT ... " )
     ...
   CATCH
     lError := .t.
   END
   oCn:lThrowError := .f.
   if lError
      oCn:RollBack()
   else
      oCn:CommitTransaction()
   endif
 

[Marcelo Roggeri]

Mr Rao, buenísimo muchas gracias es bueno saberlo
Funciona de las dos maneras.
Solo utilizo Harbour
Saludos