FiveTech Support Forums

#INCLUDE "fivewin.ch"
#INCLUDE "tdolphin.ch"
FUNCTION Armando()
LOCAL oBrw, oCn, oRs, oForm, oBtn
   CONNECT oCn HOST 'localhost' ;
                    USER 'root' ;
                    PASSWORD 'secret' ;
                    PORT 3306 ;
                    FLAGS 0;
                    DATABASE 'test'
   if oCn == nil
      ? "can not connect to server"
      return nil
   endif
   //Creo una tabla temporal
   oCn:Execute("";
    + "CREATE TEMPORARY TABLE IF NOT EXISTS armando ";
    +"( `id` INT(10) NOT NULL AUTO_INCREMENT, ";    
    +"`nombre` VARCHAR(50) NOT NULL,";    
    +"`orden` INT(10) NOT NULL,";
    +"PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8")
   //Inserto lo basico 
   oCn:Execute("INSERT INTO armando (orden,nombre) VALUES (1,'Manzana'),(2, 'Pera'),(3, 'Naranja'),(4, 'Mandarina')")
   oRs   := oCn:query("SELECT * FROM armando ORDER BY orden ")
   DEFINE DIALOG oForm TITLE "Armando" FROM 05,15 TO 36,99    
   @05,05 XBROWSE oBrw DATASOURCE oRs;
              COLUMNS "orden","nombre";
              HEADERS "Orden","Nombre";
              SIZES 80,200;
              OF oForm CELL SIZE 200,200 PIXEL       
   oBrw:CreateFromCode()
   @ 05,220 BUTTON oBtn PROMPT "Insertar" SIZE 30,10 OF oForm PIXEL ACTION Insertar(oRs,oCn,oBrw)
   ACTIVATE DIALOG oForm CENTER 
RETURN nil


STATIC FUNCTION Insertar(oRs,oCn,oBrw)
LOCAL oDlg1, nOrden := 0, cNombre := SPACE(50), oGet := ARRAY(2), oBtn := ARRAY(2), lRta := .f.
    DEFINE DIALOG oDlg1 TITLE "Insertar Registro" FROM 03,20 TO 14,60 
       
       @ 07, 05 SAY "Nombre:" OF oDlg1 PIXEL RIGHT SIZE 40,10
       @ 22, 05 SAY "Orden:"  OF oDlg1 PIXEL RIGHT SIZE 40,10       
       @ 05, 50 GET      oGet[1] VAR cNombre  OF oDlg1 PIXEL
       @ 20, 50 GET      oGet[2] VAR nOrden PICTURE "9999" OF oDlg1 PIXEL RIGHT       
       
       @ 35,30 BUTTON oBtn[1] PROMPT "&Grabar" OF oDlg1 SIZE 30,10 ;
               ACTION ((lRta := .t.), oDlg1:End() ) PIXEL
       @ 35,80 BUTTON oBtn[2] PROMPT "&Cancelar" OF oDlg1 SIZE 30,10 ;
               ACTION ((lRta := .f.), oDlg1:End() ) PIXEL
    ACTIVATE DIALOG oDlg1 CENTER 
IF !lRta .or. nOrden <=0   
   RETURN nil 
ENDIF 
oCn:Execute("UPDATE armando SET orden = orden + 1 WHERE orden >= "+str(nOrden))
oCn:Execute("INSERT INTO armando (nombre,orden) VALUES ('"+cNombre+"',"+STR(nOrden)+")")  
oRs:Refresh()
oBrw:Refresh(.t.)

RETURN nil

#include "fivewin.ch"

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

function Main()

   local oCn, oRs

   oCn   := Fw_OpenAdoConnection( "MYSQL,209.250.245.152,fwh,fwhuser,FiveTech@2022", .t. )
   oRs   := CreateTableFruits( oCn )

   // Right-click to insert a new fruit
   XBROWSER oRs TITLE "FRUITS" SETUP ( ;
      oBrw:bRClicked := { |r,c,f,o| InsertFruit( oRs ), o:Refresh() } )

   oRs:Close()
   oCn:Close()

return nil

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

function CreateTableFruits( oCn )

   local oRs, v

   oCn:Execute( "DROP TABLE IF EXISTS fruits" )
   FWAdoCreateTable( "fruits", { { "fruit", "C", 15, 0 } }, oCn )

   oRs   := FW_OpenRecordSet( oCn, "fruits" )
   oRs:Sort := "id"
   for each v in { "Manzana", "Pera", "Naranja", "Mandarina", "Mango", "Banana", "Papaya", "Grape" }
      oRs:AddNew( "fruit", v )
   next

return oRs

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

function InsertFruit( oRs )

   local nSave := oRs:AbsolutePosition
   local nId   := oRs:Fields( "id" ):Value
   local cSql

   PRIVATE cId   := LTrim( Str( nId ) )
   PRIVATE cFruit   := PadR( "Lima", 15 )

   if !MsgGet( "Fruit Name:", "At Line : " + cId, @cFruit ) .or. Empty( cFruit )
      return nil
   endif
   cFruit   := Trim( cFruit )

   CursorWait()

TEXT INTO cSql
UPDATE fruits LEFT JOIN fruits a ON fruits.id = a.id + 1
 SET fruits.fruit = a.fruit
 WHERE fruits.id > &cId
ENDTEXT

   WITH OBJECT oRs:ActiveConnection
      :Execute( "INSERT INTO fruits ( fruit ) VALUES ( '' )" )
      :Execute( cSql )
      :Execute( "UPDATE fruits SET fruit = '&cFruit' WHERE id = &cId" )
   END
   oRs:Requery()
   oRs:AbsolutePosition := nSave
   CursorArrow()

return nil

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