|
|
Home » U++ Library support » U++ SQL » Sticky problem : MySql and UTF8
Sticky problem : MySql and UTF8 [message #35044] |
Mon, 02 January 2012 10:56 ![Go to next message Go to next message](theme/default/images/down.png) |
![](https://www.ultimatepp.org/forums/images/custom_avatars/690.jpg) |
BioBytes
Messages: 307 Registered: October 2008 Location: France
|
Senior Member |
|
|
Hi alls,
Sorry to come back on a discussion that have already been developped in the forum but I still face some issues with MySql encoding UTF8.
I added the line forcing MySql encoding to UTF8 starting with opening of my application session "rmSession"
if(rmSession.Connect(cnxStr.At(3),cnxStr.At(4),"dbrisks",server,ScanInt(cnxStr.At(2),NULL,10)))
{
mysql_set_character_set(rmSession,"utf8");
SQL = rmSession;
...
}
I have an SqlArray with 7 columns linked each to a specific SqlId (fields described in Schema file). Data input are controlled by SqlCtrls (DocEdit for the first 2 columns). When I implement the action WhenAcceptEdit, I got a Sql Error telling that string encoding of the second DocEdit is incorrect if there is some accents (My application is in French).
If I delete the mysql_set_character_set(...) line, no more error when accept edit but of course the string is incorrectly encoded in the MySql table (accents are replaced with ? chararacter).
I turned this problem in another part of my source as follows. I checked the window closing and I save the current charset, forcing then charset to UTF8 then proceed SqlCtrls updating and return to saved charset. I must say that my application degfauly charset is already UTF8. So something remains undertandable for me.
[I]void rmFileDlg::Close()
{
int idRisk;
String qtfStr;
byte previousCharset = GetDefaultCharset();
try
{
if(ok.HasFocus()||(ok.HasMouse()))
{
if(GetMode()==ADD)
{
if(IsPackArrayEmpty()||IsRisksArrayEmpty())
{
qtfStr<<"[=A2*@r "<<DeQtfLf(t_("Aucune catégorie n'est cochée dans les listes de Présentation et des Risques spécifiques."))<<"]&[<10A2*@r "<<DeQtfLf(t_("Il est nécessaire de cocher des catégories de présentation et de risques pour valider un nouvel enregistrement."))<<"]";
Exclamation(qtfStr);
}
else
{
PromptOK(t_("Enregistrement du nouveau dossier et ouverture de la fiche de saisie des risques et des actions de réduction."));
SQL &numFileCtrls.Insert(TBLPROD);
for(int i=0;i<arrayPres.GetCount();i++)
if((bool)arrayPres.Get(i,2))SQL &Insert(TBLPRODPACK)(IDPRODUCT,~rmNumFileEdit)(DEVICE,As String(arrayPres.Get(i,1)));
for(int i=0;i<arrayCatSpecRisk.GetCount();i++)
if((bool)arrayCatSpecRisk.Get(i,2))
{
SQL &Select(IDRISKGEN).From(TBLRISKGEN).Where(NAME==AsString (arrayCatSpecRisk.Get(i,1)));
while(SQL.Fetch())idRisk=SQL[IDRISKGEN];
SQL.Clear();
SQL &Insert(TBLRISKGENPROD)(NUMRISKFILE,~rmNumFileEdit)(NUMG ENRISK,idRisk);
}
risksWin.SetDropLists();
risksWin.Execute();
}
}
if(GetMode()==SELECT)
{
SetDefaultCharset(CHARSET_UTF8);
SQL & numFileCtrls.UpdateModified(TBLPROD).Where(NUM==~rmNumFileEd it);
//numFileCtrls.Load(SQL,TBLPROD,NUM==~rmNumFileEdit);
numFileCtrls.ClearModify();
if(IsPackArrayModified())
{
SQL &Delete(TBLPRODPACK).Where(IDPRODUCT==~rmNumFileEdit);
for(int i=0;i<arrayPres.GetCount();i++)
if((bool)arrayPres.Get(i,2))SQL &Insert(TBLPRODPACK)(IDPRODUCT,~rmNumFileEdit)(DEVICE,As String(arrayPres.Get(i,1)));
}
if(IsRiskArrayModified())
{
SQL &Delete(TBLRISKGENPROD).Where(NUMRISKFILE==~rmNumFileEdi t);
for(int i=0;i<arrayCatSpecRisk.GetCount();i++)
if((bool)arrayCatSpecRisk.Get(i,2))
{
SQL &Select(IDRISKGEN).From(TBLRISKGEN).Where(NAME==AsString (arrayCatSpecRisk.Get(i,1)));
while(SQL.Fetch())idRisk=SQL[IDRISKGEN];
SQL.Clear();
SQL &Insert(TBLRISKGENPROD)(NUMRISKFILE,~rmNumFileEdit)(NUMG ENRISK,idRisk);
}
}
}
}
SetDefaultCharset(previousCharset);
TopWindow::Close();
}
catch(SqlExc &ex)
{
PanicMessageBox(t_("Erreur SQL"),t_("L'erreur suivante s'est produite : ")+ex.ToString());
TopWindow::Close();
}
}[/I]
Some help or comments ?
Thank you in advance and Happy New Year with U++
Cheers
Biobytes
|
|
|
Re: Sticky problem : MySql and UTF8 [message #35224 is a reply to message #35044] |
Mon, 23 January 2012 22:37 ![Go to previous message Go to previous message](theme/default/images/up.png) ![Go to next message Go to next message](theme/default/images/down.png) |
![](https://www.ultimatepp.org/forums/images/custom_avatars/690.jpg) |
BioBytes
Messages: 307 Registered: October 2008 Location: France
|
Senior Member |
|
|
Right way to solve the accentuated issue with MySql and UTF8 is to add the following code after getting the connection:
mysql_set_character_set(MySession,"utf8");
Operations of insertion must be done as follows:
String str = ToCharset(CHARSET_UTF8,AsString(~MyEditString),GetDefaultCha rset(),'?');
SQL &Insert(MYTABLE)(NAME,str)(NUM,MyEditInt.GetData());
Operations of deletion must ne done as follows:
SQL &Delete(MYTABLE).Where(NAME==ToCharset CHARSET_UTF8,~MyEditString,GetDefaultCharset(),'?'));
I will try to post a code snippet to explain in details how to proceed with MySql and UTF8 encoded tables.
Hoping this will be helpful for someones
Biobytes
|
|
|
|
Re: Sticky problem : MySql and UTF8 [message #35239 is a reply to message #35231] |
Tue, 24 January 2012 21:47 ![Go to previous message Go to previous message](theme/default/images/up.png) ![Go to next message Go to next message](theme/default/images/down.png) |
|
I use SQL schema added value:
//==================================================================================//
//==============================ÑÕÅÌÛ SQL äàííûõ===================================//
//==================================================================================//
#ifdef MYSQL_DB
#define SERIAL_ INT_
#endif
//======================================================================================//
// //
// TERMINAL //
// //
//======================================================================================//
TABLE_ (TERMINAL)
#ifdef MYSQL_DB
TABLE_SUFFIX("CHARACTER SET utf8 COLLATE utf8_general_ci\n") // <<-----this line set right MySQL options
#endif
SERIAL_ (TER_ID) PRIMARY_KEY AUTO_INCREMENT
STRING_ (TER_NAME,13)
DATE_ (TER_DATECREATE)
DATE_ (TER_DATEMODIFY)
INT_ (TER_ID_GROUP)
INT_ (TER_ID_TYPE)
INT_ (TER_ID_TEMPLATE)
INT_ (TER_PLACE)
STRING_ (TER_COMMENT,255)
END_TABLE
SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
[Updated on: Tue, 24 January 2012 22:04] Report message to a moderator
|
|
|
|
Re: Sticky problem : MySql and UTF8 [message #35255 is a reply to message #35224] |
Thu, 26 January 2012 10:01 ![Go to previous message Go to previous message](theme/default/images/up.png) ![Go to next message Go to next message](theme/default/images/down.png) |
![](https://www.ultimatepp.org/forums/images/custom_avatars/3.png) |
mirek
Messages: 14003 Registered: November 2005
|
Ultimate Member |
|
|
BioBytes wrote on Mon, 23 January 2012 16:37 | Right way to solve the accentuated issue with MySql and UTF8 is to add the following code after getting the connection:
mysql_set_character_set(MySession,"utf8");
Operations of insertion must be done as follows:
String str = ToCharset(CHARSET_UTF8,AsString(~MyEditString),GetDefaultCha rset(),'?');
SQL &Insert(MYTABLE)(NAME,str)(NUM,MyEditInt.GetData());
Operations of deletion must ne done as follows:
SQL &Delete(MYTABLE).Where(NAME==ToCharset CHARSET_UTF8,~MyEditString,GetDefaultCharset(),'?'));
I will try to post a code snippet to explain in details how to proceed with MySql and UTF8 encoded tables.
Hoping this will be helpful for someones
Biobytes
|
Thanks, I will try to integrate this into MySql session.
Mirek
|
|
|
|
|
Re: Sticky problem : MySql and UTF8 [message #36024 is a reply to message #35239] |
Thu, 19 April 2012 18:47 ![Go to previous message Go to previous message](theme/default/images/up.png) ![Go to next message Go to next message](theme/default/images/down.png) |
|
sergeynikitin wrote on Tue, 24 January 2012 23:47 | I use SQL schema added value:
//==================================================================================//
//==============================ÑÕÅÌÛ SQL äàííûõ===================================//
//==================================================================================//
#ifdef MYSQL_DB
#define SERIAL_ INT_
#endif
//======================================================================================//
// //
// TERMINAL //
// //
//======================================================================================//
TABLE_ (TERMINAL)
#ifdef MYSQL_DB
TABLE_SUFFIX("CHARACTER SET utf8 COLLATE utf8_general_ci\n") // <<-----this line set right MySQL options
#endif
SERIAL_ (TER_ID) PRIMARY_KEY AUTO_INCREMENT
STRING_ (TER_NAME,13)
DATE_ (TER_DATECREATE)
DATE_ (TER_DATEMODIFY)
INT_ (TER_ID_GROUP)
INT_ (TER_ID_TYPE)
INT_ (TER_ID_TEMPLATE)
INT_ (TER_PLACE)
STRING_ (TER_COMMENT,255)
END_TABLE
|
Some improvements:
#ifndef flagPGSQL_DB
#define SERIAL_ INT_
#else
#define AUTO_INCREMENT
#endif
#ifdef flagMYSQL_DB
#undef TABLE_
#define TABLE_(x) DOID(x) TABLE(x) \
TABLE_SUFFIX("CHARACTER SET utf8 COLLATE utf8_general_ci\n")
#endif
//======================================================================================//
// //
// USER //
// //
//======================================================================================//
TABLE_ (USER)
SERIAL_ (USR_ID) PRIMARY_KEY AUTO_INCREMENT
STRING_ (USR_NAME, 255)
STRING_ (USR_REALNAME, 255)
STRING_ (USR_LOGINNAME, 20)
STRING_ (USR_PASSWORD, 20)
STRING_ (USR_RIGHTS, 2000)
END_TABLE
SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
[Updated on: Thu, 19 April 2012 18:51] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sun Sep 01 05:37:22 CEST 2024
Total time taken to generate the page: 0.00437 seconds
|
|
|