Query in Mysql, the accented words are not correct

[jlas60]

Hello everyone

I get data from a query to Mysql, but the accented words are not correct, example:
"Placa 1 m_x0003_dulo, de aluminio, l_x000D_nea Econ_x0003_mica" must be "Placa 1 módulo, de aluminio, línea Económica"

I have defined this in my program:

REQUEST HB_LANG_ES
// REQUEST HB_CODEPAGE_ESWIN
REQUEST HB_CODEPAGE_UTF8

FUNCTION Main ()

    HB_CDPSELECT ("UTF8")
// HB_CDPSELECT ("ESWIN")
// HB_SETCODEPAGE ('ESWIN')
    HB_LANGSELECT ('ES')
    
DEFINE WINDOW Main;
AT 0.0;
WIDTH 800 HEIGHT 600;
TITLE 'Product management';
MAIN;
ICON ". \ RESOURCES \ Carrito.ico";
FONT 'Arial' SIZE 10;
ON INIT ConnectTo ();
ON RELEASE My_SQL_Logout ()
...
and I can't find the solution, any idea about it or what I'm doing wrong.

Best regards

Jose Luis

[dragancesu]

Write complete SQL steatment, SELECT ... FROM ...

[serge_girard]

And CREATE TABLE...

Serge

[jlas60]

Hi, my Sql steatment is:

FUNCTION consulta_Productos( cCategoria )

LOCAL aItem := {}, oQuery

LOCAL cQuery := "SELECT c.name AS Categoria, p.reference, p.name AS Producto, s.units as StockActual, sl.stocksecurity, sl.stockmaximum, "
cQuery += " sl.stockmaximum-s.units as PorComprar, "
cQuery += " p.pricebuy as PrecioCosto, "
cQuery += " (sl.stockmaximum-s.units)*p.pricebuy as Importe "
cQuery += "FROM products p "
cQuery += "INNER JOIN categories c ON p.CATEGORY=c.ID "
cQuery += "LEFT OUTER JOIN stockcurrent s ON s.PRODUCT=p.ID "
cQuery += "LEFT OUTER JOIN stocklevel sl ON sl.PRODUCT=p.ID "
cQuery += "WHERE s.units <= " + IIF( Frm_PRODUCTOS.Combo_0.Value==1, " sl.stocksecurity ", " sl.stockmaximum " )
cQuery += "AND sl.stockmaximum - s.units > 0 "
cQuery += IIF ( cCategoria == "Todas", "",[ AND c.name = '] + cCategoria + ['])
cQuery += " ORDER BY c.name, p.name;"

oQuery := oSrv:Query( cQuery )

If oQuery:NetErr()
MsgInfo( "SQL SELECT error: " + oQuery:Error() )
RELEASE WINDOW ALL
Quit
Endif

//DELETE ITEM ALL FROM Grid_1 OF Frm_Productos


WHILE ! oQuery:EOF()

oRow := oQuery:GetRow()

ADD ITEM { AllTrim( oRow:fieldGet(1)), ; // Categoría
AllTrim(oRow:fieldGet(2)), ; // Código
AllTrim( hb_utf8ToStr( oRow:fieldGet(3) ) ) , ; // Descripción del producto
str(oRow:fieldGet(4) ), ; //Stock actual
str(oRow:fieldGet(5) ), ; // Stock Minimo
str(oRow:fieldGet(6) ), ; // Stock Máximo
Transform( oRow:fieldGet(7), "999,999" ), ; // Cantidad a surtir
Transform( oRow:fieldGet(8), "999,999.99" ), ; // Precio de costo
Transform( oRow:fieldGet(9), "99,999,999.99" ) ; // Importe
} ;
TO Grid_1 OF Frm_Productos

AAdd( aRS, { AllTrim( oRow:fieldGet(1)), ;
AllTrim(oRow:fieldGet(2)), ;
AllTrim( hb_utf8ToStr( oRow:fieldGet(3) ) ) , ; // MyField = Replace([oRow:fieldGet(3)], Chr(10), Chr(13) + Chr(10))
oRow:fieldGet(4), ;
oRow:fieldGet(5), ;
oRow:fieldGet(6), ;
oRow:fieldGet(7), ;
oRow:fieldGet(8), ;
oRow:fieldGet(9) ;
} ;
)

