FWHMYSQL Insert failing

[vilian]

Dear Friends,

I'm having a huge problem with Insert/MySql. I have a Decimal field where I'm tryng to save the value 1(one), but it's saved as 0(zero). Bellow i'm showing a little sample where the problem is happening.

Code: Select all  Expand view

function Main()
LOCAL oCn, oAnt,nValInss,nValIn13,aVal,cvd_500

   FW_SetUnicode( .t. )
   SET FIXED ON
   SET DATE BRIT
   SET CENTURY ON
   SET DECIMAL TO 2
 
   MySql_TinyintAsLogical( .t. )

   REQUEST DBFCDX
   RddSetDefault ( "DBFCDX" )

   aVal := {}
   nValInss := nValIn13 := 0
   
   oCn  := maria_Connect( "10.10.1.10,teste_sfp,user,@#$@#@#", .t. )
   oAnt := oCN:Query("SELECT fol.nfolha,fol.cmat,fol.cvandes,fol.val_calc,fol.clotacao,fol.situacao,fol.total_inc "+;
                     "FROM folhaprc AS folp LEFT JOIN foldet AS fld ON folp.cemp = fld.cemp AND folp.nfolha = fld.nfolha AND fld.cmat = '00261' "+;
                     "RIGHT JOIN folha AS fol ON folp.cemp = fol.cemp AND folp.nfolha = fol.nfolha AND fol.cmat = '00261' "+;
                     "WHERE folp.cemp = '047' AND fld.cmat = '00261' AND folp.nfolha <> '9WZ' AND "+;
                     "Concat(folp.ano,folp.mes) = '202402' AND fld.tipo <> 'D' AND fol.filial = '1' AND Locate(fol.cvandes,'001,400')<=0")

   DO WHILE .NOT. oAnt:Eof()
      IF oAnt:cvandes = "500"
         nValInss += oAnt:val_calc
      ENDIF      
      oAnt:Skip()
   ENDDO

   cvd_500 := 238.17 - nValInss //result = 1

   Aadd(aVal,{"047","9WZ","00261","500","500",0,cvd_500,0,"1",oAnt:clotacao,1,oAnt:situacao})  
   oCN:Insert("folha","cemp,nfolha,cmat,cvandes,cvdorig,val_inf,val_calc,total_inc,filial,clotacao,prazo,situacao",aVal,.t.)
   
   oCN:Close()

return nil
 

[nageswaragunupudi]

I did this small test

Code: Select all  Expand view

function mariaInsert()

   local oCn := maria_Connect( "209.250.245.152,fwh,fwhuser,FiveTech@2022" )

   ? "start"
   oCn:DropTable( "testinsert" )
   oCn:CreateTable( "testinsert", { { "ftext", "C", 10, 0 }, { "fnum", "N", 12, 2 } } )
   oCn:Insert( "testinsert", "ftext,fnum", { "one", 1 } )
   XBROWSER oCn:testinsert
   FW_MEMOEDIT( oCn:CreateTableSQL( "testinsert" ) )

   oCn:Close()

return nil

This is working well for me:


This is the creation SQL of the table

Code: Select all  Expand view

CREATE TABLE `testinsert` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ftext` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fnum` decimal(11,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci
 

This is the SQL generated and executed by the Insert(...) method above:

Code: Select all  Expand view

INSERT INTO `testinsert` ( `ftext`,`fnum` ) VALUES ( 'one',1 )

[nageswaragunupudi]

When you face such problems, I suggest you compare the
oCn:InsertSQL( ... )
with
oCn:CreateTableSQL( <table> )

or
Please create a small sample that we can execute at our end on the above cloud server.

[vilian]

I sent through google chat a complete little sample to you. Please, try it.

[vilian]

Good Morning Mr Rao,

I made a test with oCN:InsertSQ() and it's also returning a wrong sentence. It's returning this:

INSERT INTO `folha` ( `cemp`,`nfolha`,`cmat`,`cvandes`,`cvdorig`,`val_inf`,`val_calc`,`total_inc`,`filial`,`clotacao`,`prazo`,`situacao` ) VALUES ( '047','9WZ','00261','500','500',0,0,0,'1','       ',1,'  ' ) ON DUPLICATE KEY UPDATE `cvdorig` = VALUES ( `cvdorig` ) ,`val_inf` = VALUES ( `val_inf` ) ,`val_calc` = VALUES ( `val_calc` ) ,`total_inc` = VALUES ( `total_inc` ) ,`clotacao` = VALUES ( `clotacao` ) ,`prazo` = VALUES ( `prazo` ) ,`situacao` = VALUES ( `situacao` )

But, according with the array, the right sentence should be this:

INSERT INTO `folha` ( `cemp`,`nfolha`,`cmat`,`cvandes`,`cvdorig`,`val_inf`,`val_calc`,`total_inc`,`filial`,`clotacao`,`prazo`,`situacao` ) VALUES ( '047','9WZ','00261','500','500',0,1,0,'1','       ',1,'  ' ) ON DUPLICATE KEY UPDATE `cvdorig` = VALUES ( `cvdorig` ) ,`val_inf` = VALUES ( `val_inf` ) ,`val_calc` = VALUES ( `val_calc` ) ,`total_inc` = VALUES ( `total_inc` ) ,`clotacao` = VALUES ( `clotacao` ) ,`prazo` = VALUES ( `prazo` ) ,`situacao` = VALUES ( `situacao` )

[nageswaragunupudi]

Please connect to this server

Code: Select all  Expand view

maria_Connect( "209.250.245.152,fwh,fwhuser,FiveTech@2022" )

and upload your tables there.

[vilian]

Thank you, for your amazing support!