SQL data types in ODBC

 

SQL data types are those in which data are stored in the data source. Each data source defines its own SQL data types. To identify SQL data types, ODBC defines type identifiers and describes the general characteristics of the SQL data types that might be mapped to each type identifier. How each data type in the underlying data source is mapped to a ODBC's SQL type identifier is driver specific.

 

For example, SQL_CHAR is the type identifier for a character column with a fixed length, typically between 1 and 254 characters. These characteristics correspond to the CHAR data type found in many SQL data sources. Thus, when an application discovers that the type identifier for a column is SQL_CHAR, it can assume it is probably dealing with a CHAR column. However, it should still check the byte length of the column before assuming a length between 1 and 254 characters. The driver for a non-SQL data source, for example, might map a fixed-length character column of 500 characters to SQL_CHAR or SQL_LONGVARCHAR, since neither is an exact match.

 

ODBC defines a wide variety of SQL type identifiers. However, the drivers are not required to use all of these identifiers. Instead, they use only those identifiers needed to expose the SQL data types supported by the underlying data source. If the underlying data source supports SQL data types to which no type identifier corresponds, the driver can define additional type identifiers

 

Because the mappings from underlying SQL data types to ODBC type identifiers are approximate, ODBC provides a function (SQLGetTypeInfo, called from f90SQL as f90SQLGetTypeInfo) through which a driver can completely describe each SQL data type in the data source. This function returns a resultset, each row of which describes the characteristics of a single data type, such as name, type identifier, precision, scale, and nullability.

 

This information is generally used by generic applications, which allow the user to create and alter tables. Such applications call f90SQLGetTypeInfo to retrieve the data type information and then present some or all of it to the user. Such applications need to be aware of two things:

 

 

Note that f90SQLGetTypeInfo does not necessarily describe all of the data types an application can encounter. In particular, result sets might contain data types not directly supported by the data source. For example, the data types in the columns of result sets returned by catalog functions are defined by ODBC. However, these data types might not be supported by the data source. To determine the characteristics of the data types in a resultset, an application calls f90SQLColAttribute.

 

The following example shows how a Fortran application can call the subroutine f90SQLGetTypeInfo to obtain a list of the data types supported by a data source (in this case the Microsoft's Access ODBC driver).

 

Example 4.6

 

program Example46
 
!retrieves a list of the data types allowed by a data source
 
!load f90SQL modules
use f90SQLConstants
use f90SQLStructures
use f90SQL
 
implicit none
integer(SQLHENV_KIND)::EnvHndl
integer(SQLHDBC_KIND)::ConnHndl
integer(SQLHSTMT_KIND):: StmtHndl
integer(SQLRETURN_KIND):: iRet
type(f90SQL_TYPE_INFO_STRUCT)::SourceDataTypes
integer:: i
 
!allocate an environment handle
call f90SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, EnvHndl, iRet)
 
!Set ODBC version we will be using (3.x in this case)
call f90SQLSetEnvAttr(EnvHndl, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, iRet)
 
!Allocate a connection handle
call f90SQLAllocHandle(SQL_HANDLE_DBC,EnvHndl, ConnHndl, iRet)
 
!Open DSN BookSales
call f90SQLConnect(ConnHndl,'BookSales','Admin','',iRet)
if (iRet.ne.SQL_SUCCESS .and. iRet.ne.SQL_SUCCESS_WITH_INFO) goto 10
 
!Allocate statement handle
call f90SQLAllocHandle(SQL_HANDLE_STMT,ConnHndl, StmtHndl, iRet)
 
!Request a recordset with description of all SQL data types
!allowed by the driver
call f90SQLGetTypeInfo(StmtHndl, SQL_ALL_TYPES , iRet)
 
!Bind all the fields in the structure SourceDataTypes
!this subroutine is part of the added functionality of f90SQL
!(it is not a standard function of the ODBC-API)
call f90SQLBindCol(StmtHndl, SourceDataTypes, iRet)
 
