Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot, bind-address mysql multiple, sanibleone xxxx, ftp://192.168.100.100/, www.xxxcon

Links

XODOX
Impressum

#1: re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-23 22:19:04 by Ted Byers

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

Report this message

#2: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-23 22:40:58 by smallpond

On Apr 23, 4:19 pm, Ted <r.ted.by...@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

Report this message

#3: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-23 23:23:29 by Ted Zlatanov

On Wed, 23 Apr 2008 13:40:58 -0700 (PDT) smallpond <smallpond@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

Report this message

#4: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-23 23:29:58 by Ted Byers

On Apr 23, 4:40=A0pm, smallpond <smallp...@juno.com> wrote:
> On Apr 23, 4:19 pm, Ted <r.ted.by...@rogers.com> wrote:
>
>
>
>
>
> > The program could hardly be simpler. =A0Here it is:
>
> > use DBI;
> > use IO::File;
>
> > my $db=3D'yohan';
> > my $hostname =3D 'localhost';
> > my $port =3D '3306';
> > my $user =3D 'rejbyers';
> > my $dbpwd =3D 'jesakos';
>
> > $dbh =3D DBI->connect("DBI:mysql:database=3D$db;host=3D$hostname",
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 $user, $dbpwd, {RaiseError =3D> =
1});
>
> > STDOUT->print("just before preparing statement\n");
> > my $table =3D 'etf';
> > $sth =3D $dbh->prepare("SELECT * FROM $table");
> > if (!$sth) {
> > =A0 die "Error:" . $dbh->errstr . "\n";}
>
> > STDOUT->print("Just before executing the statement\n");
> > if (!$sth->execute) {
> > =A0 die "Error:" . $sth->errstr . "\n";}
>
> > STDOUT->print("Just before getting a row to display\n");
> > my $row =3D $sth->fetchrow_arrayref();
> > STDOUT->print($row);STDOUT->print("\n");
> > my $names =3D $sth->{'NAME'};
> > my $numFields =3D $sth->{'NUM_OF_FIELDS'};
> > for (my $i =3D 0; =A0$i < $numFields; =A0$i++) {
> > =A0 =A0 printf("%s%s", $i ? "," : "", $$names[$i]);}
>
> > print "\n";
> > while (my $ref =3D $sth->fetchrow_arrayref) {
> > =A0 =A0 for (my $i =3D 0; =A0$i < $numFields; =A0$i++) {
> > =A0 =A0 =A0 =A0 printf("%s%s", $i ? "," : "", $$ref[$i]);
> > =A0 =A0 }
> > =A0 =A0 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. =A0So 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

Report this message

#5: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-23 23:40:14 by Ted Byers

On Apr 23, 5:23=A0pm, Ted Zlatanov <t...@lifelogs.com> wrote:
> On Wed, 23 Apr 2008 13:40:58 -0700 (PDT) smallpond <smallp...@juno.com> wr=
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

Report this message

#6: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 00:18:56 by xhoster

Ted <r.ted.byers@rogers.com> wrote:
> On Apr 23, 5:23=A0pm, Ted Zlatanov <t...@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.

Report this message

#7: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 00:31:57 by Joost Diepenmaat

Ted <r.ted.byers@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.

> 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.

In my opinon warnings are very useful and should be enabled as much as
possible, but in the particular cases/constructs where it they cause
problems, you shouldn't be afraid to disable them (using "no
warnings"). See perldoc warnings and perldoc no.

--
Joost Diepenmaat | blog: http://joost.zeekat.nl/ | work: http://zeekat.nl/

Report this message

#8: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 00:34:18 by Joost Diepenmaat

Joost Diepenmaat <joost@zeekat.nl> writes:

> In my opinon warnings are very useful and should be enabled as much as
> possible, but in the particular cases/constructs where it they cause
^^ disregard that
> problems, you shouldn't be afraid to disable them (using "no
> warnings"). See perldoc warnings and perldoc no.
^^ oops, there that
doens't exist. See
perldoc -f use

--
Joost Diepenmaat | blog: http://joost.zeekat.nl/ | work: http://zeekat.nl/

Report this message

#9: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 00:37:58 by xhoster

Ted <r.ted.byers@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.

Report this message

#10: re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 01:38:49 by 1usa

Ted <r.ted.byers@rogers.com> wrote in news:3b842be5-7145-42f6-9f5a-
ad1c3a50ad35@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.

2. By default, warnings go to STDERR.

3. The easiest way to deal with warnings is to avoid the behavior
causing them.

4. After careful consideration, you may also choose to disable specific
types of warnings in specific scopes. Read perldoc perllexwarn.

Sinan


--
A. Sinan Unur <1usa@llenroc.ude.invalid>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://www.rehabitation.com/clpmisc/

Report this message

#11: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 01:43:13 by 1usa

Ted <r.ted.byers@rogers.com> wrote in news:8db4922e-cd7e-4345-93cf-
b3516d3cc01a@z72g2000hsb.googlegroups.com:

> 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?

There is no choking. perl is telling you that you have attempted to use
an undefined value.

> JDBC). But there is no a priori way to tell which columns in which
> rows will be null.

Therefore, you should check values with 'defined' and use suitable
defaults before printing or manipulating them.

> 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?

I don't know what idioms you are talking about.

In Perl, you check if a value is defined using

perldoc -f defined

Sinan

--
A. Sinan Unur <1usa@llenroc.ude.invalid>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://www.rehabitation.com/clpmisc/

Report this message

#12: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 02:04:28 by rvtol+news

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."

Report this message

#13: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 02:21:06 by someone

Ted Zlatanov wrote:
> On Wed, 23 Apr 2008 13:40:58 -0700 (PDT) smallpond <smallpond@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