oQuery:Skip()

ENDDO

oQuery:Destroy()

RETURN NIL


And this is the result of query:

Categoria Reference Producto StockActual stockmaximum PorComprar
FERRETERIA 10031 Abrazadera mini-6 de acero inoxidable, blister con 4 pzas 45 12 -33
FERRETERIA 44240 Abrazadera reforzada, # 28, 1-1/2 - 2-1/4', bolsa 10 pzas 30 1 -29
FERRETERIA 44243 Abrazadera reforzada, # 40, 2-1/4 - 3', bolsa 5 piezas 13 4 -9
FERRETERIA 44247 Abrazadera reforzada, # 56, 3-1/16 - 4', bolsa 5 piezas 18 1 -17
FERRETERIA 13468 aceite aflojatodo en aerosol 110 ml 5 4 -1
FERRETERIA 13471 aceite aflojatodo en aerosol 400 ml 3 4 1
FERRETERIA 16712 Aceite multiusos, 90 ml 7 10 3
FERRETERIA 46256 Adaptador 3 a 2, naranja, a granel, Volteck 1 11 10
FERRETERIA 44466 Alambre galvanizado, calibre 14.5 4 3 -1
FERRETERIA 44467 Alambre galvanizado, calibre 16 1 1 0
FERRETERIA 44468 Alambre galvanizado, calibre 18 3 3 0
FERRETERIA 0105 amarrador de varilla 1/2 3 5 2
FERRETERIA 12815 Amarrador de varillas con grip 3 1 -2
FERRETERIA 20017 Arco de solera para segueta 12', Pretul 2 6 4
FERRETERIA 10254 Arco jardinero tubular, 21' 2 1 -1
FERRETERIA 0106 arco para cegueta 1/2 0 6 6
FERRETERIA 44382 Armella 22 x 100, abierta, caja con 72 pzas 7 1 -6
FERRETERIA 44376 Armella 23 x 110, cerrada, caja con 72 pzas 48 1 -47
FERRETERIA 49902 Asiento económico para WC, 35 cm, blanco 3 1 -2
FERRETERIA 20060 Aspersor plástico dos vías, estaca de 14 cm, Pretul 4 6 2
FERRETERIA 12578 Banda de plástico 'precaución', 300 ft 3 1 -2
FERRETERIA 44030 Bandola destorcedora, de zinc, 3' 7 12 5
FERRETERIA 44038 Bandola mosquetón, de acero, 1/4' 8 1 -7
FERRETERIA 21255 Báscula romana de resorte de 100 kg, Pretul 4 4 0
FERRETERIA 21254 Báscula romana de resorte de 50 kg, Pretul 4 1 -3
...

[jlas60]

Hi, my Sql steatment is:

FUNCTION consulta_Productos( cCategoria )

LOCAL aItem := {}, oQuery

LOCAL cQuery := "SELECT c.name AS Categoria, p.reference, p.name AS Producto, s.units as StockActual, sl.stocksecurity, sl.stockmaximum, "
cQuery += " sl.stockmaximum-s.units as PorComprar, "
cQuery += " p.pricebuy as PrecioCosto, "
cQuery += " (sl.stockmaximum-s.units)*p.pricebuy as Importe "
cQuery += "FROM products p "
cQuery += "INNER JOIN categories c ON p.CATEGORY=c.ID "
cQuery += "LEFT OUTER JOIN stockcurrent s ON s.PRODUCT=p.ID "
cQuery += "LEFT OUTER JOIN stocklevel sl ON sl.PRODUCT=p.ID "
cQuery += "WHERE s.units <= " + IIF( Frm_PRODUCTOS.Combo_0.Value==1, " sl.stocksecurity ", " sl.stockmaximum " )
cQuery += "AND sl.stockmaximum - s.units > 0 "
cQuery += IIF ( cCategoria == "Todas", "",[ AND c.name = '] + cCategoria + ['])
cQuery += " ORDER BY c.name, p.name;"

