Every column in a table has a name and a data type. The data type tells the DBMS how much physical storage to set aside for the column and the form in which the data is stored. This section includes information about JDBC null and default values and data conversions.
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 specified as NOT NULL so that they require data (they cannot contain NULL values). When a column is specified as NOT NULL, the DBMS does not add a row to the table unless the row has a value for that column.
JDBC 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.
This table shows all data types and default SAS formats that SAS/ACCESS Interface to JDBC supports.
|
JDBC Data Type |
Default SAS Format |
|---|---|
|
CHAR |
$w. |
|
VARCHAR |
$w. |
|
LONGVARCHAR |
$w. |
|
BLOB |
$w. |
|
CLOB |
$w. |
|
BINARY |
$w.1 |
|
VARBINARY |
$w.1 |
|
LONGVARBINARY |
$w.1 |
|
DECIMAL |
w. or w.d or none if w and d are not specified |
|
NUMERIC |
w. or w.d or none if w and d are not specified |
|
INTEGER |
11. |
|
BIGINT |
20. |
|
SMALLINT |
6. |
|
TINYINT |
4. |
|
BIT |
1. |
|
REAL |
none |
|
FLOAT |
none |
|
DOUBLE |
none |
|
DATE |
DATE9. |
|
TIME |
TIME8. JDBC cannot support fractions of seconds for time values |
|
TIMESTAMP |
DATETIMEw.d where w and d depend on precision |
|
ARRAY |
none |
|
DISTINCT |
Character values are mapped to $w. Numeric values are mapped to w. |
|
JAVA_OBJECT |
none |
|
REF |
none |
|
STRUCT |
none |
| 1 Because the JDBC driver does the conversion, this field is displayed as if the $HEXw. format were applied. | |
This table shows the default data types that SAS/ACCESS Interface to JDBC uses when creating tables. SAS/ACCESS Interface to JDBC lets you specify non-default data types by using the DBTYPE= data set option.
|
SAS Variable Format |
Default JDBC Data Type |
|---|---|
|
w.d |
DOUBLE or NUMERIC using w.d if the DBMS allows it |
|
$w. |
VARCHAR using w |
|
datetime formats |
TIMESTAMP |
|
date formats |
DATE |
|
time formats |
TIME |