Data Types

DB Access Module for Microsoft SQL Server User's Guide
Rogue Wave web site: Home Page | Main Documentation Page

2.3 Data Types

The classes RWDBColumn and RWDBValue have an associated RWDBValue::ValueType that specifies the data type of the database column or field being referenced. Mapping between this data type 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++ data type 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 data type. 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 data types and the actual SQL data types used on the database, please consult Microsoft's SQL Server Library documentation.

Table 1: Data type mapping when retrieving data

SQL Server Data Type	ODBC SQL Data Type	RWDBValue:: ValueType	C++ Data Type
`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*

RWDBDateTime is deprecated. Please use RWDateTime.

Table 2: Data type mapping when creating tables

RWDBValue:: ValueType	SQL Server Data Type
`Blob`	if 0 < `length` <= 8000 `varbinary` using the storageLength of the *RWDBColumn*, otherwise `image`
`Char UnsignedCharTiny 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 Data Types

Microsoft SQL Server places restrictions on the use of certain data types. Where possible, the DB Interface Module is flexible about data types. However, there are some situations where conversions are not possible. In these cases your application must be aware of the contexts in which certain data types 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 data types

RWDBValue::ValueType	Restrictions
`Blob`	No restrictions.
`Char, UnsignedChar`	No restrictions.
`Date`	No restrictions.
`DateTime`	Accuracy limited by Microsoft SQL Server to 3.33 milliseconds.
`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.10 for more information.
`String, UString`	No restrictions.
`LongDouble`	No restrictions.

2.3.2 Using Identity Columns

Microsoft SQL Server supports identity columns. Identity columns can be used within the DB Interface Module, following the restrictions specified by the Microsoft SQL Server documentation. For more information, see the Microsoft SQL Server documentation.

Applications can create tables with identity columns by providing the native SQL statement, then executing it using RWDBConnection::executeSql().

Applications can also acquire the schema of a table that contains an identity column. However, there is no way to determine if the column is actually an identity column. Applications must know the identity column of a table to use it correctly.

New rows can be inserted into tables by not providing values for identity columns, effectively allowing the SQL server to provide values. In general, you should only provide values for identity columns if an application requires a specific value. All values must follow the restrictions of the SQL server.

Of course, updating the identity columns is strictly forbidden by the SQL server.

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.
Contact Rogue Wave about documentation or support issues.