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: Martin Rieder <Martin dot Rieder at morganstanley dot com>
Subject: Re: Inserts & nulls using sybperl
Date: Jun 7 2002 4:06PM


I am not sure if this also applies to inserts, but I experienced a similar problem using the bulk copy routines, only my problem was setting numeric values to NULL.  Try explicitly setting the values that should be NULL to undef in @dat1.

Hope this helps,

"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 SELECT
>  while ($dbh1->ct_results($restype) == CS_SUCCEED) {
>      next if(! $dbh1->ct_fetchable($restype));
>      while (@dat1 = $dbh1->ct_fetch) {
>        print "Database Fields: $dat1[0]\t   $dat1[1]\t   $dat1[2]  $dat[22] \n";
>         $xx = length($dat1[1]);
>         print "$xx \n";
>         &write_table;
>  }
> 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]\"
> )");
> Code snippet from Insert ($dat1[1] changed to "")
>    $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_revi
> d,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]\")");
> Thanks for your help.
> Jim