Overview of GOLDILOCKS ODBC Driver

Concepts of GOLDILOCKS ODBC Driver

Open Database Connectivity (ODBC) is a specifications for a database Application Programming Interface (API). Microsoft ODBC version 3.0 is based on International Standards Organization/ International Electromechanical Commission (ISO/ IEC) and the recommended specifications of Call Level Interface (CLI) of X/ open. ODBC supports the SQL statements by using C library functions. The application implements the ODBC features by calling these functions.

ODBC architecture has four components which perform the following features.

The following operations can be executed by using ODBC applications.

• Connecting to a data source

• Sending SQL statements to the data source

• Processing the results of an SQL statement in the data source

• Handling errors and messages

• Terminating the connection to the data source

Overview of ODBC Components

GOLDILOCKS ODBC Driver Including Driver Manager

The following is a software architecture of when the driver manager is included in the system. In this case, the application should be linked to the driver manager library.

GOLDILOCKS ODBC Driver Not Including Driver Manager

The following is an architecture of when the application does not include a driver manager and uses GOLDILOCKS ODBC driver. In this case, the application should be linked to GOLDILOCKS ODBC driver library.

Using GOLDILOCKS ODBC Driver

Header File

goldilocks.h file installed on $GOLDILOCKS_HOME/include should be included to execute GOLDILOCKS ODBC driver. This file defines constant and type of GOLDILOCKS ODBC driver, and provides a function prototype of GOLDILOCKS ODBC driver function.

Library

The application which does not use a driver manager should be linked to a static or shared version of GOLDILOCKS ODBC driver library.

UNIX

Windows

GOLDILOCKS Windows ODBC driver libraries provide CS libraries only.

Data Source Configuration

DSN Configuration on UNIX

odbcinst.ini File

odbcinst.ini file is a configuration file for installed ODBC driver.

• unixODBC

% odbcinst -j
unixODBC 2.3.2
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/goldilocks/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

• iODBC

% iodbc-config --odbcinstini
/etc/odbcinst.ini

ODBC Driver Specification

ODBC driver specification section in odbcinst.ini file specifies the driver property values and list. The registered information section is under the driver name in each driver installed.

[driver_name]
Description = driver_description
Driver = driver_library_path
Setup = setup_library_path
FileUsage = file_usage

The following table describes keywords in the driver specification section.

The following is an example of information of GOLDILOCKS ODBC driver specifications.

[GOLDILOCKS ODBC Driver]
Description= GOLDILOCKS ODBC Driver
Driver = /home/goldilocks/home/lib/libgoldilockscs-ul64.so
Setup = /home/goldilocks/home/lib/libgoldilockscs-ul64.so
FileUsage = 0

odbc.ini File

odbc.ini file is the configuration file for the DSN connected by the application, and it is divided into a user DSN and system DSN. Typically, a user DSN file is ~ / .odbc.ini file, and a system DSN file is /etc/odbc.ini.

• unixODBC

% odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/goldilocks/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

• iODBC

% iodbc-config --odbcini
/etc/odbc.ini

Data Source Specification

Data source specification section of the odbc.ini file describes DSN.

[data_source_name]
Driver = driver_name
PROTOCOL = {DA | TCP | IPC}
CS_MODE = {default | dedicated | shared}
HOST = host_address
PORT = port_no
PREFER_IPV6 = {0 | 1}
CHARSET = {SQL_ASCII | UTF8 | UHC | GB18030}
TCP_NODELAY = {0 | 1}
ALTERNATE_SERVERS = (HOST=ADDRESS1:PORT=PORT1,HOST=ADDRESS2:PORT=PORT2)
CONNECTION_RETRY_COUNT = retry_count
CONNECTION_RETRY_DELAY = retry_delay
FAILOVER_TYPE = {CONNECTION | SESSION}
FAILOVER_GRANULARITY = {0 | 1 | 2}
FAILOVER_ROUTING_POLICY = {0 | 1}
DATE_FORMAT = date_format_string
TIME_FORMAT = time_format_string
TIME_WITH_TIME_ZONE_FORMAT = timetz_format_string
TIMESTAMP_FORMAT = timestamp_format_string
TIMESTAMP_WITH_TIME_ZONE_FORMAT = timestamptz_format_string
CHAR_LENGTH_UNITS = {BYTE | OCTETS | CHAR | CHARACTERS}
ENABLE_SQLDESCRIBEPARAM = {0 | 1}
ENABLE_SQLBINDPARAMETER_CONSISTENCY_CHECK = {0 | 1}
USE_TARGETTYPE = {0 | 1 | 2} 
LOCATOR_DSN = locator_dsn_name
LOCATOR_SERVICE = locator_service_name
LOCALITY_AWARE_TRANSACTION = {0 | 1}
LOCALITY_GROUP_POLICY = {0 | 1 | 2}
LOCALITY_GROUP_PATH = group_name1, group_name2, group_name3
LOCALITY_MEMBER_POLICY = {0 | 1 | 2 | 3 | 4}
LOCALITY_MEMBER_PATH = member_name1,member_name2, member_name3
DB_HOME = database_home_path
PACKET_COMPRESSION_THRESHOLD = packet_compression_threshold
USE_GLOBAL_SESSION = {0 | 1}
CONNECTION_TIMEOUT = connection_timeout
LOGIN_TIMEOUT = login_timeout
TRACE = {0 | 1}
TRACEFILE = file_path_name
TRACE_POLICY={DEFAULT | ERROR}
INCLUDE_SYNONYMS = {0 | 1}
DOT_NET_FOR_ODBC = {0 | 1}

[locator_dsn_name]
FILE = location_file_name
HOST = IP address(v4)
PORT = locator_port
CONNECTION_TIMEOUT = second 
ALTERNATE_LOCATORS = (HOST=ADDRESS1:PORT=PORT1,HOST=ADDRESS2:PORT=PORT2)

The following table describes keywords in the data source specification section.

The following is an example of DSN configuration of GOLDILOCKS.

[GOLDILOCKS]
Driver = GOLDILOCKS ODBC Driver
PROTOCOL = TCP
CS_MODE = SHARED
HOST = 192.168.0.10
PORT = 22581
CHARSET = UTF8
TCP_NODELAY = 1
ALTERNATE_SERVERS = (HOST=192.168.0.11:PORT=22581,HOST=192.168.0.12:PORT=22581)
CONNECTION_RETRY_COUNT = 3
CONNECTION_RETRY_DELAY = 1
FAILOVER_TYPE = SESSION
FAILOVER_GRANULARITY = 0
FAILOVER_ROUTING_POLICY = 0
DATE_FORMAT = YYYY-MM-DD
TIME_FORMAT = HH24:MI:SS.FF6
TIME_WITH_TIME_ZONE_FORMAT = HH24:MI:SS.FF6 TZH:TZM
TIMESTAMP_FORMAT = YYYY-MM-DD HH24:MI:SS.FF6
TIMESTAMP_WITH_TIME_ZONE_FORMAT = YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM
CHAR_LENGTH_UNITS = CHARACTERS
ENABLE_SQLDESCRIBEPARAM = 1
ENABLE_SQLBINDPARAMETER_CONSISTENCY_CHECK = 1

USE_TARGETTYPE = 0
INCLUDE_SYNONYMS = 0

PACKET_COMPRESSION_THRESHOLD = 2113929216

LOCALITY_AWARE_TRANSACTION = 0
LOCALITY_GROUP_POLICY = 0
LOCALITY_GROUP_PATH = G1,G2,G3
LOCALITY_MEMBER_POLICY = 0
LOCALITY_MEMBER_PATH = G1N1,G2N1,G3N1,G1N2,G2N2,G3N2

USE_GLOBAL_SESSION = 0

CONNECTION_TIMEOUT = 0
LOGIN_TIMEOUT = 0

LOCATOR_DSN = LOCATOR
LOCATOR_SERVICE = S1

TRACE = 1
TRACEFILE = /home/test/log/mytrace.log

DOT_NET_FOR_ODBC = 0

[LOCATOR]
FILE = /home/test/.location.ini
HOST = 127.0.0.1
PORT = 42581
ALTERNATE_LOCATORS=(HOST=127.0.0.1:PORT=42582,HOST=127.0.0.1:PORT=42583)

DSN Configuration on Windows

ODBC data source manager can add or set up DSN on Windows.

The following table describes keywords for DSN configuration.

GLOBAL CONNECTION

GLOBAL CONNECTION of which an application selects and performs an appropriate node for a query processing in cluster environment is supported.

Settings

LOCALITY_AWARE_TRANSACTION property value should be set together with LOCATOR file or LOCATOR server to use GLOBAL CONNECTION. USE_GLOBAL_SESSION property value should be set to 1 to use the global session feature.

• .odbc.ini of when using DSN

[GOLDILOCKS]
HOST = 192.168.0.1
PORT = 22581
UID = TEST
PWD = test
LOCALITY_AWARE_TRANSACTION = 1
LOCATOR_DSN = LOCATOR

[LOCATOR]
FILE = /home/goldilocks/.location.ini

• Using connection string

SQLDriverConnect( dbc,
                  NULL,
                  (SQLCHAR*)"HOST=192.168.0.1;PORT=22581;UID=TEST;PWD=test;LOCALITY_AWARE_TRANSACTION=1;LOCATOR_HOST=192.168.0.2;LOCATOR_PORT=42581",
                  SQL_NTS,
                  NULL,
                  0,
                  NULL,
                  SQL_DRIVER_NOPROMPT );

Processing GLOBAL CONNECTION

1. SQLAllocHandle (DBC)

   It allocates a connection handle.

2. SQLConnect

   It connects to a server which was given server information from a user, and obtains the information about the cluster system. Then builds the cluster system information through LOCATOR file or LOCATOR server, and connects to all nodes of cluster system.

3. SQLAllocHandle( STMT )

   It allocates a statement to each of all connected nodes.

4. SQLPrepare

   It prepares to execute SQL in all connected nodes.

5. SQLExecute

   If the information about the cluster system is not built, an application builds the information about the cluster system through LOCATOR file or LOCATOR server, then connects to all nodes in the cluster system.

   When connecting to a new node by adding a node to cluster, all statements of other nodes are equally created in that node, and prepares to execute SQL.

If the information about a sharding key has already built, an application selects an appropriate node and performs a query by using the sharding key information.
If the information about a sharding key is not built, an application builds the sharding key information of the SQL from an arbitrary server, then selects a node and performs a query.

If an error occurs on the selected node, an appropriate node is selected again, then performs a query.

If the information about a sharding key is altered after SQLExecute, then it deletes the built   information about a sharding key
If the information about the cluster system is altered such as adding or deleting a cluster node after SQLExecute, then it deletes the built cluster system information.

6. SQLFetch

   It brings data from the node on which SQL was executed.

7. SQLCloseCursor

   It closes a cursor from the node on which SQL was executed.

8. SQLFreeHandle( STMT )

   It releases a statement from all connected nodes.

9. SQLDisconnect

   It releases connections with all nodes.

10. SQLFreeHandle( DBC )

    It releases a connection handle.

Handling GLOBAL CONNECTION Exception

When using GLOBAL CONNECTION, if an error occurs on the selected node during the operation, then it is operated as follows according to the transaction occurrence and SELECT progress.

• When a transaction did not occur

If an error occurs on the selected node when a transaction did not occur, then it selects another node within ODBC and executes the query. Though an error occurred on the selected node, the query was normally executed on another node, so it does not transfer an error to a user.

• When a transaction occurred or SELECT is in progress

If an error occurs on the selected node when a transaction occurred or SQLFetch() is in progress, then ODBC can not proceeds the current operation any more so it transfers 19068(Retry the transactional operations) error. If 19068 error occurs, then a user should perform the transaction or SELECT again.

if( !SQL_SUCCEEDED(SQLPrepare( sStmt,
                               (SQLCHAR*)"INSERT INTO T1 VALUES ( ? )",
                               SQL_NTS )) )
{
    goto stmt_error;
}

trans_retry:

if( !SQL_SUCCEEDED(SQLExecute( sStmt )) )
{
    SQLGetDiagRec( SQL_HANDLE_STMT,
                   sStmt,
                   1,
                   sSQLState,
                   &sNativeError,
                   sMessageText,
                   sizeof(sMessageText),
                   &sTextLength );

    if( sNativeError == 19068 )
    {
        goto trans_retry;
    }
        
    goto stmt_error;
}

if( !SQL_SUCCEEDED(SQLPrepare( sStmt,
                               (SQLCHAR*)"SELECT * FROM T1 WHERE C1 = ?",
                               SQL_NTS )) )
{
    goto stmt_error;
}

trans_begin:

sReturn = SQLExecute( sStmt );

if( sRetrun == SQL_ERROR )
{
    SQLGetDiagRec( SQL_HANDLE_STMT,
                   sStmt,
                   1,
                   sSQLState,
                   &sNativeError,
                   sMessageText,
                   sizeof(sMessageText),
                   &sTextLength );

    if( sNativeError == 19068 )
    {
        goto trans_retry;
    }
        
    goto stmt_error;
}

while( 1 )
{
    sReturn = SQLFetch( sStmt );

    if( sReturn == SQL_NO_DATA )
    {
        SQLCloseCursor( sStmt );
        break;
    }
    else if( sReturn == SQL_ERROR )
    {
        SQLGetDiagRec( SQL_HANDLE_STMT,
                       sStmt,
                       1,
                       sSQLState,
                       &sNativeError,
                       sMessageText,
                       sizeof(sMessageText),
                       &sTextLength );

        if( sNativeError == 19068 )
        {
            goto trans_retry;
        }
        
        goto stmt_error;
    }

    ...
}

• When committing or rolling back a transaction

If an error occurs on the selected node when committing a transaction, then ODBC checks whether the transaction has been committed before the error occurred through another node. Though an error occurred on the selected node, if the transaction was normally committed, then it does not transfer an error. Also, if an error occurred on the selected node when a transaction has not been committed, then it transfers 19068(Retry the transactional operations) error. If 19068 error occurs, then a user should perform the transaction again.

If an error occurs on the selected node when rolling back a transaction, then ODBC does not transfer an error. It is because the transaction already has been rolled back due to the node error.

