use DBI;
use Storable qw(retrieve);
$| = 1;
my $j = retrieve("/mnt/c/temp/to-translate.sto");
my $dbh = connectdb('****','****','****','****','****');
foreach my $i (0..@$j-1) {
    my $r;
    $r->{tid} = $j->[$i]->{id};
    $r->{orig} = $j->[$i]->{text};
    #print "$r->{tid}\t$r->{orig}\n";
    insertsql($dbh,'translate',$r);
}
sub connectdb { # connects to mysql or PgPP
    my ($database,$user,$password,$driver,$server) = @_;
    unless ($driver) {
	$driver   = "mysql";
    }
    unless ($server) {
	$server   = "hdshcresearch.asu.edu";
    }
    my $url      = "DBI:$driver:$database:$server";
    unless ($user) {
        $user     = "root";
        $password = "research.HDSHC.mysql";
    }
    my $dbh = DBI->connect( $url, $user, $password ) or die "connectdb can't connect to mysql: $!\n";
    return $dbh;
}
sub insertsql {
    my ($dbh,$table,$data,$ignore) = @_;
    my @qm;
    my @keys;
    my @values;
    my $i = -1;
    foreach my $k (keys %$data) {
        if (defined($data->{$k})) {
            $i++;
            $keys[$i] = $k;
            $values[$i] = $data->{$k};
            $qm[$i] = '?';
        }
    }
    my $keylist = join(",",@keys);
    my $qlist = join(",",@qm);
    my $sqlstatement = "insert into $table ($keylist) values ($qlist)";
    if ($ignore) {
        my $sqlstatement = "insert ignore into $table ($keylist) values ($qlist)";
    }
    
    my $sth = $dbh->prepare($sqlstatement);
    #$sth->execute(@values) || die "putsql could not execute MySQL statement: $sqlstatement $sth->errstr";
    $sth->execute(@values) || die $sth->errstr. " ".join(" ",@values);
    $sth->finish();
    return $dbh->{'mysql_insertid'};
}