Is this, or is it not an SQR bug?

[Scott Kaye <Scott_Kaye@PEOPLESOFT.COM>]

Hi all-
     I'm a long time lurker on this list, but I'm having a problem with SQR
and SQRIBE.  Last week, I was getting an Invalid date, time, or timestamp
value when trying to insert a null into a date field from the MVS/DB2
version of SQR (4.1.1).  The same program worked fine when run from SQRW on
my Windows 95 desktop.  SQRIBE tech support sent me a workaround for my
problem (I've attached the text below), but they don't consider the fact
that you can't directly insert a NULL variable into DB2 from the MVS
version of SQR a bug.  Personally I feel that it is a bug, that you
shouldn't have to add the extra code listed below in order to insert a Null
value into DB2 from MVS.  Please, let me know if I'm the only one who feels
this way.  Does anyone have a contact at SQRIBE where I can voice my
opinion?

Scott Kaye
scott_kaye@peoplesoft.com

-------------------------------

Hi Scott,

These is the explanation from our developer. Hope this helps:

In essence, in previous releases of SQR for DB2, SQR did not account for
empty
strings; that is, it treated all "empty" $variables as nulls not empty
strings. For example:

   move '' to $string
   begin-sql
      insert into tbl1 values ($string)
   end-sql

This is no longer valid because when bound, $string is treated as an empty
string, not a null value.
There are several ways to work around this problem. The first is as
follows:

    begin-setup
       declare-variable
           date $date1
       end-declare
    end-setup
    ...
    move '' to $date1
    begin-sql
       insert into tbl1 values ($date1)
    end-sql

This works because there is no such thing as an empty date, so it must be
null.
Keep in mind, this will only work if the column is a timestamp (not a
date or time); that is, date variables are converted to strings in a
timestamp
format during a bind.

Another method is to build the value on the fly and use the word "NULL"
when a
value is null.
For example,

  if  $string = ''
     move 'NULL' to $value
  else
     let $value = '''' || $string || ''''
   end-if

  begin-sql
     insert into tbl1 values (\$value\)    ! Use this for AS400 or MVS
     insert into tbl1 values ([$value])    ! All others use this
  end-sql

Thank you
Regards,
Kamila




--
*******************************************
Kamila Pavlaskova  SQRIBE Technical Support

Email: kamilap@sqribe.com
Phone: (562) 436-7489   (800) 437-1663
Fax:   (562) 436-3422
*******************************************