'' != NULL for great sadness

20 messages

'' != NULL for great sadness

Reply Threaded

Hey guys,

So I just ran into this yesterday. I don't know how I didn't see it before. Anyway, if I have a field that is a nullable integer I am told that '' should automatically be converted to a NULL. MSSQL does this for me when I type the query directly into the Management Studio, but it doesn't when I am using DBIx::Class. Here is the error I am getting:

DBI Exception: DBD::ODBC::st execute failed: [Microsoft][SQL Native Client]Invalid character value for cast specification (SQL-22018) [for Statement "UPDATE QCParts SET fixture_id = ? WHERE ( id = ? )" with ParamValues: 1='', 2='001A215A1820000'] at c:/Documents and Settings/frew/My Documents/Code/aircraft_ducting/local/lib/DBIx/Class/Schema.pm line 1025

fixture_id should be an integer. Does anyone know what I can do to fix this issue?

--
fREW Schmidt
http://blog.afoolishmanifesto.com

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Mike South

Re: '' != NULL for great sadness

Reply Threaded

First thing I would try is going to

http://search.cpan.org/~timb/DBI/DBI.pm

search page for NULL, see if that helps.

mike

On Thu, Jul 9, 2009 at 9:22 AM, fREW Schmidt<[hidden email]> wrote:

> Hey guys,
>
> So I just ran into this yesterday. I don't know how I didn't see it
> before. Anyway, if I have a field that is a nullable integer I am told that
> '' should automatically be converted to a NULL. MSSQL does this for me when
> I type the query directly into the Management Studio, but it doesn't when I
> am using DBIx::Class. Here is the error I am getting:
>
> DBI Exception: DBD::ODBC::st execute failed: [Microsoft][SQL Native
> Client]Invalid character value for cast specification (SQL-22018) [for
> Statement "UPDATE QCParts SET fixture_id = ? WHERE ( id = ? )" with
> ParamValues: 1='', 2='001A215A1820000'] at c:/Documents and Settings/frew/My
> Documents/Code/aircraft_ducting/local/lib/DBIx/Class/Schema.pm line 1025
>
> fixture_id should be an integer. Does anyone know what I can do to fix this
> issue?
>
> --
> fREW Schmidt
> http://blog.afoolishmanifesto.com
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@...
>

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

fREW Schmidt

Re: '' != NULL for great sadness

Reply Threaded

On Thu, Jul 9, 2009 at 9:36 AM, Mike South <[hidden email]> wrote:

First thing I would try is going to

http://search.cpan.org/~timb/DBI/DBI.pm

search page for NULL, see if that helps.

I looked through it and didn't see anything. Someone told me it is a function of the DBD so I am looking through DBD::ODBC, but so far, no luck.

--
fREW Schmidt
http://blog.afoolishmanifesto.com

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Ryan Cone

Re: '' != NULL for great sadness

Reply Threaded

Some javascript/style in this post has been disabled (why?)

NULL is not the same as '' (blank) in MSSQL. The database is complaining that it cannot cast '' to an integer.

If you want to set it to NULL, then you can't set it to blank.

-Ryan

On Jul 9, 2009, at 11:00 AM, fREW Schmidt wrote:

On Thu, Jul 9, 2009 at 9:36 AM, Mike South <[hidden email]> wrote:
First thing I would try is going to

http://search.cpan.org/~timb/DBI/DBI.pm

search page for NULL, see if that helps.

I looked through it and didn't see anything. Someone told me it is a function of the DBD so I am looking through DBD::ODBC, but so far, no luck.

--
fREW Schmidt
http://blog.afoolishmanifesto.com
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Joel Bernstein

Re: '' != NULL for great sadness

Reply Threaded

In reply to this post by fREW Schmidt

On 9 Jul 2009, at 16:00, fREW Schmidt wrote:

> On Thu, Jul 9, 2009 at 9:36 AM, Mike South <[hidden email]> wrote:
> First thing I would try is going to
>
> http://search.cpan.org/~timb/DBI/DBI.pm
>
> search page for NULL, see if that helps.
>
> I looked through it and didn't see anything. Someone told me it is
> a function of the DBD so I am looking through DBD::ODBC, but so far,
> no luck.

