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

Posted by xhoster on April 23, 2008, 9:39 pm

Please log in for more thread options

> On Apr 23, 6:18=A0pm, xhos...@gmail.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.

Posted by Ted on April 23, 2008, 10:14 pm

Please log in for more thread options

On Apr 23, 9:39=A0pm, xhos...@gmail.com wrote:

> > On Apr 23, 6:18=3DA0pm, xhos...@gmail.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=

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

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

Posted by Peter J. Holzer on April 26, 2008, 5:55 am

Please log in for more thread options

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

Why should it? That's not specific to mysql. It is generic DBI behaviour
and therefore documented in perldoc DBI.

hp

Posted by Dr.Ruud on April 24, 2008, 12:50 pm

Please log in for more thread options

Ted schreef:

> So I maintain this IS a DBI issue,