Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
Initial Programming Considerations : Data types
 

Data types

MS SQL Server data types differ from OpenEdge data types. However, each data type supported by the DataServer has at least one OpenEdge equivalent.
The DataServer translates MS SQL Server data types into OpenEdge equivalents and places the mapping into the schema holder. You can access this mapping information using the Data Dictionary. For example, the Data Dictionary lists the MS SQL Server datetime and smalldatetime data types as timestamp, which is the equivalent ODBC SQL data type and not the MS SQL Server timestamp data type. There can also be some restrictions in data source compatibility with OpenEdge.
You can also modify these definitions using the Data Dictionary. For example, the DataServer maps the MS SQL Server tinyint data type to the OpenEdge equivalent, INTEGER. Suppose, however, that your application uses the tinyint field in such a way that the LOGICAL data type is a more suitable equivalent. In this case, you would change the data type from INTEGER to LOGICAL in the schema holder. If you do change a data type mapping, be sure to select a data type that accommodates the data in the column, otherwise conversion errors might occur at run time. Also, remember to specify a display format that is appropriate for the new data type. See Modifyinga schema holder for an explanation of how to use the Data Dictionary to change OpenEdge data types in the schema holder.
The table that follows lists these data types and their default OpenEdge equivalents. The data types in parentheses are alternative data types that you can specify in the schema holder for your MS SQL Server data source. See Data Type Details for more details about each MS SQL Server data type, its ODBC SQL equivalent, and OpenEdge data type equivalent.
Table 9. MS SQL Server data type equivalents in OpenEdge
MS SQL Server data type
OpenEdge data type equivalent1
integer
INTEGER
(DECIMAL)
(LOGICAL)
(INT64)
bigint
INT64
(DECIMAL)
(INTEGER)
smallint
INTEGER
(DECIMAL)
(LOGICAL)
(INT64)
tinyint
INTEGER
(DECIMAL)
(LOGICAL)
(INT64)
decimal
DECIMAL
(INTEGER)
(INT64)
numeric
DECIMAL
(INTEGER)
(INT64)
float
DECIMAL
(INTEGER)
(INT64)
double precision
DECIMAL
(INTEGER)
(INT64)
real
DECIMAL
(INTEGER)
(INT64)
char
CHARACTER2
nchar
CHARACTER3
varchar
CHARACTER4
nvarchar
CHARACTER5
text 6
CHARACTER7
CLOB8
varchar(max)
CHARACTER9, 10
CLOB11
ntext12
CHARACTER13
CLOB14
nvarchar(max)
CHARACTER15,16
CLOB17
money
DECIMAL
(INTEGER)
smallmoney
DECIMAL
(INTEGER)
date18
DATE
(DATETIME)
(DATETIME-TZ)
(CHARACTER)19
time20
CHARACTER21
(DATETIME )
datetime
DATE
(CHARACTER)22
datetime223
DATETIME
(DATE)
(DATETIME-TZ)
(CHARACTER)24
smalldatetime
DATE
(CHARACTER)25
datetimeoffset26
DATETIME-TZ
(DATETIME)
(DATE)
(CHARACTER)27
binary
CHARACTER28
varbinary
CHARACTER29
varbinary(max) 30
CHARACTER31, 32
BLOB33
image34
CHARACTER35 BLOB36
bit
LOGICAL
timestamp
CHARACTER37, 38
identity 39
Depends on underlying type40
uniqueidentifier
CHARACTER41, 42
computed columns
Depends on underlying type43
cursor
Not supported44
sql_variant
Not supported45
geometry
Not supported46
geography
Not supported47
hierarchyid
Not supported48
raw
Not supported49
1 The initial entry identifies the default data type. Data types in parentheses identify supported options.

2 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

3 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

4 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

5 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

6 Starting in MS SQL Server 2005, legacy "image" and "text" LOB types on the server can be expressed as BLOB and CLOB types respectively. While still supported, Progress recommends legacy LOB types be converted to varbinary(max) and varchar(max) types for better forward compatibility.

7 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

8 The default CHARACTER mapping for binary LOB server types can be changed to OpenEdge CLOB by selecting the CLOBs checkbox on the Default to OpenEdge LOB for option during object selection on schema pull. Changing the mapping from OpenEdge character to OpenEdge LOB may require code changes. However, Progress recommends mapping MSS character LOB types to OpenEdge CLOBs for better scalability.

9 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

10 Starting in MS SQL Server 2008 using native drivers, these data types can be pulled into an OpenEdge schema holder for MS SQL Server.