He gave you a pointer to the answer already.
Did you look at every occurrence of NULL on the DBI.pm POD?

I just searched the page for NULL and found the relevant info that you
needed regarding how NULL values are represented to Perl and thus how
to enter NULLs into the database. Do you really not see it?

/joel

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

fREW Schmidt

Re: '' != NULL for great sadness

Reply Threaded

On Thu, Jul 9, 2009 at 10:11 AM, Joel Bernstein <[hidden email]> wrote:

On 9 Jul 2009, at 16:00, fREW Schmidt wrote:

On Thu, Jul 9, 2009 at 9:36 AM, Mike South <[hidden email]> wrote:
First thing I would try is going to

http://search.cpan.org/~timb/DBI/DBI.pm

search page for NULL, see if that helps.

I looked through it and didn't see anything. Someone told me it is a function of the DBD so I am looking through DBD::ODBC, but so far, no luck.

He gave you a pointer to the answer already.
Did you look at every occurrence of NULL on the DBI.pm POD?

I just searched the page for NULL and found the relevant info that you needed regarding how NULL values are represented to Perl and thus how to enter NULLs into the database. Do you really not see it?

Oh yeah, I saw that for sure. I was hoping for some kind of setting that I could set that would convert '' to null in fields that aren't strings. I gave up and did this:

method fix_nullable($param) {
   if ($param eq '' ) {
      return undef;
   }
}

my $params = $self->query->Vars;
$params->{$_} = $self->fix_nullable($params->{$_}) for (qw{
      fixture_amount fixture_id photo_id photo_neg_id });

--
fREW Schmidt
http://blog.afoolishmanifesto.com

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Chisel Wright

Re: '' != NULL for great sadness

Reply Threaded

On Thu, Jul 09, 2009 at 10:17:46AM -0500, fREW Schmidt wrote:
> Oh yeah, I saw that for sure.� I was hoping for some kind of setting that
> I could set that would convert '' to null in fields that aren't strings.�

Erk! Each to their own.

Where are these empty strings coming from in the first place? Ideally,
and unsurprisingly, I'd much rather have "undef" for "a field value that
hasn't been defined".

To me, and presumably others, '' (blank) means "we know what it is, it's
nothingness".

Can't the incoming data be "fixed" instead of kludging your queries?
--
Chisel Wright
e: [hidden email]
w: http://www.herlpacker.co.uk/

Why is the rum gone?

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

fREW Schmidt

Re: '' != NULL for great sadness

Reply Threaded

On Thu, Jul 9, 2009 at 4:34 PM, Chisel Wright <[hidden email]> wrote:

On Thu, Jul 09, 2009 at 10:17:46AM -0500, fREW Schmidt wrote:
> Oh yeah, I saw that for sure.� I was hoping for some kind of setting that

> I could set that would convert '' to null in fields that aren't strings.�

Erk! Each to their own.

Where are these empty strings coming from in the first place? Ideally,
and unsurprisingly, I'd much rather have "undef" for "a field value that
hasn't been defined".

To me, and presumably others, '' (blank) means "we know what it is, it's
nothingness".

Can't the incoming data be "fixed" instead of kludging your queries?

Well that's what I'm doing. I'm fixing it. Iterating over given values and changing them from blanks to undefs. If you know a way to do that with javascript (http posts) let me know, but as far as I know it will submit blanks for blank fields.

Paul Makepeace

Re: '' != NULL for great sadness

Reply Threaded

On Thu, Jul 9, 2009 at 4:47 PM, fREW Schmidt<[hidden email]> wrote:

