MariaDB doubt about field

[wartiaga]

Hi guys,

I have a dbf table that contains a field that controls the next number of an invoice for example. I block the registry, get the value, use, increase one, save the new value and unlock. How I do this control with mariadb? Field auto increment does not work because I want to have the option to change the value at any time.
Thanks in advance.

[nageswaragunupudi]

You can do exactly the same thing in MariaDB also.

[wartiaga]

You can do exactly the same thing in MariaDB also.

Thank you Nages! Can you provide a sample to work with no problems in lan enviroment? I try using fivewin native mariadb functions/commands. I new in mariadb.

[carlos vargas]

Here, i get the next counter value at save the data.

Code: Select all  Expand view  RUN

FUNCTION IncCount( cTable, cField )
   LOCAL oQryTmp
   LOCAL nCount := 0

   oQryTmp := oServer:Query( "SELECT " + cField + " FROM " + cTable + " FOR UPDATE" )

   IF oQryTmp:RecCount() >= 0
      nCount := oQryTmp:FieldGet( 1 ) + 1
      oServer:Execute( "UPDATE " + cTable + " SET " + cField + " = " + Var2Str( nCount ) )
   ENDIF

   oQryTmp:End()

RETURN nCount
 

Code: Select all  Expand view  RUN

STATIC PROCEDURE NuevoPrestamo_Grabar()
   LOCAL i, cSqlDetalle := "INSERT INTO prestamosdet (num_pres,cuota_no,fecha_prog,valor_prog,estado) VALUES "
   LOCAL lGrabado := FALSE

   FOR i:=1 TO Len( aTabla )
      cSqlDetalle += "( &1, " + Var2Str( aTabla[ i, TABLA_ABO_NO      ] ) +  "," + ;
                                Var2Str( aTabla[ i, TABLA_ABO_FECHA   ] ) +  "," + ;
                                Var2Str( aTabla[ i, TABLA_ABO_VALPROG ] ) +  "," + "'A'),"
   NEXT

   cSqlDetalle := HB_StrShrink( cSqlDetalle )

   oServer:lThrowError := TRUE

   TRY
      oServer:BeginTransaction()

      IF ( nPresNum := IncCount( "control", "cont_pres" ) ) > 0
         oServer:Insert2( "prestamosmas", { { "num_ruta"      , nRutaCob    }, ;
                                            { "num_clie"      , nClieNum    }, ; /*datos de cliente*/
                                            { "nombre"        , cClieNom    }, ;
                                            { "cedula"        , cClieCed    }, ;
                                            { "ciudad"        , cClieCiu    }, ;
                                            { "direccion"     , cClieDir    }, ;
                                            { "telefonos"     , cClieTel    }, ;
                                            { "num_pres"      , nPresNum    }, ; /*datos del prestamo*/
                                            { "importe"       , nImporte    }, ;
                                            { "interes"       , nInteres    }, ;
                                            { "cuotas"        , nCuotas     }, ;
                                            { "modalidad_pago", nModalidad  }, ;
                                            { "valor_cuota"   , nValorCuota }, ;
                                            { "total"         , nTotal      }, ;
                                            { "fecha_ent"     , dFechaEnt   }, ;
                                            { "fecha_ini"     , dFechaIni   }, ;
                                            { "fecha_fin"     , dFechaFin   }, ;
                                            { "abonado"       , 0           }, ;
                                            { "estado"        , "A"         }, ;
                                            { "nota"          , cNota       } } )

         oServer:Execute( cSqlDetalle, { nPresNum } )
      ENDIF

      oServer:Commit()
      lGrabado := TRUE

   CATCH oError
      ShowError( oError )
      oServer:Rollback()
   END

   oServer:lThrowError := FALSE

   IF lGrabado
      NuevoPrestamo_MostrarNumero()

      IF MsgNoYes( "Desea imprimir contrato del prestamo?" )
         NuevoPrestamo_Imprimir()
      ENDIF

      NuevoPrestamo_Limpiar1( TRUE )

      oDlgE:Update()

      oBtnSearch:SetFocus()
   ENDIF

RETURN lGrabado
 

[wartiaga]

Here, i get the next counter value at save the data.

Code: Select all  Expand view  RUN

