DBI inserting accents

packetstormer has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks

I am having difficulty inserting characters into MySQL with accents e.g "Séan"
I have both the MySQL server/client set to use UTF8 and also the DBI connection has the following:

    my $dbh= DBI->connect("DBI:$db_driver:dbname=$db_name;host=$db_hos
+t;port=$db_port",
       $db_user, $db_passwd,{RaiseError=>1}); #or die $DBI::errstr;
       $dbh->{'mysql_enable_utf8'}=1;
       $dbh->do("set NAMES 'utf8'");
[download]

I have also tried decoding the string before the insert like: $case_header = Encode::decode_utf8($case_header); But that inserts a very strange character in the DB.
Does anyone have any suggestions?
BTW, if I connect to MySQL CLI and run a manual insert the string "Séan" inserts and displays fine.

Comment on DBI inserting accents Select or Download Code

Replies are listed 'Best First'.
Re: DBI inserting accents by moritz (Cardinal) on Mar 21, 2012 at 15:46 UTC
Maybe A UTF8 round trip with MySQL can be of inspiration? Things to check: did you `use utf8;`? How are you retrieving the data back from the DB? did you remember to `bindmode STDOUT, ':utf8':` before printing the returned value? Is the table set up to store text as UTF-8? What character encoding does your terminal use (in which you run the mysql CLI)? Perl 6 - second systems done right	[reply] [d/l] [select]
Re^2: DBI inserting accents by packetstormer (Monk) on Mar 21, 2012 at 16:02 UTC
I have looked at that post but it is not making sense to me to be honest. For example, is says from with a Perl script just "use utf8" then `$dbh->do('INSERT INTO test_db.test VALUES(?)', $utf8_string);` However, if I do this I get this error: DBI::db=HASH(0x9eecc70)->do(...): attribute parameter 'Séan' is not a hash ref at insert.pl line 15 Also, I don't have any problem displaying the text, only inserting. I have tried "binmode...." also but I can't get anything to work.	[reply] [d/l]
Re^3: DBI inserting accents by poj (Abbot) on Mar 21, 2012 at 17:08 UTC
try adding undef `$dbh->do('INSERT INTO test_db.test VALUES(?)', undef, $utf8_string);` [download] poj	[reply] [d/l]
Re: DBI inserting accents by moritz (Cardinal) on Mar 21, 2012 at 18:56 UTC
OK, let's try to do this step by step. First, try this script: `use utf8; binmode STDOUT, ':encoding(UTF-8)'; print "Séan\n"` [download] Does this work fine? If yes, as the next step, use the first two lines as the preamble for all of the following scripts. Try to read the string "Séan" from the database base after you have inserted it via the mysql command line client. Does that work? Then insert that string (as a literal, ie stored directly in the text file) into the database, and read it from the DB via the mysql command line. Does that work? If any of those does not work, please copy&paste the output here, even if it contains "weird" characters. If all of these work, you should also be able to round-trip the characters via perl+DBI. Perl 6 - second systems done right	[reply] [d/l]
Re: DBI inserting accents by Ralesk (Pilgrim) on Mar 22, 2012 at 10:51 UTC
Let’s not forget that the mysql CLI is outright retarded on most distros, and will default to non-UTF-8 even on UTF terminals, so it’s well possible that the Séan you insert with it is wrong and it just displays proper UTF-8 stuff broken.	[reply]