> On Thu, Jul 9, 2009 at 4:34 PM, Chisel Wright <[hidden email]>
> wrote:
>>
>> On Thu, Jul 09, 2009 at 10:17:46AM -0500, fREW Schmidt wrote:
>> > Oh yeah, I saw that for sure.� I was hoping for some kind of setting
>> > that
>> > I could set that would convert '' to null in fields that aren't
>> > strings.�
>>
>> Erk! Each to their own.
>>
>> Where are these empty strings coming from in the first place? Ideally,
>> and unsurprisingly, I'd much rather have "undef" for "a field value that
>> hasn't been defined".
>>
>> To me, and presumably others, '' (blank) means "we know what it is, it's
>> nothingness".
>>
>> Can't the incoming data be "fixed" instead of kludging your queries?
>
> Well that's what I'm doing. I'm fixing it. Iterating over given values and
> changing them from blanks to undefs. If you know a way to do that with
> javascript (http posts) let me know, but as far as I know it will submit
> blanks for blank fields.

Why do you want them to be NULL? As a general rule, unless you have a
specific requirement for NULL they're best avoided (see discussion
passim). Even when you think you have all the bases covered there's
some weird case where they turn out to be a PITA later...

Paul

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Matt Whipple

Re: '' != NULL for great sadness

Reply Threaded

Paul Makepeace wrote:

> On Thu, Jul 9, 2009 at 4:47 PM, fREW Schmidt<[hidden email]> wrote:
>
>> On Thu, Jul 9, 2009 at 4:34 PM, Chisel Wright <[hidden email]>
>> wrote:
>>
>>> On Thu, Jul 09, 2009 at 10:17:46AM -0500, fREW Schmidt wrote:
>>>
>>>> Oh yeah, I saw that for sure.� I was hoping for some kind of setting
>>>> that
>>>> I could set that would convert '' to null in fields that aren't
>>>> strings.�
>>>>
>>> Erk! Each to their own.
>>>
>>> Where are these empty strings coming from in the first place? Ideally,
>>> and unsurprisingly, I'd much rather have "undef" for "a field value that
>>> hasn't been defined".
>>>
>>> To me, and presumably others, '' (blank) means "we know what it is, it's
>>> nothingness".
>>>
>>> Can't the incoming data be "fixed" instead of kludging your queries?
>>>
>> Well that's what I'm doing. I'm fixing it. Iterating over given values and
>> changing them from blanks to undefs. If you know a way to do that with
>> javascript (http posts) let me know, but as far as I know it will submit
>> blanks for blank fields.
>>

HTML::FormFu does this I think (treats empty as undefined).
>
> Why do you want them to be NULL? As a general rule, unless you have a
> specific requirement for NULL they're best avoided (see discussion
> passim). Even when you think you have all the bases covered there's
> some weird case where they turn out to be a PITA later...
>
> Paul
>
I think the danced around suggestion is that you remove those fields
from the query entirely and let the database system handle it implicitly.

> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@...
>

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

fREW Schmidt

Re: '' != NULL for great sadness

Reply Threaded

In reply to this post by Paul Makepeace

Why do you want them to be NULL? As a general rule, unless you have a
specific requirement for NULL they're best avoided (see discussion
passim). Even when you think you have all the bases covered there's
some weird case where they turn out to be a PITA later...

How is that the case? It seems like an undefined value makes perfect sense to allow users to specify.

fREW Schmidt

Re: '' != NULL for great sadness

Reply Threaded

In reply to this post by Matt Whipple

On Thu, Jul 9, 2009 at 5:02 PM, Matt Whipple <[hidden email]> wrote:

Paul Makepeace wrote:

On Thu, Jul 9, 2009 at 4:47 PM, fREW Schmidt<[hidden email]> wrote:

On Thu, Jul 9, 2009 at 4:34 PM, Chisel Wright <[hidden email]>
wrote:

On Thu, Jul 09, 2009 at 10:17:46AM -0500, fREW Schmidt wrote:

Oh yeah, I saw that for sure.� I was hoping for some kind of setting
that
I could set that would convert '' to null in fields that aren't
strings.�

Erk! Each to their own.

Where are these empty strings coming from in the first place? Ideally,
and unsurprisingly, I'd much rather have "undef" for "a field value that
hasn't been defined".