!fetch recordset with type descriptions and print some fields
print *,'List of SQL data types supported by BookSales DSN'
print *,'First column is the type name, second column is '
print *,'maximum column length for the type (in the source)'
 
do while (.true.)
call f90SQLFetch(StmtHndl, iRet)
if (iRet.eq.SQL_NO_DATA) exit
call f90SQLStrFormat(SourceDataTypes%TYPE_NAME, SourceDataTypes%TYPE_NAME)
print *,trim(SourceDataTypes%TYPE_NAME), ',', SourceDataTypes%COLUMN_SIZE
enddo
 
!release statement handle
call f90SQLFreeHandle(SQL_HANDLE_STMT, StmtHndl, iRet)
 
!disconnect
call f90SQLDisconnect(ConnHndl,iRet)
 
10 continue
 
!release connection handle
call f90SQLFreeHandle(SQL_HANDLE_DBC, ConnHndl, iRet)
 
!release environment handle
call f90SQLFreeHandle(SQL_HANDLE_ENV, EnvHndl, iRet)
 
stop
end

 

Here is an example of the output from this program:

 

List of SQL data types supported by BookSales DSN
First column is the type name, second column is
maximum column length (in the source) for the type
BIT, 1
BYTE, 3
LONGBINARY, 1073741824
VARBINARY, 255
BINARY, 255
LONGTEXT, 1073741824
CHAR, 255
CURRENCY, 19
LONG, 10
COUNTER, 10
SHORT, 5
SINGLE, 7
DOUBLE, 15
DATETIME, 19
TEXT, 255

 

The following table lists valid SQL type identifiers for all SQL data types. It also lists the name and description of the corresponding data type from SQL92 (if one exists).

 

Table 4.2. SQL Data type identifiers

 

SQL Type Identifier [1] Typical SQL Data Type [2] Typical Type Description
SQL_CHAR CHAR(n) Character string of fixed string length n.
SQL_VARCHAR VARCHAR(n) Variable-length character string with a maximum string length n.
SQL_LONGVARCHAR LONG VARCHAR Variable length character data. Maximum length is data source–dependent. [9]
SQL_WCHAR WCHAR(n) Unicode character string of fixed string length n.
SQL_WVARCHAR VARWCHAR(n) Unicode variable-length character string with a maximum string length n
SQL_WLONGVARCHAR LONGWVARCHAR Unicode variable-length character data. Maximum length is data source-dependent.
SQL_DECIMAL DECIMAL(p,s) Signed, exact numeric value with a precision of at least p and scale s. The maximum precision is driver-defined.

(1 <= p <= 15; s <= p). [4]

SQL_NUMERIC NUMERIC(p,s) Signed, exact numeric value with precision p and scale s.(1 <= p <= 15; s <= p). [4]
SQL_SMALLINT SMALLINT Exact numeric value with precision 5 and scale 0. If signed, then

–32,768 <= n <= 32,767; If unsigned then 0 <= n <= 65,535. [3]

SQL_INTEGER INTEGER Exact numeric value with precision 10 and scale 0.

If signed then -232 <= n <= 232 – 1; if unsigned then 0 <= n <= 232 – 1. [3]

SQL_REAL REAL Signed, approximate numeric value with binary precision 24. Absolute value from 10–38 to 1038.
SQL_FLOAT FLOAT(p) Signed, approximate numeric value with a binary precision of at least p. The maximum precision is driver-defined. [5]
SQL_DOUBLE DOUBLEPRECISION Signed, approximate numeric value with binary precision 53. Absolute value from 10–308 to 10308.
SQL_BIT BIT Single bit binary data. [8]
SQL_TINYINT TINYINT Exact numeric value with precision 3 and scale 0.

If signed then –128 <= n <= 127; if unsigned then 0 <= n <= 255. [3]
SQL_BIGINT BIGINT Exact numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0.