FUNCTION IncCount( cTable, cField )
   LOCAL oQryTmp
   LOCAL nCount := 0

   oQryTmp := oServer:Query( "SELECT " + cField + " FROM " + cTable + " FOR UPDATE" )

   IF oQryTmp:RecCount() >= 0
      nCount := oQryTmp:FieldGet( 1 ) + 1
      oServer:Execute( "UPDATE " + cTable + " SET " + cField + " = " + Var2Str( nCount ) )
   ENDIF

   oQryTmp:End()

RETURN nCount
 

Code: Select all  Expand view  RUN

STATIC PROCEDURE NuevoPrestamo_Grabar()
   LOCAL i, cSqlDetalle := "INSERT INTO prestamosdet (num_pres,cuota_no,fecha_prog,valor_prog,estado) VALUES "
   LOCAL lGrabado := FALSE

   FOR i:=1 TO Len( aTabla )
      cSqlDetalle += "( &1, " + Var2Str( aTabla[ i, TABLA_ABO_NO      ] ) +  "," + ;
                                Var2Str( aTabla[ i, TABLA_ABO_FECHA   ] ) +  "," + ;
                                Var2Str( aTabla[ i, TABLA_ABO_VALPROG ] ) +  "," + "'A'),"
   NEXT

   cSqlDetalle := HB_StrShrink( cSqlDetalle )

   oServer:lThrowError := TRUE

   TRY
      oServer:BeginTransaction()

      IF ( nPresNum := IncCount( "control", "cont_pres" ) ) > 0
         oServer:Insert2( "prestamosmas", { { "num_ruta"      , nRutaCob    }, ;
                                            { "num_clie"      , nClieNum    }, ; /*datos de cliente*/
                                            { "nombre"        , cClieNom    }, ;
                                            { "cedula"        , cClieCed    }, ;
                                            { "ciudad"        , cClieCiu    }, ;
                                            { "direccion"     , cClieDir    }, ;
                                            { "telefonos"     , cClieTel    }, ;
                                            { "num_pres"      , nPresNum    }, ; /*datos del prestamo*/
                                            { "importe"       , nImporte    }, ;
                                            { "interes"       , nInteres    }, ;
                                            { "cuotas"        , nCuotas     }, ;
                                            { "modalidad_pago", nModalidad  }, ;
                                            { "valor_cuota"   , nValorCuota }, ;
                                            { "total"         , nTotal      }, ;
                                            { "fecha_ent"     , dFechaEnt   }, ;
                                            { "fecha_ini"     , dFechaIni   }, ;
                                            { "fecha_fin"     , dFechaFin   }, ;
                                            { "abonado"       , 0           }, ;
                                            { "estado"        , "A"         }, ;
                                            { "nota"          , cNota       } } )

         oServer:Execute( cSqlDetalle, { nPresNum } )
      ENDIF

      oServer:Commit()
      lGrabado := TRUE

   CATCH oError
      ShowError( oError )
      oServer:Rollback()
   END

   oServer:lThrowError := FALSE

   IF lGrabado
      NuevoPrestamo_MostrarNumero()

      IF MsgNoYes( "Desea imprimir contrato del prestamo?" )
         NuevoPrestamo_Imprimir()
      ENDIF

      NuevoPrestamo_Limpiar1( TRUE )

      oDlgE:Update()

      oBtnSearch:SetFocus()
   ENDIF

RETURN lGrabado
 

Thank you Carlos but no need to block and unblock the field like dbf table in lan enviroment?

[nageswaragunupudi]

Thank you Carlos but no need to block and unblock the field like dbf table in lan enviroment?

SELECT ... FOR UPDATE
locks the record.

[wartiaga]

Thank you Carlos but no need to block and unblock the field like dbf table in lan enviroment?

SELECT ... FOR UPDATE
locks the record.

Thank you Nages in this case I need to treat the select return before save?

[nageswaragunupudi]

You may use Mr. Carlos' function IncCount() with suitable modifications.

Alernatively, I prefer a function using a different approach.

Code: Select all  Expand view  RUN

function seq_nextval( cName )

   oCn:Execute( "UPDATE sequences SET seq_value = LAST_INSERT_ID( seq_value + 1 )" + ;
                " WHERE seq_name = '" + cName + "';" + ;
                "SELECT LAST_INSERT_ID()" )

return oCn:aNext[ 1, 1, 1 ]
 

where the table `sequences` maintains sequential ids for different tables.

This function also is network-safe (multi-user).

This is a small example using this function to maintain `id` of table `test_seq`.