To me, and presumably others, '' (blank) means "we know what it is, it's
nothingness".

Can't the incoming data be "fixed" instead of kludging your queries?

Well that's what I'm doing. I'm fixing it. Iterating over given values and
changing them from blanks to undefs. If you know a way to do that with
javascript (http posts) let me know, but as far as I know it will submit
blanks for blank fields.

HTML::FormFu does this I think (treats empty as undefined).

This is all in javascript. I'm sure I could just use formfu for the receiving end, but I've been pretty happy with Data::FormValidator.

Why do you want them to be NULL? As a general rule, unless you have a
specific requirement for NULL they're best avoided (see discussion
passim). Even when you think you have all the bases covered there's
some weird case where they turn out to be a PITA later...

Paul

I think the danced around suggestion is that you remove those fields from the query entirely and let the database system handle it implicitly.

That doesn't work. If the user is trying to change a field with 5 to null how could they do it if I removed it from the update?

Anyway, Thanks for all of your responses guys, but I think that my handling it in the controller (or per field in the model) will work fine for now.

Darren Duncan

Re: '' != NULL for great sadness

Reply Threaded

In reply to this post by fREW Schmidt

fREW Schmidt wrote:
> Why do you want them to be NULL? As a general rule, unless you have a
> specific requirement for NULL they're best avoided (see discussion
> passim). Even when you think you have all the bases covered there's
> some weird case where they turn out to be a PITA later...
>
> How is that the case? It seems like an undefined value makes perfect
> sense to allow users to specify.

Sure it makes sense for users to specify that a value is undefined. However,
using a SQL NULL isn't always the best way to record this fact. For one thing,
there could be a variety of reasons why a value is undefined (for example,
"missing" versus "not applicable" versus more specific reasons) but a SQL NULL
wouldn't let you distinguish between them. A better solution is to avoid the
use of SQL NULL and instead represent undefined in some other way, such as by
having an undefined-because meta-data field. -- Darren Duncan

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Paul Makepeace

Re: '' != NULL for great sadness

Reply Threaded

On Thu, Jul 9, 2009 at 5:23 PM, Darren Duncan<[hidden email]> wrote:

> fREW Schmidt wrote:
>>
>> Why do you want them to be NULL? As a general rule, unless you have a
>> specific requirement for NULL they're best avoided (see discussion
>> passim). Even when you think you have all the bases covered there's
>> some weird case where they turn out to be a PITA later...
>>
>> How is that the case? It seems like an undefined value makes perfect
>> sense to allow users to specify.
>
> Sure it makes sense for users to specify that a value is undefined.
> However, using a SQL NULL isn't always the best way to record this fact.
> For one thing, there could be a variety of reasons why a value is undefined
> (for example, "missing" versus "not applicable" versus more specific
> reasons) but a SQL NULL wouldn't let you distinguish between them. A better
> solution is to avoid the use of SQL NULL and instead represent undefined in
> some other way, such as by having an undefined-because meta-data field. --
> Darren Duncan

Yes indeed.
There is copious discussion online and in learned texts about how and
why NULLs can spoil your day.
http://www.bennadel.com/blog/85-Why-NULL-Values-Should-Not-Be-Used-in-a-Database-Unless-Required.htm

DEFAULT '' is almost always the better way.

P

>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@...
>

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Aaron Schrab

Re: '' != NULL for great sadness

Reply Threaded

In reply to this post by fREW Schmidt

At 17:17 -0500 09 Jul 2009, fREW Schmidt <[hidden email]> wrote:
>This is all in javascript. I'm sure I could just use formfu for the
>receiving end, but I've been pretty happy with Data::FormValidator.

It's obviously not all in JavaScript, since you're using DBIC. And
relying completely on JS for data validation is an extremely bad idea.

If you're using Data::FormValidator, it should already handle giving you
undef for fields that were submitted as empty. Normally, it won't even
include those fields in the hash of valid data that it returns, so
trying to get the value of the field out of that hash will default to
undef. If you include the missing_optional_valid option in your call to
it, empty fields will be included in the valid hash, but still with
undef as the value.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

