Re: Updating from &variables

[Juan Alvarado <juan@GYSSA.COM.GT>]

We use sqr for sybase and odbc and what we saw thru our expirence en
both plataforms is the odbc have parameters that if you dont configure
correctly with your application, show problems that affect the correct
function of the system or in the best of cases show problems of the
sintaxis of sqr.

May be your problem is the configuration of odbc.  We never saw
corruption of the data when you do a update thru sqr, but we sar others
problems.

I hope this help you

-----Original Message-----
From: Shaver, Richard H [mailto:richard.h.shaver@LMCO.COM]
Sent: Thursday, July 08, 1999 11:46 AM
To: Multiple recipients of list SQR-USERS
Subject: Updating from &variables


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.