Datatype Mapping

The SQL Server ODBC driver implements SQLDescribeCol and SQLColAttributes using the metadata information returned in the TDS header for that result set. Normally, an Open Data Services gateway application sets this metadata information via the srv_describe call, using SQL Server datatype codes, so that the Open Data Services ODBC driver maps the SQL Server datatype to the ODBC datatype and returns the information to the client.

This method of datatype mapping is valid whenever there is a one-to-one correspondence between the datatype supported by the data source, by SQL Server, and by the ODBC client (as with integer and floating-point datatypes). However, this method is not valid when an ODBC datatype is supported by the data source but has no corresponding SQL Server datatype (and therefore no underlying TDS protocol support).

For example, the TIMESTAMP datatype creates mapping incompatibilities between the three systems. Open Data Services gateways could convert TIMESTAMP data to character data using the default ODBC conversion, and then describe the data as SRVVARCHAR using srv_describe. It is important to note that if the only datatype information passed to the Open Data Services ODBC driver is the SQL Server SRVVARCHAR datatype, the driver has no way of knowing the original datatype. Thus, without additional datatype information, the SQLDescribeCol and SQLColAttributes calls made by the driver cannot return a TIMESTAMP datatype to the ODBC application.

Open Data Services gateway applications can convey lost information about the original datatype to the Open Data Services ODBC driver by filling in the Open Data Services usertype descriptor. To set this user-defined datatype field, use the srv_setutype call. The gateway should encode a user-defined datatype for all fields of a result set, regardless of whether there is an exact match to a SQL Server datatype. If the gateway does not set a usertype descriptor, the Open Data Services ODBC driver assumes that the data source supports SQL Server datatypes.

The following information about the original datatype must be conveyed in the user datatype field:

• Datatype code ¾ the ODBC-defined encoding of the original datatype. The Open Data Services ODBC driver uses this code to look up the datatype name and other attributes.
• Case Sensitivity ¾ whether a character column is case sensitive. In some DBMS implementations, case sensitivity can be set on a database, table, or column basis. Valid values are TRUE and FALSE.
• Nullability ¾ whether a character column is "nullable." This information is conveyed in the TDS metadata for numeric datatypes only, so it would be lost if it were not put in the user datatype field. Valid values are TRUE and FALSE.
• Updateability ¾ whether a column can be updated. Valid values are SQL_ATTR_READONLY, SQL_ATTR_WRITE 1, and SQL_ATTR_READWRITE_UNKNOWN 2 (from the ODBC header file SQLEXT.H).
• Datatype lookup key (fNew) ¾ whether the datatype code described above refers to an ODBC datatype or to a gateway-defined datatype. Valid values are NEWODBCTYPE and NEWUSERTYPE.
• Precision ¾ the length of the particular data value being described. For non-numeric datatypes, precision is an SWORD that holds the length of the actual data at the source. For numeric datatypes, precision is a BYTE that combines with the Scale value described below to form the num structure.
• Scale ¾ the scale of the source column data. Used only for numeric datatypes.

To convey this extra information in the 4 bytes that are provided for a user datatype, the user datatype field returned in the TDS metadata is formatted using the following structure:

typedef struct odsusertype
{
    CHAR    fType;
        BYTE fNullable:1,
            fCaseSensitive:1,
            fUpdateable:2,
            fUnused:2,
            fNew:2;
        union
        {
            struct
            {
                BYTE cbPrecision;
                BYTE ibScale;
            } num;
            SWORD    cbPrecision;
        };
    } ODSUSERTYPE;
    #define NEWODBCTYPE 1
    #define NEWUSERTYPE 2