Base de Conocimientos Técnicos

Hint Ref: 021005110010
Hint Date: 11/05/2010

Hint Details:

A customer was running a SQL stored procedure from their web server and periodically received the following error: 'Column 'mysite' not found'

PROBLEM:

Within the procedure the possibility of the variable 'mysite' not existing was taken care of thus:

 begin
   set mysite=isite
 exception
   when others then begin
       create variable mysite char(20);
       set mysite=isite
     end
 end;

In other words, if trying to set a value for the variable created an error (because the variable did not exist) then the exception error trap would catch this and the variable would be created before giving it a value again.

This method was necessary in Sybase 8 as there was no available function to determin if a SQL variable existed or not.

Whilst not ideal, the error trap method should still work. However, in Sybase 10 we have discovered that sometimes this approach does not work effectively and an error is still generated.

SOLUTION:

Fortunately, Sybase 10 provides for a function that can test for the existence of a SQL variable. The function is called VAREXISTS() and when passed the variable name as a string, the function returns 1 if the variable exists and 0 if it does not.

Therefore, the solution is to replace the code above with the following:

 IF VAREXISTS( 'mysite' ) = 0 THEN
    create variable mysite char(20);
 END IF;
 set mysite=isite;

For your information, the other area where this error trap method is often used is when obtaining an ID using the 'uniquekey()' function:

 begin
   set custid=uniquekey(isurname)
 exception
   when others then begin
       create variable uniqueroll smallint;
       set custid=uniquekey(isurname)
     end
 end;
 commit work;

and this should be replaced with:

 IF VAREXISTS( 'uniqueroll' ) = 0 THEN
    create variable uniqueroll smallint;
 END IF;
 set custid=uniquekey(isurname);
 commit work;

(Please Note: This Procedure can be destructive and should only be used by Advanced Users.  RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command.  Responsibility resides solely with the user.  

IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)