Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

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!

insert t_7474_ipcfimw1_pg  (chap_num, sect_num, unit_num,
   figure_num,pg_prfx, pg_num, pg_sufx,supp_num,supp_pg_num,    
end_pn,strt_ref,end_ref,o_hostid,o_typeid,o_objid, o_revid,o_rowid,


Hopefully this will clarify the issues for you.

Michael Peppler / / / ZetaTools, Inc /
ZetaTools: Call perl functions as Sybase stored procedures!