Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
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
BioBytes is currently offline  BioBytes
Messages: 308
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

Report message to a moderator

Re: Sticky problem : MySql and UTF8 [message #35224 is a reply to message #35044] Mon, 23 January 2012 22:37 Go to previous messageGo to next message
BioBytes is currently offline  BioBytes
Messages: 308
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

Report message to a moderator

Re: Sticky problem : MySql and UTF8 [message #35231 is a reply to message #35044] Tue, 24 January 2012 08:53 Go to previous messageGo to next message
mr_ped is currently offline  mr_ped
Messages: 826
Registered: November 2005
Location: Czech Republic - Praha
Experienced Contributor
BTW, I noticed you never use [ CODE ] [ /CODE ] markup to post source code ... do you hate it or what? Very Happy

Report message to a moderator

Re: Sticky problem : MySql and UTF8 [message #35239 is a reply to message #35231] Tue, 24 January 2012 21:47 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

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 #35252 is a reply to message #35239] Wed, 25 January 2012 21:49 Go to previous messageGo to next message
BioBytes is currently offline  BioBytes
Messages: 308
Registered: October 2008
Location: France
Senior Member
Thanks for your help Sergey,

Sure it is easier to configure MySql encoding format directly into the Sql Schema.

Hi Mr Ped,
you are right but I miss the possibility of using the CODE markups, shame on me Embarassed

Regards

Biobytes

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 messageGo to next message
mirek is currently offline  mirek
Messages: 14255
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

Report message to a moderator

Re: Sticky problem : MySql and UTF8 [message #35275 is a reply to message #35255] Sat, 28 January 2012 10:54 Go to previous messageGo to next message
BioBytes is currently offline  BioBytes
Messages: 308
Registered: October 2008
Location: France
Senior Member
Ok thank you Mirek. Smile

Just apart: This encoding issue does not occur when connection is done using ODBCsession as the ODBC driver should include encoding functions. Unfortunately, ODBC solution is not optimal in production when databases contain a lot of records and many users need to access the same tables.

Regards

Biobytes

Report message to a moderator

Re: Sticky problem : MySql and UTF8 [message #35352 is a reply to message #35275] Sat, 04 February 2012 18:55 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 14255
Registered: November 2005
Ultimate Member
I do not know: I have just spent some time with this, and it seems that at least in current U++ trunk, everything is already fixed. Suggested call is included in "Open" and MySql performs character conversions as needed.

Practically the only thing you have to satisfy is to make sure that your table (and table column, as crazy MySql allows definition of encoding on per-column basis) has the correct encoding. It does not even have to be utf-8, it just have to support characters that you are trying to push there...

Report message to a moderator

Re: Sticky problem : MySql and UTF8 [message #36024 is a reply to message #35239] Thu, 19 April 2012 18:47 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

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

Re: Sticky problem : MySql and UTF8 [message #36036 is a reply to message #35044] Fri, 20 April 2012 10:33 Go to previous message
BioBytes is currently offline  BioBytes
Messages: 308
Registered: October 2008
Location: France
Senior Member
Hi Sergey,

Thank you so much for the additional information about SQL Schema. Very useful. Applying this provision makes UTF8-encoding in MySql better controlled as I have no more problems in my own applications from now (until next time? Razz )

Regards

Biobytes

Report message to a moderator

Previous Topic: Added partial support for ODBC output parameters, but the solution is ugly
Next Topic: MSSQL, default DB, datetime
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ] [ PDF ]

Current Time: Fri Apr 25 12:45:59 CEST 2025

Total time taken to generate the page: 0.00889 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.5.
Copyright ©2001-2013 FUDforum Bulletin Board Software