Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

DBI inserting accents

by packetstormer (Monk)
on Mar 21, 2012 at 15:31 UTC ( #960790=perlquestion: print w/replies, xml ) Need Help??
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'");
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.

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)?

      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 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.
        try adding undef
        $dbh->do('INSERT INTO test_db.test VALUES(?)', undef, $utf8_string);
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"

    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.

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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://960790]
Approved by marto
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (8)
As of 2017-02-26 16:05 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (376 votes). Check out past polls.