Progress
DataServer for
Microsoft SQL Server
Guide


Data Types

SQL Server™ data types differ from Progress data types. However, each data type supported by the DataServer has at least one Progress equivalent.

The DataServer translates SQL Server™ data types into Progress 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 SQL Server™ datetime and smalldatetime data types as timestamp, which is the equivalent ODBC SQL data type and not the SQL Server™ 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 SQL Server™ with greater than 28 digits.

You can also modify these definitions using the Data Dictionary. For example, the DataServer maps the SQL Server™ 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 table in the following section lists the data types supported by the DataServer for SQL Server™ and the default Progress equivalents. Table 2–3 lists these 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 MSS data source.

Table 2–3: SQL Server™ Data Type Equivalents
SQL Server™
Data Type
SQL-ODBC1
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)
numeric2
SQL_DECIMAL
DECIMAL (INTEGER)
float3
SQL_FLOAT
DECIMAL (INTEGER)
double precision
SQL_DOUBLE
DECIMAL (INTEGER)
real
SQL_REAL
DECIMAL (INTEGER)
char4
SQL_CHAR
CHARACTER
varchar4
SQL_VARCHAR
CHARACTER
nchar4,5
SQL_CHAR
CHARACTER
nvarchar5
SQL_VARCHAR
CHARACTER
text
ntext
SQL_LONGVARCHAR
CHARACTER6
money
SQL_DECIMAL
DECIMAL (INTEGER)
smallmoney
SQL_DECIMAL
DECIMAL (INTEGER)
datetime
SQL_TIMESTAMP
CHARACTER7 (DATE8)
smalldatetime
SQL_TIMESTAMP
CHARACTER7 (DATE8)
binary4
SQL_BINARY
CHARACTER
varbinary
SQL_VARBINARY
CHARACTER
image
SQL_LONGVARBINARY
CHARACTER6
bit
SQL_BIT
LOGICAL
timestamp9
SQL_VARBINARY
Unsupported
identity
NA
You can only display these values.10
  1. The SQL-ODBC data types demonstrate the mapping of native SQL Server™ data types to the ODBC standard.
  2. The DataServer truncates values in SQL Server™ 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.
  3. Do not use the float or real data types in joins, in primary keys, or with the equality operator
  4. When you define a binary, char, or nchar column to allow nulls, SQL Server™ stores the data type definitions as varbinary, varchar, and nvarchar, respectively. This does not affect how the DataServer maps the SQL Server™ data types to Progress data types.
  5. You can access nchar and nvarchar data types as SQL Server™ objects and bypass their conversion to CHARACTER by running a SQL Server ™ stored procedure or using the send–sql–statement option supported by the DataServer.
  6. Although the SQL Server™ 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.
  7. By default, the initial value of a SQL Server™ datetime or smalldatetime column is unknown ("?"). The default initial values for binary and varbinary are also unknown (“?”). The SQL Server™ datetime and smalldatetime data types contain both date and time information. The DataServer maps these to the Progress DATE data type; however, you can change the DATE data type to CHARACTER 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 CHARACTER, specify a character format, such as x(26).
  8. When mapping of the SQL Server™ datetime or smalldatetime data types is to the Progress DATE data type, Progress truncates the time portion of the date.
  9. 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 SQL Server™ table that contains a timestamp column.
  10. 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