Updating from &variables

["Shaver, Richard H" <richard.h.shaver@LMCO.COM>]

I received the following missive from a very persistent programmer.  I would
like to know if anybody else is aware of this problem or has any comments
regarding it.
TIA
Rick


I have discovered that updating records in SQL Server 6.5 using Visual
Sqribe 4.3 with Microsoft ODBC drivers can corrupt records.  This happens
when a SQR - SQL procedure updates a field in a record with a Null value
using an &variable.
For example, the following SQR will corrupt the Equipment table record.  The
XACTION record, (xkey = '100001'), has a Null value in PPERMAREA and the
procedure will update EQ5 to that value in PPERMAREA using &PAREA
BEGIN-PROGRAM
BEGIN-SELECT
PPERMAREA               &PAREA
                FROM XACTION
                WHERE XKEY = '100001'
END-SELECT
BEGIN-SQL
UPDATE EQUIPMENT
        SET DESCRIPTION = 'TESTED SQR UPDATE', EQ5 = &PAREA WHERE EQNUM =
'115274';
COMMIT
END-SQL
END-PROGRAM


After executing this SQR if I try to view the record using  Enterprise
Manager (iSQL) all the fields following EQ5 in the select statement appear
to be blank.  But they are not blank.  If I put the fields that appear blank
before EQ5 in the select statement, then I can see their contents.
When I examine the contents of a corrupted EQ5, using the statement "Select
ASCII(SUBSTRING(EQ5,1,1)), a zero is displayed.  If I execute this statement
against a record that has not been corrupted and has a Null EQ5,  I get
<null>, not a zero.  I know the ASCII representation of Null is zero but....
I contacted Sqribe and they said "SQR has no concept of Nulls".  (Huh? -
what is the IsNull function for then?) They said that we should use an SQL
Server function to test for a Null value, store some character in a program
defined variable to indicate Null, (i.e.  $variable), then when updating or
storing test that variable for the character indicating Null and use an SQL
Server function to actually store a Null value.
Whew, I don't think I'd like having to do that for every field.
I found that by first moving the &variable to a $variable, and then updating
the record using the $variable, that the Null value is stored correctly and
the record is not corrupted.
When I try to run a SQL Update procedure within SQR against the corrupted
records I get an SQR error "003735, Could not execute SQL"  - but the same
statement works against records that were not corrupted.  I find this kind
of weird since the error description says the error occured while trying to
compile the statement.
I'm going to using the $variable method unless someone has a better idea.