11 The default CHARACTER mapping for binary LOB server types can be changed to OpenEdge CLOB by selecting the CLOBs checkbox on the Default to OpenEdge LOB for option during object selection on schema pull. Changing the mapping from OpenEdge character to OpenEdge LOB may require code changes. However, Progress recommends mapping MSS character LOB types to OpenEdge CLOBs for better scalability.

12 Starting in MS SQL Server 2005, legacy "image" and "text" LOB types on the server can be expressed as BLOB and CLOB types respectively. While still supported, Progress recommends legacy LOB types be converted to varbinary(max) and varchar(max) types for better forward compatibility.

13 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

14 The default CHARACTER mapping for binary LOB server types can be changed to OpenEdge CLOB by selecting the CLOBs checkbox on the Default to OpenEdge LOB for option during object selection on schema pull. Changing the mapping from OpenEdge character to OpenEdge LOB may require code changes. However, Progress recommends mapping MSS character LOB types to OpenEdge CLOBs for better scalability.

15 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

16 Starting in MS SQL Server 2008 using native drivers, these data types can be pulled into an OpenEdge schema holder for MS SQL Server.

17 The default CHARACTER mapping for binary LOB server types can be changed to OpenEdge CLOB by selecting the CLOBs checkbox on the Default to OpenEdge LOB for option during object selection on schema pull. Changing the mapping from OpenEdge character to OpenEdge LOB may require code changes. However, Progress recommends mapping MSS character LOB types to OpenEdge CLOBs for better scalability.

18 Starting in MS SQL Server 2008 using native drivers, these data types can be pulled into an OpenEdge schema holder for MS SQL Server.

19 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

20 Starting in MS SQL Server 2008 using native drivers, these data types can be pulled into an OpenEdge schema holder for MS SQL Server.

21 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

22 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

23 Starting in MS SQL Server 2008 using native drivers, these data types can be pulled into an OpenEdge schema holder for MS SQL Server.

24 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

25 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

26 Starting in MS SQL Server 2008 using native drivers, these data types can be pulled into an OpenEdge schema holder for MS SQL Server.

27 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

28 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

29 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

30 The FILESTREAM variant of varbinary(max) data type is also supported.

31 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

32 Starting in MS SQL Server 2008 using native drivers, these data types can be pulled into an OpenEdge schema holder for MS SQL Server.

33 The default CHARACTER mapping for binary LOB server types can be changed to OpenEdge BLOB by selecting the BLOBs checkbox on the Default to OpenEdge LOB for option during object selection on schema pull. Changing the mapping from OpenEdge character to OpenEdge LOB may require code changes. However, Progress recommends mapping MSS binary LOB types to OpenEdge BLOBs for better scalability.

34 Starting in MS SQL Server 2005, legacy "image" and "text" LOB types on the server can be expressed as BLOB and CLOB types respectively. While still supported, Progress recommends legacy LOB types be converted to varbinary(max) and varchar(max) types for better forward compatibility.

35 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

36 The default CHARACTER mapping for binary LOB server types can be changed to OpenEdge BLOB by selecting the BLOBs checkbox on the Default to OpenEdge LOB for option during object selection on schema pull. Changing the mapping from OpenEdge character to OpenEdge LOB may require code changes. However, Progress recommends mapping MSS binary LOB types to OpenEdge BLOBs for better scalability.

37 These non-updatable columns can be mapped into an MSS DataServer schema holder but cannot be written to on the server.

38 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

39 You can add an identity data type to a table if you have not already set the PROGRESS_RECID column to the table's identity column. You can only use the PROGRESS_RECID identity column if you want to set an identity column for ROWID designation in your OpenEdge file schema. identity columns created by the user cannot be designated for OpenEdge ROWID or used as part of an index composite that designates ROWID.

40 These non-updatable columns can be mapped into an MSS DataServer schema holder but cannot be written to on the server.

41 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 59

42 These non-updatable columns can be mapped into an MSS DataServer schema holder but cannot be written to on the server.

43 These non-updatable columns can be mapped into an MSS DataServer schema holder but cannot be written to on the server.

44 For more information on unsupported data types, see Workingwith unsupported data types.

45 For more information on unsupported data types, see Workingwith unsupported data types.

46 For more information on unsupported data types, see Workingwith unsupported data types.

47 For more information on unsupported data types, see Workingwith unsupported data types.

48 For more information on unsupported data types, see Workingwith unsupported data types.

49 For more information on unsupported data types, see Workingwith unsupported data types.

* Working with unsupported data types
* Working with non-updatable data types
* Processing considerations for 32-bit and 64-bit data types
* DataServer for MS SQL Server support for datetime data types
* Using datetime data types in a WHERE clause
* Support for OpenEdge ABL CLOB data type
* Support for OpenEdge ABL BLOB data type
* User-defined data types
* Arrays
* Unknown value (?)
* Zero-length character strings