If signed then –263 <= n <= 263 – 1; If unsigned then 0 <= n <= 264 – 1. [3,9]
SQL_BINARY BINARY(n) Binary data of fixed length n. [9]
SQL_VARBINARY VARBINARY(n) Variable length binary data of maximum length n. The maximum is set by the user. [9]
SQL_LONGVARBINARY LONG VARBINARY Variable length binary data. Maximum length is data source – dependent. [9]
SQL_TYPE_DATE
[6]		 DATE Year, month, and day fields, conforming to the rules of the Gregorian calendar.
SQL_TYPE_TIME
[6]		 TIME(p) Hour, minute, and second fields, with valid values for hours of 00 to 23, valid values for minutes of 00 to 59, and valid values for seconds of 00 to 61. Precision p indicates the second's precision.
SQL_TYPE_TIMESTAMP
[6]		 TIMESTAMP(p) Year, month, day, hour, minute, and second fields, with valid values as defined for the DATE and TIME data types.
SQL_INTERVAL_MONTH
[7]		 INTERVAL MONTH(p) Number of months between two dates; p is the interval leading precision.
SQL_INTERVAL_YEAR
[7]		 INTERVAL YEAR(p) Number of years between two dates; p is the interval leading precision.
SQL_INTERVAL_YEAR_TO_MONTH
[7]		 INTERVAL YEAR(p) TO MONTH Number of years and months between two dates; p is the interval leading precision.
SQL_INTERVAL_DAY
[7]		 INTERVAL DAY(p) Number of days between two dates; p is the interval leading precision.
SQL_INTERVAL_HOUR
[7]		 INTERVAL HOUR(p) Number of hours between two date/times; p is the interval leading precision.
SQL_INTERVAL_MINUTE [7] INTERVAL MINUTE(p) Number of minutes between two date/times; p is the interval leading precision.
SQL_INTERVAL_SECOND [7] INTERVAL SECOND(p,q) Number of seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
SQL_INTERVAL_DAY_TO_HOUR
[7]		 INTERVAL DAY(p) TO HOUR Number of days/hours between two date/times; p is the interval leading precision.
SQL_INTERVAL_DAY_TO_MINUTE
[7]		 INTERVAL DAY(p) TO MINUTE Number of days/hours/minutes between two date/times; p is the interval leading precision.
SQL_INTERVAL_DAY_TO_SECOND
[7]		 INTERVAL DAY(p) TO SECOND(q) Number of days/hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
SQL_INTERVAL_HOUR_TO_MINUTE
[7]		 INTERVAL HOUR(p) TO MINUTE Number of hours/minutes between two date/times; p is the interval leading precision.
SQL_INTERVAL_HOUR_TO_SECOND
[7]		 INTERVAL HOUR(p) TO SECOND(q) Number of hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
SQL_INTERVAL_MINUTE_TO_SECOND
[7]		 INTERVAL MINUTE(p) TO SECOND(q) Number of minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
SQL_GUID GUID Fixed length Globally Unique Identifier.

[1] This is the value returned in the DATA_TYPE column by a call to f90SQLGetTypeInfo.
[2] This is the name returned in the NAME column by a call to f90SQLGetTypeInfo.
[3] An application uses f90SQLGetTypeInfo or f90SQLColAttribute to determine if a particular data type or a particular column in a result set is unsigned.
[4] SQL_DECIMAL and SQL_NUMERIC data types differ only in their precision. The precision of a DECIMAL(p,s) is an implementation-defined decimal precision that is no less than p, while the precision of a NUMERIC(p,s) is exactly equal to p.
[5] Depending on the implementation, the precision of SQL_FLOAT can be either 24 or 53: if it is 24, the SQL_FLOAT data type is the same as SQL_REAL, if it is 53, the SQL_FLOAT data type is the same as SQL_DOUBLE.
[6] In ODBC 3.5, the SQL date, time, and timestamp data types are SQL_TYPE_DATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP, respectively.  In ODBC 2.x, the data types are SQL_DATE, SQL_TIME, and SQL_TIMESTAMP.
[7] For more information on the interval SQL data types, see the Appendix 4 "Interval Data Types".
[8] The SQL_BIT data type has different characteristics than the BIT type in SQL92.
[9] This data type has no corresponding data type in SQL92.