Skip to content

Latest commit

 

History

History
87 lines (68 loc) · 6.05 KB

mapping-data-types-odbc.md

File metadata and controls

87 lines (68 loc) · 6.05 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Mapping Data Types (ODBC)
Mapping Data Types (ODBC)
markingmyname
maghan
03/14/2017
sql
native-client
reference
mapping data types [ODBC]
result sets [ODBC], data types
ODBC data types, mapping
SQL Server Native Client ODBC driver, result sets
ODBC applications, result sets
data types [ODBC], mapping
sql_variant data type
SQL Server Native Client ODBC driver, data types

Mapping Data Types (ODBC)

[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW]

The [!INCLUDEssNoVersion] Native Client ODBC driver maps [!INCLUDEssNoVersion] SQL data types to ODBC SQL data types. The sections below discuss the [!INCLUDEssNoVersion] SQL data types and the ODBC SQL data types to which they map. They also discuss the ODBC SQL data types and their corresponding ODBC C data types, and the supported and default conversions.

Note

The [!INCLUDEssNoVersion]timestamp data type maps to the SQL_BINARY or SQL_VARBINARY ODBC data type because the values in timestamp columns are not datetime values, but binary(8) or varbinary(8) values that indicate the sequence of [!INCLUDEssNoVersion] activity on the row. If the [!INCLUDEssNoVersion] Native Client ODBC driver encounters a SQL_C_WCHAR (Unicode) value that is an odd number of bytes, the trailing odd byte is truncated.

Dealing with sql_variant Data Type in ODBC

The sql_variant data type column can contain any of the data types in [!INCLUDEssNoVersion] except large objects (LOBs), such as text, ntext, and image. For example, the column could contain smallint values for some rows, float values for other rows, and char/nchar values in the remainder.

The sql_variant data type is similar to the Variant data type in [!INCLUDE visual-basic-md].

Retrieving Data from the Server

ODBC does not have a concept of variant types, limiting the use of the sql_variant data type with an ODBC driver in [!INCLUDEssNoVersion]. In [!INCLUDEssNoVersion], if binding is specified, the sql_variant data type must be bound to one of the documented ODBC data types. SQL_CA_SS_VARIANT_TYPE, a new attribute specific to the [!INCLUDEssNoVersion] Native Client ODBC driver, returns the data type of an instance in the sql_variant column to the user.

If no binding is specified, the SQLGetData function can be used to determine the data type of an instance in the sql_variant column.

To retrieve sql_variant data follow these steps.

  1. Call SQLFetch to position to the row retrieved.

  2. Call SQLGetData, specifying SQL_C_BINARY for the type and 0 for the data length. This forces the driver to read the sql_variant header. The header provides the data type of that instance in the sql_variant column. SQLGetData returns the size (in bytes) of the value.

  3. Call SQLColAttribute by specifying SQL_CA_SS_VARIANT_TYPE as its attribute value. This function will return the C data type of the instance in the sql_variant column to the client.

Here is a code segment showing the preceding steps.

while ((retcode = SQLFetch (hstmt))==SQL_SUCCESS)  
{  
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)  
    {  
        SQLError (NULL, NULL, hstmt, NULL,   
                    &lNativeError,szError,MAX_DATA,&sReturned);  
        printf_s ("%s\n",szError);  
        goto Exit;  
    }  
    retcode = SQLGetData (hstmt, 1, SQL_C_BINARY,   
                                pBuff,0,&Indicator);//Figure out the length  
    if (retcode != SQL_SUCCESS_WITH_INFO && retcode != SQL_SUCCESS)  
    {  
        SQLError (NULL, NULL, hstmt, NULL, &lNativeError,   
                    szError,MAX_DATA,&sReturned);  
        printf_s ("%s\n",szError);  
        goto Exit;  
    }  
    printf_s ("Byte length : %d ",Indicator); //Print out the byte length  
  
    int iValue = 0;  
    retcode = SQLColAttribute (hstmt, 1, SQL_CA_SS_VARIANT_TYPE, NULL,   
                                        NULL,NULL,&iValue);  //Figure out the type  
    printf_s ("Sub type = %d ",iValue);//Print the type, the return is C_type of the column]  
  
// Set up a new binding or do the SQLGetData on that column with   
// the appropriate type  
}

If the user creates the binding using SQLBindCol, the driver reads the metadata and the data. The driver then converts the data to the appropriate ODBC type specified in the binding.

Sending Data to the Server

SQL_SS_VARIANT, a new data type specific to the [!INCLUDEssNoVersion] Native Client ODBC driver, is used for data sent to an sql_variant column. When sending data to the server using parameters (for example, INSERT INTO TableName VALUES (?,?)), SQLBindParameter is used to specify the parameter information including the C type and the corresponding [!INCLUDEssNoVersion] type. The [!INCLUDEssNoVersion] Native Client ODBC driver will convert the C data type to one of the appropriate sql_variant subtypes.

See Also

Processing Results (ODBC)