oQuery := oSrv:Query( cQuery )

If oQuery:NetErr()
MsgInfo( "SQL SELECT error: " + oQuery:Error() )
RELEASE WINDOW ALL
Quit
Endif

//DELETE ITEM ALL FROM Grid_1 OF Frm_Productos


WHILE ! oQuery:EOF()

oRow := oQuery:GetRow()

ADD ITEM { AllTrim( oRow:fieldGet(1)), ; // Categoría
AllTrim(oRow:fieldGet(2)), ; // Código
AllTrim( hb_utf8ToStr( oRow:fieldGet(3) ) ) , ; // Descripción del producto
str(oRow:fieldGet(4) ), ; //Stock actual
str(oRow:fieldGet(5) ), ; // Stock Minimo
str(oRow:fieldGet(6) ), ; // Stock Máximo
Transform( oRow:fieldGet(7), "999,999" ), ; // Cantidad a surtir
Transform( oRow:fieldGet(8), "999,999.99" ), ; // Precio de costo
Transform( oRow:fieldGet(9), "99,999,999.99" ) ; // Importe
} ;
TO Grid_1 OF Frm_Productos

AAdd( aRS, { AllTrim( oRow:fieldGet(1)), ;
AllTrim(oRow:fieldGet(2)), ;
AllTrim( hb_utf8ToStr( oRow:fieldGet(3) ) ) , ; // MyField = Replace([oRow:fieldGet(3)], Chr(10), Chr(13) + Chr(10))
oRow:fieldGet(4), ;
oRow:fieldGet(5), ;
oRow:fieldGet(6), ;
oRow:fieldGet(7), ;
oRow:fieldGet(8), ;
oRow:fieldGet(9) ;
} ;
)

oQuery:Skip()

ENDDO

oQuery:Destroy()

RETURN NIL


And this is the result of query:

Categoria Reference Producto StockActual stockmaximum PorComprar
FERRETERIA 10031 Abrazadera mini-6 de acero inoxidable, blister con 4 pzas 45 12 -33
FERRETERIA 44240 Abrazadera reforzada, # 28, 1-1/2 - 2-1/4', bolsa 10 pzas 30 1 -29
FERRETERIA 44243 Abrazadera reforzada, # 40, 2-1/4 - 3', bolsa 5 piezas 13 4 -9
FERRETERIA 44247 Abrazadera reforzada, # 56, 3-1/16 - 4', bolsa 5 piezas 18 1 -17
FERRETERIA 13468 aceite aflojatodo en aerosol 110 ml 5 4 -1
FERRETERIA 13471 aceite aflojatodo en aerosol 400 ml 3 4 1
FERRETERIA 16712 Aceite multiusos, 90 ml 7 10 3
FERRETERIA 46256 Adaptador 3 a 2, naranja, a granel, Volteck 1 11 10
FERRETERIA 44466 Alambre galvanizado, calibre 14.5 4 3 -1
FERRETERIA 44467 Alambre galvanizado, calibre 16 1 1 0
FERRETERIA 44468 Alambre galvanizado, calibre 18 3 3 0
FERRETERIA 0105 amarrador de varilla 1/2 3 5 2
FERRETERIA 12815 Amarrador de varillas con grip 3 1 -2
FERRETERIA 20017 Arco de solera para segueta 12', Pretul 2 6 4
FERRETERIA 10254 Arco jardinero tubular, 21' 2 1 -1
FERRETERIA 0106 arco para cegueta 1/2 0 6 6
FERRETERIA 44382 Armella 22 x 100, abierta, caja con 72 pzas 7 1 -6
FERRETERIA 44376 Armella 23 x 110, cerrada, caja con 72 pzas 48 1 -47
FERRETERIA 49902 Asiento económico para WC, 35 cm, blanco 3 1 -2
FERRETERIA 20060 Aspersor plástico dos vías, estaca de 14 cm, Pretul 4 6 2
FERRETERIA 12578 Banda de plástico 'precaución', 300 ft 3 1 -2
FERRETERIA 44030 Bandola destorcedora, de zinc, 3' 7 12 5
FERRETERIA 44038 Bandola mosquetón, de acero, 1/4' 8 1 -7
FERRETERIA 21255 Báscula romana de resorte de 100 kg, Pretul 4 4 0
FERRETERIA 21254 Báscula romana de resorte de 50 kg, Pretul 4 1 -3
...

[jairpinho]

Hi, my Sql steatment is:

FUNCTION consulta_Productos( cCategoria )

LOCAL aItem := {}, oQuery

LOCAL cQuery := "SELECT c.name AS Categoria, p.reference, p.name AS Producto, s.units as StockActual, sl.stocksecurity, sl.stockmaximum, "
cQuery += " sl.stockmaximum-s.units as PorComprar, "
cQuery += " p.pricebuy as PrecioCosto, "
cQuery += " (sl.stockmaximum-s.units)*p.pricebuy as Importe "
cQuery += "FROM products p "
cQuery += "INNER JOIN categories c ON p.CATEGORY=c.ID "
cQuery += "LEFT OUTER JOIN stockcurrent s ON s.PRODUCT=p.ID "
cQuery += "LEFT OUTER JOIN stocklevel sl ON sl.PRODUCT=p.ID "
cQuery += "WHERE s.units <= " + IIF( Frm_PRODUCTOS.Combo_0.Value==1, " sl.stocksecurity ", " sl.stockmaximum " )
cQuery += "AND sl.stockmaximum - s.units > 0 "
cQuery += IIF ( cCategoria == "Todas", "",[ AND c.name = '] + cCategoria + ['])
cQuery += " ORDER BY c.name, p.name;"

oQuery := oSrv:Query( cQuery )

If oQuery:NetErr()
MsgInfo( "SQL SELECT error: " + oQuery:Error() )
RELEASE WINDOW ALL
Quit
Endif

//DELETE ITEM ALL FROM Grid_1 OF Frm_Productos


WHILE ! oQuery:EOF()

oRow := oQuery:GetRow()

ADD ITEM { AllTrim( oRow:fieldGet(1)), ; // Categoría
AllTrim(oRow:fieldGet(2)), ; // Código
AllTrim( hb_utf8ToStr( oRow:fieldGet(3) ) ) , ; // Descripción del producto
str(oRow:fieldGet(4) ), ; //Stock actual
str(oRow:fieldGet(5) ), ; // Stock Minimo
str(oRow:fieldGet(6) ), ; // Stock Máximo
Transform( oRow:fieldGet(7), "999,999" ), ; // Cantidad a surtir
Transform( oRow:fieldGet(8), "999,999.99" ), ; // Precio de costo
Transform( oRow:fieldGet(9), "99,999,999.99" ) ; // Importe
} ;
TO Grid_1 OF Frm_Productos

AAdd( aRS, { AllTrim( oRow:fieldGet(1)), ;
AllTrim(oRow:fieldGet(2)), ;
AllTrim( hb_utf8ToStr( oRow:fieldGet(3) ) ) , ; // MyField = Replace([oRow:fieldGet(3)], Chr(10), Chr(13) + Chr(10))
oRow:fieldGet(4), ;
oRow:fieldGet(5), ;
oRow:fieldGet(6), ;
oRow:fieldGet(7), ;
oRow:fieldGet(8), ;
oRow:fieldGet(9) ;
} ;
)

oQuery:Skip()

ENDDO

oQuery:Destroy()

RETURN NIL


And this is the result of query:

