Re: Updating from &variables

[David Donnelly <Dave@ISISBIO.COM>]

Dear Rick,

The whole problem of empty variables and whether they are "null" or contain zero-length strings is a difficult and platform-dependent one.

In Oracle, with SQL*Net and not ODBC, I have never had any problem: when I pass a value that you would consider "null" because it was returned from a null database value, Oracle understands that it is null and correctly sets it so. I think this is because (at least with varchar2 types through Oracle 7), zero-length strings are not recorded in the database.

Ingres does not.  I have often ended up with zero-length strings in character variables. It seems to work OK for numeric type; I've never ended up with zero (or anything else) in a field that should have been null.

I don't have any experience with ODBC on either platform. I am a little surprised that &variables and $variables work differently.  What Sqribe meant with the remark about nulls was a little confusing, as I think they were talking about the ASCII character "nul" (value 00) and not the concept of a null value in a database field.  In at least the Oracle implementation, strings are implemented as ordinary C character strings, which means that they are terminated by the character nul (00).  Character-type &variables seem to be initialized to the null string, and there is no way to differentiate between variables that are "empty" because the query has never been executed, because the returned value was "null", or other more subtle situations (e.g. substr(field,...)) where the field contained something but the substring evaluates to "nothing" ... is that "null" or a zero-length string or what?).


Two workarounds come to mind.  When performance is not an issue, I do this:

if isnull(&col) ! or maybe if (length(&col) = 0)
        let $var = 'null'
else
        let $var = '''' || &col || ''''
end-if
 - - -
update tablename set fieldname = [$var]

This works, but will be recompiled every time it's executed, with the accompanying performance hit.

If you've got a lot of rows, but the data isn't needed until later (I mean you're not doing a multi-user thing where someone could fetch your new row the next instant) then you might be able just to do whatever you do now, and fix the data later by something like

update tablename set fieldname = null where length(fieldname) = 0

Hope this helps.

Dave

At 10:46 AM 7/8/1999 -0700, you wrote:
>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.
>