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: xhoster on 23 Apr 2008 18:37 Ted <r.ted.byers(a)rogers.com> wrote: > > 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. So? If there were only 0.0894 times as much "garbage", would that somehow be better? It would probably be even worse, as then it might slip by without even noticing that there is a problem. > > 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 You failed to separate the STDOUT and STDERR streams. That is why the warning messages and the real data are all mixed together. The exact nature of the problem was described elsewhere. It is documented under "perldiag". > 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). You can write the data into text files, and then use DBI->do to invoke mysql's specific bulk loading method: $dbh->do("load data local infile 'datafile.txt' into table foobar"); You will need to consult the mysql documentation for the "load data local infile" command. DBI/DBD just provide a method of executing the command, everything else is up to mysql. 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.
From: Dr.Ruud on 23 Apr 2008 20:04 Ted schreef: > while (my $ref = $sth->fetchrow_arrayref) { > for (my $i = 0; $i < $numFields; $i++) { > printf("%s%s", $i ? "," : "", $$ref[$i]); > } > print "\n"; > } while (my $ref = $sth->fetchrow_arrayref) { print join(",", map defined() ? $_ : 'NULL', @$ref), "\n"; } -- Affijn, Ruud "Gewoon is een tijger."
From: John W. Krahn on 23 Apr 2008 20:21 Ted Zlatanov wrote: > On Wed, 23 Apr 2008 13:40:58 -0700 (PDT) smallpond <smallpond(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 you don't really need printf() for that: print $i ? ',' : '', $ref->[ $i ] || 'NULL'; John -- Perl isn't a toolbox, but a small machine shop where you can special-order certain sorts of tools at low cost and in short order. -- Larry Wall
From: Ted on 23 Apr 2008 21:05 On Apr 23, 6:18 pm, xhos...(a)gmail.com wrote: > Ted <r.ted.by...(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. > But null values in SQL are not the same thing as undefined or uninitialized values in any other language I know. In any RDBMS I have worked with, null is a legitimate value that can be given to a specific column in a specific row, and it has a very specific meaning in a database. Any SQL programmer knows how to handle nulls, what can and can not be done with them, &c. That is very different from programming errors where a variable has been defined and then used before it has been given a value. So I maintain this IS a DBI issue, and there ought to be functionality provided within DBI to handle the processing of recordsets, including printing them, that can handle null values in a rational manner without generating spurious warnings. At a minimum the DBI documentation ought to say somethng about users needing to implement their own processing to handle null values when that is not available. The real error here is that, if I am to believe Ted, that nulls are treated as undefined in the DBI translation, and this is plain wrong. On the daatabase side, nulls have a specific meaning (and as you well said, further on, that meaning is different from that of an empty string), and DBI ought to at least provide a means of handling them correctly from the perspective of a database programmer. The fact is that if I wanted to represent, in a C++ or Java program, the idea represented by null in SQL, I could write a class that represents that idea and give me the kinds of behaviours I'd require of it; something far richer than anything possible with the null keywords in either C++ or java. Therefore, I could do precisely the same thing in Perl, if need be. I repeat yet again, it is wrong to think of this as a perl issue, rather than an issue of a deficiency in the DBI library. > > 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. > The mistake here is to assume this is a perl issue. Perl, like Java and C++ and FORTRAN and a host of other programming languages, is Turing complete, so anything that can be done in any one of them can in principle be done in any of the others. Surely you know that JDBC is to Java as DBI is to Perl. What we are talking about here is specificlly a database interface issue, and so the relevant aspect of perl programming that applies here is the use of DBI. I know how to deal with nulls when using JDBC. And I know how to deal with within web applications relying on specific Java classes called data providers (basically the same idea as DBI, but focussed on passing data to a web interface and receiving data from the same interface. The conversion from nulls to empty strings is handled by the data provider class. This is no more a language issue in Java than it is a perl issue. It is an issue that lies squarely in the realm of the library that is designed, in this instance, to handle interactions with a database. The behaviour of the java data provider classes is well documented, and that is why, when the need arises, I can provide different default behaviour. > > 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? > No, and I must emphasize that in the occassions where I changed the behaviour, the change was mandated by the meaning of the data and the way it was to be used. In the case in question, the only possible values are real numbers and null. In some instances in this application, the proper way to display the null is to use an empty string. In others, the proper way to display it is to use the string "N/A". But I could make these changes easily because both the JDBC and JSF are well documented, and by examining that documentation to see how the relevant classes/libraries handled nulls in particular and data in general, it became clear almost immediately what code I had to write to obtain the behaviour I required. > > 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. > OK. That is trivially easy. I can retrieve the data from the feed, store it in a CSV file, and use a SQL script passed to mysql to load the data and process it within the DB itself. Thanks Ted
From: Ted on 23 Apr 2008 21:24
On Apr 23, 6:31 pm, Joost Diepenmaat <jo...(a)zeekat.nl> wrote: > Ted <r.ted.by...(a)rogers.com> writes: > > [ on the conversion of NULL fields to <undefined> ] > > > This would be a useful tidbit to add to the documentation. I hadn't > > expected a mature library like DBI to behave like this. > > Well, it is the most DWIM way of representing NULL values in perl. > As I explained to Xho, it is wrong. My understanding of undefined in Perl was to relate it to uninitialized values in Java or C++. You declare a variable, but then attempt to use it before you define it (i.e. give it a value). The idea of NULL in SQL is very different. It IS a valid value that can be given to variables or to fields in specific rows, where the table definition allows it. NULLs in SQL carry a very specific meaning, and require very specific behaviours when handling them. To translate this into a language like Java or C+ +, I would write a class that provides for the same meaning and behaviours that would be familiar to a SQL programmer. Had I known that the DBI was broken in terms of translating database nulls into <undefined>, I'd have considered developeing an extension to DBI; one that included a NULL class that would serve the needs of database programmers. > > 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". > > Perl does the same thing, but it will issue a warning if you have > warnings enabled (warnings are output to STDERR, so if you're capturing > STDOUT only it shouldn't be a problem - I haven't read all of the > thread). *If* you don't want to deal with that, either don't "use > warnings" and don't use the -w flag, or do use them in general but > disable warnings in the offending code block. > I routinely treat warnings as errors, and pass my C++ code through different compilers, since each vendor has different strengths and weaknesses. This helps me identift potential problems and make my code more robust. I disable warnings ONLY if I can prove that it is wrong or I learn that it is produced by a documented bug in the compiler. So, it appears that the little scriptlet I cobbled together is behaving correctly apart from generating spurious warnings. Knowing about this behaviour, I can easily avoid triggering it. The mixing of stdout and stderr is, in this case, an emacs issue. I do not know how to get Emacs to split the window it is displaying into three (one for the program, one for stdoutt and a third for stderr) when I tell it to run the script I'm working on. :-( Thanks Ted |