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: Ben Bullock on 24 Apr 2008 08:23 On Thu, 24 Apr 2008 11:10:05 +0000, Abigail wrote: > Well, there would be some advantages of using a special NULL object > instead of 'undef'. Because an SQL NULL behaves differently from an > undef. For instance, when doing arithmetic, an 'undef' behaves like a 0, > while an SQL NULL behaves like a NaN: > > 3 + NULL == NULL (SQL) I'm guessing that when we subsequently try to use this value somewhere, then we have an unhappy consequence. > 3 + undef == 3 (Perl) This produces the kind of error message the original poster's query was about. > 3 + NaN == NaN (Perl) OK, but I can't see the advantages of translating the SQL NULL into a special Perl object unless we also translate all the SQL strings into special SQL string objects which behave just like SQL strings, and SQL number objects which behave just like SQL numbers, etc. etc. I don't see the value in it. If he wants to get SQL behaviour then why not just stick with manipulating the data inside the database via SQL statements in DBI, rather than pulling the values into Perl variables and then imposing a kind of "fake SQL" behaviour on the Perl variables.
From: Ted on 24 Apr 2008 08:48 On Apr 24, 6:57 am, Ben Bullock <benkasminbull...(a)gmail.com> wrote: > On Wed, 23 Apr 2008 21:52:51 -0700, Ted wrote: > > 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." > > Try running the following script to clarify what this means: > > #!/usr/bin/perl > use warnings; > use strict; > my $a; > my $b = ""; > print "a is defined\n" if defined($a); > print "b is defined\n" if defined($b); > > Here $b is a "real null string" and $a is undefined. > No. $b holds an empty string, and empty strings are not the same thing as NULL (as defined in either C++ and Java or SQL). > > 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. > > That's not a good analogy. Again, try the following script: > > #!/usr/bin/perl > use warnings; > use strict; > my $b = ""; > print "1: b is defined\n" if defined($b); > undef ($b); > print "2: b is defined\n" if defined($b); > > The undefined value in Perl is assigned to variables but that doesn't > mean that undefined equals uninitialized. > But that is exactly what Wall et al. said about undefined. I learned much of what I know about Perl from their book, and they said that an undefined variable is one that does not have a valid string, number or reference, and that exactly describes a variable that has not yet been initialized. The empty string is a valid string in all of the languages I use, and it means something quite different from a SQL null. > > Allowing a programming > > language to be markedly multivocal is a recipe for confusion, at least > > without extensive and pedantic documentation. > > Well, you're going to be confused then - if you feel that way, maybe it's > better not to learn Perl. > Why? Most of what I have seen about perl seems quite well defined. > > 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. > > Which seems to be exactly what the undefined value in Perl is, to me. > Then you're using a definition of the term that is quite different from what Wall et al. wrote about undefined. Where they wrong, or merely misleading? > > 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. > > Perhaps you can think of Perl's undef as being part of the normal range > of values, too. > In principal I'd have no problem with that, if it weren't for the fact that Wall et al. described them as referring to variables that were not initialized. That is, if Perl's undef could be equated to SQL's concept of null. But as Abigail pointed out, that equation would be problmatic at best. > > I see nothing similar between the idea of NULL in SQL and either null or > > undefined in the other languages. > > That's odd, because the more you talk about SQL's NULL, the more it looks > like Perl's undefined value to me. > Until now, I had seen nothing written about perl that suggested that Wall et al.'s description of undefined meant anything other than a reference to variables that had not yet been initialized, and that is a very different concept from SQL's 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. > > You seem to be getting lost in your own verbosity here - you start this > sentence including Perl and by the end of it you are only talking about > C++ and Java. > This was merely a response to Martien's reference to set theoretic operations. He'd expressed uncertainty about support C++ and Java provide for them and implied support for them implied a handling of SQL nulls in a manner similar to how Perl handles undef. > > In both cases, the containers > > have member functions that return true if the set is empty and fals > > otherwise. > > But now, as far as I can figure out, you're not talking about Perl any > more anyway. This too was merely a response to Martien's reference to set theoretic operations. In particular, it is an explanation of how it is done in those two languages. Why would you object to my leveraging what I know in other languages to learn Perl? I have routinely used a student's or colleague's knowledge of one programming language to more quickly learn another they needed but had not yet started to study. In my experience, using similarities and differences between languages a student knows and the one(s) he is trying to learn greatly accelerates his learning (but it depends on the instructor or mentor knowing both very well). Ted
From: Ted on 24 Apr 2008 09:03 On Apr 24, 8:14 am, Joost Diepenmaat <jo...(a)zeekat.nl> wrote: > Ted <r.ted.by...(a)rogers.com> writes: > > Yes, an uninitialized variable can contain random bits, or garbage, > > for whatever reason, but If you are going to say undef is a specific > > value, how do you account for Wall et al writing "A scalar that > > contains no valid string, numeric, or reference value is known as the > > undefined value, or undef for short." That sure looks like a > > description of a variable that has not yet been initialized. > > But it *has* been initialized. All scalars that aren't assigned a value > are initialized to "undef", which *is* a value (the quote explicitely > states that) and corresponds most closely to the null value in C++ / > Java et al. > > > In all of my code, in whatever language, I always initialize my > > variables to 0, or some other reasonable default, as a matter of > > course. Doing so prevents nasty surprises that may result from using > > a variable that has not been initialized and is easily tested for. > > That seems quite different from the idea of someting being undefined. > > That's fine in C, but in perl the interpreter already does this for you > as I described above. Of course that assumes the undef value is actually > a useful default for your algorithm. > > Seems to me you're taking the word "undefined" too literal (for > perl). It's not undefined as in "the consequences of doing X are > undefined". It's undefined as in "has the default (null) value because > it's not been assigned anything else". > > -- > Joost Diepenmaat | blog:http://joost.zeekat.nl/| work:http://zeekat.nl/ Fair enough. Then what Wall et al wrote could have been improved had they provided a little extra detail. But this still seems quite different to what NULL means in SQL. How would you explain the rationale for that mapping? Abigail has already explained the reason for my surprise at this. Thanks Ted
From: Ted on 24 Apr 2008 09:39 On Apr 24, 8:23 am, Ben Bullock <benkasminbull...(a)gmail.com> wrote: > On Thu, 24 Apr 2008 11:10:05 +0000, Abigail wrote: > > Well, there would be some advantages of using a special NULL object > > instead of 'undef'. Because an SQL NULL behaves differently from an > > undef. For instance, when doing arithmetic, an 'undef' behaves like a 0, > > while an SQL NULL behaves like a NaN: > > > 3 + NULL == NULL (SQL) > > I'm guessing that when we subsequently try to use this value somewhere, > then we have an unhappy consequence. > No. You just get yet another NULL. No problem. And I have seen plenty of SQL code that relies on this behaviour. > > 3 + undef == 3 (Perl) > > This produces the kind of error message the original poster's query was > about. > > > 3 + NaN == NaN (Perl) > > OK, but I can't see the advantages of translating the SQL NULL into a > special Perl object unless we also translate all the SQL strings into > special SQL string objects which behave just like SQL strings, and SQL > number objects which behave just like SQL numbers, etc. etc. I don't see > the value in it. If he wants to get SQL behaviour then why not just stick > with manipulating the data inside the database via SQL statements in DBI, > rather than pulling the values into Perl variables and then imposing a > kind of "fake SQL" behaviour on the Perl variables. Why would translating SQL NULLs into a special Perl object imply the utility of translating SQL strings or numbers into special objects in Perl? So far, I have not been surprised by how Perl handles strings or numbers. And yes, if I need string manipulation that wasn't available in perl, I'd create an appropriate class. I have, in fact, done the same thing in other languages too. In a current java project, I have developed my own decimal class because the decimal class provided in the latest java sdk does not like SQL nulls. My class is derived from the standard decimal class, but it has been extended to provide the behaviour we required. We're hardly talking about fake SQL behaviour. Do you usually object to user defined types, or just those aimed at avoiding the kind of surprise I experienced and Abigail explained? RDBMS are of limited utility without interaction with client programs, and while SQL is priceless for some kinds of data manipulation, it is of limited value for others. In some cases, I extract data from my RDBMS in order to process it using C++ or FORTRAN, because those two languages are the best available for high performance number crunching. I have seen functions in both SQl and C++ that produce the same result, but the SQL function taking days to run to completion on millions of rows of data and the C++ function taking mere seconds. And the results of such calculation are then imported into the RDBMS. In the case in question here, the task is to extract data from the RDBMS to define what data to obtain from our data feeds, and, once downloaded using LWP, it will be loaded back into the RDBMS. The LWP robot is the perfect tool for this, but it needs data drawn from the database in order to figure out what data to get and load into the database. Cheers, Ted
From: Ted on 24 Apr 2008 12:31
On Apr 24, 10:56 am, Ted Zlatanov <t...(a)lifelogs.com> wrote: > On Wed, 23 Apr 2008 14:40:14 -0700 (PDT) Ted <r.ted.by...(a)rogers.com> wrote: > > T> This would be a useful tidbit to add to the documentation. I hadn't > T> expected a mature library like DBI to behave like this. I do not, for > T> example, have to go through any such extra hoops when using JDBC with > T> JSF. If a particular record set contains nulls in one or more columns > T> in one or more records, JSF automagically displays it as an empty > T> string; something I have modified on occassion to display the string > T> "N/A". > > I saw your discussion with the others, it covered all the bases. I > agree with Abigail (unsurprisingly), but I also think the current DBI > approach is the best compromise between ideology and pragmatism. > She is a gem, isn't she?!! I learned plenty about the perl side of things from her posts. And I found Ben's last example, using undef to ignore certain values, interesting. But his first example remains problematic. Between the two, and others, I can see some logic in choosing undef as a pel primitive to represent SQL NULLS, but it seems to me that with the support now available for object oriented programming in perl, it would have been relatively trivial to provide a new class to more closely represent the SQL idea of a null. When I did it in Java, all I had to do was add a private data member of type boolean, and then check this data member in those member functions where it is relevant that override functions provided in the relevant base classes. > T> You needn't worry about rewriting the whole thing as I'll be doing > T> that anyway as I learn to translate more of what I know from C++,Java > T> and SQL into Perl. But perhaps you could give me a break and make > T> some suggestions as to what improvements you'd recommend and where to > T> look for details in the documentation. > > Use an options hash with Getopt::Long. > > Instead of $$var[$i], use $var->[$i]. > > Learn about join, map, and grep. > > Have fun. Always look for a shorter, smarter way to do something. > Thank you. > T> And one thing I haven't found yet, which I am sure must be there > T> somewhere, is how to set up a bulk insert. Can you point me in the > T> right direction to see how to do that using DBI? > > Bulk inserts are annoyingly hard to do in a generic way. You've got to > do it with the database's particular facility. Postgres, for example, > has a statement that will import a file; Sybase has an entirely external > utility for this task and no internal functionality. > Yuck. Another pet peeve WRT DB programming and portability. I can get correct C+ code to compile using almost any compiler, especially if I avoid vendor provided extensions, and the same remains true of my fortran code, but it seems notoriously hard to develop portable SQL code (my mix is MS SQL Server 2005, Postgres and MySQL. But it is trivially easy to perform bulk loads using the DB's particular facility, and thus not especially challenging to set up in Perl using, e.g., system. And it has been pointed out later on in this thread that MySQL's bulk load can be invoked through a DB handle. BTW: having read the thread, you see where Ben and I seem to be butting heads. Can you point me to places in the Perl reference that substantiate his perspective on the points where we disagree, and which provide a fuller explanation of his point of view? Thanks Ted |