if( !SQL_SUCCEEDED(SQLSetConnectAttr( sDbc,
                                      SQL_AUTOCOMMIT,
                                      (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
                                      0 ) ))
{
    goto dbc_error;
}

if( !SQL_SUCCEEDED(SQLPrepare( sStmt,
                               (SQLCHAR*)"INSERT INTO T1 VALUES ( ? )",
                               SQL_NTS )) )
{
    goto stmt_error;
}

trans_retry:

if( !SQL_SUCCEEDED(SQLExecute( sStmt )) )
{
    SQLGetDiagRec( SQL_HANDLE_STMT,
                   sStmt,
                   1,
                   sSQLState,
                   &sNativeError,
                   sMessageText,
                   sizeof(sMessageText),
                   &sTextLength );

    if( sNativeError == 19068 )
    {
        goto trans_retry;
    }
        
    goto stmt_error;
}

if( !SQL_SUCCEEDED(SQLEndTran( SQL_HANDLE_DBC,
                               sDbc,
                               SQL_COMMIT)) )
{
    SQLGetDiagRec( SQL_HANDLE_DBC,
                   sDbc,
                   1,
                   sSQLState,
                   &sNativeError,
                   sMessageText,
                   sizeof(sMessageText),
                   &sTextLength );

    if( sNativeError == 19068 )
    {
        goto trans_retry;
    }
        
    goto stmt_error;
}

Constraints of GLOBAL CONNECTION

• Use SQLPrepare() and SQLExecute() to select a node appropriate for the query.

Use SQLPrepare() and SQLExecute() to select a node appropriate for the query by using GLOBAL CONNECTION. SQLExecDirect() does not have an information required to select a node appropriate for the query, so the node is selected according to OCALITY_GROUP_POLICY and LOCALITY_MEMBER_POLICY property.

• Use SQLEndTran() to commit or rollback a transaction.

If committing or rolling back with SQL statement when using GLOBAL CONNECTION, then the status change of the transaction is not detected. It is mandatory to use SQLEndTran() to commit or rollback the transaction.

• Global session feature does not support Data Definition Language (DDL) among SQL statements.

Catalog Function

All databases have schemas of how to store the data in the database. For example, a simple sales order database will have the schemas shown in the following figure, and the ID columns are used to connect the tables.

The schema is stored in the set of system tables which is called as a database catalog along with other information such as privileges. This is also known as a data dictionary.

Applications can find this schema by calling the catalog functions. Catalog functions return the information to the result set, and typically they are implemented by SELECT statements for the tables in the catalog.

Using Catalog Data

Applications use catalog data in various ways. The followings are some common usages.

Configuring SQL statements at the time of execution

The vertical applications such as the order input application include the hard-coded SQL statements. The tables and columns used by the application were previously fixed, and the application access these tables. For example, the ordering application typically has one parameterized statement to add a new order to the system.

A general application such as a spreadsheet program using ODBC for collecting the data sometimes configures the SQL statement based on the input from a user at the time of execution. This application may request a user for the format to use tables and columns. However, if the list of tables or columns selected by a user is shown to the application, it will be easier to the user. The application will call catalog functions such as SQLTables and SQLColumns to configure these lists.

Configuring SQL statements during the development

The application development environments allow the developers to create database queries during developing the program. And then the queries are hard-coded and embedded into the application.

These environments can also create a list of what were selected by the developer by using the SQLTables and SQLColumns. The environments find out and display the relationships among the tables automatically selected by using SQLPrimaryKeys and SQLForeignKeys. Then they find out and emphasize the index fields by using SQLStatistics, so the developers can create queries effectively.

Configuring a Cursor

The application, driver, middleware which provides a scroll cursor, find out column(s) which is the only column of identifying a row by using SQLSpecialColumns. The program can configure a keyset including the values of these columns for the collected rows. The application will use these values to collect the latest data for the rows by scrolling backwards.

Catalog Function on ODBC

ODBC includes the following catalog functions.

Data Returning of Catalog Function

Each catalog function returns the data as a result set. The result set is not different from any other result set. It is usually hard-coded into the driver, or it is created by the predefined statement such as the parameterized SELECT statement stored in the procedure of data source.

The result set for each catalog function is described in For More Information paragraph of each function in this user manual. The result set can include the columns specified in the driver after the column selected last besides the listed columns. These columns are described in a user manual for the driver.

The applications bind the columns specified in the driver based on the end of result set. They calculate the number of columns specified in the driver as the number of last columns which are smaller than the number of columns after the required column. This saves the trouble of changing the application when a new column is added in future version or ODBC driver. To operate this schema, drives should add the columns specified in the new driver before the columns specified in the old driver to prevent changing the row number based on the end of result set.

Even when they include special characters, they do not quote the identifiers returned in the result set. For example, if the Accounts Payable table's identifier quote character which is specified in the driver and returned by SQL GetInfo is a double quotes ("), and the Accounts Payable table has a Customer Name column, then TABLE_NAME column value in the rows which is returned by SQLColumns is Accounts Payable, but it is not "Accounts Payable", and COLUMN_NAME column value is Customer Name, but it is not "Customer Name". 
The application collects the names of customers from the Accounts Payable table as follows.

SELECT "Customer Name" FROM "Accounts Payable"

Catalog functions are based on SQL-like model in connection based on the user name and password, and their data is returned only to the users with the proper privilege. The file password protection which is inappropriate to this model is defined by the drivers.

Most result set returned by the catalog functions can not be updated, and the application should not expect to change the database schema by updating the data in the result set.

Arguments of Catalog Function

Pattern Value Argument

Some arguments in catalog function accept the search pattern, like as TableName argument in SQLTables. The arguments accept the search pattern if SQL_ATTR_METADATA_ID attribute is set to SQL_FALSE. The arguments do not accept the search pattern if SQL_ATTR_METADATA_ID attribute is set to SQL_TRUE.

Search pattern letters have the following features.

• An underscore(_) represents any single character.

• Percent sign (%) represents zero or more characters.

• An escape character is specified in the driver and it is used to include the percent sign, underscore, escape character as literals. If an escape character is prior to a non-special character, it does not have any special meaning. But if an escape character is prior to a special character, it is a special character. For example, "\a" is treated as two characters consisting of "\" and "a", but "\%" refers to "%".

An escape character is returned by using SQL_SEARCH_PATTERN_ESCAPE option in SQLGetInfo. To include that character as a literal in an argument which accepts search patterns, it should be prior to any underscore, percent sign, or escape character.

The following table describes how to use search patterns.

Passing a NULL pointer to the search pattern argument does not force the argument to search. NULL pointer and the search patterns % (any character) are equivalent. However, a zero-length search pattern is matched with an empty string ("").

ODBC API References

SQLAllocConnect

Conformance

Introduced version: ODBC 1.0

Overview

SQLAllocConnect function is replaced by SQLAllocHandle function in ODBC 3.x. 
For more information, refer to SQLAllocHandle.

Syntax

SQLRETURN SQLAllocConnect(
    SQLHENV   EnvironmentHandle,
    SQLHDBC * ConnectionHandlePtr);

Arguments

EnvironmentHandle

[Input] It is the environment handle.

ConnectionHandlePtr

[Output] It is the pointer of the connection handle to be newly allocated.

SQLAllocEnv

Conformance

Introduced version: ODBC 1.0

Overview

SQLAllocEnv function is replaced by SQLAllocHandle function in ODBC 3.x. 
For more information, refer to SQLAllocHandle.

Syntax

SQLRETURN SQLAllocEnv(
    SQLHENV * EnvironmentHandlePtr);

Arguments

EnvironmentHandlePtr

[Output] It is the pointer of the environment handle to be newly allocated.

SQLAllocHandle

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLAllocHandle allocates the environment handle, the connection handle, or the statement handle.

Syntax

SQLRETURN SQLAllocHandle(
    SQLSMALLINT   HandleType,
    SQLHANDLE     InputHandle,
    SQLHANDLE *   OutputHandlePtr);

Arguments

HandleType

[Input] It is the handle type allocated by SQLAllocHandle and it should be one of SQL_HANDLE_DBC, SQL_HANDLE_ENV, SQL_HANDLE_STMT.

InputHandle

[Input] If HandleType is SQL_HANDLE_ENV, it is SQL_NULL_HANDLE.

If HandleType is SQL_HANDLE_DBC, it should be an environment handle.

If it is SQL_HANDLE_STMT, it should be a connection handle.

OutputHandlePtr

[Output] It is the newly allocated handle pointer.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, SQL_ERROR

Diagnosis

Description

SQLAllocHandle is used to allocate the handle for environment, connection, statement, descriptor. When using SQLAllocHandle with *OutputHandlePtr, the driver will overwrite the information on the correspoidning handle. The driver manager can not verify whether the handle in *OutputHandlePtr is already used, and it can not know the previously overwritten information.

Allocating Environment Handle

The environment handle provides the global information such as whether the connection handle is valid or active.

For requesting the environment handle, the application calls SQLAllocHandle whose HandleType is SQL_HANDLE_ENV and whose InputHandle is SQL_NULL_HANDLE. The driver allocates memory for the environment information, and passes an allocated handle to *OutputHandle argument. The application passes the value of *OutputHandle to the call requiring the environment handle argument.

After allocating the environment handle, the application should set the attribute of SQL_ATTR_ODBC_VERSIONby calling SQLSetEnvAttr. If the attribute is not set when calling SQLAllocHandle for allocating the connection handle, SQLSTATE HY010 (Function sequence error) is returned.

Allocating Connection Handle

The connection handle provides the information such as whether the statement is valid, the descriptor handle is connected, or the transaction currently is opened.

For requesting the connection handle, the application calls SQLAllocHandle whose HandleType is SQL_HANDLE_DBC. InputHandler argument is set to the environment handle returned by calling SQLAllocHandle. The driver allocates memory for the connection information, and passes an allocated handle to *OutputHandle argument. The application passes the value of *OutputHandle to the call requiring a connection handle argument.

If the environment attribute of SQL_ATTR_ODBC_VERSION is not set before the calling SQLAllocHandle which allocates a connection handle, SQLSTATE HY010(Function sequence error) is returned.

Allocating Statement Handle

The statement handle provides the information such as the error message, the cursor name and the SQL statement processing status.

For requesting the statement handle, the application connects to the data source and then calls SQLAllocHandle before sending the SQL statement. In this call, HandleType should be set to SQL_HANDLE_STMT and InputHandler should be set to the connection handle returned by calling SQLAllocHandle. The driver allocates memory for the statement information, and passes the allocated handle to *OutputHandle argument. The application passes the value of *OutputHandle to the call requiring a statement handle argument.

If the statement handle is allocated, the driver automatically allocates four descriptor sets, and these descriptor handles are allocated to the statement attribute of SQL_ATTR_APP_ROW_DESC, SQL_ATTR_APP_PARAM_DESC, SQL_ATTR_IMP_ROW_DESC and SQL_ATTR_IMP_PARAM_DESC. This is called as implicit descriptor allocation.

SQLAllocStmt

Conformance

Introduced version: ODBC 1.0

Overview

SQLAllocStmt function is replaced by SQLAllocHandle function in ODBC 3.x. 
For more information, refer to SQLAllocHandle.

Syntax

SQLRETURN SQLAllocStmt(
    SQLHDBC    ConnectionHandle,
    SQLHSTMT * StatementHandlePtr);

Arguments

ConnectionHandle

[Input] It is the connection handle.

StatementHandlePtr

[Output] It is the pointer of the statement handle to be newly allocated.

SQLBindCol

Conformance

Introduced version: ODBC 1.0
Standards compliance: ISO 92

Overview

SQLBindCol binds the application data buffer to the columns in the result set.

Syntax

SQLRETURN SQLBindCol(
    SQLHSTMT       StatementHandle,
    SQLUSMALLINT   ColumnNumber,
    SQLSMALLINT    TargetType,
    SQLPOINTER     TargetValuePtr,
    SQLLEN         BufferLength,
    SQLLEN *       StrLen_or_Ind);

Arguments

StatementHandle

[Input] It is the statement handle.

ColumnNumber

[Input] It is the column number in the result set to be bound. The number is in ascending order starting from 1.

TargetType

[Input] It is the identifier of C data type of *TargetValuePtr buffer. When retrieving data using SQLFetch, SQLFetchScroll, SQLSetPos, the driver converts the data into this type.

If TargetType is the interval data type, the default value is the interval leading precision (2), interval seconds precision (6), and it is set in each field of SQL_DESC_DATETIME_INTERVAL_PRECISION and SQL_DESC_PRECISION of ARD. If TargetType argument is SQL_C_NUMERIC, the default value is precision (38), scale (0), and it is set in each field of SQL_DESC_PRECISION and SQL_DESC_SCALE of ARD. If the default precision and scale are not appropriate, the application must explicitly set the descriptor field by calling SQLSetDescField or SQLSetDescRec.

TargetValuePtr

[Delayed Input/Output] It is the data buffer pointer to bind to the column. SQLFetch and SQLFetchScroll return data to this buffer.

If TargetValuePtr is the null pointer, the driver releases the data buffer binding for the column. The application may release the binding of all the columns by calling SQLFreeStmt as SQL_BIND option. The application can release the data buffer binding for the column by setting TargetValuePtr argument as a null pointer and calling SQLBindCol. But if StrLen_or_IndPtr is valid, the length/indicator buffer for the column is still bound.

BufferLength

[Input] It is the length of *TargetValuePtr buffer in bytes.

The driver uses BufferLength in order to avoid writing beyond the end of *TargetValuePtr buffer when returning the variable-length data such as text or binary data. It should be noted that the driver take into account the null terminator when returning the character data in *TargetValuePtr. So, *TargetValuePtr should include the space for a null terminator, otherwise the drive may drop the data. The driver assumes that the buffer is large enough to store data and it ignores BufferLength when returning a fixed-length data structure such as an integer or date. It is important that the application allocates the large buffer for the fixed-length data, otherwise the driver can write beyond the end of the buffer.

StrLen_or_IndPtr

[Delayed Input/Output] It is the pointer to the length/indicator buffer to be bound to the column. SQLFetch and SQLFetchScroll return the value to this buffer.

SQLFetch and SQLFetchScroll return the length of data for the length/indicator buffer, SQL_NO_TOTAL and SQL_NULL_DATA. If there are two separate buffers for the length and indicator, the length buffer may return all values, and the indicator buffer may only return only SQL_NULL_DATA. If StrLen_or_IndPtr is the null pointer, the value of length/indicator is not used and an error occurs when getting the null data.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, SQL_ERROR

Diagnosis

Description

SQLBindCol is used to bind columns in the result set to the data buffer and the length/indicator buffers in the application. The application calls SQLFetch and SQLFetchScroll to retrieve the data, and the driver returns the bound column data to the specified buffer.
The application does not bind the column, and the data is retrieved by calling SQLGetData.

Binding Column

The application calls SQLBindCol to bind the column, and passes the column number, the type, the address, the data buffer length and the address of length/indicator buffer.

Though the application binds the buffer by calling SQLBindCol, but the driver accesses them when calling SQLFetch and SQLFetchScroll, so these buffers are delayed when it is used. Therefore, the application should make the pointer set in SQLBindCol to be valid until the data is returned. If the application calls after making the pointer invalid, like when releasing the buffer, the result is not correct.

The binding remains until when it is replaced by a new binding, the column binding is released, or the statement is released.

Releasing Bound Column

To release only one bound column, set the column number to be released as ColumnNumber and call SQLBindCol by setting TargetValue to the null pointer in the application. If ColumnNumber is the column number whose binding is released, SQLBindCol continues returning SQL_SUCCESS.

To release all bound columns, call SQLFreeStmt by setting option to SQL_UNBIND in the application. Or, set SQL_DESC_COUNT field of ARD to 0 to release all bound columns.

Rebinding Column

The application can perform one of two operations to change the binding.

• A new binding is specified for an existing bound column by calling SQLBindCol.

• The offset is added to the specified buffer address by calling SQLBindCol. For more information, refer to Binding Offsets.

Binding Offsets

Binding offset is the value added to the address before the data and length/indicator buffers (specified in TargetValuePtr and StrLen_or_IndPtr) are dereferenced.

Using binding offset generally has the same effect as rebinding the column by calling SQLBindCol. However, the new address of the data and length/indicator buffer are specified when newly calling SQLBindCol, but binding offset does not change the address, instead it adds only the offsets to the address. The application can specify a new offset anytime, and the offset is always added to the originally bound address. Especially, if the offset is set to 0 or the statement attribute is set to NULL pointer, the driver uses the originally bound address.

The sum of the originally bound address and the offset should be a valid address, but the offset address to be added does not have to be valid.

Binding Array

If the row set size (the value of SQL_ATTR_ROW_ARRAY_SIZE statement attribute) is bigger than 1, the application binds a buffer array instead of a single buffer.

The application can bind an array in two ways as follows.

• Bind an array to each column. Each data structure (array) contains data for a single column, so it is called as Column-wise Binding.

• Define as structure containing the entire row data and bind the array of these structures. Each data structure contains data for a single row. so it is called as Row-wise Binding.

Each buffer array should have at least as many elements as the row set size.

Column-wise Binding

The application binds the separate data and length/indicator array to each column in column-wise binding.

The application should set SQL_ATTR_ROW_BIND_TYPE statement attribute to SQL_BIND_BY_COLUMN (default value) to use the column-wise binding. Then, the application performs the following processes for the columns to be bound.

1. It allocates the data buffer array.

2. It allocates the array of length/indicator buffer.

3. It calls SQLBindCol together with the following arguments.

• TargetType is the type of each element of the data buffer array.

• TargetValuePtr is the address of the data buffer array.

• BufferLength is the size of each element of the data buffer array. BufferLength is ignored when the data is fixed length data.

• StrLen_or_IndPtr is the address of the length/indicator array.

Row-wise Binding

The application defines a structure which contains the data and length/indicator buffer of each column to be bound in row-wise binding.

The application performs the following processes to use row-wise binding.

1. It defines a structure which contains a row(including both of data and the length/indicator buffer) and allocates an array of the structures.

2. SQL_ATTR_ROW_BIND_TYPE statement attribute sets the size of structure including a data row or as the buffer instance size for the result columns to be bound. The length should include the space and structure of all bound columns and the padding of the buffer. It should guarantee to point to the starting position in the same column of the next line when the address of the bound column is increased by the specified length. ANSI C guarantees it by using the sizeof operator.

3. It calls SQLBindCol together with the following arguments for each column to be bound.

• TargetType is the type of the data buffer member to be bound to the column.

• TargetValuePtr is the address of the data buffer member in the first array element.

• BufferLength is the size of the data buffer member.

• StrLen_or_IndPtr the address of the length/indicator member to be bound.

Buffer Address

The buffer address is an actual address of the data or the length/indicator buffer. The driver calculates the buffer address before writing to the buffer (such as the data collect). It is calculated by the following formula, which uses the address, binding offset, row number specified in TargetValuePtr and StrLen_or_IndPtr.

Bound Address + Binding Offset + ((Row Number -1) x Element Size )

The following table describes the definitions the formula's variables.

Descriptors and SQLBindCol

This chapter describes how SQLBindCol interacts with descriptors.

Argument Mapping

Notionally, SQLBindCol performs the following processes in order.

1. It calls SQLGetStmtAttr to obtain ARD handle.

2. It calls SQLGetDescField to obtain the descriptor of SQL_DESC_COUNT field, and if the value in the ColumnNumber argument exceeds the value of SQL_DESC_COUNT, it calls SQLSetDescField to increase the value of SQL_DESC_COUNT to ColumnNumber.

3. It calls SQLSetDescField multiple times to assign values to the following fields of ARD.

• It sets SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE to the value of TargetType.

  • Except when TargetType is one of the concise identifiers of a datetime or interval subtype, it respectively sets SQL_DESC_TYPE to SQL_DATETIME or SQL_INTERVAL. It sets SQL_DESC_CONCISE_TYPE to the concise identifier; and sets SQL_DESC_DATETIME_INTERVAL_CODE to the corresponding datetime or interval subcode.

• It appropriately sets one or more of SQL_DESC_LENGTH, SQL_DESC_PRECISION, SQL_DESC_SCALE, and SQL_DESC_DATETIME_INTERVAL_PRECISION, for TargetType.

• It sets the SQL_DESC_OCTET_LENGTH field to the value of BufferLength.

• It sets the SQL_DESC_DATA_PTR field to the value of TargetValue.

• It sets the SQL_DESC_INDICATOR_PTR field to the value of StrLen_or_Ind.

• It sets the SQL_DESC_OCTET_LENGTH_PTR field to the value of StrLen_or_Ind.

The variable referenced by StrLen_or_Ind argument is used for both indicator and length information. If the value of column when fetching is null, it stores SQL_NULL_DATA in this variable. Otherwise, it stores the data length in this variable.
Entering a null pointer, and the value of column when fetching is null, then the fetch fails because it can not return SQL_NULL_DATA.

If SQLBindCol fails, the contents of the descriptor fields which will be set in ARD are not defined, and the value of SQL_DESC_COUNT field of ARD is not updated.

Implicit Initialization of COUNT Field

SQLBindCol sets SQL_DESC_COUNT to the value of the ColumnNumber only when ColumnNumber increase the value of SQL_DESC_COUNT. If the value in the TargetValuePtr argument is a null pointer and the value in the ColumnNumber argument is equal to SQL_DESC_COUNT (when releasing the highest bound column), then SQL_DESC_COUNT is set to the number of the highest remaining bound column.

Caution for SQL_DEFAULT

The application should determine the correct length and starting point of the data in the application buffer to successfully retrieve column data. When the application explicitly specifies an TargetType, application errors are easily detected.

However, when the application specifies a TargetType of SQL_DEFAULT, SQLBindCol can be applied to a column of a different data type from the one data type intended by the application, either from changes to the metadata or by applying the code to a different column. In this case, the application may not always determine the start or length of the fetched column data. This may lead to unreported data errors or memory violations.

SQLBindParameter

Conformance

Introduced version: ODBC 2.0
Standards compliance: ODBC

Overview

SQLBindParameter binds the buffer to the parameter marker of SQL statement.

Syntax

SQLRETURN SQLBindParameter(
    SQLHSTMT        StatementHandle,
    SQLUSMALLINT    ParameterNumber,
    SQLSMALLINT     InputOutputType,
    SQLSMALLINT     ValueType,
    SQLSMALLINT     ParameterType,
    SQLULEN         ColumnSize,
    SQLSMALLINT     DecimalDigits,
    SQLPOINTER      ParameterValuePtr,
    SQLLEN          BufferLength,
    SQLLEN *        StrLen_or_IndPtr);

Arguments

StatementHandle

[Input] It is the statement handle.

ParameterNumber

[Input] It is the parameter number which is increased sequentially from 1.

InputOutputType

[Input] It is the parameter type.

ValueType

[Input] It is the C data type of the parameter.

ParameterType

[Input] It is the SQL data type of the parameter.

ColumnSize

[Input] It is the size of column or expression of the parameter marker.

DecimalDigits

[Input] It is the number of decimal point of column or expression of the parameter marker.

ParameterValuePtr

[Delayed Input] It is the data buffer pointer of the parameter.

BufferLength

[Input/Output] It is the byte length of ParameterValuePtr buffer.

StrLen_or_IndPtr

[Delayed Input] It is the pointer to the length/indicator of the parameter.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

The application calls SQLBindParameter to bind each parameter marker in an SQL statement. Bindings remain valid until the application calls SQLBindParameter again, calls SQLFreeStmt with the SQL_RESET_PARAMS option, or calls SQLSetDescField to set the SQL_DESC_COUNT header field of the APD to 0.

ParameterNumber Argument

If ParameterNumber is bigger than the value of SQL_DESC_COUNT when calling SQLBindParameter, SQLSetDescField is called to increase the value of SQL_DESC_COUNT to ParameterNumber.

InputOutputType Argument

The InputOutputType argument specifies the type of the parameter. This argument sets the SQL_DESC_PARAMETER_TYPE field of the IPD.

InputOutputType argument is one of the followings.

• SQL_PARAM_INPUT: The parameter marks a parameter in an SQL statement, not in a procedure nor in SELECT INTO statement. For example, the parameters in INSERT INTO Employee VALUES (?, ?, ?) are input parameters.

  • When the statement is executed, the driver sends data for the parameter, and the *ParameterValuePtr buffer should contain a valid input value, or the *StrLen_or_IndPtr buffer should contain SQL_NULL_DATA, SQL_DATA_AT_EXEC, or the result of the SQL_LEN_DATA_AT_EXEC macro.

• SQL_PARAM_INPUT_OUTPUT: The parameter marks an input/output parameter in a procedure.

  • When the statement is executed, the driver sends data for the parameter and the *ParameterValuePtr buffer should contain a valid input value, or the *StrLen_or_IndPtr buffer should contain SQL_NULL_DATA, SQL_DATA_AT_EXEC, or the result of the SQL_LEN_DATA_AT_EXEC macro.

  • After the statement is executed, the driver returns data to the parameter of the application. If the data source does not return a value to an input/output parameter, the driver sets SQL_NULL_DATA in *StrLen_or_IndPtr buffer.

• SQL_PARAM_OUTPUT: The parameter marks the return value of a procedure, or an output parameter in a procedure or SELECT INTO statement. For example, the parameter in SELECT ID INTO ? FROM Employee WHERE NAME = 'Paul' is an output parameter which returns the ID.

  • After the statement is executed, the driver returns data to the parameter, if the ParameterValuePtr and StrLen_or_IndPtr arguments of an application are not null pointers. Otherwise, the driver discards the output value.

  • If the data source can not return a value to an output parameter, the driver sets SQL_NULL_DATA in *StrLen_or_IndPtr buffer.

ValueType Argument

ValueType argument specifies C data type of the parameter. It sets the values of SQL_DESC_TYPE, SQL_DESC_CONCISE_TYPE, SQL_DESC_DATETIME_INTERVAL_CODE fields of APD.

When the ValueType argument is an interval data type,

• SQL_DESC_TYPE field of APD ParameterNumber record is set to SQL INTERVAL.

• SQL_DESC_CONCISE_TYPE field is set to concise interval data type.

• SQL_DESC_DATETIME_INTERVAL_CODE field is set to subcode of a specific interval data.

• The default value of interval leading precision is (2), and the default value of interval seconds precision is (6), and they are respectively set in SQL_DESC_DATETIME_INTERVAL_PRECISION and SQL_DESC_PRECISION fields of APD.

• If the default precision or scale is not appropriate, the application should explicitly set the descriptor field by calling SQLSetDescField or SQLSetDescRec.

When the ValueType argument is a datetime data type,

• SQL_DESC_TYPE field of ParameterNumber record in APD is set to SQL_DATETIME.

• SQL_DESC_CONCISE_TYPE field is set to the concise date C data type.

• SQL_DESC_DATETIME_INTERVAL_CODE field is set to the sub code of the specific datetime data.

When the ValueType argument is an SQL_C_NUMERIC data type,

• The default precision is (38), the default scale is (0), and they are respectively set in SQL_DESC_PRECISION, SQL_DESC_SCALE fields of APD.

• If the default precision or scale is not appropriate, the application should explicitly set the descriptor field by calling SQLSetDescField or SQLSetDescRec.

ParameterType Argument

ParameterType specifies the SQL data type of the parameter. It sets the values of SQL_DESC_TYPE, SQL_DESC_CONCISE_TYPE, SQL_DESC_DATETIME_INTERVAL_CODE fields of IPD.

When the ParameterType argument is a datetime data type,

• SQL_DESC_TYPE field of IPD is set to SQL_DATETIME.

• SQL_DESC_CONCISE_TYPE field is set to the concise datetime SQL data type.

• SQL_DESC_DATETIME_INTERVAL_CODE field is set to the sub code of the specific datetime data.

When the ParameterType argument is a interval data type,

• SQL_DESC_TYPE field of IPD is set to SQL_INTERVAL.

• SQL_DESC_CONCISE_TYPE field is set to the concise SQL interval data type.

• SQL_DESC_DATETIME_INTERVAL_CODE field is set to the sub code of the specific interval data.

• The interval leading precision is set in SQL_DESC_DATETIME_INTERVAL_PRECISION field of IPD, and the interval second precision is set in SQL_DESC_PRECISION field of IPD.

• If the defaults of SQL_DESC_DATETIME_INTERVAL_PRECISION and SQL_DESC_PRECISION are not appropriate, the application sets it by calling SQLSetDescField.

When the ParameterType argument is an SQL_NUMERIC data type,

• The default precision is (38), the default scale is (0), and they are respectively set in SQL_DESC_PRECISION and SQL_DESC_SCALE fields of IPD.

• If the default precision or scale is not appropriate, the application should explicitly set the descriptors field by calling SQLSetDescField or SQLSetDescRec.

ColumnSize Argument

ColumnSize argument specifies the size of the column or expression corresponding the parameter marker. It sets different fields of the IPD depending on SQL data type of ParameterType.

• If ParameterType is SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR, SQL_BINARY, SQL_VARBINARY, SQL_LONGVARBINARY, concise SQL datetime, or interval data type, it sets the value of ColumnSize in SQL_DESC_LENGTH field of IPD.

• If ParameterType is SQL_DECIMAL, SQL_NUMERIC, SQL_FLOAT, SQL_REAL, or SQL_DOUBLE, it sets the value of ColumnSize in SQL_DESC_PRECISION field of IPD.

• For other data types, ColumnSize argument is ignored.

DecimalDigit Argument

• If ParameterType argument is SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP, SQL_INTERVAL_SECOND, SQL_INTERVAL_DAY_TO_SECOND, SQL_INTERVAL_HOUR_TO_SECOND, or SQL_INTERVAL_MINUTE_TO_SECOND, it sets the value of DecimalDigits in SQL_DESC_PRECISION field of IPD.

• If ParameterType argument is SQL_NUMERIC or SQL_DECIMAL, it sets the value of DecimalDigits in SQL_DESC_SCALE field of IPD.

• For other data types, DecimalDigits argument is ignored.

ParameterValuePtr Argument

When calling SQLExecute and SQLExecDirect, ParameterValuePtr points to the actual data for the parameter. The data type should be in a form specified by ValueType argument. This argument sets the SQL_DESC_DATA_PTR field of the APD.

If *StrLen_or_IndPtr is the result of the SQL_LEN_DATA_AT_EXEC (length) macro or SQL_DATA_AT_EXEC, then ParameterValuePtr is an application-defined pointer value which is related to the parameter. It is returned to the application through SQLParamData. 
For example, ParameterValuePtr might be a non-zero token such as a parameter number, a pointer to data, or a pointer to a structure that the application used to bind input parameters.

If InputOutputType argument is SQL_PARAM_INPUT_OUTPUT or SQL_PARAM_OUTPUT, ParameterValuePtr should be a buffer pointer in which the output value is stored.

If the value in the SQL_ATTR_PARAMSET_SIZE statement attribute is bigger than 1, ParameterValuePtr points to an array. A single SQL statement processes the complete array of input values for an input or input/output parameter and returns an array of output values for an input/output or output parameter.

BufferLength Argument

For character and binary C data, the BufferLength argument specifies the length of the *ParameterValuePtr buffer (if the value in the SQL_ATTR_PARAMSET_SIZE statement attribute is 1), or specifies the length of the element in the *ParameterValuePtr array(if the value in the SQL_ATTR_PARAMSET_SIZE statement attribute is bigger than 1).

Both when input and output, BufferLength is used to determine the position in the array of *ParameterValuePtr for. It sets SQL_DESC_OCTET_LENGTH field of APD.

For an input/output parameter and an output parameter, BufferLength is used to determine whether or not to truncate the output.

• For character C data, if the number of bytes available to return is equal to or bigger than BufferLength, the data in *ParameterValuePtr is truncated to BufferLength minus 1 bytes and it is null-terminated.

• For binary C data, if the number of bytes available to return is equal to or bigger than BufferLength, the data in *ParameterValuePtr is truncated to BufferLength bytes.

• For other C data types, the BufferLength argument is ignored.

StrLen_or_IndPtr Argument

The StrLen_or_IndPtr argument contains one of the followings when SQLExecute or SQLExecDirect is called. (This argument sets the SQL_DESC_OCTET_LENGTH_PTR and SQL_DESC_INDICATOR_PTR of APD.)

• The length of the parameter value stored in *ParameterValuePtr. This is ignored except for character or binary C data.

• SQL_NTS: The parameter value is a null-terminated string.

• SQL_NULL_DATA: The parameter value is NULL.

• The result of the SQL_LEN_DATA_AT_EXEC(length) macro: The data for the parameter will be sent when performing SQLPutData.

  • If the ParameterType argument is SQL_LONGVARBINARY, SQL_LONGVARCHAR, or a long, and SQL_NEED_LONG_DATA_LEN information of SQLGetInfo returns "Y", then the length is the number of bytes of data to be sent for the parameter

  • If SQL_NEED_LONG_DATA_LEN information of SQLGetInfo is "N", length should be a nonnegative value and is ignored.

  • For example, to specify 10,000 bytes of SQL_LONGVARCAHR parameter data to be sent by calling SQLPutData for multiple times, *StrLen_or_IndPtr should be set to SQL_LEN_DATA_AT_EXEC(10000).

• SQL_DATA_AT_EXEC: The data for the parameter will be sent when performing SQLPutData.

If StrLen_or_IndPtr is a null pointer, the driver assumes that all input parameter values are non-NULL and that character and binary data is null-terminated. If InputOutputType is SQL_PARAM_OUTPUT, and ParameterValuePtr and StrLen_or_IndPtr are both null pointers, the driver discards the output value.

If the InputOutputType argument is SQL_PARAM_INPUT_OUTPUT, SQL_PARAM_OUTPUT, then StrLen_or_IndPtr points to SQL_NULL_DATA, the number of bytes available to return in *ParameterValuePtr (excluding the null-termination byte of character data), or SQL_NO_TOTAL (if the number of bytes available to return cannot be determined).

If the value in the SQL_ATTR_PARAMSET_SIZE statement attribute is greater than 1, StrLen_or_IndPtr points to an array of SQLLEN values.

Passing Parameter Values

An application can pass the value for a parameter by calling the *ParameterValuePtr buffer or multiple SQLPutData. Parameters whose data is passed through SQLPutData are known as data-at-execution parameters. These are typically used to send data for SQL_LONGVARBINARY and SQL_LONGVARCHAR parameters, and can be mixed with other parameters.

The application should perform the following process to pass the parameter values.

1. It calls SQLBindParameter for each parameter to bind buffers for the parameter's value (ParameterValuePtr argument) and length/indicator (StrLen_or_IndPtr argument). For data-at-execution parameters, ParameterValuePtr is an application-defined pointer value such as a parameter number or a pointer to data. The value will be returned later and can be used to identify the parameter.

2. It sets values for an input parameter or an input/output parameter in the *ParameterValuePtr and *StrLen_or_IndPtr buffers.

• For normal parameters, the application inputs the parameter value in the *ParameterValuePtr buffer and the length of that value in the *StrLen_or_IndPtr buffer.

• For data-at-execution parameters, the application inputs the result of the SQL_LEN_DATA_AT_EXEC(length) macro (when calling an ODBC 2.0 driver) in the *StrLen_or_IndPtr buffer.

3. It calls SQLExecute or SQLExecDirect to execute the SQL statement.

• If data-at-execution parameters do not exist, the process is complete.

• If any data-at-execution parameters exist, the function returns SQL_NEED_DATA.

4. It calls SQLParamData to retrieve the application-defined value specified in the ParameterValuePtr argument of SQLBindParameter for the first data-at-execution parameter to be processed. SQLParamData returns SQL_NEED_DATA.

5. It calls SQLPutData for one or more times to send data for the parameter. One or more calls are required if the data value is bigger than what is specified in the *ParameterValuePtr buffer of SQLPutData. Multiple SQLPutData calls for the same parameter are allowed only when sending character C data to a column with a character, binary, or data source–specific data type or when sending binary C data to a column with a character, binary, or data source–specific data type.

6. It calls SQLParamData again to signal that all data has been sent for the parameter.

• If one or more data-at-execution parameters exist, SQLParamData returns SQL_NEED_DATA and processes the application-defined value for the next data-at-execution parameter. The application repeats steps 4 and 5.

• If data-at-execution parameter does not exist, the process is complete. If the statement was successfully executed, SQLParamData returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. If the execution failed, it returns SQL_ERROR. At this point, SQLParamData can be returned by any SQLSTATE. It can also be returned by SQLExecDirect or SQLExecute which is used to execute the statement.

• Output values for any input/output or output parameters are available in the *ParameterValuePtr and *StrLen_or_IndPtr buffers after the application retrieves all result sets generated by the statement.

Calling SQLExecute or SQLExecDirect puts the statement in an SQL_NEED_DATA state. At this point, the application can call only SQLCancel, SQLGetDiagField, SQLGetDiagRec, SQLGetFunctions, SQLParamData, or SQLPutData together with the statement or the connection handle related to the statement.

If it calls any other function for the statement or the connection related to the statement, the function returns SQLSTATE HY010(Function sequence error). The statement is released from the SQL_NEED_DATA state when SQLParamData or SQLPutData returns an error, SQLParamData returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, or the statement is canceled.

If the application calls SQLCancel while the driver still needs data for data-at-execution parameters, the driver cancels statement execution. Then the application can call SQLExecute or SQLExecDirect again.

Using Parameter Array

An application prepares a statement together with parameter markers and passes it in an array of parameters in the following two ways.

• One method is for the driver to rely on the array-processing capabilities, in this case the entire statement with the array of parameters is treated as one atomic unit. Oracle is an example of a data source which supports array processing capabilities.

• The other method is for the driver to generate a batch of SQL statements. Each set of parameters in the parameter array is allocated per an SQL statement and is made into a batch, then the batch is executed. Arrays of parameters can not be used in an UPDATE WHERE CURRENT OF statement.

When processing the parameter array, the number of each result sets/ rows are available per each parameter set or the number of each result sets/ rows are available in whole. The SQL_PARAM_ARRAY_ROW_COUNTS option in SQLGetInfo indicates whether the number of rows are available for each set of parameters(SQL_PARC_BATCH) or only a single row is available (SQL_PARC_NO_BATCH).

The SQL_PARAM_ARRAY_SELECTS option in SQLGetInfo indicates whether a result set is available for each set of parameters (SQL_PAS_BATCH) or  is available only in one result set(SQL_PAS_NO_BATCH). If the driver does not allow a result set–generating statement to be executed together with an array of parameters, SQL_PARAM_ARRAY_SELECTS returns SQL_PAS_NO_SELECT. 
For more information, refer to SQLGetInfo.

To support the parameter array, the SQL_ATTR_PARAMSET_SIZE statement attribute is set to specify the number of values for each parameter. If the field is bigger than 1, the SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR fields of the APD should point to arrays. The number of elements in each array is equal to the value of SQL_ATTR_PARAMSET_SIZE.

The SQL_DESC_ROWS_PROCESSED_PTR field of the APD points to a buffer which contains the number of sets of parameters which have been processed, including error sets. As like each processed parameters set, the driver stores a new value in the buffer. Any number will not be returned if this is a null pointer. 
When arrays of parameters are used, the value pointed to by the SQL_DESC_ROWS_PROCESSED_PTR field of the APD is generated even when SQL_ERROR is returned by the setting function. If SQL_NEED_DATA is returned, the value pointed to by the SQL_DESC_ROWS_PROCESSED_PTR field of the APD is set to the set of parameters which is being processed.

Binding Column-wise Parameter

For column-wise binding, the application binds the separate parameter and length/indicator arrays to each parameter.

For column-wise binding, the application firstly sets the SQL_ATTR_PARAM_BIND_TYPE statement attribute to SQL_PARAM_BIND_BY_COLUMN. (It is the default value.) The application performs the following processes to use column-wise binding.

1. It allocates the parameter buffer array.

2. It allocates the length/indicator buffer array.

3. It calls SQLBindParameter together with the following arguments.

• ValueType is the C type of a single element in the parameter buffer array.

• ParameterType is the SQL type of the parameter.

• ParameterValuePtr is the address of the parameter buffer array.

• BufferLength is the size of a single element in the parameter buffer array. The BufferLength argument is ignored when the data is fixed-length data.

Binding Row-wise Parameter

For row-wise binding, the application defines a structure which contains parameter and length/indicator buffers for each parameter to be bound.

The application performs the following processes to use row-wise binding.

1. It defines a structure to hold a single set of parameters (including both parameter and length/indicator buffers) and allocates an array of these structures.

2. SQL_ATTR_PARAM_BIND_TYPE statement attribute sets the size of the structure which contains a single set of parameters or to the size of an instance of a buffer into which the parameters will be bound. The length should include space for all bound parameters. The length should include space for binding parameter and the structure buffer, or should be buffered to ensure the result to point to the beginning of the next parameter when the address of a bound parameter is incremented to the specified length. ANSI C guarantees it by using the sizeof operator.

3. It calls SQLBindParameter together with the following arguments for each parameter to be bound.

• ValueType is the type of the parameter buffer member to be bound to the column.

• ParameterType is the SQL type of the parameter.

• ParameterValuePtr is the address of the parameter buffer member in the first array element.

• BufferLength is the size of the parameter buffer member.

• StrLen_or_IndPtr is the address of the length/indicator member to be bound.

Error Information

If a driver does not perform parameter arrays as same as batches (the SQL_PARAM_ARRAY_ROW_COUNTS option is as same as SQL_PARC_NO_BATCH), error situations are handled as if one statement were executed.

If the driver performs parameter arrays as batches, an application can use the SQL_DESC_ARRAY_STATUS_PTR header field of the IPD to determine if a parameter of an SQL statement or a parameter in an array of parameters caused SQLExecDirect or SQLExecute to return an error. 
This field contains status information for each row of parameter values. If the field represents that an error occurs, fields in the diagnostic data structure will represent the row and parameter number of the failed parameter. The number of columns in the array will be defined by the SQL_DESC_ARRAY_SIZE header field in the APD, and it can be set by the SQL_ATTR_PARAMSET_SIZE statement attribute.

When SQLExecute or SQLExecDirect returns SQL_ERROR, the elements in the array pointed to by the SQL_DESC_ARRAY_STATUS_PTR field in the IPD contain SQL_PARAM_ERROR, SQL_PARAM_SUCCESS, SQL_PARAM_SUCCESS_WITH_INFO, SQL_PARAM_UNUSED, or SQL_PARAM_DIAG_UNAVAILABLE.

For each element in this array, the diagnostic data structure contains one or more status records. The SQL_DIAG_ROW_NUMBER field of the structure represents the row number of the parameter values which caused the error. If it is possible to determine the particular parameter in a row of parameters which caused the error, the parameter number will be entered in the SQL_DIAG_COLUMN_NUMBER field.

SQL_PARAM_UNUSED is set when a parameter is not used due to an error because SQLExecute or SQLExecDirect forcibly canceled an earlier parameter. For example, if 50 parameters exist and an error occurred while executing the 40th set of parameters which caused the cancellation by SQLExecute or SQLExecDirect, then SQL_PARAM_UNUSED is set in the status array for parameters from 41 to 50.

SQL_PARAM_DIAG_UNAVAILABLE is set when the driver treats arrays of parameters as a single unit, so it does not generate individual error information of parameter level.

Some errors in the processing of a single set of parameters cause processing of the subsequent sets of parameters in the array to stop. Other errors do not affect the processing of subsequent parameters. The driver defines which errors will stop processing. If processing does not stop, all parameters in the array are processed, SQL_SUCCESS_WITH_INFO is returned as a result of the error, and the buffer defined by SQL_ATTR_PARAMS_PROCESSED_PTR is set to the total number of sets of parameters processed which includes error sets.

When SQLExecute or SQLExecDirect returns SQL_ERROR or SQL_NEED_DATA before completing the processing of all parameter sets in a parameter array, the status array contains statuses for those parameters which have already been processed. 
The location pointed to by the SQL_DESC_ROWS_PROCESSED_PTR field in the IPD contains the row number in the parameter array which caused the SQL_ERROR or SQL_NEED_DATA error code. When an array of parameters is sent to a SELECT statement, the availability of status array values is defined by the driver. They are available after the statement is executed or result sets are fetched.

Ignoring Parameter Set

SQL_DESC_ARRAY_STATUS_PTR field of APD can be used to indicate the binding parameter set which should be ignored in SQL statement. The application should perform the following processes in order that the driver directly ignores one or more parameter sets during the execution.

1. It calls SQLSetDescField in order that the header field of SQL_DESC_ARRAY_STATUS_PTR of APD points to an array of SQLUSMALLINT values including the status information. The field also can be set through SQL_ATTR_PARAM_OPERATION_PTR of Attribute argument in SQLSetStmtAttr, and it allows to set the field without a descriptor handle which is the application.

2. It sets each element of the array defined by SQL_DESC_ARRAY_STATUS_PTR of APD to one of the following two values.

• SQL_PARAM_IGNORE: The row is excluded from the execution of the statement.

• SQL_PARAM_PROCEED: The row is included in the execution of the statement.

3. It calls SQLExecDirect or SQLExecute, and executes the prepared statement. It applies the following rules to the array defined by SQL_DESC_ARRAY_STATUS_PTR of APD.

• The pointer is set to NULL by default.

• If the pointer is NULL, all parameter set are used as if all elements are set to SQL_ROW_PROCEED.

• Setting the element to SQL_PARAM_PROCEED does not guarantee that the operation would use the specific parameter set.

• SQL_PARAM_PROCEED is defines as 0 in the header file.

An application can set SQL_DESC_ARRAY_STATUS_PTR field of APD to refer to the same array as SQL_DESC_ARRAY_STATUS_PTR field of IRD. It is very useful when binding the parameters to the row data. The parameters may be ignored depending on the status of row data.

Along with SQL_PARAM_IGNORE, the following status codes are to ignore the parameter set in the SQL statement.

• SQL_ROW_DELETED

• SQL_ROW_UPDATED

• SQL_ROW_ERROR

Along with SQL_PARAM_PROCEED, the following status codes are to process the parameter set in the SQL statement.

• SQL_ROW_SUCCESS

• SQL_ROW_SUCCESS_WITH_INFO

• SQL_ROW_ADDED

Rebinding Parameter

The application can include many parameters, but if there is a buffer area for calling SQLExecDirect or SQLExecute which uses only some parameters, rebinding the parameter is particularly useful. The remaining space of buffer area can be used to set the next parameter by modifying the existing binding through offset.

The SQL_DESC_BIND_OFFSET_PTR header field of APD points to the binding offset. If the field is not NULL, the driver dereferences to the pointer. If values of SQL_DESC_DATA_PTR, SQL_INDICATOR_PTR do not exist, and SQL_DESC_OCTET_LENGTH_PTR field is the NULL pointer, then the dereferenced value is added to the fields in the descriptor records during the execution time.

Offset is valid after rebinding. The application can directly modify the offset without calling SQLSetDescField or SQLSetDescRec to update the descriptor field because SQL_DESC_BIND_OFFSET_PTR field is the pointer to offset rather than the offset itself. The pointer is NULL by default.

SQL_DESC_BIND_OFFSET_PTR field of ARD can be set by calling SQLSetDescField or through SQL_ATTR_PARAM_BIND_OFFSET_PTR in Attribute argument of SQLSetStmtAttr. The offset binding always adds the value directly to SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR and SQL_DESC_OCTET_LENGTH_PTR. If offset is changed to another value, the new value is continuously added directly to each descriptor field. The new offset is not added to the previous one.

Descriptor

The way which the parameter is bound is determined by APD and the IPD fields. The arguments in SQLBindParameter are used to set the descriptor fields. It is more effective to use the SQLBindParameter because the application can call SQLBindParameter without obtaining the descriptor handle, but the fields can also be set by the SQLSetDescField function.

Notionally, SQLBindParameter should perform the following processes.

1. It calls SQLGetStmtAttr, and obtains the APD handle.

2. It calls SQLGetDescField, and obtains SQL_DESC_COUNT field of APD is obtained. If the value of ColumnNumber exceeds the value of SQL_DESC_COUNT, then it calls SQLSetDescField to increase the value of SQL_DESC_COUNT to the value of ColumnNumber.

3. It calls SQLSetDescField multiple times, and sets the values of the following fields of APD.

• It sets SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE to the value of ValueType.

  • It excludes ValueType if ValueType is one of the implied identifiers in datetime or interval sub format. It sets SQL_DESC_TYPE to SQL_DATETIME or SQL_INTERVAL, and it sets SQL_DESC_CONCISE_TYPE to the implied identifier, and sets SQL_DESC_DATETIME_INTERVAL_CODE to the corresponding datetime or interval subcode.

• It sets SQL_DESC_OCTET_LENGTH field to the value of BufferLength.

• It sets SQL_DESC_DATA_PTR field to the value of ParameterValue.

• It sets SQL_DESC_OCTET_LENGTH_PTR field to the value of StrLen_or_Ind.

• It also sets SQL_DESC_INDICATOR_PTR field to the value of StrLen_or_Ind.

• StrLen_or_Ind specifies both the indicator information and length of parameter value.

4. It calls SQLGetStmtAttr, and obtains the IPD handle.

5. It calls SQLGetDescField, and obtains SQL_DESC_COUNT field of IPD. If the value of ColumnNumber exceeds the value of SQL_DESC_COUNT, then it calls SQLSetDescField to increase the value of SQL_DESC_COUNT to the value of ColumnNumber.

6. It calls SQLSetDescField multiple times, and sets the values of following fields of IPD.

• It sets SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE to the value of ParameterType.

  • It excludes ParameterType, if ParameterType is one of the implied identifiers with datetime or interval sub format. It sets SQL_DESC_TYPE to SQL_DATETIME or SQL_INTERVAL, and it sets SQL_DESC_CONCISE_TYPE to the implied identifier, and sets SQL_DESC_DATETIME_INTERVAL_CODE to the corresponding datetime or interval subcode.

• It sets one or more SQL_DESC_LENGTH, SQL_DESC_PRECISION and SQL_DESC_DATETIME_INTERVAL_PRECISION for ParameterType, properly.

• It sets SQL_DESC_SCALE to the value of DecimalDigits.

If it failed to call SQLBindParameter, the contents of the descriptor fields to be set in APD are not defined and SQL_DESC_COUNT field of APD is not changed. Additionally, SQL_DESC_LENGTH, SQL_DESC_PRECISION, SQL_DESC_SCALE, and SQL_DESC_TYPE fields of the proper record in IPD are not defined and SQL_DESC_COUNT field of IPD is not changed.

SQLBrowseConnect

It is not supported.

Conformance

Introduced version: ODBC 1.0
Standards compliance: ODBC

Overview

SQLBrowseConnect finds the attribute and its value which are required to connect to the data source, and supports a method for the iterative list.

Syntax

SQLRETURN SQLBrowseConnect(
    SQLHDBC         ConnectionHandle,
    SQLCHAR *       InConnectionString,
    SQLSMALLINT     StringLength1,
    SQLCHAR *       OutConnectionString,
    SQLSMALLINT     BufferLength,
    SQLSMALLINT *   StringLength2Ptr);

SQLBulkOperations

It is not supported.

Conformance

Introduced version: ODBC 3.0
Standards compliance: ODBC

Overview

SQLBulkOperations performs the massive bookmark operations such as the bulk inserts, and the updates, deletes, fetches through the bookmark.

Syntax

SQLRETURN SQLBulkOperations(
    SQLHSTMT       StatementHandle,
    SQLUSMALLINT   Operation);

SQLCancel

It is not supported.

Conformance

Introduced version: ODBC 1.0
Standards compliance: ISO 92

Overview

SQLCancel cancels the statement in progress.
Use SQLCancelHandle function to cancel the connection or statement in progress.

Syntax

SQLRETURN SQLCancel(
    SQLHSTMT     StatementHandle);

SQLCancelHandle

It is not supported.

Conformance

Introduced version: ODBC 3.8
Standards compliance: It is not available.

Overview

SQLCancelHandle cancels processing of the connection or the statement.

Syntax

SQLRETURN SQLCancelHandle(
    SQLSMALLINT  HandleType,
    SQLHANDLE    Handle);

SQLCloseCursor

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLCloseCursor closes an open cursor on the statement and discards the remaining results.

Syntax

SQLRETURN SQLCloseCursor(
    SQLHSTMT     StatementHandle);

Arguments

StatementHandle

[Input] It is the statement handle.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

If open cursor does not exist, SQLCloseCursor returns SQLSTATE 24000(Invalid cursor state). Calling SQLCloseCursor is as same as calling SQLFreeStmt with SQL_CLOSE option. However, when open cursor does not exist, SQLCloseCursor returns SQLSTATE 24000 (Invalid cursor state), but calling SQLFreeStmt does not affect the application.

SQLColAttribute

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLColAttribute returns the descriptor information for the result set column. The descriptor information is returned as a string or integer value.

Syntax

SQLRETURN SQLColAttribute (
    SQLHSTMT        StatementHandle,
    SQLUSMALLINT    ColumnNumber,
    SQLUSMALLINT    FieldIdentifier,
    SQLPOINTER      CharacterAttributePtr,
    SQLSMALLINT     BufferLength,
    SQLSMALLINT *   StringLengthPtr,
    SQLLEN *        NumericAttributePtr);

Arguments

StatementHandle

[Input] It is the statement handle.

ColumnNumber

[Input] It is the record number to retrieve for a field value in IRD. It starts from 1 and corresponds to the column number of result data which sequentially increases. The column can be described in random order.

The number 0 column can be specified to ColumnNumber, but the undefined value is returned except for SQL_DESC_TYPE and SQL_DESC_OCTET_LENGTH.

FieldIdentifier

[Input] It is the descriptor handle. It defines the field retrieved in IRD. (e.g. SQL_COLUMN_TABLE_NAME)

CharacterAttributePtr

[Output] It is the buffer pointer returning the field value when the value of FieldIdentifier field of the ColumnNumber column of IRD is a string. If the field value is not a string, it is not used.

If CharacterAttributePtr is NULL, StringLengthPtr returns the total number of bytes which can be returned. (Except for null-termination character)

BufferLength

[Input] It is the length of *CharacterAttributePtr when FieldIdentifier is defined in ODBC and CharacterAttributePtr points to a string or binary buffer. If FieldIdentifier is defined in ODBC and *CharacterAttributePtr is an integer, it is ignored.

StringLengthPtr

[Output] It is the pointer returning the total number of bytes which can be returned in *CharacterAttributePtr. (Except for null-termination character for the character data)

For the character data, if the number of bytes which can be returned is equal to or bigger than BufferLength, the description information of *CharacterAttributePtr is truncated to the length of BufferLength minus 1, and it is null terminated by the driver.

For other data types, the value of BufferLength is ignored.

NumericAttributePtr

[Output] It is the buffer pointer which returns the field value when the value of FieldIdentifier field of the ColumnNumber column of IRD is a number such as SQL_DESC_COLUMN_LENGTH. If the field value is not a number, it is not used.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

SQLColAttribute returns information to *NumericAttributePtr or *CharacterAttributePtr. Integer information is returned as SQLLEN value to *NumericAttributePtr. All other data type information is returned to *CharacterAttributePtr. When information is returned to *NumericAttributePtr, the driver ignores CharacterAttributePtr, BufferLength, StringLengthPtr. When the information is returned to *CharacterAttributePtr, the driver ignores NumericAttributePtr.

SQLColAttribute returns the value in the descriptor field of IRD. The value of FieldIdentifier returned to SQLColAttribute can be obtained by calling SQLGetDescField with appropriate IRD handle.

The following table is the descriptor types returned to SQLColAttribute. The type of NumericAttributePtr is SQLLEN*.

SQLColAttributes

Conformance

Introduced version: ODBC 1.0
Standards compliance: It is not available.

Overview

SQLColAttributes function in ODBC 2.0 was replaced with SQLColAttribute function in ODBC 3.x. 
For more information, refer to SQLColAttribute.

SQLColumnPrivileges

Conformance

Introduced version: ODBC 1.0
Standards compliance: ODBC

Overview

SQLColumnPrivileges returns a list of columns and related privileges for the specified table as a result set.

Syntax

SQLRETURN SQLColumnPrivileges(
    SQLHSTMT      StatementHandle,
    SQLCHAR *     CatalogName,
    SQLSMALLINT   NameLength1,
    SQLCHAR *     SchemaName,
    SQLSMALLINT   NameLength2,
    SQLCHAR *     TableName,
    SQLSMALLINT   NameLength3,
    SQLCHAR *     ColumnName,
    SQLSMALLINT   NameLength4);

Arguments

StatementHandle

[Input] It is the statement handle.

CatalogName

[Input] It is the catalog name. CatalogName can not include the string search pattern.

If the SQL_ATTR_METADATA_ID statement attribute is set to SQL_TRUE, CatalogName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, CatalogName is case-sensitive, and it is an ordinary argument literally processed.

NameLength1

[Input] It is the length of *CatalogName.

SchemaName

[Input] It is the schema name. SchemaName can not include the string search pattern.

If the SQL_ATTR_METADATA_ID statement attribute is set to SQL_TRUE, SchemaName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, SchemaName is case-sensitive, and it is an ordinary argument literally processed.

NameLength2

[Input] It is the length of *SchemaName.

TableName

[Input] It is the table name. This argument can not be a null pointer. TableName can not include the string search pattern.

If the SQL_ATTR_METADATA_ID statement attribute is set to SQL_TRUE, TableName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, TableName is case-sensitive, and it is an ordinary argument literally processed.

NameLength3

[Input] It is the length of *TableName.

ColumnName

[Input] It is the string search pattern for the column name.

If the SQL_ATTR_METADATA_ID statement attribute is set to SQL_TRUE, ColumnName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, ColumnName is case-sensitive, and it is a patternvalue literally processed.

NameLength4

[Input] It is the length of *ColumnName.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

SQLColumnPrivileges returns the standard result set aligned as TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, PRIVILEGE.

The following table describes the columns in the result set.

SQLColumns

Conformance

Introduced version: ODBC 1.0
Standards compliance: Open Group

Overview

SQLColumns returns a list of column names in the specified table as a result set.

Syntax

SQLRETURN SQLColumns(
    SQLHSTMT       StatementHandle,
    SQLCHAR *      CatalogName,
    SQLSMALLINT    NameLength1,
    SQLCHAR *      SchemaName,
    SQLSMALLINT    NameLength2,
    SQLCHAR *      TableName,
    SQLSMALLINT    NameLength3,
    SQLCHAR *      ColumnName,
    SQLSMALLINT    NameLength4);

Arguments

StatementHandle

[Input] It is the statement handle.

CatalogName

[Input] It is the catalog name. CatalogName can not include the string search pattern.

If the SQL_ATTR_METADATA_ID statement attribute is set to SQL_TRUE, CatalogName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, CatalogName is case-sensitive, and it is an ordinary argument literally processed.

NameLength1

[Input] It is the length of *CatalogName

SchemaName

[Input] It is the string search pattern for the schema name.

If the SQL_ATTR_METADATA_ID statement attribute is set to SQL_TRUE, SchemaName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, SchemaName is case-sensitive, and it is an ordinary argument literally processed.

NameLength2

[Input] It is the length of *SchemaName.

TableName

[Input] It is the string search pattern for the table name.

If the SQL_ATTR_METADATA_ID statement attribute is set to SQL_TRUE, TableName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, TableName is case-sensitive, and it is an ordinary argument literally processed.

NameLength3

[Input] It is the length of *TableName.

ColumnName

[Input] It is the string search pattern for the column name.

If the SQL_ATTR_METADATA_ID statement attribute is set to SQL_TRUE, ColumnName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, ColumnName is case-sensitive, and it is a pattern value literally processed.

NameLength4

[Input] It is the length of *ColumnName.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

This function is generally used prior to the execution of statement which retrieves information about the columns of the table or tables in the catalog of the data source. SQLColumns can be used to retrieve all data types returned by SQLTables. By contrast, SQLColAttribute and SQLDescribeCol describe the columns in the result set, and SQLNumResultCols returns the number of columns in the result set.

SQLColumns returns the standard result set sorted as TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION.

The following table describes the columns in the result set.

SQLConnect

Conformance

Introduced version: ODBC 1.0
Standards compliance: ISO 92

Overview

SQLConnect sets a connection between the driver and the data source. The connection handle refers to all information about the connection including status, transaction status and error information.

Syntax

SQLRETURN SQLConnect(
    SQLHDBC        ConnectionHandle,
    SQLCHAR *      ServerName,
    SQLSMALLINT    NameLength1,
    SQLCHAR *      UserName,
    SQLSMALLINT    NameLength2,
    SQLCHAR *      Authentication,
    SQLSMALLINT    NameLength3);

Arguments

ConnectionHandle

[Input] It is the connection handle.

ServerName

[Input] It is the data source name.

NameLength1

[Input] It is the length of *ServerName.

UserName

[Input] It is the user identifier.

NameLength2

[Input] It is the length of *UserName.

Authentication

[Input] It is the authentication string (typically password).

NameLength3

[Input] It is the length of *Authentication.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE, SQL_STILL_EXECUTING

Diagnosis

Description

The driver searches for the user DSN information in an order of $HOME/.odbc.ini file and /home/.odbc.ini file which are files set in $ODBCINI environment variable. If the DSN which was input in the user DSN does not exist, the driver searches for the DSN information in an order of $ODBCSYSINI/odbc.ini file, /etc/odbc.ini file which is system DSN.

SQLCopyDesc

It is not supported.

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLCopyDesc copies the descriptor information from one descriptor handle to another descriptor handle.

Syntax

SQLRETURN SQLCopyDesc(
    SQLHDESC     SourceDescHandle,
    SQLHDESC     TargetDescHandle);

SQLDescribeCol

Conformance

Introduced version: ODBC 1.0
Standards compliance: ISO 92

Overview

SQLDescribeCol returns a column name, type, column size, decimal place, information about null permit in the result set column. The information can be used in the fields of IRD.

Syntax

SQLRETURN SQLDescribeCol(
    SQLHSTMT       StatementHandle,
    SQLUSMALLINT   ColumnNumber,
    SQLCHAR *      ColumnName,
    SQLSMALLINT    BufferLength,
    SQLSMALLINT *  NameLengthPtr,
    SQLSMALLINT *  DataTypePtr,
    SQLULEN *      ColumnSizePtr,
    SQLSMALLINT *  DecimalDigitsPtr,
    SQLSMALLINT *  NullablePtr);

Arguments

StatementHandle

[Input] It is the statement handle.

ColumnNumber

[Input] It is the column number in the result set which is started from 1 and increased sequentially.

ColumnName

[Output] It is the buffer pointer which is terminated by a null and returns the column name. This value can be read in SQL_DESC_NAME field of IRD. If the column name does not exist or the column name is unknown, the driver returns an empty string.

If ColumnName is null, NameLengthPtr returns the total number of bytes returnable. (excluding null-termination character)

BufferLength

[Input] It is the length of *ColumnName.

NameLengthPtr

[Output] It is the buffer pointer which returns the total number of bytes returnable to *ColumnName (excluding the null-termination character). If the returnable length is equal to or bigger than BufferLength, *ColumnName is truncated to the length of BufferLength minus null.

DataTypePtr

[Output] It is the buffer pointer which returns the SQL type of a column. The value can be read in SQL_DESC_CONCISE_TYPE of IRD.

ColumnSizePtr

[Output] It is the buffer pointer which returns the column size of the data source.

DecimalDigitsPtr

[Output] It is the buffer pointer which returns the decimal place of the data source.

NullablePtr

[Output] It is the buffer pointer which returns whether the column allows for null. The value can be read in SQL_DESC_NULLABLE field of IRD. It is one of the followings.

• SQL_NO_NULLS: The column does not allow NULL.

• SQL_NULLABLE: The column allows NULL.

• SQL_NULLABLE_UNKNOWN: The driver can not determine whether the column allows NULL.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

Generally, the application calls SQLDescibeCol before or after calling SQLExecute related after calling SQLPrepare. Also, the application may call SQLDescribeCol after calling SQLExecDirect.

SQLDescribeParam

Conformance

Introduced version: ODBC 1.0
Standards compliance: ODBC

Overview

SQLDescribeParam returns the description of a parameter marker related to a prepared SQL statement. This information can be used in the fields of the IPD.

Syntax

SQLRETURN SQLDescribeParam(
    SQLHSTMT        StatementHandle,
    SQLUSMALLINT    ParameterNumber,
    SQLSMALLINT *   DataTypePtr,
    SQLULEN *       ParameterSizePtr,
    SQLSMALLINT *   DecimalDigitsPtr,
    SQLSMALLINT *   NullablePtr);

Arguments

StatementHandle

[Input] It is the statement handle.

ParameterNumber

[Input] It is the parameter marker number which is started from 1 and increased sequentially.

DataTypePtr

[Output] It is the buffer pointer which returns the SQL type of parameter. The value can be read in the record field of SQL_DESC_CONCISE_TYPE of IPD.

ParameterSizePtr

[Output] It is the buffer pointer which returns the size of column or expression for the parameter marker.

DecimalDigitsPtr

[Output] It is the buffer pointer which returns the decimal places of column or expression for the parameter marker.

NullablePtr

[Output] It is the buffer pointer which returns whether the parameter allows for null. The value can be read in SQL_DESC_NULLABLE of IPD. The value is one of the followings.

• SQL_NO_NULLS: The parameter does not allow NULL.

• SQL_NULLABLE: The parameter allows NULL.

• SQL_NULLABLE_UNKNOWN: The driver can not determine whether the parameter allows NULL.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

SQL_VARCHAR should be returned to *DataTypePtr, 4000 should be returned to *ParameterSizePtr, 0 should be returned to *DecimalDigitsPtr, and SQL_NULLABLE should be returned to *NullablePtr because the driver can not provide the exact information of the parameter using the prepared SQL.

SQLDisconnect

Conformance

Introduced version: ODBC 1.0
Standards compliance: ISO 92

Overview

SQLDisconnect closes the connection related to the specific connection handle.

Syntax

SQLRETURN SQLDisconnect(
    SQLHDBC     ConnectionHandle);

Arguments

ConnectionHandle

[Input] It is the connection handle.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE, SQL_STILL_EXECUTING

Diagnosis

Description

If the application calls SQLDisconnect to the connection handle with an incomplete transaction, the driver returns SQLSTATE 25000 (Invalid transaction state), the transaction is not changed, and the connection is open. The incomplete transaction is a transaction which is not committed or rolled back by using SQLEndTran.

If the application calls SQLDisconnect before disconnecting all statements, the driver is disconnected from the data source, then deletes all statements and the descriptor explicitly assigned to the connection handle.

SQLDriverConnect

Conformance

Introduced version: ODBC 1.0
Standards compliance: ODBC

Overview

SQLDriverConnect can replace SQLConnect, and it supports the data source which requires more information of connection than three arguments of SQLConnect.

SQLDriverConnect establishes the connection by using the connection string which includes other information required by the data source name, one or more users, one or more passwords and the data sources.

When the connection is established, SQLDriverConnect returns the completed connection string. The application can use this string when requesting the next connection.

Syntax

SQLRETURN SQLDriverConnect(
    SQLHDBC         ConnectionHandle,
    SQLHWND         WindowHandle,
    SQLCHAR *       InConnectionString,
    SQLSMALLINT     StringLength1,
    SQLCHAR *       OutConnectionString,
    SQLSMALLINT     BufferLength,
    SQLSMALLINT *   StringLength2Ptr,
    SQLUSMALLINT    DriverCompletion);

Arguments

ConnectionHandle

[Input] It is the connection handle.

WindowHandle

[Input] It is the window handle. The application passes the superordinate window handle or a null pointer, and for the null pointer, SQLDriverConnect does not display the dialog box.

InConnectionString

[Input] It is the full connection string, partial connection string or empty string.

StringLength1

[Input] It is the length of *InConnectionString.

OutConnectionString

[Output] It is the buffer pointer of the completed connection string. If it is successfully connected to the target data source, the buffer contains the completed connection string. The application should allocate a buffer with at least 1,024 characters.

If OutConnectionString is NULL, the total number of returnable characters are returned in StringLength2Ptr (excluding null termination character).

BufferLength

[Input] It is the length of *OutConnectionString

StringLength2Ptr

[Output] It is the buffer pointer which returns the total number of returnable characters to *OutConnectionString (excluding null termination character). If the returnable length is equal to or bigger than BufferLength, *OutConnectionString is truncated to the length of BufferLength minus a null-termination character.

DriverCompletion

[Input] It is a flag that indicates whether the driver has to request more information. the value is one of SQL_DRIVER_PROMPT, SQL_DRIVER_COMPLETE, SQL_DRIVER_COMPLETE_REQUIRED, SQL_DRIVER_NOPROMPT.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA, SQL_ERROR, SQL_INVALID_HANDLE, SQL_STILL_EXECUTING

Diagnosis

Description

The syntax of connection string is as follows.

connection-string ::= empty-string[;] | attribute[;] | attribute; connection-string

empty-string ::=attribute ::= attribute-keyword=attribute-value | DRIVER=[{]attribute-value[}]

attribute-keyword ::= DSN | PROTOCOL | CS_MODE | HOST | PORT | UID | PWD | ALTERNATE_SERVERS | FAILOVER_TYPE | FAILOVER_GRANULARITY | DATE_FORMAT | TIME_FORMAT | TIME_WITH_TIME_ZONE_FORMAT | TIMESTAMP_FORMAT | TIMESTAMP_WITH_TIME_ZONE_FORMAT | CHAR_LENGTH_UNITS | CONN_NAME

attribute-value ::= character-string

character-string is zero or more characters. attribute-keyword is case-insensitive, and attribute-value may be case-sensitive. The value of DSN keyword does not consist only of white spaces.

The following table describes attribute-keywords.

SQLEndTran

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLEndTran requests a commit or rollback for active transactions of all statements related to the connection.

Syntax

SQLRETURN SQLEndTran(
    SQLSMALLINT   HandleType,
    SQLHANDLE     Handle,
    SQLSMALLINT   CompletionType);

Arguments

HandleType

[Input] It is the handle identifier. If it is an environment handle it is SQL_HANDLE_ENV, and if it is a connection handle it is SQL_HANDLE_DBC.

Handle

[Input] It is the handle of HandleType which indicates the transaction range.

CompletionType

[Input] It is SQL_COMMIT or SQL_ROLLBACK.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE, SQL_STILL_EXECUTING

Diagnosis

Description

If CompletionType is SQL_COMMIT, then SQLEndTran requests the commit for all active transactions of statements related to the connection. If CompletionType is SQL_ROLLBACK, then SQLEndTran requests the rollback for all active transactions of statements related to the connection. If active transaction does not exist, SQLEndTran returns SQL_SUCCESS without affecting the data source.

If the driver is a manual commit mode(The SQL_ATTR_AUTOCOMMIT attribute is set to SQL_AUTOCOMMIT_OFF by calling SQLSetConnectAttr.) and the SQL statement is executed for the current data source, a new transaction implicitly starts.

SQLEndTran does not affect the open cursor related to the connection when committing. The cursor remains on the row pointed before calling SQLEndTran.

SQLEndTran closes all open cursors on all statements when rolling back. SQLEndTran sets the statement to the ready state, and the application does not call SQLPrepare, but it calls SQLExecute.

If an active transaction does not exist, SQLEndTran returns SQL_SUCCESS.

If the driver is an auto commit mode, SQLEndTran always returns SQL_SUCCESS regardless of CompletionType.

SQLError

Conformance

Introduced version: ODBC 1.0
Standards compliance: It is not available.

Overview

SQLError returns an error or status information.

SQLExecDirect

Conformance

Introduced version: ODBC 1.0
Standards compliance: ISO 92

Overview

If SQLExecDirect has the parameter in the statement, it executs the statement by using the current value of parameter marker. SQLExecDirect is the fastest way of when the statement is executed only once.

Syntax

SQLRETURN SQLExecDirect(
    SQLHSTMT     StatementHandle,
    SQLCHAR *    StatementText,
    SQLINTEGER   TextLength);

Arguments

StatementHandle

[Input] It is the statement handle.

StatementText

[Input] It is the executed SQL statement.

TextLength

[Input] It is the length of *StatementText.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, SQL_NO_DATA, SQL_INVALID_HANDLE, SQL_PARAM_DATA_AVAILABLE

Diagnosis

Description

The application sends the SQL statement to the data source by calling SQLExecDirect.

The application can include one or more parameter markers in an SQL statement. The application should include a question mark (?) on the appropriate position of SQL statement to include a parameter marker.

If the SQL statement is the SELECT statement and the application connects the cursor with SQLSetCursorName, the driver uses the specified cursor. If the application does not connect the statement and the cursor, then the driver creates a cursor name.

If the data source is the manual commit mode, and the transaction has not yet been started, the driver starts the transaction before sending the SQL statement.

If SQLExecDirect finds the parameter of data-at-execution, it returns SQL_NEED_DATA. The application transmits data by using SQLParamData and SQLPutData.

If SQLExecDirect executes the statement such as searched update, insert, or delete, but rows have not been changed on the data source, then calling SQLExecDirect returns SQL_NO_DATA.

If the attribute value of SQL_ATTR_PARAMSET_SIZE statement is bigger than 1, and the SQL statement includes at least one parameter marker character, then SQLExecDirect executes the SQL statement once per a parameter set in the array pointed by ParameterValuePtr argument of SQLBindParameter.

SQLExecute

Conformance

Introduced version: ODBC 1.0
Standards compliance: ISO 92

Overview

If a statement includes a parameter, SQLExecute performs the prepared statement by using the current value of the parameter marker.

Syntax

SQLRETURN SQLExecute(
    SQLHSTMT     StatementHandle);

Arguments

StatementHandle

[Input] It is the statement handle.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, SQL_NO_DATA, SQL_INVALID_HANDLE, SQL_PARAM_DATA_AVAILABLE

Diagnosis

Description

SQLExecute executes the statement prepared by SQLPrepare. The application can call SQLExecute again with the new parameter value after it discards the result of calling SQLExecute.

To execute SELECT statement once or more, the application should call SQLCloseCursor before executing SELECT statement again.

If the data source is a manual commit mode, and the transaction has not yet been started, the driver starts the transaction before sending the SQL statement.

If SQLExecute finds a data-at-execution parameter, it returns SQL_NEED_DATA. The application sends data by using SQLParamData and SQLPutData.

If SQLExecute executes the statement such as searched update, insert, or delete, but rows have not been changed on the data source, calling SQLExecute returns SQL_NO_DATA.

If the attribute value of SQL_ATTR_PARAMSET_SIZE statement is bigger than 1, and the SQL statement includes at least one parameter marker character, then SQLExecute executes the SQL statement once per a parameter set in the array pointed by ParameterValuePtr argument of SQLBindParameter.

SQLExtendedFetch

Conformance

Introduced version: ODBC 1.0.
Compliance: It is not available.

Overview

SQLExtendedFetch fetches the specified data set from the result set, and returns it to all bound columns.

Syntax

SQLRETURN SQLExtendedFetch(
    SQLHSTMT         StatementHandle,
    SQLUSMALLINT     FetchOrientation,
    SQLLEN           FetchOffset,
    SQLULEN *        RowCountPtr,
    SQLUSMALLINT *   RowStatusArray);

SQLFetch

Conformance

Introduced version: ODBC 1.0
Standards compliance: ISO 92

Overview

SQLFetch fetches the next row set from the result set, and returns it to all columns bound.

Syntax

SQLRETURN SQLFetch(
    SQLHSTMT     StatementHandle);

Arguments

StatementHandle

[Input] It is the statement handle.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA, SQL_STILL_EXECUTING, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

SQLFetch returns the next data set from the result set. SQLFetch can be called while the result set exists, which is after the result set is created and before the cursor is closed. If the column is bound, the data is returned to the column. If the application specifies the pointer to a row status array or specifies the buffer which returns the number of fetched rows, SQLFetch also returns this information. SQLFetch and SQLFetchScroll can be called being mixed together.

Cursor Position

If the result set is generated, the cursor is positioned before the start of result set. SQLFetch fetches the next row set. It is as same as calling SQLFetchScroll which FetchOrientation is set to SQL_FETCH_NEXT.

The attribute of SQL_ATTR_ROW_ARRAY_SIZE statement specifies the number of rows in the row set. If the row set fetched by SQLFetch overlaps with the end of result set, SQLFetch returns a partial row set. If S is the start row of fetched row set, R is the size of row set, L is the last row of result set, S + R - 1 is bigger than L, then only the first L - S + 1 rows of row set is valid. All remaining rows are empty, and it becomes the SQL_ROW_NOROW state.

After returning SQLFetch, the current row is the first row of row set.

The rules in the following table describes the cursor position according to the conditions in the second table of the session after calling SQLFetch.

[1] If the row set size is changed during fetching, the row set size is the row set size used before fetching. 
[2] If the row set size is changed during fetching, the row set size is the row set size used in new fetch.

The following is an example of when 100 rows exist in the result set and the row set size is 5, then the following table describes the row set and the return code returned by SQLFetch for the different start position.

Returing the Data in the Bound Column

Like as SQLFetch returns each row, it inserts the data into each bound column in the buffer bound to the column. If the bound column does not exist, SQLFetch does not return any data, but it does not move the cursor forward. Data can be continuously fetched through SQLGetData. If the cursor is a multiple row cursor (SQL_ATTR_ROW_ARRAY_SIZE is bigger than 1.), SQLGetData can be called when after setting InfoType of SQLGetInfo to SQL_FETDATA_EXTENSIONS, or when returning SQL_GD_BLOCK. 
For more information, refer to SQLGetData.

SQLFetch executes the followings for each bound columns in the row.

1. If data is NULL, the length/indicator buffer is set to SQL_NULL_DATA and the next row is processed. If the data is NULL and the length/indicator buffer is not bound, SQLFetch returns SQLSTATE 22002 (Indicator variable required but not supplied) for the row and the next row to be processed. For more information about how to determine the address of the length/indicator, refer to Buffer Address of SQLBindCol. If the column data is not NULL, SQLFetch executes the process number 2.

2. If the attribute of SQL_ATTR_MAX_LENGTH statement is set to non-zero and the column includes the character or binary data, the data is truncated to the length of SQL_ATTR_MAX_LENGTH in bytes.

3. The data is converted to the format specified in TargetType of SQLBindCol.

4. If the data is converted to the variable length data type such as a string or binary, SQLFetch confirms if the data length exceeds the data buffer length. If the character data (including NULL termination character) exceeds the data buffer length, SQLFetch truncates the data to the data buffer length which is smaller than NULL termination character length. In this case, the data is terminated by NULL. If the binary data length exceeds the data buffer length, SQLFetch truncates the data to the data buffer length. The data buffer length is specified in BufferLength of SQLBindCol. SQLFetch never truncate the data converted to the fixed length data format because the data buffer length is always equal to the data type length.

5. The converted data (truncated data if possible) is put in the data buffer. For more information about how to determine the data buffer address, refer to Buffer Address of SQLBindCol.

6. The data length is put in the length/indicator buffer. If both the length pointer and indicator pointer are set in the same buffer (by calling SQLBindCol), the valid data length is recorded in the buffer, and SQL_NULL_DATA is recorded in the buffer if the data is NULL. If the length/indicator buffer is not bound, SQLFetch does not return the length.

• For the character or binary data, it is the data length before truncated after the data is converted because of too small buffer size. If the driver can not determine the length of too long data after conversion, the length is set to SQL_NO_TOTAL. If the data is truncated due to the attribute of SQL_ATTR_MAX_LENGTH statement, this attribute value is put in the length/indicator buffer instead of the actual length. It is because the attribute is designed to truncate the data in the server before conversion. So, the driver can not calculate the actual length.

• For all other data types, it is the data length after conversion.

7. If the data is truncated without any lost of significant digits during conversion(for example, the real number 1.234 is converted into 1 by truncation.), SQLFetch returns SQLSTATE 01S07(Fractional truncation) and SQL_SUCCESS_WITH_INFO. If the data is truncated because the data buffer length is too small (For example the string "abcdef" is put in the buffer of four bytes.), SQLFetch returns SQLSTATE 01004 (Data truncated) and SQL_SUCCESS_WITH_INFO. If the data is truncated because of the attribute of SQL_ATTR_MAX_LENGTH statement, SQLFetch returns SQL_SUCCESS and it does not return SQLSTATE 01S07(Fractional truncation) nor SQLSTATE 01004(Data truncated). If the significant digits of data is truncated while the data is converted (for example, the value of SQL_INTEGER which is bigger than 100,000 is converted into SQL_C_TINYINT.), SQLFetch returns SQLSTATE 22003(Numeric value out of range), SQL_ERROR (If the row set size is 1.) or SQL_SUCCESS_WITH_INFO (If the row set size is bigger than 1.).

If SQLFetch, SQL_SUCCESS of SQLFetchScroll, or SQL_SUCCESS_WITH_INFO is not returned, then the contents of the bound data buffer and the length/indicator buffer are not defined.

Row Status Array

The row status array is used to return the status of each row set. The array address is specified in the attribute of SQL_ATTR_ROW_STATUS_PTR statement. The array should assign the elements as many as specified by the attribute of SQL_ATTR_ROW_ARRAY_SIZE statement in the application. The value is set by SQLFetch, SQLFetchScroll, SQLBulkOperations or SQLSetPos. If the attribute value of SQL_ATTR_ROW_STATUS_PTR statement is the null pointer, the function does not return the row status.

The content of row status buffer is not defined if SQL_SUCCESS or SQL_SUCCESS_WITH_INFO is not returned by SQLFetch, SQLFetchScroll.

The following values are returned to the row status array.

Row Fetch Buffer

The row fetch buffer is used to return the number of fetched rows. When the data is fetched, the row without data due to an error is also included. It is the number of rows which is not SQL_ROW_NOROW value in the row status array. This buffer address is specified in the arribute of SQL_ATTR_ROWS_FETCHED_PTR statement. The buffer is allocated by the application, and it is set by SQLFetch, SQLFetchScroll. If the attribute value of SQL_ATTR_ROWS_FETCHED_PTR statement is the null pointer, the function does not return the number of fetched rows. The application calls SQLGetStmtAttr as the attribute of SQL_ATTR_ROW_NUMBER to determine the number of current rows in the result set.

The content of row fetch buffer is not defined if SQLFetch, SQLFetchScroll does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. If SQL_NO_DATA is returned, the value of row fetch buffer is set to 0.

Error Processing

Errors and warnings can be applied to the individual row or the entire function.

Error and Warning for Entire Function

If the error is applied to the entire function, as like SQLSTATE HYT00 (Timeout expired) or SQLSTATE 24000 (Invalid cursor state), SQLFetch returns SQL_ERROR and the corresponding SQLSTATE. The content of row buffer is not defined and the cursor position is not changed.

If the warning is applied to the entire function, SQLFetch returns SQL_SUCCESS_WITH_INFO and the corresponding SQLSTATE. The warning applied to the entire function is returned before the state is recorded to each row.

Error and Warning for Individual Row

The error such as SQLSTATE 22012 (division by zero) or the warning such as SQLSTATE 01004 (data truncated) is applied to the individual row.

SQLFetch executes the followings.

• It sets the element of row status array to SQL_ROW_ERROR for an error or SQL_ROW_SUCCESS_WITH_INFO for a warning.

• It adds one or more records which includes SQLSTATE for the error or warnig.

• It sets the row and column number fields in the status record. If SQLFetch can not determine the row or column number, it sets each number to SQL_ROW_NUMBER_UNKNOWN or SQL_COLUMN_NUMBER_UNKNOWN. If the status record is not applied to the specified column, SQLFetch sets the column number to SQL_NO_COLUMN_NUMBER.

If an error occurs in all rows of the row set (excluding the row in SQL_ROW_NOROW status), SQLFetch returns SQL_ERROR. If an error occurs in some rows, it returns SQL_SUCCESS_WITH_INFO. If the row set size is 1 and an error occurs in the row, SQLFetch returns SQL_ERROR.

Descriptor and SQLFetch

SQLFetch uses the following descriptor fields.

All descriptor fields can be set through SQLSetDescField.

Separating Length and Indicator Buffer

The application can bind one or two buffers to store the length and indicator value. If the application calls SQLBindCOl, SQL_DESC_OCTET_LENGTH_PTR and SQL_DESC_INDICATOR_PTR field of ARD is set in the address passed to StrLen_or_IndPtr argument. The application can set the two fields to another addresses by calling SQLSetDescField or SQLSetDescRec.

SQLFetch determines whether the application specifies a separate length and indicator buffer. If the data is not NULL, SQLFetch sets the indicator buffer to 0, and returns the length to the length buffer. If the data is NULL, SQLFetch sets the indicator buffer to SQL_NULL_DATA, and it does not modify the length buffer.

SQLFetchScroll

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLFetchScroll fetches the specified data set from the result set, and returns it to all bound columns.

Syntax

SQLRETURN SQLFetchScroll(
    SQLHSTMT      StatementHandle,
    SQLSMALLINT   FetchOrientation,
    SQLLEN        FetchOffset);

Arguments

StatementHandle

[Input] It is the statement handle.

FetchOrientation

[Input] It is the fetch type: SQL_FETCH_NEXT, SQL_FETCH_PRIOR, SQL_FETCH_FIRST, SQL_FETCH_LAST, SQL_FETCH_ABSOLUTE, SQL_FETCH_RELATIVE, SQL_FETCH_BOOKMARK

FetchOffset

[Input] It is the number of rows to fetch. The interpretation of this argument depends on the value of FetchOrientation argument.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA, SQL_STILL_EXECUTING, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

SQLFetchScroll returns the specified row set in the result set. The row set is set as the absolute or relative position or the bookmark position. SQLFetchScroll can be called while the result set exists, which is after the result set is created and before the cursor is closed. If the column is bound, the data is returned to the column. If the application specifies the pointer to a row status array or specifies the buffer which returns rows fetched, SQLFetchScroll also returns this information. SQLFetch and SQLFetchScroll can be called being mixed together.

Cursor Position

If the result set is created, the cursor is positioned before the start of result set. SQLFetchScroll positions the block cursor according to FetchOrientation argument and FetchOffset argument as follows. The following rules are used to determine the beginning of new row set.

The attribute of SQL_ATTR_ROW_ARRAY_SIZE statement specifies the number of rows of the row set. If the row set fetched by SQLFetchScroll overlaps with the end of result set, SQLFetchScroll returns a partial row set. If S is the start row of fetched row set, R is the row set size, L is the last row of result set, S + R - 1 is bigger than L, and the first L - S + 1 rows of row set is valid. All remaining rows are empty, and it becomes SQL_ROW_NOROW state.

After returning SQLFetchScroll, the current row is the first row of row set.

Cursor Position Rules

The following chapters describe rules for each FetchOrientation. The following notations are used for the rules.

SQL_FETCH_NEXT

The following rule is applied.

[1] If the row set size is changed during fetching the row set size is the row set size used before fetching.

SQL_FETCH_PRIOR

The following rule is applied.

[1] If the row set size is changed during fetching, the row set size is the row set size used in new fetch.

SQL_FETCH_RELATIVE

The following rule is applied.

[1] SQLFetchScroll returns the row set as same as when FetchOrientation is set to SQL_FETCH_ABSOLUTE and it is called.
[2] If the row set size is changed during fetching, the row set size is the row set size used in new fetch.

SQL_FETCH_ABSOLUTE

The following rule is applied.

[1] If the row set size is changed during fetching, the row set size is the row set size used in new fetch.

SQL_FETCH_FIRST

The following rule is applied.

SQL_FETCH_LAST

The following rule is applied.

[1]If the row set size is changed during fetching, the row set size is the row set size used in new fetch.

SQL_FETCH_BOOKMARK

The following rule is applied.

Buffer Address

SQLFetchScroll determines the data address and length/indicator buffer address in the same way as SQLFetch. For more information, refer to Buffer Address of SQLBindCol.

Row Status Array

SQLFetchScroll sets the row status array in the same way as SQLFetch. For more information, refer to Row Status Array of SQLFetch.

Row Fetch Buffer

SQLFetchScroll returns the number of rows fetched in the same way as SQLFetch. For more information, refer to Row Fetch Buffer of SQLFetch.

Error Processing

SQLFetchScroll returns the errors and warning in the same way as SQLFetch. For more information, refer to Error Processing of SQLFetch.

SQLForeignKeys

Conformance

Introduced version: ODBC 1.0
Standards compliance: ODBC

Overview

SQLForeignKeys returns the followings.

• The foreign key list of the specified table (the column in the specified table which refers to the the primary key of another table)

• The foreign key list of another table which refers to the primary key of the specified table

The driver returns each list as the result set.

Syntax

SQLRETURN SQLForeignKeys(
    SQLHSTMT       StatementHandle,
    SQLCHAR *      PKCatalogName,
    SQLSMALLINT    NameLength1,
    SQLCHAR *      PKSchemaName,
    SQLSMALLINT    NameLength2,
    SQLCHAR *      PKTableName,
    SQLSMALLINT    NameLength3,
    SQLCHAR *      FKCatalogName,
    SQLSMALLINT    NameLength4,
    SQLCHAR *      FKSchemaName,
    SQLSMALLINT    NameLength5,
    SQLCHAR *      FKTableName,
    SQLSMALLINT    NameLength6);

Arguments

StatementHandle

[Input] It is the statement handle.

PKCatalogName

[Input] It is the primary key table catalog name. PKCatalogName can not include the string search pattern.

If the attribute of SQL_ATTR_METADATA_ID statement is set to SQL_TRUE, then PKCatalogName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, then PKCatalogName is case-sensitive, and it is an ordinary argument literally processed.

NameLength1

[Input] It is the length of *PKCatalogName

PKSchemaName

[Input] It is the primary key table schema name. PKSchemaName can not include the string search pattern.

If the attribute of SQL_ATTR_METADATA_ID statement is set to SQL_TRUE, then PKSchemaName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, then PKSchemaName is case-sensitive, and it is an ordinary argument literally processed.

NameLength2

[Input] It is the length of *PKSchemaName

PKTableName

[Input] It is the primary key table name. PKTableName can not include the string search pattern.

If the attribute of SQL_ATTR_METADATA_ID statement is set to SQL_TRUE, then PKTableName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, then PKTableName is case-sensitive, and it is an ordinary argument literally processed.

NameLength3

[Input] It is the length of *PKTableName

FKCatalogName

[Input] It is the foreign key table catalog name. FKCatalogName can not include the string search pattern.

If the attribute of SQL_ATTR_METADATA_ID statement is set to SQL_TRUE, then FKCatalogName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, then FKCatalogName is case-sensitive, and it is an ordinary argument literally processed.

NameLength4

[Input] It is the length of *FKCatalogName

FKSchemaName

[Input] It is the foreign key table schema name. FKSchemaName can not include the string search pattern.

If the attribute of SQL_ATTR_METADATA_ID statement is set to SQL_TRUE, FKSchemaName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, FKSchemaName is case-sensitive, and it is an ordinary argument literally processed.

NameLength5

[Input] It is the length of *FKSchemaName

FKTableName

[Input] It is the foreign key table name. FKTableName can not include the string search pattern.

If the attribute of SQL_ATTR_METADATA_ID statement is set to SQL_TRUE, then FKTableName is treated as a case-insensitive identifier. If it is set to SQL_FALSE, FKTableName is case-sensitive, and it is an ordinary argument literally processed.

NameLength6

[Input] It is the length of *FKTableName

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

If *PKTableName includes the table name, SQLForeignKeys returns a result set which includes the primary key of the specified table and all foreign keys referring it. The foreign key list of another table does not include the foreign key pointing to the unique constraint on the specified table.

If *FKTableName includes the table name, SQLForeignKeys returns the result set which contains the foreign key in the specified table pointing to the primary key of another table, and returns its primary key of another table which is referenced by them.

If both *PKTableName and *FKTableName include the table name, SQLForeignKeys returns the foreign key of the table specified in *FKTableName which refers to the primary key of the table specified in *PKTableName. The key should be one.

SQLForeignKeys returns the standard result set. If the foreign key related to the primary key is requested, the result set is sorted as FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ. If the primary key related to the foreign key is requested, the result set is sorted as PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, KEY_SEQ.

The following table describes the columns of result set.

SQLFreeConnect

Conformance

Introduced version: ODBC 1.0
Standards compliance: It is not available.

Overview

SQLFreeConnect function is replaced with SQLFreeHandle function in ODBC 3.x. 
For more information, refer to SQLFreeHandle.

SQLFreeEnv

Conformance

Introduced version: ODBC 1.0
Standards compliance: It is not available.

Overview

SQLFreeEnv function is replaced with SQLFreeHandle function in ODBC 3.x. 
For more information, refer to SQLFreeHandle.

SQLFreeHandle

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLFreeHandle releases the resources related to the specified environment, connection, statement, and descriptor handles.

Syntax

SQLRETURN SQLFreeHandle(
    SQLSMALLINT   HandleType,
    SQLHANDLE     Handle);

Arguments

HandleType

[Input] It is the handle type to be released by using SQLFreeHandle. It should be one of SQL_HANDLE_DBC, SQL_HANDLE_DESC, SQL_HANDLE_ENV, SQL_HANDLE_STMT.

If HandleType is not one of the values above, SQLFreeHandle returns SQL_INVALID_HANDLE.

Handle

[Input] It is the handle to be released.

Returns

SQL_SUCCESS, SQL_ERROR, SQL_INVALID_HANDLE.
If SQLFreeHandle returns SQL_ERROR, the handle is still valid.

Diagnosis

Description

SQLFreeHandle is used to release the environment, connection, statement, descriptor handles.
After the handle is released, the application can not use the released handle.

Releasing the Environment Handle

Before calling SQLFreeHandle whose HandleType is SQL_HANDLE_ENV, the application should call SQLFreeHandle whose HandleType is SQL_HANDLE_DBC for all connection allocated from the environment. Otherwise, SQLFreeHandle returns SQL_ERROR, and the environment and active connection remain valid.

Releasing the Connection Handle

Before calling SQLFreeHandle whose HandleType is SQL_HANDLE_DBC, if the handle is connected, the application should call SQLDisconnect. Otherwise, SQLFreeHandle returns SQL_ERROR, and the connection remains valid.

Releasing the Statement Handle

SQLFreeHandle whose HandleType is SQL_HANDLE_STMT, releases all resources allocated by calling SQLAllocHandle whose HandleType is SQL_HANDLE_STMT. If the application calls SQLFreeHandle with the remaining statement, the remaining result is deleted. When the application releases the statement handle, the driver releases the four auto allocation descriptors related to the statement.

SQLDisconnect automatically deletes all statements and descriptors which are opened for the connection.

SQLFreeStmt

Conformance

Introduced version: ODBC 1.0
Standards compliance: ISO 92

Overview

SQLFreeStmt stops the processing related to the specified statement, closes an open cursor, removes the remaining result or selectively releases all resources connected to the statement handle.

Syntax

SQLRETURN SQLFreeStmt(
    SQLHSTMT       StatementHandle,
    SQLUSMALLINT   Option);

Arguments

StatementHandle

[Input] It is the statement handle.

Option

[Input] It is one of the following options.

• SQL_ CLOSE: It closes the cursor related to StatementHandle, and deletes the remaining result. The applications executes SELECT statement with the same or different parameter values, and it can open cursor again later. If the cursor is not open, this option does not affect the application. SQLCloseCursor also closes the cursor.

• SQL_DROP: This option is not used any more.

• SQL_UNBIND: It releases all column buffers bound with SQLBindCol for the specified StatementHandle, and sets SQL_DESC_COUNT field of ARD to 0.

• SQL_RESET_PARAMS: It releases all parameter buffers set to SQLBindParameter for the specified StatementHandle, and sets SQL_DESC_COUNT field of APD to 0.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

Calling SQLFreeStmt with SQL_CLOSE option is as same as calling of SQLCloseCursor. However, if an open cursor does not exist, calling SQLFreeStmt with SQL_CLOSE option does not affect the application. SQLCloseCursor returns SQLSTATE 24000 (Invalid cursor state).

SQLGetConnectAttr

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLGetConnectAttr returns the current setting of the connection attribute.

Syntax

SQLRETURN SQLGetConnectAttr(
    SQLHDBC        ConnectionHandle,
    SQLINTEGER     Attribute,
    SQLPOINTER     ValuePtr,
    SQLINTEGER     BufferLength,
    SQLINTEGER *   StringLengthPtr);

Arguments

ConnectionHandle

[Input] It is the connection handle.

Attribute

[Input] It is the attribute to be searched

ValuePtr

[Output] It is a memory pointer which returns the current setting value of the attribute specified by Attribute.

If ValuePtr is null, StringLengthPtr returns the total number of bytes returnable (excluding the null-termination character).

BufferLength

[Input] If Attribute is a defined field in ODBC and ValuePtr points to the string or binary buffer, this argument should be the length of *ValuePtr. If Attribute is a defined field in ODBC and *ValuePtr is an integer, this argument is ignored.

StringLengthPtr

[Output] It is the pointer which returns the total number of bytes returnable in *Value_Ptr (excluding null-termination byte for character data)

For the character data, if the number of returnable bytes is equal to or bigger than BufferLength, *ValuePtr is truncated to length of BufferLength minus 1, and it is null terminated by the driver.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

If an attribute specifies the attribute which returns the string, ValuePtr should have the pointer to the string buffer. The maximum length of the returned string including null-termination character is BufferLength in bytes.

SQLGetConnectOption

Conformance

Introduced version: ODBC 1.0
Standards compliance: It is not available.

Overview

SQLGetConnectOption function is replaced with SQLGetConnectAttr function in ODBC 3.x. 
For more information, refer to SQLGetConnectAttr.

SQLGetCursorName

Conformance

Introduced version: ODBC 1.0
Standards compliance: ISO 92

Overview

SQLGetCursorName returns the specified statement and related cursor name.

Syntax

SQLRETURN SQLGetCursorName(
    SQLHSTMT        StatementHandle,
    SQLCHAR *       CursorName,
    SQLSMALLINT     BufferLength,
    SQLSMALLINT *   NameLengthPtr);

Arguments

StatementHandle

[Input] It is the statement handle.

CursorName

[Output] It is the buffer pointer which returns the cursor name

If CursorName is NULL, NameLengthPtr returns the returnable byte length (excluding the null-termination character)

BufferLength

[Input] It is the length of *CursorName.

NameLengthPtr

[Output] It is the memory pointer for the length of bytes returnable to *CursorName (excluding the null-termination character). If the returnable byte length is equal to or bigger than it, *sCursorName is truncated to the length of BufferLength minus 1.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

The cursor name is used only in positioned update and positioned delete statements. (e.g. UPDATE table-name ...WHERE CURRENT OF cursor-name) If the cursor name is not set with SQLSetCursorName in the application, the driver generates the cursor name starting with SQL_CUR.

SQLGetCursorName can re-set the cursor name, if the statement is the assigned or ready state.

The cursor name which is explicitly or implicitly set is valid until the related statement is deleted by calling SQLFreeHandle whose HandleType is SQL_HANDLE_STMT.

SQLGetData

Conformance

Introduced version: ODBC 1.0
Standards compliance: ISO 92

Overview

SQLGetData retrieves a column data in the result set, and it can be called multiple times to retrieve the variable length data.

Syntax

SQLRETURN SQLGetData(
    SQLHSTMT       StatementHandle,
    SQLUSMALLINT   Col_or_Param_Num,
    SQLSMALLINT    TargetType,
    SQLPOINTER     TargetValuePtr,
    SQLLEN         BufferLength,
    SQLLEN *       StrLen_or_IndPtr);

Arguments

StatementHandle

[Input] It is the statement handle.

Col_or_Param_Num

[Input] It is the column number for retrieving the column data from the returned data. Result set columns are numbered in increasing order, starting from 1.

TargetType

[Input] It is the C data type identifier of *TargetValuePtr buffer.

If TargetType is SQL_ARD_TYPE, the driver uses the type identifier specified in SQL_DESC_CONCISE_TYPE field of ARD.

TargetValuePtr

[Output] It is the buffer pointer to which the data is returned

TargetValuePtr can not be NULL.

BufferLength

[Input] It is the byte length of *TargetValuePtr buffer.

When the driver returns the variable length data such as string or binary data, it uses BufferLength to avoid writing beyond the end of *TargetValuePtr buffer. Be cautious when returning character data to *TargetValuePtr because the driver calculates the null-termination character. Therefore, *TargetValuePtr should include the space for the null-termination character. Otherwise, the driver can truncate the data.

When the driver returns the fixed length data such as integer or date structure, the driver assumes that the buffer is large enough to store the data, and it ignores BufferLength. Therefore, the application should allocate the buffer which is large enough to the fixed length data. If not, the driver can write beyond the end of buffer.

SQLGetData returns SQLSTATE HY090(Invalid string or buffer length), if BufferLength is smaller than 0.

StrLen_or_IndPtr

[Output] It is the buffer pointer which returns the length or indicator value. If the argument is the null pointer, the length and indicator values are not returned, and an error occurs when fetching NULL data.

SQLGetData can return SQL_NO_TOTAL, SQL_NULL_DATA, and the data length which can be returned to the length/indicator buffer.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA, SQL_STILL_EXECUTING, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

SQLGetData returns the data of the specified column, and it can be called only after one or more rows are fetched from the result set of SQLFetch or SQLFetchScroll. When the variable length data is too large to be returned with a single call SQLGetData (because of application restrictions), SQLGetData can partially retrieve it.

Using SQLGetData

If TargetType argument is the interval data type, the default value is interval leading precision(2), interval seconds precision(6), and it is set to SQL_DESC_DATETIME_INTERVAL_PRECISION field and SQL_DESC_PRECISION field of ARD each. If TargetType argument is the SQL_C_NUMERIC data type, the default value is precision(38), scale(0), and it is set to SQL_DESC_PRECISION field and SQL_DESC_SCALE field of ARD each. If the default precision and scale are not appropriate, the application should explicitly set the descriptor field by calling SQLSetDescField or SQLSetDescRec.

Partial Retrieving of Variable Length Data

SQLGetData can partially retrieve the variable length data whose SQL data type is SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR, SQL_WCHAR, SQL_WVARCHAR, SQL_WLONGVARCHAR, SQL_BINARY, SQL_VARBINARY, SQL_LONGVARBINARY.

The application continuously calls SQLGetData multiple times for the same column to partially retrieve the data of the column. SQLGetData returns the next part of the data at each call. The applications should reassemble the parts, being careful to remove the null-termination character in the middle of the character data. If more data should be returned, SQLGetData returns SQL_SUCCESS_WITH_INFO, SQLSTATE 01004(Data truncated). If the last part of data is returned, SQL_SUCCESS is returned.

SQLGetData can not be used for returning the part of fixed length data. If SQLGetData is called once or more for a column which contains the fixed-length data, SQL_NO_DATA is returned after the first call.

Retrieving Data Using SQLGetData

SQLGetData performs the following processes to return the data for the specified column.

1. If all data is returned to the column, SQL_NO_DATA is returned.

2. If the data is NULL, *StrLen_or_IndPtr is set to SQL_NULL_DATA. If the data is NULL and *StrLen_or_IndPtr is the null pointer, SQLGetData returns SQLSTATE 22002 (Indicator variable required but not supplied).

   If the column data is not NULL, SQLGetData performs the third process.

3. If the attribute of SQL_ATTR_MAX_LENGTH statement is set to non-zero value or the column contains the string or binary data or SQLGetData is not called for the column before, then the data is truncated to length of SQL_ATTR MAXLENGTH bytes.

4. The data is converted to the data type specified in TargetType. The default precision and scale values for the data type are given to the data. If TargetType is SQL_ARD_TYPE, the data type in SQL_DESC_CONCISE_TYPE field of ARD is used. The data provides the precision and scale of SQL_DESC_DATETIME_INTERVAL_PRECISION field, SQL_DESC_PRECISION field, SQL_DESC_SCALE field of ARD depending on the data type of SQL_DESC_CONCISE_TYPE field. If all default precisions and scales are not appropriate, the application should explicitly set the descriptor field by calling SQLSetDescField or SQLSetDescRec.

5. If the data is converted to the variable length data type such as the string or binary, SQLGetData checks whether the data length exceeds BufferLength (including the null-termination character). If the data length exceeds BufferLength, SQLGetData truncates the data to the length of BufferLength minus the null-termination character. If the binary data length exceeds the data buffer length, SQLGetData truncates it to the length of BufferLength bytes.

   If the null-termination character is not stored in the provided data buffer, SQLGetData returns SQL_SUCCESS_WITH_INFO and SQLSTATE 01004.

   SQLGetData does not truncate the data converted to the fixed length data type. In this case, the length of *TargetValuePtr is always assumed as the size of the data type.

6. It stores the converted data in *TargetValuePtr. Be cautious that SQLGetData can not return data out of line.

7. The data length is stored in *StrLen_or_IndPtr. If StrLen_or_IndPtr is the null pointer, SQLGetData does not return the length.

   • For the string and binary data, it is the length after conversion and before truncated to BufferLength. If the driver can not check the data length after conversion (it can happen when it is a long data type), it returns SQL_SUCCESS_WITH_INFO, and it sets the length to SQL_NO_TOTAL.(The last call of SQLGetData should return the value of data length which is neither 0 nor is SQL_NO_TOTAL) If the data is truncated by the attribute of SQL_ATTR_MAX_LENGTH statement, the attribute value is stored in *StrLen_or_IndPtr. It is because the data is designed to be passed on the server before the attribute is converted, and the driver can not know the actual length. When SQLGetData is continuously called multiple times for the same column, this is the data length available at the beginning of the current call. In other words, the length is reduced due to each subsequent calls.

   • For all other data types, this is the data length after conversion. In other words, it is the size of the type to which data is converted.

8. If the data is truncated without loss of the default value(for example, the real number 1.234 is truncated to an integer 1 when converted), or if it is truncated because BufferLength is too small (e.g. the string "abcdef" is stored in the buffer of 4-byte length) during data conversion, then SQLGetData returns SQLSTATE 01004 (Data truncated) and SQL_SUCCESS_WITH_INFO. If the data is truncated without loss of the default value due to the attribute of SQL_ATTR_MAX_LENGTH statement, SQLGetData returns SQL_SUCCESS but it does not return SQLSTATE 01004 (Data truncated).

If SQLGetData does not return SQL_SUCCESS nor SQL_SUCCESS_WITH_INFO (When SQLGetData is called for the bound column) the contents of bound data buffer, and the length/indicator buffer are not defined.

If SQLGetData is continuously called, it retrieves the data from the last column requested. The previous offset is not valid.

The following is an example.

SQLGetData(icol=n), SQLGetData(icol=m), SQLGetData(icol=n)

The second call SQLGetData(icol=n) retrieves the data starting from the column n. All offsets of the data are not valid any more due to the previous SQLGetData call.

SQLGetData and Descriptor

SQLGetData does not directly interact with any descriptor field.

If TargetType is SQL_ARD_TYPE, the data type of SQL_DESC_CONCISE_TYPE field of ARD is used. If TargetType is SQL_ARD_TYPE or SQL_C_DEFAULT, the precisions and scales of SQL_DESC_DATETIME_INTERVAL_PRECISION field, SQL_DESC_PRECISION field, and SQL_DESC_SCALE field of ARD are given according to the data type of SQL_DESC_CONCISE_TYPE field.

SQLGetDescField

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLGetDescField returns the current setting or value of a single field of descriptor record.

Syntax

SQLRETURN SQLGetDescField(
    SQLHDESC        DescriptorHandle,
    SQLSMALLINT     RecNumber,
    SQLSMALLINT     FieldIdentifier,
    SQLPOINTER      ValuePtr,
    SQLINTEGER      BufferLength,
    SQLINTEGER *    StringLengthPtr);

Arguments

DescriptorHandle

[Input] It is the descriptor handle.

RecNumber

[Input] It is the descriptor record of information which the application tries to find. If FieldIdentifier argument is the header field, RecNumber is ignored. If RecNumber is equal to or less than SQL_DESC_COUNT, and the row does not include the data for the column or parameter, SQLGetDescField returns the default value of field.

FieldIdentifier

[Input] It is the descriptor field to which the value is returned.

ValuePtr

[Output] It is the buffer pointer which returns the descriptor information. The data type is dependent on the value of FieldIdentifier.

If ValuePtr is the integer type, the application should use SQLULEN buffer initialized to 0.

If ValuePtr is null, StringLengthPtr returns the number of the total returnable bytes (excluding null-termination character).

BufferLength

[Input] If FieldIdentifier field is defined in ODBC and ValuePtr points to the string or binary buffer, the argument should be the length of *ValuePtr. If FieldIdentifier field is defined in ODBC and *ValuePtr is an integer, the argument is ignored.

StringLengthPtr

[Output] It is the pointer which returns the number of the total returnable bytes from *ValuePtr(excluding null-termination byte for the character data).

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_NO_DATA, SQL_INVALID_HANDLE

If RecNumber is bigger than the number of current descriptor records, SQL_NO_DATA is returned.
If DescriptorHandle is IRD handle and the statement is in the ready state or executing state but associated cursor does not exist, SQL_NO_DATA is returned.

Diagnosis

Description

The application can call SQLGetDescField to return a single field value of the descriptor record. Calling SQLGetDescField returns the settings for all fields of all descriptor types including the header field, record fields, and bookmarks field. The application can obtain the multiple field settings in an arbitrary order in the same or another descriptor by repeatedly calling SQLGetDescField. SQLGetDescField can be called to return the driver definition descriptor field.

For the performance reason, the application should not call SQLGetDescField for IRD before executing the statement.

Multiple field settings for the size of name, data type, column or parameter data can be retrieved by calling SQLGetDescRec once. SQLGetStmtAttr can be called to return a single field settings in the descriptor header which is the statement attribute. SQLColAttribute, SQLDescribeCol, SQLDescribeParam return the records or book mark fields.

When the application calls SQLGetDescField to retrieve the undefined field value for a particular descriptor type, the function returns SQL_SUCCESS but the returned field value is not defined. For example, calling SQLGetDescField for SQL_DESC_NAME field of APD/ ARD or calling SQLGetDescField for SQL_DESC_NULLABLE field returns SQL_SUCCESS, but the field value is not defined.

When the application calls SQLGetDescField to retrieve the field value whose particular descriptor is defined but any settings such as default value are not defined, then the function returns SQL_SUCCESS but the returned field value is not defined.

Header Field

Each descriptor consists of the following fields.

SQL_DESC_ALLOC_TYPE[All]

It is the header field which is read-only SQLSMALLINT. It specifies whether the descriptor is automatically assigned by the driver or it is explicitly assigned by the application. The application can read the field but can not modify it. When the descriptor is automatically allocated by the driver, the field is set to SQL_DESC_ALLOC_AUTO by the driver.

SQL_DESC_ARRAY_SIZE[Application descriptors]

It is the header field which is SQLULEN in ARD. It specifies the number of rows in the row set. This is the number of rows returned by the operation caused by calling SQLFetch, SQLFetchScroll, SQLBulkOperations or SQLSetPos.

It is the header field which is SQLULEN in APD. It specifies the number of values of each parameter.

The default value of the field is 1. If SQL_DESC_ARRAY_SIZE is bigger than 1, SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, SQL_DESC_OCTET_LENGTH_PTR of APD or ARD points to the array. The constant of each array is equal to the field value.

This field in ARD can be set by calling SQLSetStmtAttr together with SQL_ATTR_ROW_ARRAY_SIZE attribute. The field in APD can also be set by calling SQLSetStmtAttr together with SQL_ATTR_PARAMSET_SIZE.

SQL_DESC_ARRAY_STATUS_PTR[All]

For each descriptor type, the header field which is SQLUSMALLINT* points to the array of SQLUSMALLINT value. The arrays are named as row status array (IRD), parameter status array (IPD), row operation array (ARD), parameter operation array (APD).

This header field in IRD points to the row status array which contains the status value after calling SQLBulkOperations, SQLFetch, SQLFetchScroll, or SQLSetPos. The application allocates SQLUSMALLINT array, and this field should point to the array. The field is generally the NULL pointer. The driver will create an array unless SQL_DESC_ARRAY_STATUS_PTR is set to the NULL pointer.

The array is initialized and created by calling SQLBulkOperations, SQLFetch, SQLFetchScroll or SQLSetPos. If this call does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, then the content of array pointed by the field is not defined.

Elements in the array can contain the following values.

• SQL_ROW_SUCCESS: The row is successfully retrieved, and it is not updated since the last retrieving.

• SQL_ROW_SUCCESS_WITH_INFO: The row is successfully retrieved, and it is not updated since the last return. However, the warning for the row is returned.

• SQL_ROW_ERROR: An error occurs during retrieving the row.

• SQL_ROW_UPDATED: The row is successfully retrieved, and it is updated since the last retrieving. If the row is retrieved again, the state is SQL_ROW_SUCCESS.

• SQL_ROW_DELETED: The row is deleted since the last retrieving.

• SQL_ROW_ADDED: The row is entered by SQLBulkOperations. If the row is retrieved again, the state is SQL_ROW_SUCCESS.

• SQL_ROW_NOROW: The row set and the last of result set are overlapped. And a row is not returned corresponding to the elements of row status array.

This field of IRD can be set by calling SQLSetStmtAttr together with SQL_ATTR_ROW_STATUS_PTR attribute.

SQL_DESC_ARRAY_STATUS_PTR field of IRD is valid only after SQL_SUCCESS or SQL_SUCCESS_WITH_INFO is returned. If the return code is not one of these, the thing pointed by SQL_DESC_ROWS_PROCESSED_PTR is not defined.

This header field in IPD calls SQLExecute or SQLExecDirect and then points to the parameter status array which includes each parameter status information. If SQLExecute or SQLExecDirect is called and SQL_SUCCESS or SQL_SUCCESS_WITH_INFO is not returned, then the content of array pointed by this field is not defined. The application allocates SQLUSMALLINT array, and this field should point the array. The driver will create an array if SQL_DESC_ARRAY_STATUS_PTR field is not set to the NULL pointer.

Elements in the array can contain the following values.

• SQL_PARAM_SUCCESS: SQL statement was successfully executed for this parameter set.

• SQL_PARAM_SUCCESS_WITH_INFO: SQL statement was successfully executed for this parameter set, but there is a warning information available to the diagnostic data structure.

• SQL_PARAM_ERROR: An error occurred when processing the parameter set. Additional error information is in the diagnostic data structure.

• SQL_PARAM_UNUSED: An error occurred while processing some previous parameters, or it is not used because SQL_PARAM_IGNORE is set in the parameter set of the array pointed by SQL_DESC_ARRAY_STATUS_PTR field of APD.

• SQL_PARAM_DIAG_UNAVAILABLE: The diagnostic information can not be used. For example, the driver treats the array of the parameter as one and does not generate the level of the error information.

This field in IPD can be set by calling SQLSetStmtAttr together with SQL_ATTR_PARAM_STATUS_PTR attribute.

In ARD, the corresponding field points to the row operation array for the values set by the application to determine whether or not to ignore the row in SQLSetPos operation.

Elements in the array can contain the following values.

• SQL_ROW_PROCEED: The row is included in the bulk operation which uses SQLSetPos. (This setting does not guarantee that the operation occurs on the row. If the row has SQL_ROW_ERROR state of the row status array of IRD, the driver can not implement the operation for the row.)

• SQL_ROW_IGNORE: The row is excluded from the bulk operation which uses SQLSetPos.

If elements of the array is not set, all rows are included in the bulk operation. If the value of SQL_DESC_ARRAY_STATUS_PTR field of ARD is the NULL pointer, all rows are included in the bulk operation. It is translated as same as when the pointer points to a valid array and all elements of the array are SQL_ROW_PROCEED. When all elements in the array are set to SQL_ROW_IGNORE, the values in the row status array for the ignored rows are not updated.

This field in ARD can also be set by calling SQLSetStmtAttr together with SQL_ATTR_ROW_OPERATION_PTR attribute.

When SQLExecute or SQLExecDirect is called, this header field in APD points to the parameter operation array of the values set by the application to indicate whether or not to ignore the parameter set.

Elements in the array can contain the following values.

• SQL_PARAM_PROCEED: The parameter set is included in calling SQLExecute or SQLExecDirect.

• SQL_PARAM_IGNORE: The parameter set is not included in calling SQLExecute or SQLExecDirect.

If elements of the array are not set, all parameter sets of the array are used to call SQLExecute or SQLExecDirect. If the value of SQL_DESC_ARRAY_STATUS_PTR field of APD is the NULL pointer, all parameter set are used. It is read as same as when the pointer points to the valid array, or the array whose all elements are SQL_PARAM_PROCEED.

This field in APD can also be set by calling SQLSetStmtAttr together with SQL_ATTR_PARAM_OPERATION_PTR attribute.

SQL_DESC_BIND_OFFSET_PTR[Application descriptors]

This header field is SQLLEN* and it points to the binding offset. It is set to the NULL pointer by default. If this field is not the NULL pointer, the driver dereferences the pointer and each value of the deferred field which has non-NULL value of the descriptor record (SQL_DESC_DATA_PTR_, SQL_DESC_INDICATOR_PTR and SQL_DESC_OCTET_LENGTH_PTR) at fetching time, and uses a new pointer value when binding.

The binding offset is always directly added to SQL_DESC_DATA_PTR field, SQL_DESC_INDICATOR_PTR field and SQL_DESC_OCTET_LENGTH_PTR field. If the offset value is changed to another value, the new value is directly and continuously added as each descriptor field value. The new offset is not added to the previous offset value of the field.

It is the deferred field. This field is not used at the time of setting, but it is used by the driver when the data buffer address is checked.

This field in ARD can be set by calling SQLSsetStmtAttr together with SQL_ATTR_ROW_BIND_OFFSET_PTR attribute.

For more information, refer to SQLFetchScroll or SQLBindParameter.

SQL_DESC_BIND_TYPE[Application descriptors]

This header field is SQLUINTEGER and is used to set the binding direction.

This field in ARD specifies the binding direction when SQLFetchScroll or SQLFetch is called on the related statement handle.

This field can be set to SQL_BIND_BY_COLUMN(default) to select the column-wise binding for the columns.

The field in ARD can be set by calling SQLSetStmtAttr together with SQL_ATTR_ROW_BIND_TYPE attribute.

The field specifies the binding direction used in the dynamic parameter.

This field may be set to SQL_BIND_BY_COLUMN(default) to select the column-wise binding for the parameter.

This field can be set by calling SQLSetStmtAttr together with SQL_ATTR_PARAM_BIND_TYPE attribute.

SQL_DESC_COUNT[All]

This header field is SQLSMALLINT and specifies the 1-based index of the top-level record which includes the data. When the driver sets the data structure to the descriptor, SQL_DESC_COUNT should be set to display the number of important records. It is not required to specify how many space to reserve for the record when the application allocates instances of data structure. Like as the application specifies the contents of the records, the driver performs the requested operation for the descriptor handle to guarantee displaying the sufficient size of the data structure.

It is not the number of all data column or all parameters which SQL_DESC_COUNT is bound to, but it is the number of top-level records. If the binding of the top-level column or parameter is released, SQL_DESC_COUNT is changed to the number of next top-level columns or parameters. If the binding of the top-level column, the column which is smaller than the parameter, or parameters is released (if TargetValuePtr argument is set to the NULL pointer, and SQLBindCol is called, or if ParameterValuePtr argument is set to the NULL pointer and SQLBindParameter is called), SQL_DESC_COUNT is not changed. If the added column or parameter is bound with bigger number than the number of top level records which includes the data, the driver automatically increases the value of SQL_DESC_COUNT field. If the binding for all columns is released by calling SQLFreeStmt with the SQL_UNBIND option, SQL_DESC_COUNT in ARD and IRD is set to 0. If SQLFreeStmt is called with the SQL_RESET_PARAMS option, SQL_DESC_COUNT fields in APD and IPD is set to 0.

The value of SQL_DESC_COUNT is explicitly set by calling SQLSetDescField in the application. If the value of SQL_DESC_COUNT is explicitly decreased, the records which have the value bigger than the new value of SQL_DESC_COUNT are effectively removed. If the value of SQL_DESC_COUNT field of ARD is set to 0, all buffers are released except for the bound bookmark column. The number of records of the field Of ARD does not include the bound bookmark column. The only way to release the binding of book mark column is to set SQL_DESC_DATA_PTR to NULL pointer.

SQL_DESC_ROWS_PROCESSED_PTR[Implementation descriptors]

This header field is SQLULEN* of IRD, and points to the buffer. The buffer includes the number of rows returned after calling SQLFetch or SQLFetchScroll, and the number of rows or number of errors affected by calling SQLBulkOperations or SQLSetPos and executing the bulk operation.

This header field is SQLUINTEGER* of IPD and points to the buffer which includes the processed parameter set or the number of errors. If it is NULL pointer, the number is not returned.

SQL_DESC_ROWS_PROCESSED_PTR is valid only after calling SQLFetch or SQLFetchScroll(IRD) or after SQL_SUCCESS or SQL_SUCCESS_WITH_INFO is returned by calling SQLExecute, SQLExecDirect or SQLParamData(IPD). If the functions above do not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, then the buffer contents are not defined and the buffer value is set to 0 until SQL_NO_DATA is returned.

The field in ARD can be set by calling SQLSetStmtAttr together with SQL_ATTR_ROWS_FETCHED_PTR attribute. APD can be set by calling SQL_ATTR_PARAMS_PROCESSED_PTR.

The buffer pointed by this field is allocated by the application. It is the deferred output buffer defined by the driver. NULL pointer is set by default.

Record Field

Each descriptor includes one or more records consisting of the fields which define the column data or dynamic parameter depending on the descriptor type. Each record is a complete specification of a single column or parameter.

SQL_DESC_AUTO_UNIQUE_VALUE[IRDs]

This record field is read-only SQLINTEGER. It is SQL_TRUE if the column is automatically increased. Otherwise, it is SQL_FALSE. The auto-increment column does not need to be read-only.

SQL_DESC_BASE_COLUMN_NAME[IRDs]

This record field is read-only SQLCHAR*. It includs the default column name of the result set column. If the default column name does not exist, the field should include an empty string.

SQL_DESC_TABLE_NAME[IRDs]

This record field is read-only SQLCHAR*. It includes the base table name of the result set column. If the base table name is not defined or used, the field should include an empty string.

SQL_DESC_CASE_SENSITIVE[Implementation descriptors]

This record field is read-only SQLINTEGER. It includes SQL_TRUE if the column or parameter is case-sensitive when sorting or comparing columns or parameters, but it includes SQL_FALSE if the column or parameter is case-insensitive or non-character column.

SQL_DESC_CATALOG_NAME[IRDs]

This record field that is read-only SQLCHAR*. It includes the catalog of the base table which includes the column. If the column is an expression or a part of a view, the return value is dependent on the driver. If the data source does not support the catalog nor does it check the catalog, the field should include an empty string.

SQL_DESC_CONCISE_TYPE[All]

This header field is SQLSMALLINT. It speifies the simple format for the data types which includes datetime and interval data types.

The value of SQL_DESC_CONCISE_TYPE field, SQL_DESC_TYPE field and SQL_DESC_DATETIME_INTERVAL_CODE field are interdependent. If time is set in one of the fields, it should be set on other fields as well. SQL_DESC_CONCISE_TYPE can be set by calling SQLBindCol, SQLBindParameter or SQLSetDescField. SQL_DESC_TYPE is set by calling SQLSetDescField or SQLSetDescRec.

If SQL_DESC_CONCISE_TYPE is set as the simple data types except for the interval or datetime data type, then SQL_DESC_TYPE field is set to the same value, and SQL_DESC_DATETIME_INTERVAL_CODE field is set to 0.

If SQL_DESC_CONCISE_TYPE is set as the simple datetime or interval data type, then SQL_DESC_TYPE field is set to the detailed type (SQL_DATETIME or SQL_INTERVAL), and SQL_DESC_DATETIME_INTERVAL_CODE field is set to the proper subcode.

SQL_DESC_DATA_PTR [Application descriptors and IPD]

The SQLPOINTER record field points to the address of a variable that stores either parameter values (in the case of the APD) or column values (in the case of the ARD). This field is a deferred field, meaning it is not used at the time it is set, but rather when the driver retrieves the actual data later.

A column specified in the SQL_DESC_DATA_PTR field of the ARD becomes unbound if the TargetValuePtr argument is a null pointer when calling SQLBindCol, or if the SQL_DESC_DATA_PTR field of the ARD is set to a null pointer calling SQLSetDescField or SQLSetDescRec. However, other fields remain unaffected even if the SQL_DESC_DATA_PTR field is set to a null pointer.

When the buffer pointed to by this field is filled upon calling SQLFetch or SQLFetchScroll, if SQL_SUCCESS or SQL_SUCCESS_WITH_INFO is not returned, the contents of the buffer remain in an undefined state.

Whenever the SQL_DESC_DATA_PTR field of the APD, ARD, or IPD is set, the driver verifies that the SQL_DESC_TYPE field contains a valid ODBC C data type or a driver-specific data type. It also checks for consistency in other fields related to the data type. In particular, the SQL_DESC_DATA_PTR field of the IPD is used solely for consistency checks. That is, even if an application sets the SQL_DESC_DATA_PTR field of the IPD, calling SQLGetDescField on this field later does not necessarily return the previously set value.

SQL_DESC_DATETIME_INTERVAL_CODE[All]

This record field is SQLSMALLINT. It includes the subcode which specifies the datetime or interval data type when SQL_DESC_TYPE field is SQL_DATETIME or SQL_INTERVAL. It is the same for both SQL and C support it, the code includes the data type name such as "TYPE" or "C_TYPE" for datetime type, "CODE" replaced from "INTERVAL" or "C_INTERVAL" for interval types.

If SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE of the application descriptor are set to SQL_C_DEFAULT and the descriptor is not related to the statement handle, then the content of SQL_DESC_DATETIME_INTERVAL_CODE is not defined.

This field can set the datetime data types listed in the following table.

This field can set the interval data types listed in the following table.

SQL_DESC_DATETIME_INTERVAL_PRECISION[All]

This SQLINTEGER record field includes the interval leading precision if SQL_DESC_TYPE field is SQL_INTERVAL. When SQL_DESC_DATETIME_INTERVAL_CODE field is set to the interval data type, the field sets the default interval leading precision.

SQL_DESC_DISPLAY_SIZE[IRDs]

This read-only SQLLEN record field contains the maximum number of characters required to show the data from the column.

SQL_DESC_FIXED_PREC_SCALE[Implementation descriptors]

This read-only SQLSMALLINT record field sets SQL_TRUE if the column is the exact numeric column and it has non-zero scale and the fixed precision. Otherwise, it sets SQL_FALSE.

SQL_DESC_INDICATOR_PTR[Application descriptors]

This SQLLEN* record field in ARD represents the indicator variable. The variable includes SQL_NULL_DATE if the column value is NULL. For APD, the indicator variable is set to SQL_NULL_DATA to specify a dynamic factor as NULL, otherwise the variable is zero.

If SQL_DESC_INDICATOR_PTR field of ARD is NULL pointer, the driver prevents the information returned for whether the column is NULL. If the column is NULL and SQL_DESC_INDICATOR_PTR is NULL pointer, SQLSTATE 22002(Indicator variable required but not supplied) is returned when the driver creates the buffer after calling SQLFetch or SQLFetchScroll. If calling SQLFetch or SQLFetchScroll does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the buffer content is not defined.

SQL_DESC_INDICATOR_PTR field determines whether to set the field represented by SQL_DESC_OCTET_LENGTH_PTR. If the column value is NULL, the driver sets the indicator variable to SQL_NULL_DATA. The field represented by SQL_DESC_OCTET_LENGTH_PTR is not set at that moment. If NULL is not returned while retrieving the data, the buffer represented by SQL_DESC_INDICATOR_PTR is set to 0, and the buffer represented by SQL_DESC_OCTET_LENGTH_PTR sets the data length.

If SQL_DESC_INDICATOR_PTR field of APD is NULL pointer, the application can not use the descriptor record to specify the argument as NULL.

This field is the deferred field. It is not used when the driver is set, but it is used when the driver displays NULL possibility (ARD) or determines the NULL possibility (APD).

SQL_DESC_LABEL[IRDs]

This read-only SQLCHAR* record field includes the label or mark of a column. If the column does not have label, then the variable contains the column name. If the column is not named or it can not use the label, then the variable includes an empty string.

SQL_DESC_LENGTH[All]

This SQLULEN record field is the maximum length or actual length of the string or binary data in bytes. It is the maximum length of fixed length data type data or the actual length of variable length data type data. The value always excludes NULL termination character at the end of string. If its data type is SQL_TYPE_DATE, SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP or SQL interval data type, the field has the string length of when datetime or interval value is rewritten to string.

The field value is different from the value of "length" defined in ODBC 2.x.

SQL_DESC_LITERAL_PREFIX[IRDs]

This read-only SQLCHAR* record field contains characters or the characters recognized as a prefix by the driver. This variable holds an empty string for data types to which a character prefix cannot be applied.

SQL_DESC_LITERAL_SUFFIX[IRDs]

This read-only SQLCHAR* record field contains characters or the characters recognized as a suffix by the driver. This variable holds an empty string for data types to which a character suffix cannot be applied.

SQL_DESC_LOCAL_TYPE_NAME[Implementation descriptors]

This read-only SQLCHAR* record field includes the localized name in the data type, and it could be different from the regular name in the data type. If the localized name does not exist, an empty string is returned. The field is only for the purpose of displaying.

SQL_DESC_NAME[Implementation descriptor]

This SQLCHAR* record field includes the field alias in the row descriptor. If the column alias is not applied, the column name is returned. In some cases, the driver sets SQL DESC_UNNAMED field to SQL_NAMED when setting SQL_DESC_NAME field. If neither column name nor column alias existm the driver returns the empty string of SQL_DESC_NAME field, and sets SQL_DESC_UNNAME field to SQL_UNNAMED.

The application can set SQL_DESC_NAME field of IPD for an alias to specify the parameter name or stored named procedure parameter. SQL_DESC_NAME field of IRD is the read-only field. If the application tries to set this field, SQLSTATE HY091 (invalid descriptor field identifier) will be returned.

For IPD, if the driver does not support the named parameter, the field is not defined. If the driver supports the named parameter and specifies the parameter, the name of the parameter is returned to this field.

SQL_DESC_NULLABLE[Implementation descriptors]

For IRD, this read-only SQLSMALLINT record field is SQL_NULLABLE if the column have NULL, otherwise it is SQL_NO_NULLS. If it is unknown whether the column allows NULL, it is SQL_NULLABLE_UNKNOWN. The field exists particularly for the column in the result set.

For IPD, The field is always set to SQL_NULLABLE because the dynamic parameter can always be NULL and it can not set by the application.

SQL_DESC_NUM_PREC_RADIX[All]

This SQLINTEGER field has the value of 2 when SQL_DESC_TYPE field is the approximate numeric data type. It is because SQL_DESC_PRECISION field includes the number of bits. The field has the value of 10 when SQL_DESC_TYPE field is the exact numeric data type and SQL_DESC_PRECISION contains the number of decimal places. The field sets 0 for a non-numeric data types.

SQL_DESC_OCTET_LENGTH[All]

This SQLLEN record field includes the length in bytes of a string or binary data type. For the fixed length character or binary data, it is the actual length in bytes. For the variable length character or binary data, it is the maximum length in bytes. The value does not include the empty string of NULL termination character for the implement descriptor, and it includes the empty string of NULL termination character for the application descriptor. For the application data, the field contains the buffer size. For APD, the field is defined only for the output or input/output parameters.

SQL_DESC_OCTET_LENGTH_PTR[Application descriptors]

The SQLLEN* record field points to the variable including the total length in bytes of the dynamic argument(parameter descriptor) or the binding column values (row descriptor).

This value is ignored for all arguments excluding the string or binary data in APD. If the field is SQL_NTS, the dynamic argument should be terminated with NULL. The application sets the variable including the macro result of SQL_DATA_AT_EXEC or SQL_LEN_DATA_AT_EXEC in this record field of APD to indicate that the bound parameter is to be a data parameter at the run-time. If one or more fields exist, SQL_DESC_DATA_PTR can be set as the value which identifies the parameter required by the application, and which is helpful to determine the parameter.

If OCTET_LENGTH_PTR field of ARD is NULL pointer, the driver does not return the column length. If SQL_DESC_OCTET_LENGTH_PTR of APD is NULL pointer, the driver assumes that the string and binary values are terminated by NULL. (Binary values should not be terminated by NULL, but the length should be given to avoid data interruption.)

If SQLFetch or SQLFetchScroll which fills the buffer pointed by the field does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the buffer content is not defined. It is the deferred field. The field is not immediately used, but it is used later when the driver displays or determines the data length in octet.

SQL_DESC_PARAMETER_TYPE[IPDs]

This SQLSMALLINT record field sets the input parameter to SQL_PARAM_INPUT, the input/output parameter to SQL_PARAM_INPUT_OUTPUT, the output parameter to SQL_PARAM_OUTPUT, the stream input/output parameter to SQL_PARAM_INPUT_OUTPUT_STREAM or the stream output parameter to SQL_PARAM_OUTPUT_STREAM. It is set to SQL_PARAM_INPUT by default.

SQL_DESC_PRECISION[All]

This SQLSMALLINT record field includes the number of valid integers for the exact numeric data types, and it includes the number of bits in mantissa (the binary precision) for the approximate numeric data types. Or, it contains the number of valid integer of the fractional seconds parts of the SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP, or SQL_INTERVAL_SECOND data type. The field is not defined for all other data types.

The field value is different from the "precision" value defined in ODBC 2.x.

SQL_DESC_ROWVER[Implementation descriptors]

This SQLSMALLINT record field indicates whether the column is automatically updated by DBMS when the row is updated(for example, "timestamp" in SQL Server). The record field value is set to SQL_TRUE for the row versioning column, otherwise it is set to SQL_FALSE. The column attribute is similar to setting and calling SQL_ROWVER in IdentifierType argument of SQLSpecialColumn to determine whether to automatically update column.

SQL_DESC_SCALE[All]

This SQLSMALLINT record field includes the number of decimal places defined in the decimal and numeric data types. The field is not defined for all other data types.

The field value is different from the "scale" value defined in ODBC 2.x.

SQL_DESC_SCHEMA_NAME[IRDs]

This read-only SQLCHAR* record field includes the schema name of the base table that includes the column. If the column is an expression or a part of the view, the return value is dependent on the driver. If the data source does not support the schema or can not identify the schema name, then this variable contains an empty string.

SQL_DESC_SEARCHABLE[IRDs]

This read-only SQLSMALLINT record field sets one of the following values.

• If the column can not be used in WHERE clause, it is SQL_PRED_NONE. (It is as same as SQL_UNSEARCHABLE in ODBC 2.X.)

• If the column can only be used with LIKE predicate in WHERE clause, it is SQL_PRED_CHAR.

• If the column can be used with all comparison operators except for LIKE in WHERE clause, it is SQL_PRED_BASIC. (It is as same as the value of SQL_EXCEPT_LIKE in ODBC 2.x.)

• If the column can be used with any comparison operator in WHERE clause, it is SQL_PRED_SEARCHABLE.

SQL_DESC_TABLE_NAME[IRDs]

This read-only SQLCHAR* record field includes the base table name which includes column. The return value is dependent on the driver if the column is an expression or a part of the view.

SQL_DESC_TYPE[All]

This SQLSMALLINT record field includes an abbreviated SQL data types or C data types for all data types except for the interval and datetime data types. The field specifies SQL_DATETIME or SQL_INTERVAL for datetime and interval data types.

Whenever this field includes SQL_DATETIME or SQL_INTERVAL, SQL_DESC_DATETIME_INTERVAL_CODE field should include the appropriate subcode in the implied format. For datetime data types, SQL_DESC_TYPE includes SQL_DATETIME, and SQL_DESC_DATETIME_INTERVAL_CODE field includes the subcode that specifies the datetime data type. For interval data types, SQL_DESC_TYPE includes SQL_INTERVAL, and SQL_DESC_DATETIME_INTERVAL_CODE field includes the subcode that specifies the interval data type.

The values of SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE field are interdependent. When one of the fields is set, the other field should also be set. SQL_DESC_TYPE can be set by calling SQLSetDescField or SQLSetDescRec. SQL_DESC_CONCISE_TYPE can be set by calling SQLBindCol, SQLBindParameter or SQLSetDescField.

If SQL_DESC_TYPE is set to the implied data type other than the interval or datetime data type, SQL_DESC_CONCISE_TYPE field is set to the same value and SQL_DESC_DATETIME_INTERVAL_CODE is set to 0.

If SQL_DESC_TYPE is set to the long data type of datetime or interval data type (SQL_DATETIME or SQL_INTERVAL), SQL_DESC_DATETIME_INTERVAL_CODE field is set to the approprieate subcode, SQL_DESC_CONCISE_TYPE may be set to a value corresponding to the implied data type. If SQL_DESC_TYPE is set to one of the implied datetime or interval data type, SQLSTATE HY021 (Inconsistent descriptor information) is returned.

When SQL_DESC_TYPE is set by calling SQLBindCol, SQLBindParameter or SQLSetDescField, the next fields are set to the default values in the following table. The values of remaining fields in the same record are not defined.

When the application sets the descriptor field by calling not SQLSetDescRec but SQLSetDescField, the application should define the data type firstly. Therefore other fields in the previous table are implicitly set. If it is not allowed to implicitly set any value, the application explicitly sets the value by calling SQLSetDescField or SQLSetDescRec.

SQL_DESC_TYPE_NAME[Implementation descriptors]

This read-only SQLCHAR* record field includes the data source dependent type name ("CHAR", "VARCHAR", etc.). If the data type name is unknown, the variable includes an empty string.

SQL_DESC_UNNAMED[Implementation descriptors]

If SQL_DESC_NAME field is set, the SQLSMALLINT record field in the row descriptor is set to one of SQL_NAME or SQL_UNNAMED by the driver. If SQL_DESC_NAME field does not include the column alias or if the column alias does not apply, the driver sets SQL_DESC_UNNAMED field to SQL_NAMED. If the application sets SQL_DESC_NAME field of IPD to the parameter name or alias, the driver sets SQL_DESC_UNNAMED field of IPD to SQL_NAMED. If neither column name nor does alias exist, the driver sets SQL_DESC_UNNAMED field of IPD to SQL_UNNAMED.

The application sets SQL_DESC_UNNAMED field of IPD to SQL_UNNAMED. If the application tries to set SQL_DESC_UNANMED field of IPD to SQL_NAMED, the driver returns SQLSTATE HY091 (Invalid descriptor field identifier). SQLSTATE HY091 (Invalid descriptor field identifier) is returned if the read-only application tries to set SQL_DESC_UNNAMED field of IRD.

SQL_DESC_UNSIGNED[Implemetation descriptors]

This read-only SQLSMALLINT record field is set to SQL_TRUE, if the column type is the unsigned or non-numeric data type. It is set to SQL_FALSE, if the column type is the signed data type.

SQL_DESC_UPDATABLE[IRDs]

The read-only SQLSMALLINT record field is set to one of the following values.

• If the result set column is read-only, it is SQL_ATTR_READ_ONLY.

• If the result set column is read and write, it is SQL_ATTR_WRITE.

• If the updatability for the result set column is unknown, it is SQL_ATTR_READWRITE_UNKNOWN.

SQL_DESC_UPDATABLE explains the updatability of the result set column (the column is not in the base table). The updatabilty of the column in the primary table which is a base of the result set columns may be different from the value in this field. The updatability is based on the data type, the user privileges and the result set definition. If it is not sure that the column can be updated, SQL_ATTR_READWITE_UNKNOWN should be returned.

SQL_DESC_CHAR_LENGTH_UNITS[Implemetation descriptors]

This SQLSMALLINT record field indicates the length units of the column whose SQL types are SQL_CHAR, SQL_VARCHAR and SQL_LONGVARCHAR.

• SQL_CLU_CHARACTERS: The length unit is CHARACTER. For example, the length of data "문자열" is 3 if the encoding method is UHC(Unified Hangul Code).

• SQL_CLU_OCTETS: The length unit is OCTETS. For example, the length of data "문자열" is 6 if the encoding method is UHC(Unified Hangul Code).

• SQL_CLU_NONE: The length unit is undefined. It is the value which is returned for the SQL types excluding the SQL types listed above.

SQLGetDescRec

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLGetDescRec returns the current value or setting for the multiple fields of the descriptor record. The returned field describes the name, the data type, the column size or the argument data.

Syntax

SQLRETURN SQLGetDescRec(
    SQLHDESC        DescriptorHandle,
    SQLSMALLINT     RecNumber,
    SQLCHAR *       Name,
    SQLSMALLINT     BufferLength,
    SQLSMALLINT *   StringLengthPtr,
    SQLSMALLINT *   TypePtr,
    SQLSMALLINT *   SubTypePtr,
    SQLLEN *        LengthPtr,
    SQLSMALLINT *   PrecisionPtr,
    SQLSMALLINT *   ScalePtr,
    SQLSMALLINT *   NullablePtr);

Arguments

DescriptorHandle

[Input] It is the descriptor handle.

RecNumber

[Input] It is the descriptor record of the information which the application wants to retrieve. The record number 0 is set as the bookmark record and the descriptor record starts from 1. If RecNumber is equal to or smaller than SQL_DESC_COUNT, but the data for the column or parameter is not included in the row, SQLGetDescField returns the default value of the field.

Name

[Output] It is the buffer pointer which returns SQL_DESC_NAME for the descriptor record. If Name is NULL, StringLengthPtr returns the buffer length of Name (including null-termination character).

BufferLength

[Input] It is the length of name buffer.

StringLengthPtr

[Output] It is the pointer which returns the number of characters returnable in *Name buffer excluding null-termination character. If the number of characters are equal to or bigger than BufferLength, the data of *Name is truncated to BufferLength minus the length of a null-termination character, and is null-terminated.

TypePtr

[Output] It is the buffer pointer which returns the value of SQL_DESC_TYPE for the descriptor field.

SubTypePtr

[Output] It is the buffer pointer which returns the value of SQL_DESC_DATETIME_INTERVAL_CODE field for the record of SQL_DATETIME or SQL_INTERAVL type.

LengthPtr

[Output] It is the buffer pointer which returns the value of SQL_DESC_OCTET_LENGTH field for the descriptor field.

PrecisionPtr

[Output] It is the pointer which returns the value of SQL_DESC_PRECISION field for the descriptor record.

ScalePtr

[Output] It is the pointer which returns the value of SQL_DESC_SCALE field for the descriptor record.

NullablePtr

[Output] It is the pointer which returns the value of SQL_DESC_NULLABLE field for the descriptor record.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_NO_DATA, SQL_INVALID_HANDLE

If RecNumber is bigger than the number of current descriptor records, SQL_NO_DATA is returned. If DescriptorHandle is the IRD handle and the statement is on the preparation or execution state but related cursor does not exist, then SQL_NO_DATA is returned.

Diagnosis

Description

The application can call SQLGetDescRec to retrieve the following descriptor field value for a column or parameter.

• SQL_DESC_NAME

• SQL_DESC_TYPE

• SQL_DESC_DATETIME_INTERVAL_CODE

• SQL_DESC_OCTET_LENGTH

• SQL_DESC_PRECISION

• SQL_DESC_SCALE

• SQL_DESC_NULLABLE

SQLGetDescRec does not retrieve the header field value.

The application can set the argument corresponding to the null pointer field so that it prevents the return for the field setting.

When the application retrieves the value of the undefined field for the specific descriptor type by calling SQLGetDescRec, the function returns SQL_SUCCESS, but the return value for the field is not defined. For example, if SQLGetDescRec is called for SQL_DESC_NAME field or SQL_DESC_NULLABLE field of APD or ARD, then SQL_SUCCESS is returned but the field value is not defined.

When the application calls SQLGetDescRec to retrieve the value of the field which is defined as the specific descriptor type but is not set as the default, the function returns SQL_SUCCESS, but the field value is not defined. For more information, refer to Initializing Descriptor Field of SQLSetDescField.

Each field value can be retrieved separately by calling SQLGetDescField. For more information about the descriptor header or the fields in the record, refer to SQLSetDescField.

SQLGetDiagField

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLGetDiagField returns the current value of the record fields in the diagnostic data structure which contains an error, warning, and status information.

Syntax

SQLRETURN SQLGetDiagField(
    SQLSMALLINT     HandleType,
    SQLHANDLE       Handle,
    SQLSMALLINT     RecNumber,
    SQLSMALLINT     DiagIdentifier,
    SQLPOINTER      DiagInfoPtr,
    SQLSMALLINT     BufferLength,
    SQLSMALLINT *   StringLengthPtr);

Arguments

HandleType

[Input] It is the handle type identifier which requires diagnostics. The identifier should be one of the followings.

• SQL_HANDLE_DBC

• SQL_HANDLE_DESC

• SQL_HANDLE_ENV

• SQL_HANDLE_STMT

Handle

[Input] It is the handle for the diagnostic data structure, of the type pointed by HandleType. If HandleType is SQL_HANDLE_ENV, the Handle can be the shared or non-shared environment handle.

RecNumber

[Input] It indicates the state record of the information found by the application. The status record starts from 1. If DiagIdentifier points any field of the diagnostic header, RecNumber is ignored. Otherwise, RecNumber should be bigger than 0.

DiagIdentifier

[Input] It refers to the diagnostic field whose value is returned. For more information, refer to DiagIdentifier Argument in the Description section.

DiagInfoPtr

[Output] It is the buffer pointer to which return the diagnostic information. The data type depnends on the value of DiagIdentifier. If DiagInfoPtr is an integer type, the application should use the SQLULEN buffer and initialize it to 0 before calling the function because some drivers record only the lower 32-bits or 16-bits of the buffer and leave the upper bits intact. If DiagInfoPtr is NULL, StringLengthPtr returns the total number of returnable bytes excluding null-termination character in the buffer pointed by DiagInfoPtr.

BufferLength

[Input] If DiagIdentifier is the ODBC defined diagnosis, and DiagInfoPtr points to the string or binary buffer, the argument should be the length of DiagInfoPtr. If DiagIdentifier is the ODBC defined diagnosis field, and DiagInfoPtr is a number, BufferLength is ignored. If the value of DiagInfoPtr is the Unicode string (When calling SQLGetDiagFieldW), BufferLength should be an even number. If DiagIdentifier is a driver defined field, the application refers to the field characteristic to the driver manager by setting BufferLength argument. BufferLength can have the following values.

• If DiagInfoPtr is a string buffer pointer, BufferLength is the string length or SQL_NTS.

• If DiagInfoPtr is a binary buffer pointer, the application stores the macro result of SQL_LENG_BINARY_ATTR(length) in BufferLength. A negative number is stored in BufferLength.

• If DiagInfoPtr is not a string or binary buffer pointer, BufferLength should have the value of SQL_IS_POINTER.

• If DiagInfoPtr is the fixed length data type, BufferLength is one of SQL_IS_INTEGER, SQL_IS_UINTEGER, SQL_IS_SMALLINT, or SQL_IS_USMALLINT.

StringLengthPtr

[Output] It is the buffer pointer which returns the total length of bytes excluding the null-termination character which is returned to *DiagInfoPtr for the character data. If the returnable length in bytes is equal to or bigger than BufferLength, the text in *DiagInfoPtr is truncated to the length of BufferLength minus null-termination character.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE, SQL_NO_DATA

Diagnosis

SQLGetDiagField does not post the diagnostic record. The result of the return value is used as follows.

• SQL_SUCCESS: The function successfully returns the diagnosis information.

• SQL_SUCCESS_WITH_INFO: The data in the diagnostic field is truncated because *DiagInfoPtr is too small to store the requested diagnosis field. The application should compare BufferLength to the actual byte number stored in *StringLengthPtr to check whether the truncation occurred.

• SQL_INVALID_HANDLE: The handle is not valid for a type indicating HandleType.

• SQL_ERROR: One of the followings occurs.

  • DiagIdentifier argument does not have the valid value.

  • DiagIdentifier argument is one of SQL_DIAG_CURSOR_ROW_COUNT, SQL_DIAG_DYNAMIC_FUNCTION, SQL_DIAG_DYNAMIC_FUNCTION_CODE, SQL_DIAG_ROW_COUNT , and Handle is not the statement handle.

  • When DiagIdentifier refers to the diagnosis record field, RecNumber is a negative number or 0. RecNumber is ignored for the header field.

  • The requested value is a string, and BufferLength is smaller than 0.

  • If an asynchronous notification is used, the asynchronous execution of handle is not completed.

• SQL_NO_DATA: RecNumber is bigger than the number of diagnosis records for the handle specified in Handle. If the diagnosis record for handle does not exist, then the function returns SQL_NO_DATA for RecNumber of any positive number.

Description

The application generally calls SQLGetDiagField for one of the three purposes.

• To obtain specific error or warning information when a function returns SQL_ERROR or SQL_SUCCESS_WITH_INFO (SQLBrowseConnect function returns SQL_NEED_DATA.)

• To obtain the number of rows in the data source when insert, delete, or update operations were performed with a call to SQLExecute, SQLExecDirect, SQLBulkOperations or SQLSetPos. Or, to obtain the number of rows which exist in the current open cursor, if the driver can provide this information.

• To obtain the information of which function is executed by a calling SQLExecDirect or SQLExecute

Whenever the function is called, all ODBC functions can post 0 or more diagnosis records. Therefore, the application can call SQLGetDiagField after calling all functions. There is no limitation on the number of diagnosis records which can be stored at a time. SQLGetDiagField retrieves the diagnosis data structure specified in Handle and the most recent related diagnosis information. If the application calls another ODBC function instead of SQLGetDiagField or SQLGetDiagRec, diagnostic information obtained by the previous call of the same handle can be lost.

As long as SQLGetDiagField returns SQL_SUCCESS, the application can read the diagnosis record by increasing RecNumber. The number of state records is displayed on the header field of SQL_DIAG_NUMBER. Calling SQLGetDiagField does not affect the header and record field. As long as a function except for the diagnostic function is not called, the application can retrieve a record field by calling SQLGetDiagField again using the same handle.

The application can call SQLGetDiagField anytime to return any diagnostic fields. If Handle is not the statement handle, SQL_DIAG_CURSOR_ROW_COUNT or SQL_DIAG_ROW_COUNT is excluded because it returns SQL_ERROR. If any diagnosis field is not defined, calling SQLGetDiagField returns SQL_SUCCESS and the undefined value.

API call other than the function executed asynchronously causes HY010 (Function sequence error). However, the error code is not retrieved before the asynchronous processing is completed.

HandleType Argument

Each handle type has the related diagnosis information, and HandleType argument refers to the handle type.

Some headers and record fields are not returned for the environment, connection, statement, descriptor handles. The handles which are not applicable are described in the following Header Fields and Record Fields tables.

If HandleType is SQL_HANDLE_ENV, the handle can be the shared or non-shared environment handle.
A specific header diagnosis field of the driver is not related to the environment handle.
The diagnosis header defined for the descriptor handle is only SQL_DIAG_NUMBER and SQL_DIAG_RETURNCODE.

DiagIdentifier Argument

The argument is the field identifier required in the diagnosis data structure. If RecNumber is equal to or bigger than 1, the data in the field is the diagnostic information returned by the function. If RecNumber is 0, the field is in the header of the diagnostic data structure, and it has the data related to the function call returning the diagnostic information, not the specific information.

The driver may define a driver-specific header and record fields in the diagnosis data structure.

ODBC 3.x application which uses ODBC 2.x driver can call SQLGetDiagField when DiagIdentifier argument is SQL_DIAG_CLASS_ORIGIN, SQL_DIAG_CLASS_SUBCLASS_ORIGIN, SQL_DIAG_CONNECTION_NAME, SQL_DIAG_MESSAGE_TEXT, SQL_DIAG_NATIVE, SQL_DIAG_NUMBER, SQL_DIAG_RETURNCODE, SQL_DIAG_SERVER_NAME, SQL_DIAG_SQLSTATE, and it returns SQL_ERROR for other diagnostic fields.

Header Field

Record Field

Dynamic Function Field Value

Sequence of Status Record

The state record is sequentially located according to the row number and diagnostic type. The driver manager determines the final step of returning the created state record.

If the diagnostic record is posted by the driver manager and the driver, the driver manager is responsible for determining the order of the diagnostic records.

If two or more state records exist, the order of the record is determined first by the row number. The following rules are applied to determine the order of the diagnostic record by the row.

• The record not corresponding to any row because SQL_NO_ROW_NUMBER is defined as -1, is positioned in front of the record corresponding to the particular row.

• The record which does not know the row number because SQL_ROW_NUMBER_UNKNOWN is defined as -2 is positioned in front of all the other records.

• For all record which are related to a particular row, the records are sorted by the value of SQL_DIAG ROW_NUMBER. All errors and warnings for the first row are listed, and then all the errors and warnings for the next row are sequentially listed.

Within each row, or for all rows which do not correspond to the row or which do not know the row number or which have the row number as same as SQL_NO_ROW_NUMBER, the first record listed is determined by using the set of ordering rules. After the first record, the order of other records which affect the row is not defined. The application can not assume that an error precedes a warning after the first record. The application should check the complete diagnostic data structure to get the complete information about the failed function call.

The following rules are used to determine the first record in the row. The highest grade record is the first record. The record source (driver manager, driver, gateway, etc.) does not affect the determination of the record ranks.

• Errors state record describing the error has the highest grade. The following rules are applied to align the errors.

  • The record which refers to the transaction failure or possible transaction failure has a higher grade than any other record.

  • If two or more records describe the same error condition, SQLSTATE defined in the Open Group CLI specifications(Class 03 ~ HZ) has a higher grade than SQLSTATE defined in ODBC and the driver.

• Implementation-defined No Data Values state record which describes No Data values (class 02) defined by the driver has the second highest rank.

• Warnings state record(class 01) describing the warning has the lowest grade. If two or more records describe the same error condition, SQLSTATE defined in the Open Group CLI specifications has a higher grade than SQLSTATE defined in ODBC and the driver.

SQLGetDiagRec

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLGetDiagRec returns the current value of the record field of the diagnostic data source (related to the specified handle) which contains an error, warning, state information.

Syntax

SQLRETURN SQLGetDiagRec(
    SQLSMALLINT     HandleType,
    SQLHANDLE       Handle,
    SQLSMALLINT     RecNumber,
    SQLCHAR *       SQLState,  
    SQLINTEGER *    NativeErrorPtr,  
    SQLCHAR *       MessageText,  
    SQLSMALLINT     BufferLength,  
    SQLSMALLINT *   TextLengthPtr);

Arguments

HandleType

[Input] It is the handle type identifier of which diagnosis is required. The identifier should be one of the followings.

• SQL_HANDLE_DBC

• SQL_HANDLE_DESC

• SQL_HANDLE_ENV

• SQL_HANDLE_STMT

Handle

[Input] It is the handle for the diagnosis data structure of the type pointed by HandleType. If HandleType is SQL_HANDLE_ENV, the handle can be the shared or non-shared environment handle.

RecNumber

[Input] It indicates the state record of the information found by the application. The number of the state record starts from 1.

SQLState

[Output] It is the buffer pointer which returns five character SQLSTATE code for the diagnostic record RecNumber. The first two characters indicate a class and the next three characters indicate a subclass. The information is in the SQL_DIAG_SQLSTATE diagnostic field.

NativeErrorPtr

[Output] It is the buffer pointer which returns the specific native error code to the data source. The information is in the diagnostic field SQL_DIAG_NATIVE.

MessageText

[Output] It is the buffer point which returns the diagnostic message text string. The information is in the SQL_DIAG_MESSAGE_TEXT diagnostic field.

If MessageText is NULL, TextLegnthPtr returns the total number of returnable characters (excluding null-termination character) to the buffer pointed by MessageText.

BufferLength

[Input] It is the length of characters within *MessageText buffer. Diagnostic message text does not have the maximum length.

TextLegnthPtr

[Output] It is the buffer pointer to which the total number of characters returnable to *MessageText is returned (excluding null-termination character). If the number of returnable characters is bigger than BufferLength, the diagnostic message text of *MessageText is truncated to the length of BufferLength minus null-termination character.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

SQLGetDiagRec does not post the diagnostic record. It uses the result of the return value as follows.

• SQL_SUCCESS: The function successfully returns the diagnosis information.

• SQL_SUCCESS_WITH_INFO: *MessageText buffer is too small to store the requested diagnosis message. The diagnostic record is not generated. The application should compare BufferLength to the actual byte number stored in *StringLengthPtr to check whether the truncation occurred.

• SQL_INVALID_HANDLE: The handle is not valid for a type indicating HandleType.

• SQL_ERROR: One of the followings occurs.

  • RecNumber argument is the negative number or 0.

  • BufferLength is smaller than 0.

  • If an asynchronous notification is used, the asynchronous execution of handle is not completed.

• SQL_NO_DATA: RecNumber is bigger than the number of diagnosis records for the handle specified in Handle. If the diagnosis record for handle does not exist, then the function returns SQL_NO_DATA for RecNumber of any positive number.

Description

When the ODBC function returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, generally the application calls SQLGetDiagRec. However, the application can call the SQLGetDiagRec after any function is called because any ODBC function can post zero or more diagnostic records. The application can call SQLGetDiagRec several times to return some or all records in the diagnostic data structure. ODBC does not have limit on the number of diagnostic records which can be stored at a time.

SQLGetDiagRec can not be used to return the header field of the diagnostic data structure.(RecNumber should be bigger than 0.) The application should call SQLGetDiagField instead of SQLGetDiagRec.

SQLGetDiagRec retrieves only the handle specified to Handle and the latest related diagnostic information. If the application calls any other ODBC function (excluding SQLGetDiagRec, SQLGetDiagField, SQLError), diagnostic information from the previous call of the same handle is lost.

The application can repeatedly retrieve the diagnostic record by increasing RecNumber as long as SQLGetDiagRec returns SQL_SUCCESS. SQLGetDiagRec call do not affect the header and record fields. If intervention of other function does not exist except for SQLGetDiagRec, SQLGetDiagField, SQLError, then the applicatin can call SQLGetDiagRec again to retrieve the field in the record. The application can retrieve the total number of diagnostic records available by calling SQLGetDiagField to retrieve the value of the SQL_DIAG_NUMBER field, and calling SQLGetDiagRec several times.

HandleType Argument

Each handle type has the related diagnosis information, and HandleType argument refers to the Handle type.

Some header and record fields are not returned for the environment, connection, statement, descriptor handles. In the header field and record field table of SQLGetDiagField, the inappropriate handles in the field are described.

If HandleType is SQL_HANDLE_SENV which indicates the shared environment handle, then calling SQLGetDiagRec returns SQL_INVALID_HANLDE. If HandleType is SQL_HANDLE_ENV, then the handle can be the shared or non-shared environment handle.

SQLGetEnvAttr

Conformance

Introduced version: ODBC 3.0
Standards compliance: ISO 92

Overview

SQLGetEnvAttr returns the current settings of the environment attributes.

Syntax

SQLRETURN SQLGetEnvAttr(
    SQLHENV        EnvironmentHandle,
    SQLINTEGER     Attribute,
    SQLPOINTER     ValuePtr,
    SQLINTEGER     BufferLength,
    SQLINTEGER *   StringLengthPtr);

Arguments

EnvironmentHandle

[Input] It is the environment handle.

Attribute

[Input] It is the attribute to be retrieved.

ValuePtr

[Output] It is the buffer pointer in which to return the current attribute value specified by Attribute. If ValuePtr is NULL, StringLengthPtr returns the total number of bytes returnable to the buffer pointed by ValuePtr (excluding null-termination character).

BufferLength

[Input] If ValuePtr points to a string, the argument should be the length of *ValuePtr. If ValuePtr is an integer, BufferLength is ignored. If *ValuePtr(when SQLGetEnvAttrW is called) is a unicode string, BufferLength should be an even number. If the attribute value is not a string, BufferLength is not used.

StringLengthPtr

[Output] It is the buffer pointer to which the total number of bytes to be returned to *ValuePtr (excluding null-termination character) is returned. If ValuePtr is the null pointer, the length is not returned. If the attribute value is a string and the number of bytes returnable is equal to or bigger than BufferLength, *ValuePtr is truncated to the length of BufferLength minus the length of a null-termination character and is a null-terminated by the driver.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA, SQL_ERROR, SQL_INVALID_HANDLE

Diagnosis

Description

The driver-specific environment attributes does not exist. If Attribute specifies the attribute which returns a string, ValuePtr should be the buffer pointer whose string is returned. The maximum string length including the null-termination character is the BufferLength in bytes.

SQLGetEnvAttr can be called at any point between the allocation and release of the environment handle. All environment attributes successfully allocated by the application for the environment are maintained until SQLFreeHandle is called with Handletype of SQL_HANDLE_ENV in EnvrionmentHandle. It is recommended to use only one environment handle.

The following table describes attribute list which can be queried through SQLGetEnvAttr.

SQLGetFunctions

Conformance