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 21:39 Ted <r.ted.byers(a)rogers.com> wrote: > On Apr 23, 6:18=A0pm, xhos...(a)gmail.com wrote: > > Ted <r.ted.by...(a)rogers.com> wrote: > > > On Apr 23, 5:23=3DA0pm, Ted Zlatanov <t...(a)lifelogs.com> wrote: > > > > > > The problem is that there are NULL values, which are undefined in > > > > the DBI translation. =3DA0So 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. =A0I > > > hadn't expected a mature library like DBI to behave like this. > > > > This is not DBI behavior. =A0It is Perl behavior when undefined valued > > are encountered. =A0It is documented, just not in the DBI docs. =A0It > > is n= > ot > > 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. There isn't a built-in map function like Perl's in any other language I know, either. If you want to use another language, use it. If you want to use Perl, use Perl. > In any RDBMS I > have worked with, null is a legitimate value that can be given to a > specific column in a specific row, No duh. The question is how to represent those values when the data is *not* in the RDBMS, but in the language you are using to talk to the RDBMS. > 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. There is plenty of empirical evidence that this is not the case. > 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. There are. You have failed to use them. > 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 DBI docs say, several time, that it translates NULL to undef. It says what it does, it does what it says. > 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. I am a database programmer and it handles them correctly from my perspective. > 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. Kudos to you. Either learn how to handle them in DBI just like you learned to use JDBC, or don't use DBI. .... > > > > 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. So you made the effort to read the JDBC docs, but not the DBI docs. How is this Perl's fault or DBI's fault? It makes it completely clear how NULLs are handled. In fact, it even says how to convert them to empty strings: (From the docs for DBI v1.58): Here's how to convert fetched NULLs (undefined values) into empty strings: while($row = $sth->fetchrow_arrayref) { # this is a fast and simple way to deal with nulls: foreach (@$row) { $_ = '' unless defined } print "@$row\n"; } 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: Ted on 23 Apr 2008 21:40 On Apr 23, 6:37 pm, xhos...(a)gmail.com wrote: > Ted <r.ted.by...(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. > No. I'd have just expressed my concerns differently. > > > > 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". > So I've learned. But how does one separate these two streams? That is the question that remains. > > 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. > Ah. OK. I've done the bulk load countless times, for MySQL, PostgreSQL and MS SQL Server. They are annoyingly different, but all relatively easy to handle using a relatively simple SQL script. The new thing here, for me, and for which I thank you, is how to use DBI to execute these scripts. Thanks Ted
From: Ted on 23 Apr 2008 21:44 On Apr 23, 7:38 pm, "A. Sinan Unur" <1...(a)llenroc.ude.invalid> wrote: > Ted <r.ted.by...(a)rogers.com> wrote in news:3b842be5-7145-42f6-9f5a- > ad1c3a50a...(a)x35g2000hsb.googlegroups.com: > > > 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. > > 1. Those are warnings, not errors. The difference matters. > Yes, but treating warnings as if they're errors alows one to develop more robust code. > 2. By default, warnings go to STDERR. Right. But the trick is to figure out how to separate STDOUT from STDERR within Emacs. Doing so on the commandline is trivial using redirection, but within Emacs? That may be an annoyance I'll have to live with for now. > 3. The easiest way to deal with warnings is to avoid the behavior > causing them. > Right. Hence my normal practice of treating warnings as if they were errors. > 4. After careful consideration, you may also choose to disable specific > types of warnings in specific scopes. Read perldoc perllexwarn. > That is something I'd do only if the warning proves to be a due to a bug in my development tools (yes, I have encountered this before, with both Borland and M$ C++ compilers). Thanks Ted
From: Ted on 23 Apr 2008 22:14 On Apr 23, 9:39 pm, xhos...(a)gmail.com wrote: > Ted <r.ted.by...(a)rogers.com> wrote: > > On Apr 23, 6:18=A0pm, xhos...(a)gmail.com wrote: > > > Ted <r.ted.by...(a)rogers.com> wrote: > > > > On Apr 23, 5:23=3DA0pm, Ted Zlatanov <t...(a)lifelogs.com> wrote: > > > > > > The problem is that there are NULL values, which are undefined in > > > > > the DBI translation. =3DA0So 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. =A0I > > > > hadn't expected a mature library like DBI to behave like this. > > > > This is not DBI behavior. =A0It is Perl behavior when undefined valued > > > are encountered. =A0It is documented, just not in the DBI docs. =A0It > > > is n= > > ot > > > 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. > > There isn't a built-in map function like Perl's in any other language I > know, either. If you want to use another language, use it. If you want to > use Perl, use Perl. > I use a variety of languages, and I'll include Perl in the mix. I am aware of the map function. There are, in fact, comparable utilities in, for example, the standard C++ library. Look especially at the generic algorithms it includes. And thee are specialized variants for some of the standard C++ containers. Each seems to have its own strengths and weaknesses. > > In any RDBMS I > > have worked with, null is a legitimate value that can be given to a > > specific column in a specific row, > > No duh. The question is how to represent those values when the data is > *not* in the RDBMS, but in the language you are using to talk to the RDBMS.. > Precisely, and using undefined is not appropriate since it confounds two distinctly different ideas. > > 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. > > There is plenty of empirical evidence that this is not the case. > So, as a DB programmer, you have contempt for your peers. But perhaps I should have been more specific and said that I, and the DB programmers I know, know how to use them well. My mistake, here I suppose was to assume that my own observations apply more generally to a wider population. > > 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. > > There are. You have failed to use them. > Not quite. I failed to find them quickly, having started with DBI today. The page for "DBD::mysql" says nothing about either this or the fact that DBI translates nulls into undefined. > > 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 DBI docs say, several time, that it translates NULL to undef. It says > what it does, it does what it says. > > > 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. > > I am a database programmer and it handles them correctly from my > perspective. > The meaning null has in SQL is quite different from the meaning given to undefined in the perl books I use. How, then, do you reconcile the two? > > 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. > > Kudos to you. Either learn how to handle them in DBI just like you learned > to use JDBC, or don't use DBI. > > ... > That is precisely what I am trying to do. But it would appear I haven't found all the relevant bits of documentation that would support such an effort. > > > > > 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. > > So you made the effort to read the JDBC docs, but not the DBI docs. How > is this Perl's fault or DBI's fault? Not quite. I am making the effort to learn DBI, but it would appear I haven't found all the relevant documentation for it. While I have been using perl for a variety of things for a while, I started with DBI only today. I don't think anyone here has attempted to assign blame. The only criticism I made was to point out the difference in meaning between the idea of undefined in perl and the meaning of null in SQL. > It makes it completely clear how > NULLs are handled. In fact, it even says how to convert them to empty > strings: > > (From the docs for DBI v1.58): > > Here's how to convert fetched NULLs (undefined values) into empty > strings: > > while($row = $sth->fetchrow_arrayref) { > # this is a fast and simple way to deal with nulls: > foreach (@$row) { $_ = '' unless defined } > print "@$row\n"; > } > I'll take your word for it, but I haven't found this nugget in the documentation yet. Thanks Ted > > -- > --------------------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.- Hide quoted text - > > - Show quoted text -
From: Ted on 24 Apr 2008 00:52
On Apr 23, 11:10 pm, Martien Verbruggen <m...(a)tradingpost.com.au> wrote: > On Wed, 23 Apr 2008 18:24:31 -0700 (PDT), > Ted <r.ted.by...(a)rogers.com> wrote: > > > 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 > > Your whole argument seems to be based on this assumption. I'm pretty > sure that that assumption is wrong. Could you tell us what that > assumption is based on? And if that was the only reason for the undef > value to exist, why does the undef function exist? > OK. You're absolutely right. I got my understanding of undefined from the book, published by O'Reilly, "Programming Perl" by Larry Wall, Tom Christiansen and Randal Schwartz. On page 155, about half way down the page talking about the function defined, I see "A scalar that contains no valid string, numeric, or reference value is known as the undefined value, or undef for short. Many operations return the undefined value ender exceptional conditions, such as end of file, uninitialized variable, system error, and such. This function allows you to distinguish between an undefined null string and a defined null string when you're using operators that might return a real null string." From what I'm reading here, it seems my understanding of undefined is not far off the mark. > Comparing it to null in Java or NULL in C or C++ is probably a good > analogy. In all those cases the value is a special one that carry a > meaning that is close to the null value in SQL: A value that is not part > of the normal range of values. The fact that nulls in SQL have special > 'handling' does not change that. If Jave or C has set operations, then > they would need to deal with null in very much the same way, and if Perl > has them, it would probably do similar things to undef. > Here you're confusing me. I agree, given what Wall et al. wrote about it, comparing undefined to null in Java or C/C++ is a decent analogy, but not exact. It is more correct to relate it to variables or identifiers that have been declared but not yet initialized. In fact, Wall et al. explicitly relate it to uninitialized variables (the very first sentence I quoted above). One can, in C++, forward declare a class, which then allows pointers to the class to be declared, but all one knows, until the definition of the class is provided, is that the class exists, and so a pointer to an instance of the class can be declared, but it can't really be used with out the defintion. Similarly, you can declare functions with prototypes, which can be called, but you better be sure to link to an object file that provides the definitions or your linker will complain. But bugs where a variable is used before it is initialized can be a nightmare to track down because the problem may only manifest itself sometimes. NULL, in C++, is specifically a value of 0 given to a pointer. An uninitialized variable can contain random bits, though the standard was changed so that you could use a slightly different declaration that initializes a pod variable to 0. A variable in Java is given a value of null until it is given an instance of the type of variable it was defined to represent. It is also clear from what Wall et al. said that undef can play a role similar to the 'end' iterator defined for all of the containers in the standard C++ library. I am not exactly happy with that notion because it is again a very different concept from that of a null pointer or an uninitialized variable, but it is admittedly functional. Allowing a programming language to be markedly multivocal is a recipe for confusion, at least without extensive and pedantic documentation. But ALL of these concepts are very different from the idea of NULL in SQL. For example, The book, from Osborne, "SQL: The complete reference" by James Groff and Paul Weinberg, described it as referring to data that is missing, unknown, or don't apply. There is some debate about it with some advocating avoiding it at all costs and others avocating wide use of it and even suggesting the term be extended to have different null indicators to allow one to distinguish between, e.g. data that is unknown vs data that doesn't apply. While NULL in SQL is different from numeric or string data, I see nothing in the idea that indicates it is a value that is not part of the normal range of values. In a table that handles returns on investment for exchange traded funds for various time frames with columns representing monthly, quarterly, annual and five year returns, the column containing five year returns would probably have a model value of NULL, since the majority exchange traded funds are younger than five years old. In this specific case, the idea is that a five year return is not applicable to funds that are not yet five years old. Yet, the numbers of exchange traded funds has been continually increasing and it is just a matter of time until that column IS applicable. I see nothing similar between the idea of NULL in SQL and either null or undefined in the other languages. Both Java and C++ have containers that support set theoretic operations. But I see nothing specific to those containers and the algorithms they support that relates to either null or undefined in C+ + or Java, and I'd have to think about the extent to which they provide a concept comparable to the idea of NULL in SQL. I can see, though, how one could easily emulate the idea of NULL as found in SQL using these containers with a UDT defined to be able to assume a "null" value and to behave the way NULLs behave in SQL when they have the null value defined for them. Doing it this way, though, has virtually nothing in common with the idea of null as it exists in either C++ or java. Unless you want to relate the SQL idea of NULL to the empty set, I do not see any necessary relatin between programming set theoretic algorithms and the idea of null. But if you see the idea of the empty set as being integral to the idea of null as it exists in SQL, then the relation with either null or undefined in C++, Java or Perl becomes even more distant because the relevant containers in C++ and Java make no direct use of nulls in handling empty sets as far as I can see. In both cases, the containers have member functions that return true if the set is empty and fals otherwise. > SQL NULL maps, IMO, very well to Perl undef. > Unless you can explain how the ideas can be reconciled, I can not agree. I see nothing in common between them. Thanks Ted |