SQL: The error 'Error Array element value overflow. (12664)' is generated selecting table array fields

Selecting array fields that contain data longer than their SQLWidth generate error (12664) when the database is a UTF-8 database and the error (8184) when the database is not a UTF-8 database.

Dec 6, 2021
Knowledge

Title

URL Name

000037376

Article Number

000161344

Environment

Product: OpenEdge Version: 10.x, 11.x OS: All Supported platforms Other: SQL

Question/Problem Description

Querying array fields whose data is longer that their SQLWidth generate error (12664) or (8184).

Error (12664) is generated when the database is a UTF-8 database.

Error (8184) is generated when the database is NOT a UTF-8 database.

Executing queries that reference array fields whose SQLWidth is shorter than their data length.

The error is generated executing SELECT statements along the following lines:
SELECT myArrayField FROM PUB.myTable

Steps to Reproduce

1. Login to an OpenEdge 10.x or to an OpenEdge 11.x Windows machine. 2. Unzip the attached DbToolBug.zip into your OpenEdge 10.x or 11.x working directory. 3. Start a an OpenEdge 10.x or 11.x Proenv session: Start > Programs > OpenEdge > Proenv 4. Create an empty UTF-8 database named "DbToolBug": prodb DbToolBug %DLC%\prolang\utf\empty 5. Serve the database (Change host and port to reflect your environment): proserve DbToolBug -H localhost -N tcp -S 12345 6. Execute the following command to Load Data Definitions: prowin32 -pf DbToolBug.pf -p LoadDataDefinitions.p 7. Execute the following command to Load Table Contents: prowin32 -pf DbToolBug.pf -p LoadTableContents.p 8. Execute the following three queries, separately, using the ODBC or JDBC client of your choice. SELECT LogArrayField FROM PUB.TestDbTool SELECT IntArrayField FROM PUB.TestDbTool SELECT ChrArrayField FROM PUB.TestDbTool 9. Observe the execution of each of the above three queries generates the following error. Array element value overflow. (12664) 10. Run the DBTOOL utility to fix the SQL width for all fields and tables. DBTool DbToolBug > 2 > 1 > 0 > all > all > 3 11. Observe the DBTOOL utility will report that it fixed one SQLWidth error. 12. Execute the same three queries again: SELECT LogArrayField FROM PUB.TestDbTool SELECT IntArrayField FROM PUB.TestDbTool SELECT ChrArrayField FROM PUB.TestDbTool 13. The same error is generated by the two queries referencing INTEGER and LOGICAL array fields. 14. The error is not generated by query referencing the CHARACTER array field.

Clarifying Information

Error Message

Array element value overflow. (12664) Character string is too long (8184)

Defect Number

Enhancement Number

Cause

The SQL-Width of the record was exceeded.

Resolution

Use DBTOOL to fix the SQL Width. For more information on DBTOOL, please refer to the following KB article:
What is DBTOOL?

Please note that the DBTOOL utility can fix the SQL Width of CHARACTER and DECIMAL array fields only, but it is unable to fix the SQL Width of DATE, INTEGER, INT64, LOGICAL, DATETIME and DATETIME-TZ array fields.

Alternatively expand the SQL Width of the offending fields manually to the desired size using one of these options:

Any field's SQL Width may be adjusted using the Data Dictionary > Options > Adjust Field Width.
Any field's SQL Width may also be adjusted programmatically. For example, the following 4GL/ABL code snippet expands the SQL Width of the "Name" field or the "Customer" table to 40:

DEFINE VARIABLE cTableName    AS CHARACTER NO-UNDO.
DEFINE VARIABLE cFieldName    AS CHARACTER NO-UNDO.
DEFINE VARIABLE iNewSQLWidth  AS INTEGER     NO-UNDO.

ASSIGN
    cTableName = "Customer"
    cFieldName = "Name"
    iNewSQLWidth = 40.

FIND FIRST _File WHERE _File._File-Name = cTableName NO-LOCK.
FIND FIRST _Field OF _File WHERE _Field._Field-Name = cFieldName.
ASSIGN
    _Field._Width = iNewSQLWidth.

Array fields are stored within the SQL engine as semicolon separated lists, so the SQL Width should be the total size of all the array elements.

Workaround

Notes

Progress Article(s)
How to use Data Dictionary to adjust a field's SQL Width. A step-by-step approach.

Keyword Phrase

Last Modified Date

12/6/2021 11:47 AM

SQL: The error 'Error Array element value overflow. (12664)' is generated selecting table array fields

Files(1)

Was this article helpful?

Was this article helpful?

SQL: The error 'Error Array element value overflow. (12664)' is generated selecting table array fields

Information

Related Articles