Report this message

#14: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 03:05:35 by Ted Byers

On Apr 23, 6:18=A0pm, xhos...@gmail.com wrote:
> Ted <r.ted.by...@rogers.com> wrote:
> > On Apr 23, 5:23=3DA0pm, Ted Zlatanov <t...@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. 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. =A0If 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. =A0A
> 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. =A0Can you point me in the right direction to see how to do that=

> > using DBI?
>
> execute_array is supposed to do that. =A0It does not work in all DBDs, and=

> does not work well in some of the others. =A0Generally for maximum efficie=
ncy
> 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

Report this message

#15: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 03:24:31 by Ted Byers

On Apr 23, 6:31=A0pm, Joost Diepenmaat <jo...@zeekat.nl> wrote:
> Ted <r.ted.by...@rogers.com> writes:
>
> =A0[ on the conversion of NULL fields to <undefined> ]
>
> > This would be a useful tidbit to add to the documentation. =A0I 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.

> > =A0I do not, for example, have to go through any such extra hoops when
> > using JDBC with JSF. =A0If 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

Report this message

#16: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 03:39:28 by xhoster

Ted <r.ted.byers@rogers.com> wrote:
> On Apr 23, 6:18=A0pm, xhos...@gmail.com wrote:
> > Ted <r.ted.by...@rogers.com> wrote:
> > > On Apr 23, 5:23=3DA0pm, Ted Zlatanov <t...@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.

Report this message

#17: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 03:40:28 by Ted Byers

On Apr 23, 6:37=A0pm, xhos...@gmail.com wrote:
> Ted <r.ted.by...@rogers.com> wrote:
>
> > Running it from within Emacs, all seems fine until I examine the
> > output. =A0All the calls to "STDOUT->print" behave correctly, and all
> > the correct data is returned. =A0I KNOW that database well, and the
> > contents of the table I used for this test. =A0ALL of the data in that
> > table is returned. =A0However, 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." =A0That is the
> > printf in the for loop within the while loop. =A0This increases the
> > number of lines of output from precisely 529 to more than 1800! =A0That
> > is almost three times as much garbage as there is real data.
>
> So? =A0If there were only 0.0894 times as much "garbage", would that someh=
ow
> be better? =A0It 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. =A0That is why the
> warning messages and the real data are all mixed together. =A0The exact
> nature of the problem was described elsewhere. =A0It 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. =A0DBI/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

Report this message

#18: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 03:44:03 by Ted Byers

On Apr 23, 7:38=A0pm, "A. Sinan Unur" <1...@llenroc.ude.invalid> wrote:
> Ted <r.ted.by...@rogers.com> wrote in news:3b842be5-7145-42f6-9f5a-
> ad1c3a50a...@x35g2000hsb.googlegroups.com:
>
> > table is returned. =A0However, 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." =A0That is the
> > printf in the for loop within the while loop. =A0This increases the
> > number of lines of output from precisely 529 to more than 1800! =A0That
> > 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

Report this message

#19: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 04:14:26 by Ted Byers

On Apr 23, 9:39=A0pm, xhos...@gmail.com wrote:
> Ted <r.ted.by...@rogers.com> wrote:
> > On Apr 23, 6:18=3DA0pm, xhos...@gmail.com wrote:
> > > Ted <r.ted.by...@rogers.com> wrote:
> > > > On Apr 23, 5:23=3D3DA0pm, Ted Zlatanov <t...@lifelogs.com> wrote:
>
> > > > > The problem is that there are NULL values, which are undefined in
> > > > > the DBI translation. =3D3DA0So 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. =3DA0I
> > > > hadn't expected a mature library like DBI to behave like this.
>
> > > This is not DBI behavior. =3DA0It is Perl behavior when undefined valu=
ed
> > > are encountered. =3DA0It is documented, just not in the DBI docs. =3DA=
0It
> > > is n=3D
> > 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. =A0If you want to use another language, use it. =A0If you wa=
nt 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. =A0The 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. =A0Any 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. =A0So 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. =A0You 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. =A0It sa=
ys
> 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. =A0On 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. =A0Perl, 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. =A0Surely you know that JDBC
> > is to Java as DBI is to Perl. =A0What 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. =A0I know how to
> > deal with nulls when using JDBC.
>
> Kudos to you. =A0Either learn how to handle them in DBI just like you lear=
ned
> 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. =A0In the case in question, the only possible
> > values are real numbers and null. =A0In some instances in this
> > application, the proper way to display the null is to use an empty
> > string. =A0In others, the proper way to display it is to use the string
> > "N/A". =A0But 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. =A0How=

> 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.

>=A0It makes it completely clear how
> NULLs are handled. =A0In fact, it even says how to convert them to empty
> strings:
>
> (From the docs for DBI v1.58):
>
> =A0 =A0 =A0 =A0Here's how to convert fetched NULLs (undefined values) into=
empty
> =A0 =A0 =A0 =A0strings:
>
> =A0 =A0 =A0 =A0 =A0while($row =3D $sth->fetchrow_arrayref) {
> =A0 =A0 =A0 =A0 =A0 =A0# this is a fast and simple way to deal with nulls:=

> =A0 =A0 =A0 =A0 =A0 =A0foreach (@$row) { $_ =3D '' unless defined }
> =A0 =A0 =A0 =A0 =A0 =A0print "@$row\n";
> =A0 =A0 =A0 =A0 =A0}
>
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 -

Report this message

#20: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 05:10:02 by Martien Verbruggen

On Wed, 23 Apr 2008 18:24:31 -0700 (PDT),
Ted <r.ted.byers@rogers.com> wrote:
> On Apr 23, 6:31 pm, Joost Diepenmaat <jo...@zeekat.nl> wrote:
>> Ted <r.ted.by...@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?

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.

