Progress
DataServer
for ODBC Guide


Data Types

ODBC data-source data types differ from Progress data types. However, each data-source data type supported by the DataServer has at least one Progress equivalent.

The DataServer translates ODBC data-source data types into Progress equivalents and places the mapping into the schema holder. You can access this mapping information using the Data Dictionary. Note, however, that the Data Dictionary lists the ODBC SQL equivalents for data-source data types rather than the actual data-source data types. For example, the Data Dictionary lists the Sybase datetime and smalldatetime data types as timestamp, which is the equivalent ODBC SQL data type and not the Sybase timestamp data type. There may also be some restrictions in data-source compatibility with Progress. For example, the Progress database cannot support a numeric or decimal field defined in Sybase with greater than 28 digits.

You can also modify these definitions using the Data Dictionary. For example, the DataServer maps the Sybase tinyint data type to the Progress 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 the "Modifying a Schema Holder" section in "The DataServer Tutorial," for an explanation of how to use the Data Dictionary to change Progress data types in the schema holder.

The tables in the following sections list, for each ODBC data source, the data types supported by the DataServer, the ODBC SQL equivalents, and the default Progress equivalents. The notes that follow some tables provide additional information.

DB2

Table 2–3 lists the DB2 data types supported by the DataServer, their ODBC SQL equivalents, and their default Progress equivalents. The data types in parentheses are alternative data types that you can specify in the schema holder for your DB2 data source. You cannot change the list of default data types in a schema holder for a DB2 data source.

Table 2–3: DB2 Data-type Equivalencies
DB2
Data Type
SQL-ODBC
Data Type
Progress
Default
integer
SQL_INTEGER
INTEGER (DECIMAL or LOGICAL)
smallint
SQL_SMALLINT
INTEGER (DECIMAL or LOGICAL)
decimal1
SQL_DECIMAL
DECIMAL (INTEGER)
real/float2
SQL_REAL
DECIMAL (INTEGER)
double precision/float2
SQL_FLOAT
DECIMAL (INTEGER)
date
SQL_TIMESTAMP
CHARACTER (DATE)
time
SQL_TIMESTAMP
CHARACTER3
timestamp
SQL_TIMESTAMP
CHARACTER
(DATE3,4)
char(n)
SQL_CHARACTER
CHARACTER
varchar(n)
SQL_VARCHAR
CHARACTER
long varchar(n)
SQL_LONGVARCHAR
CHARACTER
graphic
SQL_BINARY
CHARACTER
vargraphic(n)
SQL_VARBINARY
CHARACTER
long vargraphic
SQL_LONGBINARY
CHARACTER
  1. The DataServer truncates values in DB2 decimal or numeric columns defined with a scale greater than 10. In the case of float columns, it reliably translates up to 10 places only.
  2. Do not use the float or real data types in joins, in primary indexes, or with the equality operator.
  3. When you change the default mapping of the DB2 timestamp or time data types to the Progress DATE data type, Progress truncates the time portion of the date.
  4. The DB2 timestamp data type contains both date and time information. The DataServer maps this to the Progress CHARACTER data type; however, you can change the CHARACTER data type to INTEGER or DATE in the schema holder. If you do, remember to change the format to match the new data type. For example, if you change the data type to DATE, specify a date format, such as 99/99/99.

Informix

Table 2–4 lists the Informix data types supported by the DataServer, their ODBC SQL equivalents, and their default Progress equivalents.

Table 2–4: Informix Data-type Equivalencies
Informix
Data Type
SQL-ODBC
Data Type
Progress
Default
Byte
SQL_LONGVARBINARY
CHARACTER
Char
SQL_CHAR
CHARACTER
Date
SQL_TYPE_DATE
DATE
Decimal
SQL_DECIMAL
DECIMAL
Float
SQL_DOUBLE
DECIMAL
Integer
SQL_INTEGER
INTEGER
Money
SQL_DECIMAL
DECIMAL
Serial
SQL_INTEGER
INTEGER
Smallfloat
SQL_REAL
DECIMAL
Smallint
SQL_SMALLINT
INTEGER
Text
SQL_LONGVARCHAR
CHARACTER
Varchar
SQL_VARCHAR
CHARACTER

Microsoft Access

Table 2–5 lists the Microsoft Access data types supported by the DataServer, their ODBC SQL equivalents, and their default Progress equivalents. The data types in parentheses are alternative data types that you can specify in the schema holder for your Microsoft Access data source.