Categoria Reference Producto StockActual stockmaximum PorComprar
FERRETERIA 10031 Abrazadera mini-6 de acero inoxidable, blister con 4 pzas 45 12 -33
FERRETERIA 44240 Abrazadera reforzada, # 28, 1-1/2 - 2-1/4', bolsa 10 pzas 30 1 -29
FERRETERIA 44243 Abrazadera reforzada, # 40, 2-1/4 - 3', bolsa 5 piezas 13 4 -9
FERRETERIA 44247 Abrazadera reforzada, # 56, 3-1/16 - 4', bolsa 5 piezas 18 1 -17
FERRETERIA 13468 aceite aflojatodo en aerosol 110 ml 5 4 -1
FERRETERIA 13471 aceite aflojatodo en aerosol 400 ml 3 4 1
FERRETERIA 16712 Aceite multiusos, 90 ml 7 10 3
FERRETERIA 46256 Adaptador 3 a 2, naranja, a granel, Volteck 1 11 10
FERRETERIA 44466 Alambre galvanizado, calibre 14.5 4 3 -1
FERRETERIA 44467 Alambre galvanizado, calibre 16 1 1 0
FERRETERIA 44468 Alambre galvanizado, calibre 18 3 3 0
FERRETERIA 0105 amarrador de varilla 1/2 3 5 2
FERRETERIA 12815 Amarrador de varillas con grip 3 1 -2
FERRETERIA 20017 Arco de solera para segueta 12', Pretul 2 6 4
FERRETERIA 10254 Arco jardinero tubular, 21' 2 1 -1
FERRETERIA 0106 arco para cegueta 1/2 0 6 6
FERRETERIA 44382 Armella 22 x 100, abierta, caja con 72 pzas 7 1 -6
FERRETERIA 44376 Armella 23 x 110, cerrada, caja con 72 pzas 48 1 -47
FERRETERIA 49902 Asiento económico para WC, 35 cm, blanco 3 1 -2
FERRETERIA 20060 Aspersor plástico dos vías, estaca de 14 cm, Pretul 4 6 2
FERRETERIA 12578 Banda de plástico 'precaución', 300 ft 3 1 -2
FERRETERIA 44030 Bandola destorcedora, de zinc, 3' 7 12 5
FERRETERIA 44038 Bandola mosquetón, de acero, 1/4' 8 1 -7
FERRETERIA 21255 Báscula romana de resorte de 100 kg, Pretul 4 4 0
FERRETERIA 21254 Báscula romana de resorte de 50 kg, Pretul 4 1 -3
...

what type of data in the product field, checked with heidsql if the database is accented, need to check if it is wrong in the database or when loading the data in your application

[jlas60]

Is VARCHAR(255)

[dragancesu]

CREATE TABLE tablename (
fielname1 fieldtype,
...
...
) ENGINE = InnoDB DEFAULT CHARSET=ut8 ;

[jlas60]

<code>
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
`ID` varchar(255) NOT NULL,
`REFERENCE` varchar(255) NOT NULL,
`CODE` varchar(255) NOT NULL,
`CODETYPE` varchar(255) DEFAULT NULL,
`NAME` varchar(255) NOT NULL, <------------------------ ok but in program nok
`PRICEBUY` double NOT NULL DEFAULT '0',
`PRICESELL` double NOT NULL DEFAULT '0',
`CATEGORY` varchar(255) NOT NULL,
`TAXCAT` varchar(255) NOT NULL,
`ATTRIBUTESET_ID` varchar(255) DEFAULT NULL,
`STOCKCOST` double DEFAULT NULL,
`STOCKVOLUME` double DEFAULT NULL,
`IMAGE` mediumblob,
`ISCOM` bit(1) NOT NULL DEFAULT b'0',
`ISSCALE` bit(1) NOT NULL DEFAULT b'0',
`ISKITCHEN` bit(1) NOT NULL DEFAULT b'0',
`PRINTKB` bit(1) NOT NULL DEFAULT b'0',
`SENDSTATUS` bit(1) NOT NULL DEFAULT b'0',
`ISSERVICE` bit(1) NOT NULL DEFAULT b'0',
`ATTRIBUTES` mediumblob,
`DISPLAY` varchar(255) DEFAULT NULL,
`ISVPRICE` smallint(6) NOT NULL DEFAULT '0',
`ISVERPATRIB` smallint(6) NOT NULL DEFAULT '0',
`TEXTTIP` varchar(255) DEFAULT '',
`WARRANTY` smallint(6) NOT NULL DEFAULT '0',
`STOCKUNITS` double NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
UNIQUE KEY `PRODUCTS_INX_0` (`REFERENCE`),
UNIQUE KEY `PRODUCTS_INX_1` (`CODE`),
UNIQUE KEY `PRODUCTS_NAME_INX` (`NAME`),
KEY `PRODUCTS_FK_1` (`CATEGORY`),
KEY `PRODUCTS_TAXCAT_FK` (`TAXCAT`),
KEY `PRODUCTS_ATTRSET_FK` (`ATTRIBUTESET_ID`),
CONSTRAINT `PRODUCTS_ATTRSET_FK` FOREIGN KEY (`ATTRIBUTESET_ID`) REFERENCES `attributeset` (`ID`),
CONSTRAINT `PRODUCTS_FK_1` FOREIGN KEY (`CATEGORY`) REFERENCES `categories` (`ID`),
CONSTRAINT `PRODUCTS_TAXCAT_FK` FOREIGN KEY (`TAXCAT`) REFERENCES `taxcategories` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
</code>