Code: Select all  Expand view  RUN

#include "fivewin.ch"

static oCn

//----------------------------------------------------------------------------//

function Main()

   local oRs, nID

   SET DATE ITALIAN
   SET CENTURY ON
   SET TIME FORMAT TO "HH:MM:SS"

   oCn   := FW_DemoDB()

//   oCn:DropTable( "test_seq" )
//   oCn:DropTable( "sequences" )

   CheckTables()

   XBROWSER oCn:test_seq FASTEDIT

   oCn:Close()

return nil

//----------------------------------------------------------------------------//

function test_seq_rsedit( oRec )

   local oDlg, oFont

   if oRec:RecNo == 0 // new record
      oRec:id  := seq_nextval( "test_seq" )
   endif

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 400,240 PIXEL TRUEPIXEL FONT oFont ;
      TITLE If( oRec:RecNo == 0, "NEW", "EDIT" )

   oDlg:bInit := <||
   @  40, 30 SAY "ID    :" GET oRec:ID   SIZE 100,24 PIXEL PICTURE "999999" READONLY
   @  70, 30 SAY "Name  :" GET oRec:Name SIZE 300,24 PIXEL
   @ 100, 30 SAY "City  :" GET oRec:City SIZE 300,24 PIXEL
   return nil
   >

   @ 160, 30 BTNBMP PROMPT "SAVE"   SIZE 100,40 PIXEL OF oDlg FLAT ;
      ACTION ( oRec:Save(), oDlg:End() )

   @ 160,270 BTNBMP PROMPT "CANCEL" SIZE 100,40 PIXEL OF oDlg FLAT ;
      ACTION ( oDlg:End() )

   ACTIVATE DIALOG oDlg CENTERED ON PAINT oDlg:Box( 20, 10, 150, 390 )
   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//

function CheckTables()

   local nID

   if !oCn:TableExists( "test_seq" )

      oCn:CreateTable( "test_seq", { { "id",   "N",  6, 0, "PRI" }, ;
                                     { "name", "C", 40, 0 }, ;
                                     { "city", "C", 40, 0 }, ;
                                     { "upddt","=",  8, 0 } } )

   endif

   nID   := oCn:QueryResult( "SELECT MAX( id ) FROM test_seq" )

   if !oCn:TableExists( "sequences" )

      oCn:CreateTable( "sequences", { { "seq_name",  "C", 64, 0, "PRI" }, ;
                                      { "seq_value", "N",  6, 0 } } )

      oCn:Insert( "sequences", "seq_name,seq_value", { "test_seq", nID } )

   endif

return nil

//----------------------------------------------------------------------------//

function seq_nextval( cName )

   oCn:Execute( "UPDATE sequences SET seq_value = LAST_INSERT_ID( seq_value + 1 )" + ;
                " WHERE seq_name = '" + cName + "';" + ;
                "SELECT LAST_INSERT_ID()" )

return oCn:aNext[ 1, 1, 1 ]

//----------------------------------------------------------------------------//
 

You may copy this code to \fwh\samples folder and use buildh.bat or buildx.bat to build and run.

Note: The names "sequence" and "nextval()" are adapted from Oracle with the same functionality.

[Horizon]

Hi Mr. Rao,

Everytime press + and cancel button id is incremented by 1. Is there any solution to increment when press cancel button?

Thanks.

[nageswaragunupudi]

Do you want to increment when pressing cancel button? why?

[Horizon]

Pardon mr. Rao.

I mean not to increment.

[nageswaragunupudi]

There are two options.
1) Increment the id when a new record is saved. The most common solution for this is to Auto Increment field.
This is what most users use.
In this case, we know the new ID only when the record is saved, not before that. So, while editing a new record, we do not know the new ID that will be finally saved.

2) Decide the new ID in advance and display while editing a new record and save the same ID. This is not what many programmers prefer. The above solution is for those who want this option for whatever reasons.

What you want is easily done by using the auto-increment field and most examples we posted use that method. To check the behaviour, please build and run this small test

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oCn := FW_DemoDB()

   XBROWSER oCn:customer FASTEDIT

   oCn:Close()

return nil
 

You may just ignore the methods suggested in this post.

[wartiaga]

You may use Mr. Carlos' function IncCount() with suitable modifications.

Alernatively, I prefer a function using a different approach.

Code: Select all  Expand view  RUN

