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 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
- 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.
- Do not use the float or real data types in joins, in primary indexes, or with the equality operator.
- 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.
- 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.
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)
Sybase
Microsoft SQL Server 6.5Table 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.5Data 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 SQL_CHAR CHARACTER nvarchar4 SQL_VARCHAR CHARACTER textntext SQL_LONGVARCHAR CHARACTER5 money SQL_DECIMAL DECIMAL (INTEGER) smallmoney SQL_DECIMAL DECIMAL (INTEGER) datetime SQL_TIMESTAMP smalldatetime SQL_TIMESTAMP 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
- 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.
- Do not use the float or real data types in joins, in primary keys, or with the equality operator.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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 |