Previous Page | Next Page

SAS/ACCESS Interface to OLE DB

Data Types for OLE DB


Overview

Each data source column in a table has a name and a data type. The data type tells the data source how much physical storage to set aside for the column and the form in which the data is stored. This section includes information about OLE DB null and default values and data conversions.


OLE DB Null Values

Many relational database management systems have a special value called NULL. A DBMS NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a DBMS NULL value, it interprets it as a SAS missing value.

In most relational databases, columns can be defined as NOT NULL so that they require data (they cannot contain NULL values). When a column is defined as NOT NULL, the DBMS does not add a row to the table unless the row has a value for that column. When creating a DBMS table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.

OLE DB mirrors the behavior of the underlying DBMS with regard to NULL values. See the documentation for your DBMS for information about how it handles NULL values.

For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data in SAS/ACCESS for Relational Databases: Reference.

To control how SAS missing character values are handled by the DBMS, use the NULLCHAR= and NULLCHARVAL= data set options.


LIBNAME Statement Data Conversions

This table shows all data types and default SAS formats that SAS/ACCESS Interface to OLE DB supports. It does not explicitly define the data types as they exist for each data source. It lists the types that each data source's data type might map to. For example, an INTEGER data type under DB2 might map to an OLE DB data type of DBTYPE_I4. All data types are supported.

OLE DB Data Types and Default SAS Formats
OLE DB Data Type Default SAS Format
DBTYPE_R8 none
DBTYPE_R4 none
DBTYPE_I8 none
DBTYPE_UI8 none
DBTYPE_I4 11.
DBTYPE_UI4 11.
DBTYPE_I2 6.
DBTYPE_UI2 6.
DBTYPE_I1 4.
DBTYPE_UI1 4.
DBTYPE_BOOL 1.
DBTYPE_NUMERIC m or m.n or none, if m and n are not specified
DBTYPE_DECIMAL m or m.n or none, if m and n are not specified
DBTYPE_CY DOLLARm.2
DBTYPE_BYTES $n.
DBTYPE_STR $n.
DBTYPE_BSTR $n.
DBTYPE_WSTR $n.
DBTYPE_VARIANT $n.
DBTYPE_DBDATE DATE9.
DBTYPE_DBTIME TIME8.
DBTYPE_DBTIMESTAMP

DBTYPE_DATE

DATETIMEm.n, where m depends on precision and n depends on scale
DBTYPE_GUID $38.

The following table shows the default data types that SAS/ACCESS Interface to OLE DB uses when creating DBMS tables. SAS/ACCESS Interface to OLE DB lets you specify non-default data types by using the DBTYPE= data set option.

Default OLE DB Output Data Types
SAS Variable Format Default OLE DB Data Type
m.n DBTYPE_R8 or DBTYPE_NUMERIC using m.n if the DBMS allows it
$n. DBTYPE_STR using n
date formats DBTYPE_DBDATE
time formats DBTYPE_DBTIME
datetime formats DBTYPE_DBTIMESTAMP

Previous Page | Next Page | Top of Page