SQL NULL maps, IMO, very well to Perl undef.

If you still think the DBI is broken, instead of you having the wrong
idea of what undef means, you should probably have this argument with
the DBI developers, who will probably be much better be able to explain
to you why it is not broken.

Martien
--
|
Martien Verbruggen |
| "Mr Kaplan. Paging Mr Kaplan..."
|

Report this message

#21: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 06:52:51 by Ted Byers

On Apr 23, 11:10=A0pm, Martien Verbruggen <m...@tradingpost.com.au>
wrote:
> On Wed, 23 Apr 2008 18:24:31 -0700 (PDT),
> =A0 =A0 =A0 =A0 Ted <r.ted.by...@rogers.com> wrote:
>
> > On Apr 23, 6:31=A0pm, Joost Diepenmaat <jo...@zeekat.nl> wrote:
> >> Ted <r.ted.by...@rogers.com> writes:
>
> >> =A0[ on the conversion of NULL fields to <undefined> ]
>
> >> > This would be a useful tidbit to add to the documentation. =A0I 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. =A0My understanding of undefined in
> > Perl was to relate it to uninitialized values in Java or C++. =A0You
>
> 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

Report this message

#22: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 07:17:24 by jurgenex

Ted <r.ted.byers@rogers.com> wrote:
>It is more correct to relate it to variables or
>identifiers that have been declared but not yet initialized.

No. An unitialized variable can have any value. Mabye a left-over from a
previous run, maybe some temporary result that was computed a while ago
and left on the stack or whatever. It is mostly a concession to the
laziness of programmers as well as some security concerns that nowadays
runtime environments initialize declared variables with 0 or some
similarly reasonable default.

Undef on the other hand is a well defined, very specific value, which
you can even set explizitely and test against.
You cannot 'reuninitialize' a variable in e.g. C after it was assigned a
value.

jue

Report this message

#23: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 11:59:34 by benkasminbullock

On Wed, 23 Apr 2008 18:24:31 -0700, Ted wrote:

> 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).

I don't know Java, but uninitialized values in C++ are completely
different from undefined values in Perl. An uninitialized C++ variable
just contains random garbage, but Perl's undefined values are something
meaningful, i.e. you can test whether a variable is undefined or not.

> 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.

So it sounds just like an undefined value in Perl. What value would you
give a Perl scalar to represent an SQL null, if not the undefined value?
A string saying "NULL"? A special "NULL" object? What would their
advantage be over just Perl's regular undefined value?

> 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 really do not think that it's broken.

> I'd have
> considered developeing an extension to DBI; one that included a NULL
> class that would serve the needs of database programmers.

What would it do, exactly?

Report this message

#24: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 12:12:21 by RedGrittyBrick

Ted wrote:
> On Apr 23, 7:38 pm, "A. Sinan Unur" <1...@llenroc.ude.invalid> wrote:
>> Ted <r.ted.by...@rogers.com> wrote in news:3b842be5-7145-42f6-9f5a-
>> ad1c3a50a...@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.

That may be generally true of Perl too but, with Perl, there are a few
circumstances where it may be best to suppress specific warnings for a
limited scope.

I occasionally use @SuppressWarnings("unchecked") in Java because some
Java core classes are not generified or because type-erasure renders
some type-casts uncheckable at runtime. Treating those as errors (or as
bugs in Eclipse) would be wrong IMO.

Notwithstanding the above, I find it unhelpful to adopt Java (or C++)
habits or principles when writing Perl.


>> 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.

I use Perl, I don't use Emacs. I think this problem is an Emacs problem
and shouldn't be muddled together with discussion here of your concerns
about Perl DBI/DBD.



--
RGB

Report this message

#25: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 12:57:14 by benkasminbullock

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.

> 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.

> 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.

> 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.

> 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.

> 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.

> 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.

> 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.

Report this message

#26: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 13:10:05 by Abigail

_
Ben Bullock (benkasminbullock@gmail.com) wrote on VCCCL September
MCMXCIII in <URL:news:fuplm6$7nl$1@ml.accsnet.ne.jp>:
%% On Wed, 23 Apr 2008 18:24:31 -0700, Ted wrote:
%%
%% > 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).
%%
%% I don't know Java, but uninitialized values in C++ are completely
%% different from undefined values in Perl. An uninitialized C++ variable
%% just contains random garbage, but Perl's undefined values are something
%% meaningful, i.e. you can test whether a variable is undefined or not.
%%
%% > 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.
%%
%% So it sounds just like an undefined value in Perl.

Up to there, yes. But it behaves quite differently. Undefined values in
Perl become 0 or the empty string if the context requires so. But not
so with SQL NULLs (see below).

%% What value would you
%% give a Perl scalar to represent an SQL null, if not the undefined value?
%% A string saying "NULL"? A special "NULL" object? What would their
%% advantage be over just Perl's regular undefined value?

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)
3 + undef == 3 (Perl)
3 + NaN == NaN (Perl)

Unfortunally, there's no NaN equivalent for strings, and when you get
something out of a database, DBI cannot know whether it's going to be
used as a string, or as a number.

%% > 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 really do not think that it's broken.

I'd say, mapping SQL NULLs to undef was a choice. It's often convenient,
but can be surprising if you're used to SQL NULLs and the wrong choice
if you need the same behaviour. Another choice would have been NaN, but
that makes string handling more cumbersome.

%% > I'd have
%% > considered developeing an extension to DBI; one that included a NULL
%% > class that would serve the needs of database programmers.
%%
%% What would it do, exactly?


If I were to implement is, an object of such a class would have overloaded
all operations and return another NULL object for all operations, except
when comparing it with NULL (in which case it would return a true value).



