Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:
I am using DBD::CSV and fetchrow_hashref and not getting results. In my example below, the variables $Cposition and $Cyear have already had values assigned.
Here is my database handler:
my $dbh = DBI->connect(qq{DBI:CSV:csv_eol="\n";csv_sep_char=\\~});
$dbh->{'csv_tables'}->{'JPDObj'} = {'file' => 'data/objectives-cqdc.tx
+t','col_names' => ["Obj","PosNbr","Year","Div","AVP","Dept","SDescr",
+"Major","Methods","Results","Accompl","JanUp","LDescr","Changes","Cha
+rs","Goals","Budget","CFObj"]};
$dbh->{'RaiseError'} = 1;
$@ = '';
my $query = "SELECT Obj,SDescr,Major,Year,LDescr FROM JPDObj WHERE Pos
+Nbr = '$Cposition' AND Year = '$Cyear'";
my $sth = $dbh->prepare($query);
$sth->execute();
my $found_results = 0;
my $count = 0;
my $Obj;
my $PosNbr;
my $Year;
my $Div;
my $AVP;
my $Dept;
my $SDescr;
my $Major;
my $Methods;
my $Results;
my $Accompl;
my $JanUpd;
my $LDescr;
my $CFObj;
while (my $row = $sth->fetchrow_hashref)
{
$found_results = 1;
$count=$count+1;
#SETUP VARIABLES
$Obj=$row->{'Obj'};
$Year = $row->{'Year'};
$SDescr = $row->{'SDescr'};
$Major = $row->{'Major'};
$LDescr = $row->{'LDescr'};
my $Nyear = $nextYear + 1;
$CFObj=$row->{'CFObj'};
if ($found_results) {
# some output code here
}
}
It yields no results despite this record being in the file:
20140000020~00001576~2014~"testdata"~"testdata"~"testdata"~-~0~-~"Inse
+rted on December 10, 2013"~-~-~-~-~20000000001~20130000001~1~-
And it should find results based on the second and third fields. Permissions on all files are correct and nothing comes across in my error log.
-Jacvivs
Re: DBD::CSV and fetchrow_hashref not working
by rnewsham (Curate) on Dec 11, 2013 at 15:43 UTC
|
I am not sure about your connect method, I think that might be the source of your problem. It could be something to do with the escaping of your separation character when providing the arguments in that format.
Here is a modified version of your code which seems to do what you want.
use strict;
use warnings;
use DBI;
use Data::Dumper;
my $dbh = DBI->connect("DBI:CSV:", undef, undef, { csv_eol=> "\r\n", c
+sv_sep_char => '~'});
$dbh->{'csv_tables'}->{'JPDObj'} = {'file' => 'data/objectives-cqdc.tx
+t','col_names' => ["Obj","PosNbr","Year","Div","AVP","Dept","SDescr",
+"Major","Methods","Results","Accompl","JanUp","LDescr","Changes","Cha
+rs","Goals","Budget","CFObj"]};
$dbh->{'RaiseError'} = 1;
$@ = '';
my $Cposition = '00001576';
my $Cyear = 2014;
my $results = $dbh->selectall_arrayref( "SELECT Obj,SDescr,Major,Year,
+LDescr FROM JPDObj WHERE PosNbr = ? AND Year = ?", { Slice => {} }, $
+Cposition, $Cyear);
for ( @$results )
{
print Dumper( $_ ) . "\n";
}
| [reply] [d/l] |
Re: DBD::CSV and fetchrow_hashref not working
by tangent (Parson) on Dec 11, 2013 at 16:33 UTC
|
Well I ran your code on the sample input and it works, provided $Cposition is '00001576' and $Cyear is '2014'
while (my $row = $sth->fetchrow_hashref) {
for my $k (keys %$row) {
print qq|$k: $row->{$k}\n|;
}
}
# Output
# Year: 2014
# Major: 0
# SDescr: -
# LDescr: -
# Obj: 20140000020
At first I thought it was the csv_sep_char=\\~ that was the problem and that should be csv_sep_char=\~ but it works with either for some strange reason.
It could simply be that you don't actually do anything to inform you of the results - did you remove the code from the line where it says: # some output code here?
| [reply] [d/l] [select] |
|
Thank you for your reply. I still cannot get it to work despite trying the code. Perhaps it is a server setting. It's like it doesn't fetch at all.
And, yes, in the full code, I am actually doing something to output the results.
-Jacvivs
| [reply] |
|
Maybe you should test the connection and other workings first, something like:
my $dbh = DBI->connect("dbi:CSV:csv_eol=\n;csv_sep_char=\~") or die $D
+BI::errstr;
$dbh->{'RaiseError'} = 1;
$@ = '';
eval {
$dbh->{'csv_tables'}->{'JPDObj'} = {'file' => 'data/objectives-cqd
+c.txt','col_names' => ["Obj","PosNbr","Year","Div","AVP","Dept","SDes
+cr","Major","Methods","Results","Accompl","JanUp","LDescr","Changes",
+"Chars","Goals","Budget","CFObj"]};
my $query = "SELECT * FROM JPDObj";
my $sth = $dbh->prepare($query);
$sth->execute();
while (my $row = $sth->fetchrow_hashref) {
for my $k (keys %$row) {
print qq|$k: $row->{$k}\n|;
}
}
};
$@ and die "SQL database error: $@";
| [reply] [d/l] |
|
It ended up being bad data in some of the other records that were manually put in the file. I found that I actually had to remove the quotes from the eol specification in the dbh line and it worked.
Here's my code for the dbh line:
my $dbh = DBI->connect(qq{DBI:CSV:csv_eol=\n;csv_sep_char=\\~});
$dbh->{'csv_tables'}->{'JPDObj'} = {'file' => 'data/objectives-cqdc.tx
+t','col_names' => ["Obj","PosNbr","Year","Div","AVP","Dept","SDescr",
+"Major","Methods","Results","Accompl","JanUp","LDescr","Changes","Cha
+rs","Goals","Budget","CFObj"]};
-Jacvivus | [reply] [d/l] |
Re: DBD::CSV and fetchrow_hashref not working
by Tux (Canon) on Dec 12, 2013 at 08:08 UTC
|
The first question would be: "Does the CSV have a header line, or can it be made to have a header line?". If so, your initialisation could be much simpler. And the ~ does not have to be escaped at all.
$ cat data/objectives-cqdc.txt
Obj~PosNbr~Year~Div~AVP~Dept~SDescr~Major~Methods~Results~Accompl~JanU
+p~LDescr~Changes~Chars~Goals~Budget~CFObj
20140000020~00001576~2014~"testdata"~"testdata"~"testdata"~-~0~-~"Inse
+rted on December 10, 2013"~-~-~-~-~20000000001~20130000001~1~-
$ cat test.pl
use 5.16.2;
use warnings;
use DBI;
use Data::Peek;
my ($Cposition, $Cyear) = ("00001576", 2014);
my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
f_dir => "data",
f_ext => ".txt/r",
csv_sep_char => "~",
});
my $sth = $dbh->prepare (qq{select * from "objectives-cqdc" where PosN
+br = ? and year = ?});
$sth->execute ($Cposition, $Cyear);
while (my $row = $sth->fetchrow_hashref) {
DDumper $row;
}
$ perl test.pl
{ accompl => '-',
avp => 'testdata',
budget => 1,
cfobj => '- ',
changes => '-',
chars => '20000000001',
dept => 'testdata',
div => 'testdata',
goals => '20130000001',
janup => '-',
ldescr => '-',
major => 0,
methods => '-',
obj => '20140000020',
posnbr => '00001576',
results => 'Inserted on December 10, 2013',
sdescr => '-',
year => 2014
}
$
Also note that file has been deprecated in favor of f_file to be more consistent with DBD::File specifications. An aditional disadvantage of using specific csv_tables initialisation is that the table name in not case insensitive anymore and that the initialisation takes MUCH longer, as DBD::CSV will scan the default f_dir (defaults to ".") for usable table names possible taking a lot of IO you do not want. Your new code when not having a header could look like this:
use DBI;
use Data::Peek;
my ($Cposition, $Cyear) = ("00001576", 2014);
my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
csv_sep_char => "~",
csv_tables => {
JPDObj => {
f_file => "data/objectives-cqdc.txt",
col_names => [qw(
Obj PosNbr Year Div AVP Dept SDescr Major Methods Resu
+lts
Accompl JanUp LDescr Changes Chars Goals Budget CFObj
+)],
},
}
});
my $sth = $dbh->prepare (qq{select * from JPDObj where PosNbr = ? and
+year = ?});
$sth->execute ($Cposition, $Cyear);
while (my $row = $sth->fetchrow_hashref) {
DDumper $row;
}
Enjoy, Have FUN! H.Merijn
| [reply] [d/l] [select] |
|
|