Ingredients:

• Perl v5.10.1 (*) built for darwin-2level
• DBI v 1.622
• MySQL Server v 5.1.42
• OS X Terminal App
• GNU bash, version 4.0.35(2)-release (i386-apple-darwin10.8.0)
• BBEdit 9.6.3

MySQL table:

CREATE TABLE `quotes` (
 `text` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
[download]

Insert one row with some smart quote characters in it

INSERT INTO `test`.`quotes` (
`text`
)
VALUES (
'“” ‘’'
);
[download]

Command-line MySQL to check the characters got inserted correctly (having connected with --default-character-set=utf8 flag):

mysql> select * from quotes where 1;
+----------------+
| text           |
+----------------+
| “”  ‘’         |
+----------------+

mysql> select hex(text) from quotes where 1;
+----------------------------------+
| hex(text)                        |
+----------------------------------+
| E2809C E2809D 2020 E28098 E28099 |
+----------------------------------+
## just so we know exactly what bytes are in there, my spacing
[download]

Minimal perl script to get that row:

#!/usr/local/bin/perl
use utf8;
use strict;
use DBI;

my $dbh =
  DBI->connect( 'DBI:mysql:test:localhost', 'root', 'xxxxxxxx' )
  or die("couldn't connect to database");

my $text_display = $dbh->prepare('select * from quotes where 1');

$text_display->execute();

my ($text) = $text_display->fetchrow_array();

print "Text: $text" . $/;
[download]

I have my OS X Terminal set to UTF-8 encoding. I have a UTF-8 aware version of bash (I can create files with those quotes in the name and successfully ls and display them.

But when I run the script above from the terminal, it outputs this:

    Text: ??  ??
[download]

And when I run it via BBEdit's very handy Run command for Unix scripts, I get this:

    Text: ìî  ëí
[download]

Any assistance you can give me will be very gratefully received. Something is obviously going wrong between the database and the output hitting the screen, but everything in the chain is supposed to be UTF-8 aware.

What should I try?

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.