Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
Initial Programming Considerations : Data types : Support for OpenEdge ABL CLOB data type
 

Support for OpenEdge ABL CLOB data type

The DataServer provides support for the OpenEdge ABL CLOB (Character Large Object) data type by mapping it to the equivalent data types in the MS SQL Server. This support is limited to MS SQL Server 2005 and later versions. The existing default and legacy mappings of server CLOB to the OpenEdge CHARACTER data type can be optionally replaced with OpenEdge CLOB mapping. OpenEdge CLOB mapping provides additional support for CLOB server types that are unavailable with CHARACTER mapping.You must keep in mind that CHAR-mapped columns that are replaced with CLOB mappings requires that the table in which the CLOB resides has a ROWID index designation.
The ability to map to an OpenEdge CLOB data type enables you to:
*Migrate an OpenEdge database with CLOB data type to an MS SQL Server database as VARCHAR(MAX) or NVARCHAR(MAX) and to pull data back as a CLOB date type into the schema holder. For more information on enabling the CLOB data type during a schema migration, see Migrating an OpenEdge database to MS SQL Server.
*Pull MS SQL Server VARCHAR(MAX), NVARCHAR(MAX),TEXT, or NTEXT data types as an OpenEdge CLOB data type into the schema holder by using the Default to OpenEdge LOB option.
*Read and write LOB data of any encoding, including UNICODE, from or to an MS SQL Server database that is CLOB-enabled by using OpenEdge ABL ASSIGN or COPY-LOB operations.
*Pass a CLOB as an INPUT or an OUTPUT parameter to the stored procedure. You can use the OpenEdge MEMPTR and LONGCHAR data types as parameters to RUN STORED-PROCEDURE operation to map BLOB and CLOB server types, respectively. For more information on RDBMS Stored Procedure Details, see RDBMS Stored Procedure Details.
Note: RUN STORED-PROCEDURE is not referred as an RDBMS transaction. It is a part of a transaction but is not in itself inherently a transaction. The ability to pass LOB parameters does not give you the ability to run stored procedures.
Note: OpenEdge provides partial read/write capability to BLOB and CLOB data types using STARTING AT, FOR, and OVERLAY AT n [TRIM] operations. This capability does not extend to the MS SQL Server legacy native data types, TEXT and NTEXT.
When the Default to OpenEdge LOB for: CLOBs is selected in Pre-Selection Criteria of the schema pull (see Independent OpenEdge MS SQL Server Schema Pull), it breaks the backward compatibility of the MS SQL Server DataServer which previously mapped server LOB types to the OpenEdge CHARACTER data type. Since the Adjust Schema operation requires CHARACTER type mapping for server LOB types, an Adjust Schema operation will fail to "adjust" back to CHARACTER . Since this schema adjustment will not occur, server CLOBs will now be mapped to OpenEdge CLOBs and will be useable as LOB objects in your OpenEdge DataServer application. LOB objects are manipulated differently from their original CHARACTER data types and the requirements for your CLOB-mapped application may be different from your original database if such server LOBs were originally migrated from CHARACTER types.
For more information, on using the ASSIGN and COPY-LOB statements, see OpenEdge Development: ABL Reference.
* ABL-to-MS SQL Server data type mapping