Consultor Eletrônico

Status: Verified

SYMPTOM(s):

Error 7682 when referencing a CLOB or BLOB field in a SQL-92 statement

Error 7682 when inserting or updating a CLOB / LVARCHAR field

Error 7682 when inserting or updating a BLOB / LVARBINARY field

Error 7682 when referencing a CLOB / BLOB field in a WHERE, GROUP BY or ORDER BY clause in a SELECT statement

Invalid long datatype column reference (7682)

[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OpenEdge]Internal error 2 (A buffer other than the NLS system handle memory was not large enough) in SQL from subsystem NLS SERVICES function nls_conversion_t::convert_buffer called from clob_info_t::putOptimizeBuffer on . for .

FACT(s) (Environment):

Using a static SQL statement to INSERT or UPDATE a CLOB / BLOB field
Using a static SQL statement to SELECT data and referencing a CLOB / BLOB field in the WHERE, GROUP BY or ORDER BY clause
OpenEdge 10.0x
OpenEdge 10.1x
OpenEdge 10.2x
All Supported Operating Systems

CAUSE:

Currently for both ODBC and JDBC applications, the values for CLOB (LVARCHAR) and BLOB (LVARBINARY) columns cannot be put directly into the INSERT or UPDATE SQL statements.
Furthermore, OpenEdge SQL-92 does not support CLOB/BLOB references in a predicate (with 1 exception), function, arithmetic operator, or comparison operator. It is possible to reference a CLOB / BLOB with the IS [NOT] NULL predicate. In addition, it is not possible to use a CLOB/BLOB column as an ORDER BY or GROUP BY key.

FIX:

Use a Prepared Statement to INSERT or UPDATE values for a CLOB or BLOB field as a parameter.
The following is a JDBC example for a CLOB field:

PreparedStatement pstmt = connection.prepareStatement("INSERT INTO Test (XML) VALUES (?)");
String xml = "<Tag>MyData</Tag>";
StringReader sr = new StringReader(xml);
pstmt.setCharacterStream(1,sr,xml.length());
pstmt.executeUpdate();
pstmt.close();