Table 2–5: Microsoft Access Data-type Equivalencies
Microsoft Access
Data Type
SQL-ODBC
Data Type
Progress
Default
text1
SQL_CHAR
CHARACTER
Memo
SQL_VARCHAR
CHARACTER
Memo
SQL_LONGVARCHAR
CHARACTER
Not applicable
SQL_TIME
CHARACTER
Not applicable
SQL_TIMESTAMP
CHARACTER (DATE)
Not applicable
SQL_BINARY
CHARACTER
Not applicable
SQL_VARBINARY
CHARACTER
Not applicable
SQL_LONGVARBINARY
CHARACTER
integer
number
SQL_INTEGER
INTEGER (LOGICAL or DECIMAL)
Not applicable
SQL_SMALLINT
INTEGER (LOGICAL or DECIMAL)
Not applicable
SQL_TINYINT
INTEGER (LOGICAL or DECIMAL)
Not applicable
SQL_NUMERIC
DECIMAL (INTEGER)
currency
SQL_DECIMAL
DECIMAL (INTEGER)
Not applicable
SQL_FLOAT
DECIMAL (INTEGER)
Not applicable
SQL_REAL
DECIMAL (INTEGER)
Not applicable
SQL_DOUBLE
DECIMAL (INTEGER)
Not applicable
SQL_BIGINT
DECIMAL (INTEGER)
datetime
SQL_DATE
DATE
logical
SQL_BIT
LOGICAL (INTEGER)
  1. Microsoft Access stores trailing blanks in columns with the text data type. When you do comparisons (equals or matches), place a wildcard (*) at the end of the string.

Sybase
Microsoft SQL Server 6.5

Table 2–6 lists the Sybase and Microsoft SQL Server data types, their ODBC SQL equivalents, and their default Progress equivalents. The data types in parentheses are alternative data types that you can specify in the schema holder for your Sybase or Microsoft SQL Server data source.

Table 2–6: Sybase and Microsoft SQL Server 6.5 Data-type Equivalencies
Sybase or Microsoft SQL Server 6.5
Data Type
SQL-ODBC Data Type
Progress Default
integer
SQL_INTEGER
INTEGER (DECIMAL)
smallint
SQL_SMALLINT
INTEGER (DECIMAL or LOGICAL)
tinyint
SQL_TINYINT
INTEGER (DECIMAL or LOGICAL)
decimal
SQL_DECIMAL
DECIMAL (INTEGER)
numeric1
SQL_DECIMAL
DECIMAL (INTEGER)
float2
SQL_FLOAT
DECIMAL (INTEGER)
double precision
SQL_DOUBLE
DECIMAL (INTEGER)
real
SQL_REAL
DECIMAL (INTEGER)
char3
SQL_CHAR
CHARACTER
varchar3
SQL_VARCHAR
CHARACTER
nchar3,4
SQL_CHAR
CHARACTER
nvarchar4
SQL_VARCHAR
CHARACTER
text
ntext
SQL_LONGVARCHAR
CHARACTER5
money
SQL_DECIMAL
DECIMAL (INTEGER)
smallmoney
SQL_DECIMAL
DECIMAL (INTEGER)
datetime
SQL_TIMESTAMP
CHARACTER6 (DATE7)
smalldatetime
SQL_TIMESTAMP
CHARACTER6 (DATE7)
binary3
SQL_BINARY
CHARACTER
varbinary
SQL_VARBINARY
CHARACTER
image
SQL_LONGVARBINARY
CHARACTER5
bit
SQL_BIT
LOGICAL
timestamp8
SQL_VARBINARY
Unsupported
identity
NA
You can only display these values.9
  1. The DataServer truncates values in Sybase or Microsoft SQL Server 6.5 decimal or numeric columns defined with a scale greater than 10. In the case of float columns, it reliably translates up to 10 places only.
  2. Do not use the float or real data types in joins, in primary keys, or with the equality operator.
  3. When you define a binary or char column to allow nulls, Sybase or Microsoft SQL Server 6.5 stores the data type definitions as varbinary and varchar respectively. The equivalent SQL data types that the Progress Data Dictionary displays for these are SQL VARBINARY, SQL VARCHAR, and SQL VARCHAR. This does not affect how the DataServer maps the Sybase or Microsoft SQL Server 6.5 data types to Progress data types.
  4. You can access nchar and nvarchar data types as Sybase or Microsoft SQL Server 6.5 objects and bypass their conversion to CHARACTER by running a Sybase or Microsoft SQL Server 6.5 stored procedure or using the send–sql–statement option supported by the DataServer.
  5. Although the Sybase or Microsoft SQL Server 6.5 text and image fields can hold up to 2MB, Progress retrieves only up to 32K. If you are using a Progress Format phrase, there might be additional limits on the size of text and image fields. See the Format Phrase entry in the Progress Language Reference. You can use the DataServer (-Dsrv MAX_LENGTH) startup parameter to limit the amount of returned text data.
  6. By default, the initial value of a Sybase or Microsoft SQL Server 6.5 datetime or smalldatetime column is unknown ("?"). The default initial values for binary and varbinary are also unknown (“?”). The Sybase or Microsoft SQL Server 6.5 datetime and smalldatetime data types contain both date and time information. The DataServer maps these to the Progress CHARACTER data type; however, you can change the CHARACTER data type to INTEGER or DATE in the schema holder. If you do this, remember to change the format to match the new data type. For example, if you change the data type to DATE, specify a date format, such as 99/99/99.
  7. When you change the default mapping of Sybase or Microsoft SQL Server 6.5 datetime or smalldatetime data types to the Progress DATE data type, Progress truncates the time portion of the date.
  8. The DataServer considers a timestamp data type to be a hidden value. It is not visible to the Progress user, but you can still access a Sybase or Microsoft SQL Server 6.5 table that contains a timestamp column.
  9. You can display values in identity columns, but you cannot insert or update them.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095