Abigail
--
$_ = "\nrekcaH lreP rehtona tsuJ"; my $chop; $chop = sub {print chop; $chop};
$chop -> () -> () -> () -> () -> () -> () -> () -> () -> () -> () -> () -> ()
-> () -> () -> () -> () -> () -> () -> () -> () -> () -> () -> () -> () -> ()

Report this message

#27: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 13:56:02 by Ted Byers

On Apr 24, 1:17=A0am, J=FCrgen Exner <jurge...@hotmail.com> wrote:
> Ted <r.ted.by...@rogers.com> wrote:
> >It is more correct to relate it to variables or
> >identifiers that have been declared but not yet initialized.
>
> No. An unitialized variable can have any value. Mabye a left-over from a
> previous run, maybe some temporary result that was computed a while ago
> and left on the stack or whatever. It is mostly a concession to the
> laziness of programmers as well as some security concerns that nowadays
> runtime environments initialize declared variables with 0 or some
> similarly reasonable default.
>
> Undef on the other hand is a well defined, very specific value, which
> you can even set explizitely and test against.
> You cannot 'reuninitialize' a variable in e.g. C after it was assigned a
> value.
>
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.

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.

Ted

Report this message

#28: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 14:08:59 by Ted Byers

On Apr 24, 5:59=A0am, Ben Bullock <benkasminbull...@gmail.com> wrote:
> On Wed, 23 Apr 2008 18:24:31 -0700, Ted wrote:
> > My understanding of undefined in
> > Perl was to relate it to uninitialized values in Java or C++. =A0You
> > declare a variable, but then attempt to use it before you define it
> > (i.e. give it a value).
>
> I don't know Java, but uninitialized values in C++ are completely
> different from undefined values in Perl. An uninitialized C++ variable
> just contains random garbage, but Perl's undefined values are something
> meaningful, i.e. you can test whether a variable is undefined or not.
>
That perl provides an easy way to test for a variable being undefined
is a neat capability, as you're thereby able to test for metadata that
can change at runtime. But that has little to do with the meaning of
what it is for a variable to be undefined.

> > =A0The 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.
>
> So it sounds just like an undefined value in Perl. What value would you
> give a Perl scalar to represent an SQL null, if not the undefined value?
> A string saying "NULL"? A special "NULL" object? What would their
> advantage be over just Perl's regular undefined value?
>
It seems completely different from Wall et al.'s description of them.
Or are you going to say that what they wrote was wrong or misleading
and that I shouldn't have believed them?
My answer as to what value to give a perl scalar is given below in
text you quoted in brief, and in more detail by Abigail later on in
this thread.

> > NULLs in SQL carry a very
> > specific meaning, and require very specific behaviours when handling
> > them. =A0To 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. =A0Had I known that the DBI was
> > broken in terms of translating database nulls into <undefined>
>
> I really do not think that it's broken.
>
> > I'd have
> > considered developeing an extension to DBI; one that included a NULL
> > class that would serve the needs of database programmers.
>
> What would it do, exactly?

See Abigail's answer. for such details. She's right on the money.

Ted

Report this message

#29: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 14:14:02 by Joost Diepenmaat

Ted <r.ted.byers@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/

Report this message

#30: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 14:23:46 by benkasminbullock

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.

Report this message

#31: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 14:48:16 by Ted Byers

On Apr 24, 6:57=A0am, Ben Bullock <benkasminbull...@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. =A0Many operations return the undefined value ender exception=
al
> > conditions, such as end of file, uninitialized variable, system error,
> > and such. =A0This function allows you to distinguish between an undefine=
d
> > 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 =3D "";
> 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. =A0I 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. =A0It 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 =3D "";
> 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. =A0For example, The book, from Osborne, "SQL: The complete referenc=
e"
> > 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.

> > =A0In 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

Report this message

#32: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 15:03:45 by Ted Byers

On Apr 24, 8:14=A0am, Joost Diepenmaat <jo...@zeekat.nl> wrote:
> Ted <r.ted.by...@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." =A0That 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. =A0Doing 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

Report this message

#33: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 15:39:14 by Ted Byers