function seq_nextval( cName )

   oCn:Execute( "UPDATE sequences SET seq_value = LAST_INSERT_ID( seq_value + 1 )" + ;
                " WHERE seq_name = '" + cName + "';" + ;
                "SELECT LAST_INSERT_ID()" )

return oCn:aNext[ 1, 1, 1 ]
 

where the table `sequences` maintains sequential ids for different tables.

This function also is network-safe (multi-user).

This is a small example using this function to maintain `id` of table `test_seq`.

Code: Select all  Expand view  RUN

#include "fivewin.ch"

static oCn

//----------------------------------------------------------------------------//

function Main()

   local oRs, nID

   SET DATE ITALIAN
   SET CENTURY ON
   SET TIME FORMAT TO "HH:MM:SS"

   oCn   := FW_DemoDB()

//   oCn:DropTable( "test_seq" )
//   oCn:DropTable( "sequences" )

   CheckTables()

   XBROWSER oCn:test_seq FASTEDIT

   oCn:Close()

return nil

//----------------------------------------------------------------------------//

function test_seq_rsedit( oRec )

   local oDlg, oFont

   if oRec:RecNo == 0 // new record
      oRec:id  := seq_nextval( "test_seq" )
   endif

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 400,240 PIXEL TRUEPIXEL FONT oFont ;
      TITLE If( oRec:RecNo == 0, "NEW", "EDIT" )

   oDlg:bInit := <||
   @  40, 30 SAY "ID    :" GET oRec:ID   SIZE 100,24 PIXEL PICTURE "999999" READONLY
   @  70, 30 SAY "Name  :" GET oRec:Name SIZE 300,24 PIXEL
   @ 100, 30 SAY "City  :" GET oRec:City SIZE 300,24 PIXEL
   return nil
   >

   @ 160, 30 BTNBMP PROMPT "SAVE"   SIZE 100,40 PIXEL OF oDlg FLAT ;
      ACTION ( oRec:Save(), oDlg:End() )

   @ 160,270 BTNBMP PROMPT "CANCEL" SIZE 100,40 PIXEL OF oDlg FLAT ;
      ACTION ( oDlg:End() )

   ACTIVATE DIALOG oDlg CENTERED ON PAINT oDlg:Box( 20, 10, 150, 390 )
   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//

function CheckTables()

   local nID

   if !oCn:TableExists( "test_seq" )

      oCn:CreateTable( "test_seq", { { "id",   "N",  6, 0, "PRI" }, ;
                                     { "name", "C", 40, 0 }, ;
                                     { "city", "C", 40, 0 }, ;
                                     { "upddt","=",  8, 0 } } )

   endif

   nID   := oCn:QueryResult( "SELECT MAX( id ) FROM test_seq" )

   if !oCn:TableExists( "sequences" )

      oCn:CreateTable( "sequences", { { "seq_name",  "C", 64, 0, "PRI" }, ;
                                      { "seq_value", "N",  6, 0 } } )

      oCn:Insert( "sequences", "seq_name,seq_value", { "test_seq", nID } )

   endif

return nil

//----------------------------------------------------------------------------//

function seq_nextval( cName )

   oCn:Execute( "UPDATE sequences SET seq_value = LAST_INSERT_ID( seq_value + 1 )" + ;
                " WHERE seq_name = '" + cName + "';" + ;
                "SELECT LAST_INSERT_ID()" )

return oCn:aNext[ 1, 1, 1 ]

//----------------------------------------------------------------------------//
 

You may copy this code to \fwh\samples folder and use buildh.bat or buildx.bat to build and run.

Note: The names "sequence" and "nextval()" are adapted from Oracle with the same functionality.

Thank you Mr. Nages.

[Horizon]

There are two options.
1) Increment the id when a new record is saved. The most common solution for this is to Auto Increment field.
This is what most users use.
In this case, we know the new ID only when the record is saved, not before that. So, while editing a new record, we do not know the new ID that will be finally saved.

2) Decide the new ID in advance and display while editing a new record and save the same ID. This is not what many programmers prefer. The above solution is for those who want this option for whatever reasons.

What you want is easily done by using the auto-increment field and most examples we posted use that method. To check the behaviour, please build and run this small test

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oCn := FW_DemoDB()

   XBROWSER oCn:customer FASTEDIT

   oCn:Close()

return nil
 

You may just ignore the methods suggested in this post.

Thank you for clarification Mr. Rao.