Bad Code Alert

Brigitta, this is not a personal attack on you. I see this error all over
the place at my company and I keep trying to get it stopped.

Recently the following code snippet was presented.

Exec SQL Declare CsrC01 cursor for
Select TS1, VarChar1, TS2, Decimal1
From ...
Where ...;

Exec SQL Open CsrC01;

DoU 1=0;
Exec SQL Fetch Next From CsrC01
into :LocTS1 :LocTS1Ind, :MyVarChar1,
:LocTS2 :LocTS2Ind, :MYDec1;
If SQLCODE = 100
or SQLCODE < *Zeros;
//Handle SQL Error;
Exec SQL Close CsrC01;
Leave;
EndIf;

This code represents a severe problem. The problem. No SQL error checking.

It is absolutely critical that SQL statement be error checked. If an error
occurs on a File I/O, an error is thrown but unless the error is critical
the only thing that happens in SQL is that an error code is returned in
SQLSTATE (I use SQLSTATE. SQLCODE can also be used).

This is all that happens. If an SQL error is not checked the program will
simply continue on. I cannot tell you the number of times that I have gone
into SQL programs running in production that I determined where failing
and had been failing for years. The effect from those errors was minor to
severe.

The above code should look like the following.I am using my standard error
handler for these examples because it throws an exception message and stops
processing. . Note that I have no error checking on Declare. It does not
generate any machine code and does not need to be error checked.

dcl-c SQL_STATE_NO_ROW '02000';
dcl-c SQL_STATE_OK '00000';

Exec SQL Declare CsrC01 cursor for
Select TS1, VarChar1, TS2, Decimal1
From ...
Where ...;

Exec SQL Open CsrC01;
If SqlState <> SQL_STATE_OK;
// Report error and stop processing.
XVERRH_Throw('CPF9898':
CPF_MESSAGE_FILE_NAME:
'Open Cursor CsrC01 Failed! SqlState = ' + SqlState);
EndIf;

DoU 1=0;
Exec SQL Fetch Next From CsrC01
into :LocTS1 :LocTS1Ind, :MyVarChar1,
:LocTS2 :LocTS2Ind, :MYDec1;
Select;
When SqlState = SQL_STATE_NO_ROW;
Exec SQL Close CsrC01;
Leave;
When SqlState = SQL_STATE_OK;
// process record.
Other;
// Report error and stop processing.
XVERRH_Throw('CPF9898':
CPF_MESSAGE_FILE_NAME:
'Fetch Next From CsrC01 failed! Sql State = ' +
SqlState);
EndSl;
EndDo;

I want to emphasize again the importance of stopping processing. If an
error occurs, we must stop processing. If we continue, we are just
compounding the problem.

Please note also that I am stopping processing for both warning and
errors.It has been my experience that warning error are always masking some
form of programming error and need to be investigated.

I appreciate their may be certain rare condition were we want processing to
continue after a warning but they are the exception.

I again apologize if I have hurt anyone feeling but I felt it was important
to bring this to the groups attention. SQL error checking is absolutely
critical.