#!/usr/bin/perl -w
#
# readfile_insertdb.pl tests Perl, MySQL and UTF-8 altoghether
#

use DBI;

%opt = (
    dbname => 'utf8_test',
    dbuser => 'root',
    dbpass => 'xxx',
);

#
# Read UTF-8 strings from file
#
my @input_strings;

open INPUT, '<'.$ARGV[0];
while (<INPUT>) {
    chomp;
    push @input_strings, $_;
}
close INPUT;

#
# Insert UTF-8 strings into database
#
my $dbh = DBI->connect(
    'DBI:mysql:'.$opt{dbname},
    $opt{dbuser},
    $opt{dbpass}
);

$dbh->do('SET NAMES \'UTF8\';');

$id = 1;

foreach my $string (@input_strings) {
    my $query = '
INSERT
  INTO strings
  (id, value)
  VALUES
  ('.$id++.', \''.$string.'\')
';
    my $sth = $dbh->prepare($query);
    $sth->execute();
}

#
# Retrieve UTF-8 strings from database
#
my @output_strings;

my $query = '
SELECT id, value
  FROM strings
';

my $sth = $dbh->prepare($query);
$sth->execute();
while (my $row = $sth->fetchrow_hashref()) {
    push(
        @output_strings,
        {
            id    => $row->{id},
            value => $row->{value},
        }
    );
}

#
# Print UTF-8 strings
#
foreach my $string (@output_strings) {
    print '{', $string->{id}, '} ';
    print $string->{value};
    print "\n";
}

# readfile_insertdb.pl ends here