Datatypes

DB Access Module for Microsoft SQL Server User's Guide

2.3 Datatypes

The classes RWDBColumn and RWDBValue have an associated RWDBValue::ValueType that specifies the datatype of the database column or field being referenced. Mapping between this datatype and the database vendor type runs in both directions:

When fetching data or retrieving information about tables, the DB Access Module for Microsoft SQL Server converts the SQL type code provided by the Microsoft SQL Server ODBC driver into RWDBValue::ValueTypes. This mapping is shown in Table 1, which also gives the C++ datatype corresponding to the RWDBValue::ValueType.
When creating tables, the DB Access Module for Microsoft SQL Server maps the given RWDBValue::ValueType directly to the Microsoft SQL Server datatype. This mapping is shown in Table 2.

The RWDBValue::ValueTypes, accessible through the type() method, are uniform across all Access Modules. For a complete explanation of the DB Interface Module data model, see Chapter 7, "The Data Model," in the DB Interface Module User's Guide, and RWDBValue in the SourcePro C++ API Reference Guide.

We update the mapping tables regularly, but Rogue Wave cannot be responsible for changes made by other vendors. For the latest information on mappings between the Microsoft SQL Server SQL symbolic datatypes and the actual SQL datatypes used on the database, please consult Microsoft's SQL Server Library documentation.

Table 1: Datatype mapping when retrieving data

SQL Server Datatype	ODBC SQL Datatype	RWDBValue:: ValueType	C++ Datatype
`char(n)` `varchar(n)` `text`	`SQL_CHAR` `SQL_VARCHAR` `SQL_LONGVARCHAR`	`String`	*RWCString*
`nchar(n)` `nvarchar(n)` `ntext`	`SQL_WCHAR` `SQL_WVARCHAR` `SQL_WLONGVARCHAR`	`UString`	*RWBasicUString* or *RWUString*
`binary(n)` `varbinary(n)` `image`	`SQL_BINARY` `SQL_VARBINARY` `SQL_LONGVARBINARY`	`Blob`	*RWDBBlob*
`bit`	`SQL_BIT`	`UnsignedChar`	`unsigned char`
`tinyint`	`SQL_TINYINT`	`UnsignedChar`	`unsigned char`
`smallint`	`SQL_SMALLINT`	`Short`	`short int`
`bigint`	`SQL_BIGINT`	`LongLong`	`long long`
`int`	`SQL_INTEGER`	`Long`	`long int`
`float(p)`	`SQL_FLOAT`	`Double`	`double`
`real`	`SQL_REAL`	`Float`	`float`
`decimal(p,s)` `money` `smallmoney`	`SQL_DECIMAL`	`Decimal`	*RWDecimalPortable*
`timestamp`	`SQL_BINARY`	`Blob`	*RWDBBlob*
`datetime` `smalldatetime`¹	`SQL_TYPE_TIMESTAMP`	`DateTime`	*RWDBDateTime² RWDateTime*

Values of type datetime and smalldatetime are affected by the time zone setting on either RWDBDatabase or RWDBConnection while sending and retrieving data. The time zone setting can be set programmatically with the timeZone() method in each of these classes.
RWDBDateTime is deprecated. Please use RWDateTime.

Table 2: Datatype mapping when creating tables

RWDBValue:: ValueType	SQL Server Datatype
`Blob`	if 0 < `length` <= 8000 `varbinary` using the storageLength of the *RWDBColumn*, otherwise `image`
`Char` `UnsignedChar` `Tiny` `UnsignedTiny`	`tinyint`
`Date` `DateTime`	`datetime`
`Decimal`	`decimal(p,s)` using `precision` and `scale` of the *RWDBColumn* supplied, otherwise `decimal(8, 0)`
`Duration`	Not supported by Microsoft SQL Server
`Float`	`real`
`Double`	`float`
`Int` `UnsignedInt`	`smallint`
`LongDouble`	Not supported by Microsoft SQL Server
`Long` `UnsignedLong`	`int`
`Short` `UnsignedShort`	`smallint`
`String`	if 0 < `length` <= 8000 bytes, `varchar` using the `storageLength` of the *RWDBColumn*, otherwise text
`UString` `MBString` `WString`	if 0 < `length` <= 4000 bytes, `nvarchar` using the `storageLength` of the *RWDBColumn*, otherwise `ntext`
`LongLong` `UnsignedLongLong`	`bigint`

2.3.1 Restrictions on the Use of Datatypes

Microsoft SQL Server places restrictions on the use of certain datatypes. Where possible, the DB Interface Module is flexible about datatypes. However, there are some situations where conversions are not possible. In these cases your application must be aware of the contexts in which certain datatypes can be used. Table 3 outlines the restrictions associated with each DB Interface Module type.

Table 3: Restrictions on the use of DB Interface Module datatypes

RWDBValue::ValueType	Restrictions
`Blob`	No restrictions.
`Char` `UnsignedChar`	No restrictions.
`Date`	No restrictions.
`DateTime`	Microsoft SQL Server rounds to increments of .000, .003, or .007 seconds
`Decimal`	No restrictions.
`Double`	No restrictions.
`Duration`	Not supported by Microsoft SQL Server
`Float`	No restrictions.
`Int` `UnsignedInt`	No restrictions.
`Long` `UnsignedLong`	No restrictions.
`LongLong` `UnsignedLongLong`	No restrictions.
`MBString`	Not supported by Microsoft SQL Server
`Tiny` `UnsignedTiny`	No restrictions.
`Short` `UnsignedShort`	No restrictions.
`WString`	No restrictions. Refer to Section 2.11 for more information.
`String` `UString`	No restrictions.
`LongDouble`	No restrictions.

The Rogue Wave name and logo, and SourcePro, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.