|
Posted by Ted on April 23, 2008, 4:19 pm
Please log in for more thread options
The program could hardly be simpler. Here it is:
use DBI;
use IO::File;
my $db='yohan';
my $hostname = 'localhost';
my $port = '3306';
my $user = 'rejbyers';
my $dbpwd = 'jesakos';
$dbh = DBI->connect("DBI:mysql:database=$db;host=$hostname",
$user, $dbpwd, {RaiseError => 1});
STDOUT->print("just before preparing statement\n");
my $table = 'etf';
$sth = $dbh->prepare("SELECT * FROM $table");
if (!$sth) {
die "Error:" . $dbh->errstr . "\n";
}
STDOUT->print("Just before executing the statement\n");
if (!$sth->execute) {
die "Error:" . $sth->errstr . "\n";
}
STDOUT->print("Just before getting a row to display\n");
my $row = $sth->fetchrow_arrayref();
STDOUT->print($row);STDOUT->print("\n");
my $names = $sth->;
my $numFields = $sth->;
for (my $i = 0; $i < $numFields; $i++) {
printf("%s%s", $i ? "," : "", $$names[$i]);
}
print "\n";
while (my $ref = $sth->fetchrow_arrayref) {
for (my $i = 0; $i < $numFields; $i++) {
printf("%s%s", $i ? "," : "", $$ref[$i]);
}
print "\n";
}
Running it from within Emacs, all seems fine until I examine the
output. All the calls to "STDOUT->print" behave correctly, and all
the correct data is returned. I KNOW that database well, and the
contents of the table I used for this test. ALL of the data in that
table is returned. However, The correct output is punctuated about
every 100 lines by hundreds of error messages: "Use of uninitialized
value in printf at k:/MerchantData/MSDB.pl line 34." That is the
printf in the for loop within the while loop. This increases the
number of lines of output from precisely 529 to more than 1800! That
is almost three times as much garbage as there is real data.
It is satisfying that I can get a little scriptlet to connect to the
DB and get data so quickly, but it is frustrating that this extra
garbage is present within the correct data and I see nothing in the
documentation from which I copied the last dozen lines or so of code
that says anything about this error message or what can be done about
it.
Where I want to go is to be able to retrieve data from the database,
to use to structure requests made across the internet (to a data
provider to which we have bought access), and then, when the data is
received (as a large XML file) feed it into a pair of tables in the
database (the tables will already exist in the DB, I am just unsure of
how to use the DBI to do a bulk insert, or even if it can - haven't
found that part of the documentation yet).
Any help is appreciated.
Thanks
Ted
|
|
Posted by smallpond on April 23, 2008, 4:40 pm
Please log in for more thread options
> The program could hardly be simpler. Here it is:
>
> use DBI;
> use IO::File;
>
> my $db='yohan';
> my $hostname = 'localhost';
> my $port = '3306';
> my $user = 'rejbyers';
> my $dbpwd = 'jesakos';
>
> $dbh = DBI->connect("DBI:mysql:database=$db;host=$hostname",
> $user, $dbpwd, {RaiseError => 1});
>
> STDOUT->print("just before preparing statement\n");
> my $table = 'etf';
> $sth = $dbh->prepare("SELECT * FROM $table");
> if (!$sth) {
> die "Error:" . $dbh->errstr . "\n";}
>
> STDOUT->print("Just before executing the statement\n");
> if (!$sth->execute) {
> die "Error:" . $sth->errstr . "\n";}
>
> STDOUT->print("Just before getting a row to display\n");
> my $row = $sth->fetchrow_arrayref();
> STDOUT->print($row);STDOUT->print("\n");
> my $names = $sth->;
> my $numFields = $sth->;
> for (my $i = 0; $i < $numFields; $i++) {
> printf("%s%s", $i ? "," : "", $$names[$i]);}
>
> print "\n";
> while (my $ref = $sth->fetchrow_arrayref) {
> for (my $i = 0; $i < $numFields; $i++) {
> printf("%s%s", $i ? "," : "", $$ref[$i]);
> }
> print "\n";
>
> }
>
<snip unrelated stuff>
Why aren't you testing for errors on connect?
Each call to fetchrow_arrayref gets a new row, but you
only check numFields once. So your rows don't all have
the same number of fields.
--S
|
|
Posted by Ted Zlatanov on April 23, 2008, 5:23 pm
Please log in for more thread options
s> Each call to fetchrow_arrayref gets a new row, but you
s> only check numFields once. So your rows don't all have
s> the same number of fields.
That SELECT SQL query will not return a variable number of fields.
The problem is that there are NULL values, which are undefined in the
DBI translation. So this
printf("%s%s", $i ? "," : "", $$ref[$i]);
should be (for example)
printf("%s%s", $i ? "," : "", $$ref[$i]||'NULL');
Of course, the code could be greatly improved with a join() call and in
many other places, but I don't feel like rewriting the whole thing.
Ted
|
|
Posted by Ted on April 23, 2008, 5:40 pm
Please log in for more thread options ote:
>
> s> Each call to fetchrow_arrayref gets a new row, but you
> s> only check numFields once. =A0So your rows don't all have
> s> the same number of fields.
>
> That SELECT SQL query will not return a variable number of fields.
>
> The problem is that there are NULL values, which are undefined in the
> DBI translation. =A0So this
>
> printf("%s%s", $i ? "," : "", $$ref[$i]);
>
> should be (for example)
>
> printf("%s%s", $i ? "," : "", $$ref[$i]||'NULL');
>
> Of course, the code could be greatly improved with a join() call and in
> many other places, but I don't feel like rewriting the whole thing.
>
> Ted
Thanks Ted,
Great name BTW! ;-)
This would be a useful tidbit to add to the documentation. I hadn't
expected a mature library like DBI to behave like this. I do not, for
example, have to go through any such extra hoops when using JDBC with
JSF. If a particular record set contains nulls in one or more columns
in one or more records, JSF automagically displays it as an empty
string; something I have modified on occassion to display the string
"N/A".
You needn't worry about rewriting the whole thing as I'll be doing
that anyway as I learn to translate more of what I know from C++,Java
and SQL into Perl. But perhaps you could give me a break and make
some suggestions as to what improvements you'd recommend and where to
look for details in the documentation. And one thing I haven't found
yet, which I am sure must be there somewhere, is how to set up a bulk
insert. Can you point me in the right direction to see how to do that
using DBI?
Thanks again
Ted
|
|
Posted by xhoster on April 23, 2008, 6:18 pm
Please log in for more thread options > >
> > The problem is that there are NULL values, which are undefined in the
> > DBI translation. =A0So this
> >
> > printf("%s%s", $i ? "," : "", $$ref[$i]);
> >
> > should be (for example)
> >
> > printf("%s%s", $i ? "," : "", $$ref[$i]||'NULL');
> >
>
> This would be a useful tidbit to add to the documentation. I hadn't
> expected a mature library like DBI to behave like this.
This is not DBI behavior. It is Perl behavior when undefined valued
are encountered. It is documented, just not in the DBI docs. It is not
reasonable to document every non-DBI problem that one may encounter when
using DBI in the DBI docs.
> I do not, for
> example, have to go through any such extra hoops when using JDBC with
> JSF. If a particular record set contains nulls in one or more columns
> in one or more records, JSF automagically displays it as an empty
> string;
If you want a language/framework that does just one thing well, then
keep using it rather than switching to a general purpose language. A
framework that is tightly designed to display stuff without any processing
might reasonably default to silently converting nulls to empty strings.
A general purpose language cannot reasonable make that decision silently.
Of course, you could alway turn off uninitialized value warnings if you
prefer to silence (potential) problems rather than fix them.
> something I have modified on occassion to display the string
> "N/A".
In mysql, a null string and an empty string are two different things.
Should both of them be converted to 'N/A', or just the null?
> And one thing I haven't found
> yet, which I am sure must be there somewhere, is how to set up a bulk
> insert. Can you point me in the right direction to see how to do that
> using DBI?
execute_array is supposed to do that. It does not work in all DBDs, and
does not work well in some of the others. Generally for maximum efficiency
of bulk loading, you need to bypass DBI and go to the bulk loading tool
that comes with (and is specific for) each database.
Xho
--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.
|
| Similar Threads | Posted | | Multiple Inheritance: mixed base class refs (hash, array) | April 29, 2005, 2:29 am |
| FAQ 9.3: How can I get better error messages from a CGI program? | December 5, 2004, 6:03 pm |
| FAQ 9.3: How can I get better error messages from a CGI program? | December 29, 2004, 12:03 pm |
| FAQ 9.3 How can I get better error messages from a CGI program? | March 25, 2005, 12:03 pm |
| FAQ 9.3 How can I get better error messages from a CGI program? | June 4, 2005, 5:03 am |
| FAQ 9.3 How can I get better error messages from a CGI program? | September 23, 2005, 4:03 pm |
| FAQ 9.3 How can I get better error messages from a CGI program? | November 29, 2005, 5:03 am |
| FAQ 9.3 How can I get better error messages from a CGI program? | September 17, 2006, 9:03 pm |
| FAQ 9.3 How can I get better error messages from a CGI program? | March 21, 2007, 3:03 am |
| FAQ 9.3 How can I get better error messages from a CGI program? | November 11, 2007, 9:03 pm |
|
XML Sitemap