On Apr 24, 8:23=A0am, Ben Bullock <benkasminbull...@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:
>
> > =A0 =A0 3 + NULL  ==3D NULL =A0(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.

> > =A0 =A0 3 + undef == 3 =A0 =A0 (Perl)
>
> This produces the kind of error message the original poster's query was
> about.
>
> > =A0 =A0 3 + NaN =A0 == NaN =A0 (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

Report this message

#34: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 15:41:30 by Abigail

_
Ben Bullock (benkasminbullock@gmail.com) wrote on VCCCL September
MCMXCIII in <URL:news:fupu4i$8t8$1@ml.accsnet.ne.jp>:
`` 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.

Depends on how you use it.

But that already happens in Perl as well, for example, @{undef} also leads
to unhappy consequences (if you've turned on strict).

`` > 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.


Ah well, if Perl+CPAN only had things you could see the advantage of...

I won't object if the OP writes an extension to the DBI that used special
NULL objects.


Abigail
--
perl -Mstrict='}); print "Just another Perl Hacker"; ({' -le1

Report this message

#35: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 15:45:07 by Joost Diepenmaat

Ted <r.ted.byers@rogers.com> writes:

> 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.

I think that undef is a good choice for NULL if you want to map SQL
values to "native" perl values. undef is /conceptually/ the closest perl
has to SQL NULL.

If on the other hand, you want values that /behave/ like their SQL
counterparts, that would mean that most values would behave differently
from the built-in perl types. And that would make it much more
cumbersome to use data from the database in perl code that doesn't
expect that behaviour (which would be almost all reusable code).

It would probably not be too much work to wrap DBI to return a special
NULL object instead of undef, for example, but I think the current
mapping makes the most sense for a relatively low-level interface like
DBI.

--
Joost Diepenmaat | blog: http://joost.zeekat.nl/ | work: http://zeekat.nl/

Report this message

#36: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 15:55:59 by Abigail

_
Joost Diepenmaat (joost@zeekat.nl) wrote on VCCCL September MCMXCIII in
<URL:news:877iene598.fsf@zeekat.nl>:
^^ Ted <r.ted.byers@rogers.com> writes:
^^
^^ > 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.
^^
^^ I think that undef is a good choice for NULL if you want to map SQL
^^ values to "native" perl values. undef is /conceptually/ the closest perl
^^ has to SQL NULL.

No, 'undef' is a compromise. When using numbers, and numerical operators,
NaN is much closer to an SQL NULL than undef is. However, Perl has only
typed values, and mixes and matches strings and numbers without explicite
casting. And there isn't a string alternative for NaN.

^^ If on the other hand, you want values that /behave/ like their SQL
^^ counterparts, that would mean that most values would behave differently
^^ from the built-in perl types. And that would make it much more
^^ cumbersome to use data from the database in perl code that doesn't
^^ expect that behaviour (which would be almost all reusable code).

Really? I claim that good code, that is, code that uses warnings, will
have to deal with 'undef's explicite anyway, because most operators will
issue a warning if one or more of their arguments is undefined anyway.

^^ It would probably not be too much work to wrap DBI to return a special
^^ NULL object instead of undef, for example, but I think the current
^^ mapping makes the most sense for a relatively low-level interface like
^^ DBI.


Abigail
--
perl -wle 'eval {die [[qq [Just another Perl Hacker]]]};; print
${${${@}}[$#{@{${@}}}]}[$#{${@{${@}}}[$#{@{${@}}}]}]'

Report this message

#37: Re: use of DBI; I am getting multiple error messages mixed in with ?the correct output.

Posted on 2008-04-24 16:10:08 by benkasminbullock

Ted <r.ted.byers@rogers.com> wrote:

>> 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.

Excuse me, this should say '$b is a "defined null string"' not "real".

> 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).

But I'm not talking about C++ or Java or SQL. I'm talking about
Perl. This is a perl newsgroup. "" is what the book you're referring
to means by a "defined null string", as opposed to an undefined value.

> 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.

We're not talking about the empty string any more, the above is
talking about an undefined value. As I pointed out in the two scripts,
$b="" and $b=undef are two totally different things.

>> > 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?

Look at this kind of example:

my @arr = (undef, 1, 3, undef, 4);

If you then refer to $arr[3] you get the undefined value. So you can
assign it into a variable. What is more you can do things like

(undef, $x, $y) = @z;

to ignore the value of $z[0].

I don't know how that compares to SQL's NULL in detail, but it seems
to me that this refers to data that is "missing, unknown or does not
apply", thus from your statement this "seems to be exactly what the
undefined value in Perl is, to me", as I said.

>> > 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.

You keep on and on repeating this but I've already pointed out to you
that it doesn't mean that. Did you actually try running or even
reading those scripts I gave? You need to spend a lot more time
reading and writing Perl code and a lot less time in creating mounds
of verbiage.

>> > 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.

Please don't say "not yet" again, I've already given you counter
examples for that.

> Why would you object to my leveraging what I know in other languages
> to learn Perl?

Since you've replied to my post, I assume you're addressing this
comment to me, so let me say that I don't remember objecting to
that.

Report this message

#38: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 16:56:36 by Ted Zlatanov

On Wed, 23 Apr 2008 14:40:14 -0700 (PDT) Ted <r.ted.byers@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.

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.

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.

Ted

Report this message

#39: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 16:58:55 by xhoster

Abigail <abigail@abigail.be> wrote:
> _
>
> %% > I'd have
> %% > considered developeing an extension to DBI; one that included a NULL
> %% > class that would serve the needs of database programmers.
> %%
> %% What would it do, exactly?
>
> If I were to implement is, an object of such a class would have
> overloaded all operations and return another NULL object for all
> operations, except when comparing it with NULL (in which case it would
> return a true value).

But in SQL, comparing NULL with NULL gives NULL, not true. At least with
ordinary comparison operators. Also, for the "and" and "or" operators,
sometimes they give non-NULLs.

NULL and false = false
NULL and true = NULL
NULL or false = NULL
NULL or true = true

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.

Report this message

#40: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 17:46:51 by Ted Byers

On Apr 24, 10:10=A0am, benkasminbull...@gmail.com (Ben Bullock) wrote:
> Ted <r.ted.by...@rogers.com> wrote:
> >> Try running the following script to clarify what this means:
>
> >> #!/usr/bin/perl
> >> use warnings;
> >> use strict;
> >> my $a;
> >> my $b =3D "";
> >> 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.
>
> Excuse me, this should say '$b is a "defined null string"' not "real".
>
OK. Where does this definition of a defined null string come from?
Wall et al. say nothing about it, either in their chapter two, where
they talk about types and other basic matters, or on page 155 where
they talk about undef.

> > 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).
>
> But I'm not talking about C++ or Java or SQL. I'm talking about
> Perl. This is a perl newsgroup. "" is what the book you're referring
> to means by a "defined null string", as opposed to an undefined value.
>
Then why are you arguing in this thread.

The DBI is all about mapping SQL concepts to Perl concepts. Surely
this newsgroup is more logical as a place to discuss that than any
other. I'd assume that it was developed by perl programmers to meet
the need to be able to interact with databases.

And where exactly are you getting the idea that the empty string is a
defined null string. Wall et al. certainly make no reference to such
an idea whether where they talk about types in chapter 2 or where they
talk about undefined on page 155. It is, in fact, a bit of a
frustration that they reference the defined null string without
defining it. But I don't criticise them since there is no such thing
as a perfect product, and therefore any product can be improved.

> > But that is exactly what Wall et al. said about undefined. =A0I 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. =A0The empty string is a valid string in all of the
> > languages I use, and it means something quite different from a SQL
> > null.
>
> We're not talking about the empty string any more, the above is
> talking about an undefined value. As I pointed out in the two scripts,
> $b=3D"" and $b=3Dundef are two totally different things.
>
Agreed. But the empty string is not a defined null, unless that
definition is provided somewhere in the Perl documentation. $b in
$b=3D"" is an empty string, neither null nor undefined. Apart from what
you have written here, I have seen nothing written about perl that
equates the empty string to a defined null.

Wall et al. say nothing about an empty string being conceived as being
the same thing as a defined null, either in their second chapter,
where they talk about types and other basic matters, or on page 155
where they talk about undef.

> >> > But ALL of these concepts are very different from the idea of NULL in=

> >> > SQL. =A0For example, The book, from Osborne, "SQL: The complete refer=
ence"
> >> > by James Groff and Paul Weinberg, described it as referring to data t=
hat
> >> > 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. =A0Where they wrong, or
> > merely misleading?
>
> Look at this kind of example:
>
> my @arr =3D (undef, 1, 3, undef, 4);
>
> If you then refer to $arr[3] you get the undefined value. So you can
> assign it into a variable. What is more you can do things like
>
> (undef, $x, $y) =3D @z;
>
> to ignore the value of $z[0].
>
This is new to me, and quite interesting. And it shows a behaviour
related to the behaviour of SQL NULLs.

> I don't know how that compares to SQL's NULL in detail, but it seems
> to me that this refers to data that is "missing, unknown or does not
> apply", thus from your statement this "seems to be exactly what the
> undefined value in Perl is, to me", as I said.
>
Read Abigail's posts. I found them priceless, and I thank her for
them, for I learned much from her comparison of SQL NULLs and Perl's
undef, especially what she said bout what happens on the Perl side.

> >> > While NULL in SQL is different from
> >> > numeric or string data, I see nothing in the idea that indicates it i=
s 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.
>
> You keep on and on repeating this but I've already pointed out to you
> that it doesn't mean that. Did you actually try running or even
> reading those scripts I gave?
I repeated it because that is what they wrote, and there seems to be a
gulf between what they wrote and what you're writing. Yes, the
scripts show how Perl behaves in the xample context, but there is a
problem with your example because there is no substantiation of your
claim that the empty string is the same thing as a defined null
string. I do not have a problem with such a definition, but would
like to see it somewhere in the perl documents. Is it given there?
If so, where?

But the notion of a defined null string is really a side issue. It
has become appearent to me from what others have written in this
thread that Perl is doing implicitly, using undef, what I routinely do
explicitly in my C++ and Java code. It also does not address the core
problem that a variable that is not defined is not the same idea as
that represented in an SQL null.

> >> > 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 loo=
ks
> >> 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.
>
> Please don't say "not yet" again, I've already given you counter
> examples for that.
>
And as I pointed out, your examples that used $a and $b are
problematic, and thus don't settle the question. Further, your
counter examples don't address the fact I have not seen your use of
undefined as being equivalent in meaning to SQL's NULLs in other Perl
references. See Abigail's posts for a detailed explanation of the
problems with such an equivalence.

Yes, I did find the information you referred to on the DBI page, but
there was no explanation there for the rationale behind such a choice.

> > Why would you object to my leveraging what I know in other languages
> > to learn Perl?
>
> Since you've replied to my post, I assume you're addressing this
> comment to me, so let me say that I don't remember objecting to
> that.

You did so implicitly by apparently complaining that I wrote too much
about these ideas as they are reflected in C++ and Java.
To quote what you said above: "But I'm not talking about C++ or Java
or SQL. I'm talking about Perl. This is a perl newsgroup." You seem
to be implying that mention of ideas expressed in C++ or Java or SQL
is forbidden in this newsgroup. If that is correct, it makes it
impossible to leverage what one knows of these other languages to
learn more about perl, and in particular that would make understanding
how and why the DBI maps SQL cncepts to Perl concepts.

If I misinterpreted what you meant by such comments, I apologize.

Cheers,

Ted

Report this message

#41: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 18:31:05 by Ted Byers

On Apr 24, 10:56=A0am, Ted Zlatanov <t...@lifelogs.com> wrote:
> On Wed, 23 Apr 2008 14:40:14 -0700 (PDT) Ted <r.ted.by...@rogers.com> wrot=
e:
>
> T> This would be a useful tidbit to add to the documentation. =A0I hadn't
> T> expected a mature library like DBI to behave like this. =A0I do not, fo=
r
> T> example, have to go through any such extra hoops when using JDBC with
> T> JSF. =A0If a particular record set contains nulls in one or more column=
s
> 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. =A0I
> 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. =A0But 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. =A0
>
> Use an options hash with Getopt::Long.
>
> Instead of $$var[$i], use $var->[$i].
>
> Learn about join, map, and grep.
>
> Have fun. =A0Always 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. =A0Can 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. =A0You've got to
> do it with the database's particular facility. =A0Postgres, 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

Report this message

#42: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 18:48:31 by RedGrittyBrick

Ted wrote:
> On Apr 24, 10:56 am, Ted Zlatanov <t...@lifelogs.com> wrote:
>>
>> 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?!!
>

He (Abigail) may well be a gem.

--
RGB

Report this message

#43: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 18:50:18 by rvtol+news

Ted schreef:

> 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.

Troll alert.

--
Affijn, Ruud

"Gewoon is een tijger."

Report this message

#44: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 19:01:12 by 1usa

Ted <r.ted.byers@rogers.com> wrote in
news:064ae74c-23e2-4034-828d-4c22ee292381@l42g2000hsc.google groups.com:

> On Apr 24, 10:56 am, Ted Zlatanov <t...@lifelogs.com> wrote:

....

>> 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?!!

s/[Ss]he/he/g

Sinan


--
A. Sinan Unur <1usa@llenroc.ude.invalid>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://www.rehabitation.com/clpmisc/

Report this message

#45: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 20:23:03 by Ted Zlatanov

On Thu, 24 Apr 2008 09:31:05 -0700 (PDT) Ted <r.ted.byers@rogers.com> wrote:

T> She is a gem, isn't she?!!

Abigail's gender should be in the FAQ :)

T> I learned plenty about the perl side of things from her posts. And I
T> found Ben's last example, using undef to ignore certain values,
T> interesting. But his first example remains problematic. Between the
T> two, and others, I can see some logic in choosing undef as a pel
T> primitive to represent SQL NULLS, but it seems to me that with the
T> support now available for object oriented programming in perl, it
T> would have been relatively trivial to provide a new class to more
T> closely represent the SQL idea of a null. When I did it in Java, all
T> I had to do was add a private data member of type boolean, and then
T> check this data member in those member functions where it is relevant
T> that override functions provided in the relevant base classes.

Objects in Perl are not always the right solution. undef takes a lot
less memory than a blessed object, for example, and it's faster. Also,
it's annoying if you just want to deal with values (compare with Java's
painfully horrible Integer vs. int hack, repeated for a bunch of
primitives).

Like I said, it's a compromise. By definition some people won't like
it. I think it's the right thing in the context of Perl, and obviously
most people like the DBI as it is or it would have been changed long
ago. I would address it by adding a DBI function is_null() that can be
attached to a row somehow, so you can say

while (my $row = ...)
{
if (is_null($row, $column)) { ... } # doesn't matter how is_null is invoked
}

T> BTW: having read the thread, you see where Ben and I seem to be
T> butting heads. Can you point me to places in the Perl reference that
T> substantiate his perspective on the points where we disagree, and
T> which provide a fuller explanation of his point of view?

I'm not sure what exactly you're asking. I don't think Ben said
anything wrong, and I don't think (but could be wrong) there's a
specific reference document on the topic you were discussing. There's
bits and pieces all over. I think Ben would have pointed you to the
right document already if it was in one place.

Ted

Report this message

#46: Re: use of DBI; I am getting multiple error messages mixed in with

Posted on 2008-04-24 21:57:09 by Ted Byers

On Apr 24, 2:23=A0pm, Ted Zlatanov <t...@lifelogs.com> wrote:
> On Thu, 24 Apr 2008 09:31:05 -0700 (PDT) Ted <r.ted.by...@rogers.com> wrot=
e:
>
> T> She is a gem, isn't she?!!
>
> Abigail's gender should be in the FAQ :)
>
Must be a cultural thing. I have always seen the name Abigail used
for women.

My apologies to Abigail!

> T> I learned plenty about the perl side of things from her posts. =A0And I=

> T> found Ben's last example, using undef to ignore certain values,
> T> interesting. =A0But his first example remains problematic. =A0Between t=
he
> T> two, and others, I can see some logic in choosing undef as a pel
> T> primitive to represent SQL NULLS, but it seems to me that with the
> T> support now available for object oriented programming in perl, it
> T> would have been relatively trivial to provide a new class to more
> T> closely represent the SQL idea of a null. =A0When I did it in Java, all=

> T> I had to do was add a private data member of type boolean, and then
> T> check this data member in those member functions where it is relevant
> T> that override functions provided in the relevant base classes.
>
> Objects in Perl are not always the right solution. =A0undef takes a lot
> less memory than a blessed object, for example, and it's faster. =A0Also,
> it's annoying if you just want to deal with values (compare with Java's
> painfully horrible Integer vs. int hack, repeated for a bunch of
> primitives).
>
;-)

