Text::CSV not able to execute insert query dynamically.

[Ankur_kuls]

Hi, I have the below script which reads one csv file in a directory, connect to the SQLite database, and insert the data of csv files into the database tables..

#! /usr/bin/perl

use warnings;
use strict;
use DBI;
use Text::CSV;
use Data::Dumper;

our $FH;
open ($FH, "abc.csv") or die "can't open the fileo $!";

my $csv = Text::CSV->new({
                sep_char => ";",
                binary => 1,
                auto_diag => 1 });


my $dbh;

$dbh = DBI->connect('DBI:SQLite:vodafone.db') || die "$DBI::errstr\n";

my $columns = $csv->getline ($FH); # skip header
my $count = $#{$columns};

my @array5;
for (0..$count) {
$array5[$_] = "'\$row->[$_]'";
}
my $query = join (", ", @array5);
print $query."\n";


while (my $row = $csv->getline($FH) ) {
#$dbh->do("insert into VoucherMRPDataTable_Frt_TariffModel values ('$r
+ow->[0]', '$row->[1]', '$row->[2]', '$row->[3]');");
$dbh->do("insert into VoucherMRPDataTable_Frt_TariffModel values ($que
+ry);");
}
close($FH);
$dbh->disconnect;

why i am using $query because I don't know how many columns this CSV file contains... Now when I execute the first insert query (commented out) I am getting the desired result ie field values of csv file. but when I am using the second insert query (which is my requirement) it is storing $row->[0], $row->1 etc values in database...please help...

[kennethk]

What is the error message you are getting? I think you'll get a lot of clarity if you swap your connector to:

$dbh = DBI->connect('DBI:SQLite:vodafone.db',
                            {
                                PrintError  => 0,
                                RaiseError  => 1,
                                AutoCommit  => 0,
                            },
                    );

since it will report what is going wrong. I expect it's because a database table has a defined number of columns in it, and you don't have a defined number of columns in your dataset.

As a side note, you'll likely get more traction in DB interaction if you prepare a query using placeholders. At the least, what you've written is subject to all sorts of escaping issues.

Update: Your issue is that you have $array5[$_] = "'\$row->[$_]'"; instead of a $array5[$_] = "'$row->[$_]'"; after you know your values, and are thus aren't interpolating your values. Again, placeholders would have prevented this issue.

my $sql = do {
    my $count = @$columns;
    my $qs = join ',', ('?') x $count;
    <<EOSQL
INSERT INTO VoucherMRPDataTable_Frt_TariffModel
VALUES ($qs)
EOSQL
};

my $query = $dbh->prepare($sql);
while (my $row = $csv->getline($FH) ) {
    $query->execute(@$row);
}

[Ankur_kuls]

hi...its not giving any error.. when I run the 1st insert query its storing the field values of csv file (like 34, 67, 29) but when I run the second insert statement instead...it giving me values like ($row->[0], $row->1 etc). also If I remove \ before $ its failing because it doesn't have any value of $row variable at that time...thanks

[kennethk]

Did you try the suggested code for your insert? Sorry for any lack of clarity in the explanation.

[Anonymous Monk]

As a note; regarding "escaping issues", see Exploits

[Tux]

<shameless_plug>What about using a recent version of Text::CSV_XS and make this readable and maintainable?</shameless_plug>

#!/pro/bin/perl

use 5.16.2;
use warnings;

use DBI;
use Text::CSV_XS qw( csv );

my $n_columns = 4;

my $dbh = DBI->connect ("dbi:SQLite:vodafone.db", undef, undef, {
    RaiseError         => 1,
    PrintError         => 1,
    AutoCommit         => 1,
    ChopBlanks         => 1,
    ShowErrorStatement => 1,
    FetchHashKeyName   => "NAME_lc",
    }) or die "$DBI::errstr\n";
my $sth = $dbh->prepare (qq;
    insert into VoucherMRPDataTable_Frt_TariffModel
    values (@{[join ", " => ("?") x $n_columns]});
    );

csv (in       => "abc.csv",
     sep_char => ";",
     headers  => "skip",
     fragment => "col=1-$n_columns",
     on_in    => sub { $sth->execute (@{$_[1]}); },
     );

$dbh->commit;
$dbh->disconnect;

[GotToBTru]

When you run this, what does the print $query output look like?

[Ankur_kuls]

hi...its giving the expected value of $query

'$row->[0]', '$row->[1]', '$row->[2]', '$row->[3]'

[kennethk]

But if you don't eval the string, Perl won't know to interpolate the values; it just inserts the literal $row->[0]. See my post above for a better solution.