Query in Mysql, the accented words are not correct
Moderator: Rathinagiri
-
- Posts: 5
- Joined: Sun Apr 16, 2017 12:29 pm
- DBs Used: DBF, MySQL, MariaDB, SQLite, FireBird, ODBC, MS sqlserver, db2
- Location: Tres Valles, Veracruz, México
- Contact:
Query in Mysql, the accented words are not correct
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
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
- Posts: 926
- Joined: Mon Jun 24, 2013 11:53 am
- DBs Used: DBF, MySQL, Oracle
- Location: Subotica, Serbia
Re: Query in Mysql, the accented words are not correct
Write complete SQL steatment, SELECT ... FROM ...
- serge_girard
- Posts: 3178
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: Query in Mysql, the accented words are not correct
And CREATE TABLE...
Serge
Serge
There's nothing you can do that can't be done...
-
- Posts: 5
- Joined: Sun Apr 16, 2017 12:29 pm
- DBs Used: DBF, MySQL, MariaDB, SQLite, FireBird, ODBC, MS sqlserver, db2
- Location: Tres Valles, Veracruz, México
- Contact:
Re: Query in Mysql, the accented words are not correct
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
...
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
...
-
- Posts: 5
- Joined: Sun Apr 16, 2017 12:29 pm
- DBs Used: DBF, MySQL, MariaDB, SQLite, FireBird, ODBC, MS sqlserver, db2
- Location: Tres Valles, Veracruz, México
- Contact:
Re: Query in Mysql, the accented words are not correct
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
...
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
...
- Attachments
-
- this is a Image
- Anotación 2019-08-07 163323.png (69.33 KiB) Viewed 3230 times
Re: Query in Mysql, the accented words are not correct
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 applicationjlas60 wrote: ↑Fri Aug 09, 2019 5:15 pm 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
...
-
- Posts: 5
- Joined: Sun Apr 16, 2017 12:29 pm
- DBs Used: DBF, MySQL, MariaDB, SQLite, FireBird, ODBC, MS sqlserver, db2
- Location: Tres Valles, Veracruz, México
- Contact:
Re: Query in Mysql, the accented words are not correct
Is VARCHAR(255)
- dragancesu
- Posts: 926
- Joined: Mon Jun 24, 2013 11:53 am
- DBs Used: DBF, MySQL, Oracle
- Location: Subotica, Serbia
Re: Query in Mysql, the accented words are not correct
CREATE TABLE tablename (
fielname1 fieldtype,
...
...
) ENGINE = InnoDB DEFAULT CHARSET=ut8 ;
fielname1 fieldtype,
...
...
) ENGINE = InnoDB DEFAULT CHARSET=ut8 ;
-
- Posts: 5
- Joined: Sun Apr 16, 2017 12:29 pm
- DBs Used: DBF, MySQL, MariaDB, SQLite, FireBird, ODBC, MS sqlserver, db2
- Location: Tres Valles, Veracruz, México
- Contact:
Re: Query in Mysql, the accented words are not correct
<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>
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>
- Attachments
-
- Anotación 2019-08-09 174513.png (83.4 KiB) Viewed 3194 times
Re: Query in Mysql, the accented words are not correct
jlas60 wrote: ↑Fri Aug 09, 2019 10:51 pm <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