fREW Schmidt

Re: Re: '' != NULL for great sadness

Reply Threaded

Yeah, I wrote a small filter for DFV that would translate '' to undef because that is not built in behavior (or at least not according to Stosberg :-) ). I am using missing_optional_valid, but it turns out that that doesn't matter because of how dbix::class::validation works. It doesn't validate the params of update or insert, it validates the state of the object just before saving it to the db.

And yes, clearly I'm not using js for EVERYTHING as I said I was using dfv, dbic, and obviously perl.

Anyway, I'm reading up on what another poster said about nulls in the db. So far I'm not convinced, but give me a while to read up on some of this stuff. I'd appreciate any other, more complete links relating to nulls in the database.

It just seems to me that it make sense to have undefined values in a field, but I need to think through that some more.

Thanks guys.

On Jul 9, 2009 6:59 PM, "Aaron Schrab" <[hidden email]> wrote:

At 17:17 -0500 09 Jul 2009, fREW Schmidt <[hidden email]> wrote: > > This is all in javascript. I...
It's obviously not all in JavaScript, since you're using DBIC. And relying completely on JS for data validation is an extremely bad idea.

If you're using Data::FormValidator, it should already handle giving you undef for fields that were submitted as empty. Normally, it won't even include those fields in the hash of valid data that it returns, so trying to get the value of the field out of that hash will default to undef. If you include the missing_optional_valid option in your call to it, empty fields will be included in the valid hash, but still with undef as the value.
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/lis...

Matt Whipple

Re: Re: '' != NULL for great sadness

Reply Threaded

fREW Schmidt wrote:

>
> Yeah, I wrote a small filter for DFV that would translate '' to undef
> because that is not built in behavior (or at least not according to
> Stosberg :-) ). I am using missing_optional_valid, but it turns out
> that that doesn't matter because of how dbix::class::validation
> works. It doesn't validate the params of update or insert, it
> validates the state of the object just before saving it to the db.
>
> And yes, clearly I'm not using js for EVERYTHING as I said I was using
> dfv, dbic, and obviously perl.
>
> Anyway, I'm reading up on what another poster said about nulls in the
> db. So far I'm not convinced, but give me a while to read up on some
> of this stuff. I'd appreciate any other, more complete links relating
> to nulls in the database.
>
> It just seems to me that it make sense to have undefined values in a
> field, but I need to think through that some more.
>

To me it appears that the aversion to using NULL is caused because it is
being used probably more often than not as a result of sloppiness rather
than design (particularly with the differences with the NULLs), and even
when properly implemented, there can be a negative impact on the
performance of some (all?) DBMS's when tables have columns that allow
NULL values. With char fields the argument gets foggier with NULL vs an
empty string (or the myriad of possible values), but with numeric fields
(as in this case) NULL and 0 (or any other number) mean very different
things conceptually and so far any other suggestion to represent an
unknown value is more complex (quite likely needlessly). Having to deal
with NULLs is going to pop up in outside joins anyway, I don't
understand why intelligent, deliberate use of them elsewhere would be so
besieged. I don't recall seeing such strong opposition in database
systems references...more of just "this probably doesn't work the way
you think it does, make sure you learn how it does work before you use
it"...and I'd add a "and document it".

> Thanks guys.
>
>> On Jul 9, 2009 6:59 PM, "Aaron Schrab" <[hidden email]
>> <mailto:[hidden email]>> wrote:
>>
>> At 17:17 -0500 09 Jul 2009, fREW Schmidt <[hidden email]
>> <mailto:[hidden email]>> wrote: > > This is all in javascript. I...
>>
>> It's obviously not all in JavaScript, since you're using DBIC. And
>> relying completely on JS for data validation is an extremely bad idea.
>>
>> If you're using Data::FormValidator, it should already handle giving
>> you undef for fields that were submitted as empty. Normally, it
>> won't even include those fields in the hash of valid data that it
>> returns, so trying to get the value of the field out of that hash
>> will default to undef. If you include the missing_optional_valid
>> option in your call to it, empty fields will be included in the valid
>> hash, but still with undef as the value.
>>
>> _______________________________________________ List:
>> http://lists.scsys.co.uk/cgi-bin/mailman/lis...
>>
> ------------------------------------------------------------------------
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Ryan Cone

