Error 76 fills the database log with CAN-FIND query

Jul 25, 2017
Knowledge

Title

URL Name

error-76-fills-the-database-log-with-can-find-query

Article Number

000123054

Environment

Product: OpenEdge Version: 11.x OS: 2012 R2 Server Other:

Question/Problem Description

Error 76 fills the database log with CAN-FIND query

Running code that checks addresses of subscribers. For each subscriber there are many error 76 written to the database log file. This causes the database log to grow rapidly. The log file entries are:

[2017/05/15@09:29:03.598+0200] P-2160 T-2820 I SRV 5: (76) ** Invalid character in numeric input 0.
[2017/05/15@09:29:03.600+0200] P-2160 T-2820 I SRV 5: (76) ** Invalid character in numeric input D.

The code being used and seems to trigger the issue includes a CAN-FIND statement:

DEFINE VARIABLE lFound AS LOGICAL INITIAL FALSE.

FOR EACH Subscriber /* WHERE Subscriber.SubscriberNr = 1058 */:
   IF CAN-FIND(AddressInfo WHERE 
       AddressInfo.Category = "HOUSE" AND
       AddressInfo.AddressNo = Subscriber.SubscriberNr AND
       INTEGER(AddressInfo.CategoryProperty) > 1 NO-LOCK) THEN lFound = TRUE.
   DISPLAY Subscriber.SubscriberNr lFound.
END.

If the CAN-FIND is removed from the code the problem does not happen.

Steps to Reproduce

Clarifying Information

The problem happens only when connected to the database in client server mode. It does not happen when the client is connected shared memory.

Error Message

** Invalid character in numeric input <character>. (76)

Defect Number

Enhancement Number

Cause

This is expected behavior. In this particular case the problem is caused by three things:

The index being used by the CAN-FIND on the AddressInfo table has a single component, AddressNo.
There are several AddressInfo records for each AddressNo value.
Some of the AddressInfo.CategoryProperty values have character data that cannot be converted to INTEGER values.

The CAN-FIND function:

returns TRUE only if exactly one record satisfies the selection criteria.
if an error occurs it returns FALSE without error.

Because CAN-FIND only returns TRUE if one record matches its WHERE clause, it has to read all records in its selected index bracket to determine if there are multiple records matching its WHERE clause. When the database server process reads each record it tries to convert the AddressInfo.CategoryProperty field value to an INTEGER, and fails for some of them because they contain character values (e.g. " " or 'D'). This results in the error 76 written to the database log.

When the same code is run with a shared memory connection, because CAN-FIND returns FALSE without raising an error when an error occurs, it appears like the problem doesn't happen. But it is simply that the error 76 isn't being displayed.

Resolution

Correct the CategoryProperty data so that it only contains numeric data.
If the CategoryProperty data can't be changed, perhaps it's possible to add an index that would exclude records with character CategoryProperty values (e.g. AddressNo + Category)

Workaround

Notes

References to Other Documentation:

OpenEdge Development: ABL Reference, "CAN-FIND function"
OpenEdge Getting Started: ABL Essentials, Chapter 4: "Procedure Blocks and Data Access"

Keyword Phrase

Last Modified Date

7/25/2017 9:55 AM

Error 76 fills the database log with CAN-FIND query

Files(0)

Was this article helpful?

Was this article helpful?

Error 76 fills the database log with CAN-FIND query

Information

Related Articles