If I were inclined to obstructionist, I could waste a lot of time
criticizing each of the languages I use. There are lots of things in
Java I'd love to change if I could, but .... And even Stroustrup, the
primary inventor of C++, has described a number of things he'd have
done differently. But then nothing is perfect, and it is important to
understand the rationale behind the compromises that have been made in
each.

> Like I said, it's a compromise. =A0By definition some people won't like
> it. =A0I think it's the right thing in the context of Perl, and obviously
> most people like the DBI as it is or it would have been changed long
> ago. =A0I would address it by adding a DBI function is_null() that can be
> attached to a row somehow, so you can say
>
> while (my $row =3D ...)
> {
> =A0if (is_null($row, $column)) { ... } # =A0doesn't matter how is_null is =
invoked
>
> }
>
Nice idea. But the kind of object I had in mind is about as stripped
down as the smart pointer in C++, or perhaps some of the format
classes in Java.

> T> BTW: having read the thread, you see where Ben and I seem to be
> T> butting heads. =A0Can you point me to places in the Perl reference that=

> T> substantiate his perspective on the points where we disagree, and
> T> which provide a fuller explanation of his point of view?
>
> I'm not sure what exactly you're asking. =A0I don't think Ben said
> anything wrong, and I don't think (but could be wrong) there's a
> specific reference document on the topic you were discussing. =A0There's
> bits and pieces all over. =A0I think Ben would have pointed you to the
> right document already if it was in one place.
>
There seem to be two key disagreements. In the book I've relied on
for Perl Programming, by Wall et al., the description of undef they
give seems to be to be a near perfect definition of what is true of an
uninitialized variable in, e.g., C++ or Java. His treatment of it,
though, implies that what I read in Wall et al.'s book is either wrong
or misleading. He objected most strenuously to the notion that it
refers to an uninitialized variable, even though his first example
clearly used an uninitialized variable to illustrate the idea of
undef. The second is that he maintains that the empty string is the
idea of a defined null, and that that is something Wall et al. were
referring to. But I have found nothing in that book that suggests
that in perl the empty string is the same thing as a defined null
string. Wall et al. don't say what they mean by a defined null
string, but then they say nothing that implies that they use that term
to mean an empty string either. I have five books on Perl, include
that by Wall et al., along with the documentation that came with
ActiveState's build of version 5.8.8, and none mentions anything would
either supports or explains his position on either of these two
issues, at least as far as I've been able to progress through them.

I don't have a problem regarding the empty string as a defined null
string, if that's the way things are defined in Perl, but I have not
seen such a definition except in what Ben wrote. For that, I was just
hoping to be pointed to a page in the perl reference that states this
explicitly. Either way, I can write code that means what I intend.
Had Ben just pointed me to the right page in the perl reference that
gives, and explains the rational behind, this definition, I would have
thanked him and said nothing more on the matter.

On the matter of undefined, though. Wall et al. say that an undefined
variable is one that does not presently hold a valid string, numeric
or reference value. The only way I see that happening is if the
variable is not initialized. Ben's first example, in fact, then uses
an uninitialized variable to illustrate the idea of undefined. And
then I learn in this thread, that perl silently, implicitly does to
undefined variables what I do explicitly in my C++ and Java code when
a valid value in not yet known or available at the point of the
declaration. And Ben maintains that this is not focussed on
uninitialized variables. Do you see the contradiction between Wall et
al.'s description of undefined variables and their behaviour in Ben's
first example on the one hand and Ben's insistence that they are not
uninitialized variables on the other? Or at least why someone coming
to perl programming from extensive experience with languages like C++
and Java would see this as a contradiction? Surely this is something
that needs explanation, all the better if reference, and comparison,
is made to how uninitialized variables are handled in other
programming languages for the benefit of those of us trying to learn
Perl who have an extnesive hisotyr with other programming languages.

