List:MySQL++« Previous MessageNext Message »
From:Charles J. Daniels Date:May 28 2013 7:42pm
Subject:Re: query not yielding a utf8 result
View as plain text  
Hey Warren,

Describe doesn't mention character set at all:

WoIDint(11) unsignedNOPRI auto_incrementWoLgIDint(11) unsignedNOMULWoText
varchar(250)NOWoTextLC varchar(250)NOMULWoStatustinyint(4)NOMULWoTranslation
varchar(500)NOMUL *WoRomanizationvarchar(100)YESWoSentencevarchar(1000)YES
WoCreated timestampNOMULCURRENT_TIMESTAMPWoStatusChangedtimestampNOMUL0000-00-00
00:00:00WoTodayScore doubleNOMUL0WoTomorrowScoredoubleNOMUL0WoRandomdoubleNO

So I assume the table goes by the defaults in place at it's time of
creation. The create statement on record for this table does give a default
of utf8:

DESCRIBE wordswordsdelimiter $$

CREATE TABLE `words` (
  `WoID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `WoLgID` int(11) unsigned NOT NULL,
  `WoText` varchar(250) NOT NULL,
  `WoTextLC` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `WoStatus` tinyint(4) NOT NULL,
  `WoTranslation` varchar(500) NOT NULL DEFAULT '*',
  `WoRomanization` varchar(100) DEFAULT NULL,
  `WoSentence` varchar(1000) DEFAULT NULL,
  `WoStatusChanged` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `WoTodayScore` double NOT NULL DEFAULT '0',
  `WoTomorrowScore` double NOT NULL DEFAULT '0',
  `WoRandom` double NOT NULL DEFAULT '0',
  UNIQUE KEY `WoLgIDTextLC` (`WoLgID`,`WoTextLC`),
  KEY `WoLgID` (`WoLgID`),
  KEY `WoStatus` (`WoStatus`),
  KEY `WoTextLC` (`WoTextLC`),
  KEY `WoTranslation` (`WoTranslation`(333)),
  KEY `WoCreated` (`WoCreated`),
  KEY `WoStatusChanged` (`WoStatusChanged`),
  KEY `WoTodayScore` (`WoTodayScore`),
  KEY `WoTomorrowScore` (`WoTomorrowScore`),
  KEY `WoRandom` (`WoRandom`)

So I am assuming that this table was either created with error, or it's
utf8, unless someone knows of another dominating feature in the final
choice of encoding.

The thing is this, wouldn't mysql++ return a latin-1 encoding to me
properly? I mean, it can handle either underlying format, latin-1 or utf8
right? But I lose data upon retrieving it. If it were properly stored in
latin-1, I wouldn't expect much of a conversion to take place, but
conversion from utf8 to latin-1 is designed with non-compatible data loss
in mind. So it seems to me it's in utf8 and getting converted, resulting in
the loss I see. If it's stored latin-1, then I'm still losing character
values, which seems less likely to me that mysql++ couldn't get me a full
value when stored in latin-1. Do you agree with this assessment?

And I would hope anyone would realize that mysql++ support utf8 by design,
the docs are explicit about this.

What I haven't yet tried, and will soon, are the settings I received

// set result encoding to UTF-8
        mysqlpp::Query q1 = m_pConnect->query();
        q1 << "set character_set_results=utf8";
        mysqlpp::SimpleResult res1 = q1.execute ()

        // set connexion encoding to UTF-8
        mysqlpp::Query q2 = m_pConnect->query();
        q2 << "set character_set_connection=utf8"**;
        mysqlpp::SimpleResult res2 = q2.execute () ;

        // set client encoding to UTF-8
        mysqlpp::Query q3 = m_pConnect->query();
        q3 << "set character_set_client=utf8";
        mysqlpp::SimpleResult res3 = q3.execute () ;

I do not set those settings currently. I don't tell it to do utf8, I worked
on the belief it would function that way by default. But perhaps these
setttings, which i didn't encounter in the docs (be they present or not),
will take care of me. I will test this at some point and let it be known.
If that doesn't work, next on my list will be the samples, though even if
they work it doesn't answer why my case hasn't been, except to support the
idea that regardless of what anything says the encoding is in fact not
utf8. Though, the table does store and retrieve the string
properly, which I'm not sure it even could store that last character if it
insisted upon latin-1 input.

Thank you for your help.

On Tue, May 28, 2013 at 11:36 AM, Warren Young <mysqlpp@stripped> wrote:

> On 5/25/2013 14:48, Charles J. Daniels wrote:
>> I'm using a connection to mysql through to a database I did not
>> create.
> What do you get when you say "DESCRIBE MyTable" in an interactive SQL tool
> such as the mysql(1) command line program?
>  The table files have a db.opt file among them whose only contents
>> say:
>> default-character-set=utf8
>> default-collation=utf8_**general_ci
> I doubt that overrides per-table settings.  Hence my previous question.
> I suspect your table actually holds ISO 8859-x.
>  I do not know what encoding is actually held in the db,
> DESCRIBE will tell you.
> Also: what happens when you try to run the MySQL++ example programs?
> Several of them require UTF-8 support, and demonstrate that it works.
> Have you read the Unicode chapter in the MySQL++ user manual?
>  I've
>> wrapped my own ODBC before, so I guess I'm gonna go that way. mysql++ was
>> looking really nice however.
> You asked your question over a long holiday weekend here in the US.  I
> typically deal with MySQL++ questions at work, so this is the first I've
> seen them.
> Even if you've already given up permanently on MySQL++, I'd at least like
> to get some of the answers to questions above in case someone finds your
> thread in the archives and concludes from that that MySQL++ doesn't support
> UTF-8.
> --
> MySQL++ Mailing List
> For list archives:
> To unsubscribe:   

query not yielding a utf8 resultCharles J. Daniels25 May
  • Re: query not yielding a utf8 resultWarren Young28 May
    • Re: query not yielding a utf8 resultCharles J. Daniels28 May