Hello Monks! I have been having problems reading data from an xml file and inserting it into mysql. As a test I created a file with one word in it:
Sustitución
And wrote a quick script to to read it from the file and insert it into the database:

 Perl 5.8.8  (test.pl)
use strict;
use warnings;
use DBI;
use Encode;
use utf8;

my $filename = 'test.xml';
my $data;
open (my $fh, '<:encoding(UTF-8)', $filename) or die "Could not open f
+ile '$filename' $!";
while (my $row = <$fh>){
    chomp $row;
    print "$row " . ( utf8::is_utf8 ( $row ) + 0) . "\n"; # prints: Su
+stitución1
    $data = $row;
    print "$data " . ( utf8::is_utf8 ( $data ) + 0) . "\n"; # prints: 
+Sustitución1
}

######
my $sql = "INSERT INTO testTable (a,b,c,es) VALUES (?,?,?,?)";

# USE EITHER THIS LINE OF CODE OR ...
#my @array = qw(3 2 1 Sustitución);

# THESE 2 LINES OF CODE
my @array = (3, 2, 1);
push @array, $data;

print $array[3] . ( utf8::is_utf8 ( $array[3] ) + 0) . "\n"; # prints:
+ Sustitución1

my $connect = "DBI:mysql:$dbname";
my $dbh = DBI->connect($connect, $dbuser, $dbpass, {mysql_enable_utf8 
+=> 1, 'RaiseError'=>1, 'AutoCommit'=>0, 'PrintError'=>1}) or die "Err
+or: $DBI::errstr\n";
my %query = ( sql => $sql, dbh => $dbh, data => \@array );

###

# call function that tests execute(@array)
runQuery(\%query,1);

sub runQuery{
    my %args = %{$_[0]};
    my $commit = $_[1];
    my $sth = $args{dbh}->prepare($args{sql});
    
    # execute the query
    eval{
        $sth->execute(@{$args{data}});
    } or do{
        my $error = DBI->errstr;
        $args{dbh}->rollback();
    };
    # commit when we are told
    if($commit){
        # test the commit - if all went well, this day was inserted su
+ccessfully!
        eval{
            my $rc = $args{dbh}->commit(); # commit to local database,
+ $dbh2
            print "$rc = successful commit!\n";
        } or do{
            my $error = DBI->errstr;
            $args{dbh}->rollback(); # rollback local database, $dbh2
            die "Rollback - There has been a problem! $error\n";
        };
    }
} # end sub runQuery
[download]

The problem I am encountering is if I insert the data from the file the database contains (as viewed in both Mysql Workbench 6.3 and phpmyadmin):
Sustitución

If I switch the commented lines of code to the line with the word Sustitución hard coded into the array, the database contains (viewed the same as above):
Sustitución

What is the difference in embedding the word Sustitución directly into the array vs reading it in from a file and then appending it to the end of the array? In both cases they print properly to the terminal. The mysql portion of the code appears to be functioning properly but maybe there is an issue there as well. I've also found if I change the encoding of the perl file (test.pl) from the default to utf8 then I always get Sustitución in the db, switching it back to the default I get Sustitución (when using just the hard coded array).

I think I'm in a condition of information overload and need guidance from a friendly Monk!!

Thank you in advance!

---

---

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.