[jairpinho]

<code>
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
`ID` varchar(255) NOT NULL,
`REFERENCE` varchar(255) NOT NULL,
`CODE` varchar(255) NOT NULL,
`CODETYPE` varchar(255) DEFAULT NULL,
`NAME` varchar(255) NOT NULL, <------------------------ ok but in program nok
`PRICEBUY` double NOT NULL DEFAULT '0',
`PRICESELL` double NOT NULL DEFAULT '0',
`CATEGORY` varchar(255) NOT NULL,
`TAXCAT` varchar(255) NOT NULL,
`ATTRIBUTESET_ID` varchar(255) DEFAULT NULL,
`STOCKCOST` double DEFAULT NULL,
`STOCKVOLUME` double DEFAULT NULL,
`IMAGE` mediumblob,
`ISCOM` bit(1) NOT NULL DEFAULT b'0',
`ISSCALE` bit(1) NOT NULL DEFAULT b'0',
`ISKITCHEN` bit(1) NOT NULL DEFAULT b'0',
`PRINTKB` bit(1) NOT NULL DEFAULT b'0',
`SENDSTATUS` bit(1) NOT NULL DEFAULT b'0',
`ISSERVICE` bit(1) NOT NULL DEFAULT b'0',
`ATTRIBUTES` mediumblob,
`DISPLAY` varchar(255) DEFAULT NULL,
`ISVPRICE` smallint(6) NOT NULL DEFAULT '0',
`ISVERPATRIB` smallint(6) NOT NULL DEFAULT '0',
`TEXTTIP` varchar(255) DEFAULT '',
`WARRANTY` smallint(6) NOT NULL DEFAULT '0',
`STOCKUNITS` double NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
UNIQUE KEY `PRODUCTS_INX_0` (`REFERENCE`),
UNIQUE KEY `PRODUCTS_INX_1` (`CODE`),
UNIQUE KEY `PRODUCTS_NAME_INX` (`NAME`),
KEY `PRODUCTS_FK_1` (`CATEGORY`),
KEY `PRODUCTS_TAXCAT_FK` (`TAXCAT`),
KEY `PRODUCTS_ATTRSET_FK` (`ATTRIBUTESET_ID`),
CONSTRAINT `PRODUCTS_ATTRSET_FK` FOREIGN KEY (`ATTRIBUTESET_ID`) REFERENCES `attributeset` (`ID`),
CONSTRAINT `PRODUCTS_FK_1` FOREIGN KEY (`CATEGORY`) REFERENCES `categories` (`ID`),
CONSTRAINT `PRODUCTS_TAXCAT_FK` FOREIGN KEY (`TAXCAT`) REFERENCES `taxcategories` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
</code>

your application is writing correct to the database, what is not working would be your sample on the grid.

application:
write = ok mysql -> ok
read = nok -> hmg nok select or convert configuration error