PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: "Scott Zetlan" <scottzetlan at aol dot com>
Subject: RE: A patch proposal to the NULL-value problem on blk_rowxfer
Date: Jun 21 2002 1:36PM

Ok, I've confirmed a couple things:

1. The documentation clearly states that SEPARATOR => '|' in the config hash
will allow you to use pipes as separators, when in fact it absolutely has to
be SEPARATOR => '\|'.  I recommend the documentation be changed to include
the '\' character, and a note added to indicate why it's there.

2. The NULL configuration parameter defaults to an empty string -- but not
empty space, so a record like "a,,c" would yield a null value, but "a,
,c" would not (assume SEPARATOR => ',').  You'd get a blank space for
character data or a 0 for integer data.

3. You can set NULL to a regular expression.  I tried NULL =>
'(^[\s]*$|foo)', and fields which were empty/blank along with fields
containing the text "foo" were all passed as null values to the database.
For columns which didn't allow nulls, this condition resulted in an error:
No value or default value available and NULL not allowed.

So the issue isn't whether you get defined vs. undefined values -- although
undef will always produce NULL --  in fact, the code goes something like
this:

my $null_pattern = $self->{_blk_global}->{'NULL'} || '^$';
# Skip a bit, brother...
if (defined ($null_pattern) && $data[$i] =~ /$null_pattern/o) {
    $data[$i] = undef;
}

So if you wanted to get rid of null values entirely, you could pass NULL =>
undef to Sybase::BLK::configure().

How does that sound?

Scott

> -----Original Message-----
> From: owner-SYBPERL-L@list.cren.net
> [mailto:owner-SYBPERL-L@list.cren.net]On Behalf Of Scott Zetlan
> Sent: Thursday, June 20, 2002 5:35 PM
> To: SybPerl Discussion List
> Subject: RE: A patch proposal to the NULL-value problem on blk_rowxfer
>
>
> Hmm -- I seem to recall that Sybase::BCP and Sybase::BLK will both assume
> empty strings are null values unless you set a NULL_PATTERN option during
> config.  However, I will have to check on that.
>
> Scott
>
> > -----Original Message-----
> > From: owner-SYBPERL-L@list.cren.net
> > [mailto:owner-SYBPERL-L@list.cren.net]On Behalf Of Michael Peppler
> > Sent: Thursday, June 20, 2002 5:21 PM
> > To: SybPerl Discussion List
> > Subject: Re: A patch proposal to the NULL-value problem on blk_rowxfer
> >
> >
> > On Thu, 2002-06-20 at 08:53, Masatsuyo Takahashi wrote:
> > >
> > > To summarize,
> > > [Rule1] undef is the only value that is converted to NULL.
> > > [Rule2] defined values are always also defined values in Sybase
> >
> > I think that that is consistent.
> >
> > > You have to be aware of the fact that you get  DEFINED values
> > > (i.e. ""s) when you split a CSV line such as
> > > ,,,,,
> > > which doesn't seem to define any values.
> >
> > Indeed. I *think* that Sybase::BCP and/or Sybase::BLK will do the
> > "right" thing here, but I may be wrong.
> >
> > Scott?
> >
> > Michael
> >
>
>