sybperl-l Archive

From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: Inserts & nulls using sybperl
Date: Jun 7 2002 4:09PM

On Fri, 2002-06-07 at 08:47, Jeffries, Jim J wrote:
> 
> I am trying to do a table conversion using Sybperl's ctlib functions. The source table has fields that are NULL that must be moved to the new table. I can successfully read the source table. The elements of the array returned from the select clause have a length of 0 for the null fields (as I would expect). 
> 
> When I try to do the insert into the new table, I am unable to get these fields to be NULL. My guess is that I am either doing the insert incorrectly or a parameter of some sort needs to be set. I even tried to do the insert without the variable and still do not get a null value in the database. 

> Code snippet from Insert ($dat1[1] can be NULL)
> 
>  $dbh2->ct_execute("insert t_7474_ipcfimw1_pg  (chap_num, sect_num, unit_num, figure_num,pg_prfx, pg_num, pg
> _sufx,supp_num,supp_pg_num,pg_src,pg_type_name,strt_pn,end_pn,strt_ref,end_ref,o_hostid,o_typeid,o_objid, o_rev
> id,o_rowid, del_flag,del_usr,fig_item) values(\"$dat1[0]\",\"$dat1[1]\",\"$dat1[2]\",\"$dat1[3]\",\"$dat1[4]\",
> $dat1[5],\"$dat1[6]\",$dat1[8],$dat1[9],\"$dat1[11]\",\"$dat1[10]\",\"$dat1[12]\",\"$dat1[13]\",\"$dat1[14]\",\
> "$dat1[15]\",$dat1[16],\"$dat1[17]\",$dat1[18],$dat1[19],\"$dat1[20]\",\"$dat1[21]\",\"$dat1[22]\",\"$dat1[7]\"
> )");

Time for a little didactic course, I think :-)

How do you insert a NULL in a plain isql statement?

1> insert ... values(NULL, ...)

So you need to do the same thing.

Now your insert statement has additional *potential* problems, depending
on whether the source data could contain double quotes.

To protect against that possibility, and to handle NULL correctly, I
sometimes use a quote() subroutine, which could go like this:

sub quote {
    my $data = shift;
    my $need_quote = shift;

    if(!defined($data)) {
        $data = 'NULL';
    } elsif($need_quote) {
        $data =~ s/\"/""/g;
    }
    
    return $data;
}

And you call this for each of your parameters, passing a second argument
of TRUE (1) if this is a string variable.

Off the top of my head I think I'd do something like this:

my @need_quote = (1) x 23;     # create an array of 23 '1' values
$need_quote[8] = 0;
$need_quote[9] = 0;            # these two don't need quotes - there may
                               # be others - I didn't check thoroughly.
for(my $i = 0; $i < 23; ++$i) {
    $dat1[$i] = quote($dat1[$i], $need_quote[$i]);
}

# Use qq() to quote the string - avoid's having to escape the
# quotes!

$dbh2->ct_execute(qq(
insert t_7474_ipcfimw1_pg  (chap_num, sect_num, unit_num,
   figure_num,pg_prfx, pg_num, pg_sufx,supp_num,supp_pg_num,    
pg_src,pg_type_name,strt_pn,
end_pn,strt_ref,end_ref,o_hostid,o_typeid,o_objid, o_revid,o_rowid,
del_flag,del_usr,fig_item)
values($dat1[0],"",$dat1[2],$dat1[3],$dat1[4],$dat1[5],
$dat1[6],$dat1[8],$dat1[9],$dat1[11],$dat1[10],$dat1[12],
$dat1[13],$dat1[14],$dat1[15],$dat1[16],$dat1[17],$dat1[18],
$dat1[19],$dat1[20],$dat1[21],$dat1[22],$dat1[7])
));

etc.

Hopefully this will clarify the issues for you.

Michael
-- 
Michael Peppler / mpeppler@peppler.org / http://www.mbay.net/~mpeppler
mpeppler@zetatools.com / ZetaTools, Inc / http://www.zetatools.com
ZetaTools: Call perl functions as Sybase stored procedures!