That you saw nothing wrong in what Ben wrote would suggest to me that
he is drawing on a common belief in the perl programming community,
and it seems to me that there remain things here to be explained in
more detail.

Thanks

Ted

Report this message

#47: Re: use of DBI; I am getting multiple error messages mixed in with the correct output.

Posted on 2008-04-24 23:36:58 by Ted Zlatanov

On Thu, 24 Apr 2008 12:57:09 -0700 (PDT) Ted <r.ted.byers@rogers.com> wrote:

T> He objected most strenuously to the notion that [undef] refers to an
T> uninitialized variable, even though his first example clearly used an
T> uninitialized variable to illustrate the idea of undef. The second
T> is that he maintains that the empty string is the idea of a defined
T> null, and that that is something Wall et al. were referring to.

I'll illustrate with code.

use Data::Dumper;

my $null; # uninitialized variable
my $one = 1;
my $formerly_defined = 1;
undef $formerly_defined;
foreach my $check (1, 0, '0', '0.00', '', $null, $one, undef, $formerly_defined)
{
print $check ? 'true' : 'false';
print ', ';
print defined $check ? 'defined' : 'not defined';
print ', ';
print 'value = ', Dumper($check);
}

This produces:

true, defined, value = $VAR1 = 1;
false, defined, value = $VAR1 = 0;
false, defined, value = $VAR1 = '0';
true, defined, value = $VAR1 = '0.00';
false, defined, value = $VAR1 = '';
false, not defined, value = $VAR1 = undef;
true, defined, value = $VAR1 = 1;
false, not defined, value = $VAR1 = undef;
false, not defined, value = $VAR1 = undef;

See how $formerly_defined has been initialized to 1, then undefined.
It has been set twice, the second time to the undefined value. This is,
I think, what Ben meant.

I don't know if this is in a single place in the docs, but see `perldoc
perltrap' `perldoc perlsyn' `perldoc perlnumber' among others.

T> I don't have a problem regarding the empty string as a defined null
T> string, if that's the way things are defined in Perl

Basically the rules above are what they are, and are essential to Perl's
DWIM. The DBI, to get back to the point of the thread, does the best it
can with what's available. You can't get an undef back unless the value
is NULL, so really your problem is in the further treatment of the undef
vs. how SQL would treat the NULL. That Perl behavior is not likely to
change in the future, so either use the undef and know the effects, or
wrap it in your own classes that do the right thing for your needs.

T> That you saw nothing wrong in what Ben wrote would suggest to me that
T> he is drawing on a common belief in the perl programming community,
T> and it seems to me that there remain things here to be explained in
T> more detail.

I don't think it's productive to direct the argument at Ben. Say what's
still unclear and we'll clear it up. Code always is more precise than
words, so ask about code if possible.

Ted

Report this message

#48: Re: use of DBI; I am getting multiple error messages mixed in with?the correct output.

Posted on 2008-04-25 00:31:55 by Martijn Lievaart

On Thu, 24 Apr 2008 14:10:08 +0000, Ben Bullock wrote:

> Ted <r.ted.byers@rogers.com> wrote:
>
>>> 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.
>
> Excuse me, this should say '$b is a "defined null string"' not "real".

No, $b is an empty string. Certainly not a null string. There is no such
thing as a null string in perl. C++ has a null string concept IIRC, but
Java and C don't, they allow the variable to be set to NULL, which in
perl is setting it to undef.

M4

Report this message