• Print |
• E-mail  |
• Bookmark

A customer reported the following error message when attempting to
delete from an empty Ingres table:

  ERROR: Invalid SQL statement entered. See the log for details.
  ERROR: INGRES execute error:

The following explanation is taken from the Ingres documentation, SQL
Reference manual:

Delete statement:
The delete statement removes rows from the specified table. The result
is a valid but empty table. Delete does not automatically recover the
space in a table left by the deleted rows. To recover lost space after
deleting many rows from a table, modify the table. To delete all rows
from a table, use "modify...to truncated".

If the delete statement does not delete any rows, the sqlcode variable
in the SQLCA structure is set to 100.

The documentation for the Modify statement states that it does the same
thing when it does not delete any rows. Also, the appendix on "Generic
Error Codes" lists error +100 as "no more data." This error is issued
when a request for data to be processed was issued, but no data was
found.

This is similar to what SAS Development found when they investigated the
issue reported by the customer.

Development ran a test SAS program using both the Delete statement and
Modify statement. The Delete statement generated the error because the
table was empty. We point this out because it contradicts the customer.
We assume that the customer did not realize that the Delete statement
returns an error if the table is empty.

We suspect the case where the customer reported no error was due to a
table that contained at least one row of data. As expected, the Delete
statement did not produce an error if the table had at least one row.

The modify statement contradicted the Ingres documentation and produced
an error no matter whether the table was populated or not. Using the SAS
debugger, Development verified that error code 100 was responsible for
all the errors reported in SAS.

Development then tested the same statements using Ingres interactive
SQL. While no errors were reported in any case, we believe the behavior
was similar to SAS. When the table is empty, both statements report "(0)
rows". But, when the table is populated, the delete statement shows "(1)
row" while the modify statement shows "(0) rows".  We suspect the
incorrect number of rows when using the modify statement is the result
of the same bug we have seen in the Ingres embedded SQL.

Therefore, the problem is with Ingres and not SAS. The SAS code is
properly handling the +100 error being returned from Ingres. If you
receive this error in this situation, Development recommends that you
contact Ingres and report the bug. If Ingres is made aware of the
problem, perhaps a fix may be available in a later release or patch of
Ingres.

However, there will be many situations where a delete from an empty
table is done, and customers would rather that this did not generate an
error in the SAS logs. This type of SQL is not an error from other
point of views, unlike deleting from a non-existant table where an error
is desirable.

A hot fix that will cause the the return codes outlined above to
generate a warning in the SAS Log, instead of the errors that are
currently being produced, is available.


A Technical Support hot fix for Release 8.2 (TS2M0) for this
issue is available at:

http://www.sas.com/techsup/download/hotfix/82_sbcs_prod_list.html#010494

Operating System and Release Information

A fix for Release 8.2 (TS2M0) for this issue is available at: