How to allocate handles and connect to SQL Server (ODBC)

To allocate handles and connect to SQL Server

1. Include the ODBC header files Sql.h, Sqlext.h, Sqltypes.h.
2. Include the Microsoft® SQL Server™ driver-specific header file, Odbcss.h.
3. Call SQLAllocHandle with a HandleType of SQL_HANDLE_ENV to initialize ODBC and allocate an environment handle.
4. Call SQLSetEnvAttr with Attribute set to SQL_ATTR_ODBC_VERSION and ValuePtr set to SQL_OV_ODBC3 to indicate the application will use ODBC 3.x-format function calls.
5. Optionally, call SQLSetEnvAttr to set other environment options or SQLGetEnvAttr to get environment options.
6. Call SQLAllocHandle with a HandleType of SQL_HANDLE_DBC to allocate a connection handle.
7. Optionally, call SQLSetConnectAttr to set connection options or SQLGetConnectAttr to get connection options.
8. Call SQLConnect to use an existing data source to connect to SQL Server.

   Or

   Call SQLDriverConnect to use a connection string to connect to SQL Server.

   A minimum complete SQL Server connection string has one of two forms:

   DSN=dsn_name;UID=login_id;PWD=password;

   DRIVER={SQL Server};SERVER=server;UID=login_id;PWD=password;

   If the connection string is not complete, SQLDriverConnect can prompt for the required information. This is controlled by the value specified for the DriverCompletion parameter.

   Or

   Call SQLBrowseConnect multiple times in an iterative fashion to build the connection string and connect to SQL Server.

9. Optionally, call SQLGetInfo to get driver attributes and behavior for the SQL Server data source.
10. Allocate and use statements.
11. Call SQLDisconnect to disconnect from SQL Server and make the connection handle available for a new connection.
12. Call SQLFreeHandle with a HandleType of SQL_HANDLE_DBC to free the connection handle.
13. Call SQLFreeHandle with a HandleType of SQL_HANDLE_ENV to free the environment handle.

Examples

A. Allocate handles, then connect by using SQLConnect

This example shows allocating an environment handle and a connection handle, then connecting by using SQLConnect. It has been simplified by removing much of the error checking.

#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <odbcss.h>

SQLHENV        henv = SQL_NULL_HENV;
SQLHDBC        hdbc1 = SQL_NULL_HDBC;
SQLHSTMT        hstmt1 = SQL_NULL_HSTMT;

int main() {
    RETCODE retcode;

    UCHAR    szDSN[SQL_MAX_DSN_LENGTH+1] = "MyDSN",
            szUID[MAXNAME] = "sa",
            szAuthStr[MAXNAME] = "MyPassword";


     // Allocate the ODBC Environment and save handle.
    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);


    // Let ODBC know this is an ODBC 3.0 application.
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                            (SQLPOINTER)SQL_OV_ODBC3,
                            SQL_IS_INTEGER);


    // Allocate an ODBC connection handle and connect.
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

    retcode = SQLConnect(hdbc1, szDSN, (SWORD)strlen(szDSN),
                        szUID, (SWORD)strlen(szUID),
                        szAuthStr, (SWORD)strlen(szAuthStr));
    if ( (retcode != SQL_SUCCESS) &&
        (retcode != SQL_SUCCESS_WITH_INFO) ) {
           // Connect failed, call SQLGetDiagRec for errors.
    }
    else {
        // Connects to SQL Server always return
        // informational messages.  These messages can be
        // retrieved by calling SQLGetDiagRec.
    }


    // Allocate statement handles and do ODBC processing.


    /* Clean up. */
    SQLDisconnect(hdbc1);
    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
    return(0);
}

B. Connect to SQL Server without an existing ODBC data source

This example shows a call to SQLDriverConnect to connect to a server running SQL Server without requiring an existing ODBC data source:

#define MAXBUFLEN    255

SQLHENV        henv = SQL_NULL_HENV;
SQLHDBC        hdbc1 = SQL_NULL_HDBC;
SQLHSTMT        hstmt1 = SQL_NULL_HSTMT;

SQLCHAR        ConnStrIn[MAXBUFLEN] =
            "DRIVER={SQL Server};SERVER=MyServer;"
            "UID=sa;PWD=MyPassWord;DATABASE=pubs;";

SQLCHAR        ConnStrOut[MAXBUFLEN];
SQLSMALLINT    cbConnStrOut = 0;

// Make connection without data source. Ask that driver not
// prompt if insufficient information. Driver returns
// SQL_ERROR and application prompts user
// for missing information. Window handle not needed for
// SQL_DRIVER_NOPROMPT.
retcode = SQLDriverConnect(hdbc1,        // Connection handle
                        NULL,            // Window handle
                        ConnStrIn,        // Input connect string
                        SQL_NTS,            // Null-terminated string
                        ConnStrOut,        // Address of output buffer
                        MAXBUFLEN,        // Size of output buffer
                        &cbConnStrOut,    // Address of output length
                        SQL_DRIVER_NOPROMPT);

See Also