use of DBI; I am getting multiple error messages mixed in with the correct output.
in [Perl]
|
Prev: FAQ 1.15 Where can I get a list of Larry Wall witticisms?
Next: use of DBI; I am getting multiple error messages mixed in with the correct output.
From: Ted on 23 Apr 2008 16:19 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->{'NAME'}; my $numFields = $sth->{'NUM_OF_FIELDS'}; 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
From: smallpond on 23 Apr 2008 16:40 On Apr 23, 4:19 pm, Ted <r.ted.by...(a)rogers.com> wrote: > 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->{'NAME'}; > my $numFields = $sth->{'NUM_OF_FIELDS'}; > 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
From: Ted on 23 Apr 2008 17:29 On Apr 23, 4:40 pm, smallpond <smallp...(a)juno.com> wrote: > On Apr 23, 4:19 pm, Ted <r.ted.by...(a)rogers.com> wrote: > > > > > > > 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->{'NAME'}; > > my $numFields = $sth->{'NUM_OF_FIELDS'}; > > 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- Hide quoted text - > > - Show quoted text - As I mentioned, I constructed this test by copying code from the documentation. I was going to worry about extra detail, like comprehensive error checking, once I got the core issues solved. But, if I understand your final statement correctly, am I to belief it is choking on the fact that some of the fields in some of the rows have null values? I knew that the table I am querying has a lot of nulls for well over half the columns and more than half the rows. If this is corect, is there a way to tell it to replace the null value found in the row by the string "null"? For example, in my JSF app (different project) nulls get displayed as an empty string for most types, and as the string "N/A" for instances of some of my classes written to handle nulls well (and it all works seemlessly using JDBC). But there is no a priori way to tell which columns in which rows will be null. I have found it easy t work around nulls in SQL and JDBC and C++, so what it the Perl counterpart to the routine idioms I use in these other languages? Thanks Ted
From: Ted on 23 Apr 2008 17:40 On Apr 23, 5:23 pm, Ted Zlatanov <t...(a)lifelogs.com> wrote: > On Wed, 23 Apr 2008 13:40:58 -0700 (PDT) smallpond <smallp...(a)juno.com> wrote: > > 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 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
From: xhoster on 23 Apr 2008 18:18
Ted <r.ted.byers(a)rogers.com> wrote: > On Apr 23, 5:23=A0pm, Ted Zlatanov <t...(a)lifelogs.com> wrote: > > > > 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. |