Re: Re: '' != NULL for great sadness

Reply Threaded

In reply to this post by fREW Schmidt

On Jul 9, 2009, at 9:21 PM, fREW Schmidt wrote:
> Anyway, I'm reading up on what another poster said about nulls in
> the db. So far I'm not convinced, but give me a while to read up on
> some of this stuff. I'd appreciate any other, more complete links
> relating to nulls in the database.
>
> It just seems to me that it make sense to have undefined values in a
> field, but I need to think through that some more.
>

Both sides of this allow/disallow NULL issue are well represented
online. I find that we generally pick one camp to fall into and
rationalize our choices accordingly.

For what it's worth, here is my rationalization...When using any ORM,
I strive to create data objects that closely resemble my programmed
ones. When I find myself "needing" a NULL, it often means I have not
really defined my class properly.

From your original example you defined QCParts as having Fixtures.

If a QCPart can exist without a Fixture, then the Fixture does not
belong in the QCPart class. And you might need q_c_part_id in
Fixtures or a Fixtures_QCParts table depending on the reverse
relationship.

If a QCPart cannot exist without a Fixture, then it seems that it
should not be allowed to be unknown but may be allowed to be undefined-
because per Darren Duncan's suggestion.

-Ryan

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

fREW Schmidt

Re: Re: '' != NULL for great sadness

Reply Threaded

I would agree except that fixture_id is not a join field. Its just a product of a legacy database that hasn't had all columns renamed to something sensible.

Here's another fun example: what do you think plane_747 means? I'd have guess a bool for type of plane. It actually means how long is the part. So yeah, ignore col names from my real life examples :-) I'm renaming the cols when I get to them though (and changing structures too) so it should get better.

On Jul 10, 2009 8:16 AM, "Ryan Cone" <[hidden email]> wrote:

On Jul 9, 2009, at 9:21 PM, fREW Schmidt wrote: > > Anyway, I'm reading up on what another poster s...
Both sides of this allow/disallow NULL issue are well represented online. I find that we generally pick one camp to fall into and rationalize our choices accordingly.

For what it's worth, here is my rationalization...When using any ORM, I strive to create data objects that closely resemble my programmed ones. When I find myself "needing" a NULL, it often means I have not really defined my class properly.

>From your original example you defined QCParts as having Fixtures.

If a QCPart can exist without a Fixture, then the Fixture does not belong in the QCPart class. And you might need q_c_part_id in Fixtures or a Fixtures_QCParts table depending on the reverse relationship.

If a QCPart cannot exist without a Fixture, then it seems that it should not be allowed to be unknown but may be allowed to be undefined-because per Darren Duncan's suggestion.

-Ryan
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/lis...

Ryan Cone

Re: Re: '' != NULL for great sadness

Reply Threaded

On Jul 10, 2009, at 9:33 AM, fREW Schmidt wrote:

> I would agree except that fixture_id is not a join field. Its just
> a product of a legacy database that hasn't had all columns renamed
> to something sensible.
>
> Here's another fun example: what do you think plane_747 means? I'd
> have guess a bool for type of plane. It actually means how long is
> the part. So yeah, ignore col names from my real life
> examples :-) I'm renaming the cols when I get to them though (and
> changing structures too) so it should get better.
>

Since fixture_id is an integer it most likely maps to some list, hash,
text file or other data source. If it is a very poorly named count,
then 0 is a suitable default. If it is supposed to be a measurement,
then one integer field cannot accurately describe a measurement since
measurements have units and are sometimes not integers (same goes for
plane_747 unless it's a string I guess).

The question still remains whether a fixture mapping is inherent to a
QCPart. My guess is since it is legacy data, that it is not. Which
would also explain why you wanted NULLs and why some QCParts would not
have Fixtures.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...