SQLConnect() - Connect to a data source

SQLConnect() establishes a connection to the target database. The application must supply a target SQL database. You must use SQLAllocHandle() to allocate a connection handle before you can call SQLConnect(). Subsequently, you must call SQLConnect() before you allocate a statement handle.

ODBC specifications for SQLConnect()

Table 1. SQLConnect() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
1.0 Yes Yes

Syntax

SQLRETURN   SQLConnect       (SQLHDBC           hdbc,
                              SQLCHAR     FAR   *szDSN,
                              SQLSMALLINT       cbDSN,
                              SQLCHAR     FAR   *szUID,
                              SQLSMALLINT       cbUID,
                              SQLCHAR     FAR   *szAuthStr,
                              SQLSMALLINT       cbAuthStr);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. SQLConnect() arguments
Data type Argument Use Description
SQLHDBC hdbc input Specifies the connection handle for the connection.
SQLCHAR * szDSN input Specifies the data source: the name or alias name of the database to which you are connecting.
SQLSMALLINT cbDSN input Specifies the length , in bytes, of the contents of the szDSN argument.
SQLCHAR * szUID input Specifies an authorization name (user identifier). This parameter is validated and authenticated.
SQLSMALLINT cbUID input Specifies the length, in bytes, of the contents of the szUID argument. This parameter is validated and authenticated.
SQLCHAR * szAuthStr input Specifies an authentication string (password). This parameter is validated and authenticated.
SQLSMALLINT cbAuthStr input Specifies the length, in bytes, of the contents of the szAuthStr argument. This parameter is validated and authenticated.

Usage

The target database (also known as a data source) for IBM® relational database management systems is the location name that is defined in SYSIBM.LOCATIONS when DDF is configured in the Db2 subsystem. Call SQLDataSources() to obtain a list of databases that are available for connections.

In many applications, a local database is accessed (DDF is not being used). In these cases, the local database name is the name that was set during Db2 installation as 'Db2 LOCATION NAME' on the DSNTIPR installation panel for the Db2 subsystem. Your local Db2 administration staff can provide you with this name, or you can use a null connect.

A connection that is established by SQLConnect() recognizes externally created RRS contexts and allows multiple connections to the same data source from different RRS contexts.

Specifying a null connect: With a null connect, you connect to the default local database without supplying a database name.

For a null SQLConnect(), the default connection type is the value of the CONNECTTYPE keyword, which is specified in the common section of the initialization file. To override this default value, specify the SQL_ATTR_CONNECTTYPE attribute by using one of the following functions before you issue the null SQLConnect():
  • SQLSetConnectAttr()
  • SQLSetEnvAttr()
Use the szDSN argument for SQLConnect() as follows:
  • If the szDSN argument pointer is null or the cbDSN argument value is 0, you perform a null connect.
    A null connect, like any connection, requires you to allocate both an environment handle and a connection handle before you make the connection. The reasons you might code a null connect include:
    • Your Db2 ODBC application needs to connect to the default data source. (The default data source is the Db2 subsystem that is specified by the MVSDEFAULTSSID initialization file setting.)
    • Your Db2 ODBC application is mixing embedded SQL and Db2 ODBC calls, and the application connected to a data source before invoking Db2 ODBC.
    • Your Db2 ODBC application runs as a stored procedure. Db2 ODBC applications that run as stored procedures must issue a null connect.
  • If the szDSN argument pointer is not null and the cbDSN argument value is not 0, Db2 ODBC issues a CONNECT statement to the data source.

Specifying length arguments: You can set the input length arguments of SQLConnect() (cbDSN, cbUID, cbAuthStr) either to the actual length (in bytes) of their associated data (which does not include nul-terminating characters), or to SQL_NTS to indicate that the associated data is nul-terminated.

Authenticating a user: To authenticate a user, you must pass SQLConnect() both a user ID (which you specify in the szUID argument) and a password (which you specify in the szAuthStr argument). If you specify a null or empty user ID for the szUID argument, SQLConnect() ignores the szAuthStr argument and uses the primary authorization ID that is associated with the application for authentication. SQLConnect() does not accept the space character in either the szUID or szAuthStr arguments.

Using SQLDriverConnect(): Use the more extensible SQLDriverConnect() function to connect when the application needs to override any or all of the keyword values specified for this data source in the initialization file.

Users can specify various connection characteristics (attributes) in the section of the initialization file associated with the szDSN data source argument. Your application should set connection attributes with SQLSetConnectAttr(). To set additional attributes, call the extended connect function, SQLDriverConnect(). You can also perform a null connect with SQLDriverConnect().

Return codes

After you call SQLConnect(), it returns one of the following values:
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_ERROR
  • SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 3. SQLConnect() SQLSTATEs
SQLSTATE Description Explanation
08001 Unable to connect to data source. This SQLSTATE is returned for one or more of the following reasons:
  • Db2 ODBC is not able to establish a connection with the data source.
  • The connection request is rejected because a connection that was established with embedded SQL already exists.
08002 Connection in use. The specified connection handle is being used to establish a connection with a data source, and that connection is still open.
08004 The application server rejected establishment of the connection. This SQLSTATE is returned for one or more of the following reasons:
  • The data source rejects the establishment of the connection.
  • The number of connections that are specified by the MAXCONN keyword has been reached.
58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY013 Unexpected memory handling error. Db2 ODBC is not able to access the memory that is required to support execution or completion of the function.
HY024 Invalid argument value. A nonmatching double quotation mark (") is found in the szDSN, szUID, or szAuthStr arguments.
HY090 Invalid string or buffer length. This SQLSTATE is returned for one or more of the following reasons:
  • The specified value for the cbDSN argument is less than 0 and is not equal to SQL_NTS, and the szDSN argument is not a null pointer.
  • The specified value for the cbUID argument is less than 0 and is not equal to SQL_NTS, and the szUID argument is not a null pointer.
  • The specified value for the cbAuthStr argument is less than 0 and is not equal to SQL_NTS, and the szAuthStr argument is not a null pointer.
HY501 Invalid data source name. An invalid data source name is specified in the szDSN argument.

Restrictions

The implicit connection (or default database) option for IBM relational database management systems is not supported. SQLConnect() must be called before any SQL statements can be executed.

Example

The following example shows an application that makes a connection to a data source with SQLConnect().
Figure 1. An application that connects to a data source
/* ... */
/* Global variables for user id and password, defined in main module.
   To keep samples simple, not a recommended practice.
   The INIT_UID_PWD macro is used to initialize these variables.
*/
extern    SQLCHAR   server[SQL_MAX_DSN_LENGTH + 1];
/********************************************************************/
SQLRETURN
DBconnect(SQLHENV henv,
          SQLHDBC * hdbc)
{
    SQLRETURN       rc;
    SQLSMALLINT     outlen;
    /* Allocate a connection handle     */
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv, hdbc) != SQL_SUCCESS) {
        printf(">---ERROR while allocating a connection handle-----\n");
        return (SQL_ERROR);
    }
    /* Set AUTOCOMMIT OFF */
    rc = SQLSetConnectAttr(*hdbc, SQL_ATTR_AUTOCOMMIT,(void*) 
                           SQL_AUTOCOMMIT_OFF,SQL_NTS);
    if (rc != SQL_SUCCESS) {
        printf(">---ERROR while setting AUTOCOMMIT OFF ------------\n");
        return (SQL_ERROR);
    }
    rc = SQLConnect(*hdbc, server, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
    if (rc != SQL_SUCCESS) {
        printf(">--- Error while connecting to database: %s -------\n", server);
        SQLDisconnect(*hdbc);
        SQLFreeHandle (SQL_HANDLE_DBC, *hdbc);
        return (SQL_ERROR);
    } else {                    /* Print connection information */
        printf(">Connected to %s\n", server);
    }
    return (SQL_SUCCESS);
}
/********************************************************************/
/* DBconnect2 - Connect with connection type                            */
/* Valid connection types SQL_CONCURRENT_TRANS, SQL_COORDINATED_TRANS  */
/********************************************************************/
SQLRETURN DBconnect2(SQLHENV henv,
           SQLHDBC * hdbc, SQLINTEGER contype)
           SQLHDBC * hdbc, SQLINTEGER contype, SQLINTEGER conphase)
{
    SQLRETURN       rc;
    SQLSMALLINT     outlen;
    /* Allocate a connection handle     */
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv, hdbc) != SQL_SUCCESS) {
        printf(">---ERROR while allocating a connection handle-----\n");
        return (SQL_ERROR);
    }
    /* Set AUTOCOMMIT OFF */
    rc = SQLSetConnectAttr(*hdbc, SQL_ATTR_AUTOCOMMIT,(void*)
                            SQL_AUTOCOMMIT_OFF,SQL_NTS);
    if (rc != SQL_SUCCESS) {
        printf(">---ERROR while setting AUTOCOMMIT OFF ------------\n");
        return (SQL_ERROR);
    }
    rc = SQLSetConnectAttr(hdbc[0], SQL_ATTR_CONNECTTYPE,(void*)contype,SQL_NTS);
    if (rc != SQL_SUCCESS) {
        printf(">---ERROR while setting Connect Type -------------\n");
        return (SQL_ERROR);
    }
    if (contype == SQL_COORDINATED_TRANS ) {
    rc=SQLSetConnectAttr(hdbc[0],SQL_ATTR_SYNC_POINT,(void*)conphase,
SQL_NTS);
        if (rc != SQL_SUCCESS) {
            printf(">---ERROR while setting Syncpoint Phase --------\n");
            return (SQL_ERROR);
        }
    }
    rc = SQLConnect(*hdbc, server, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
    if (rc != SQL_SUCCESS) {
        printf(">--- Error while connecting to database: 
        SQLDisconnect(*hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, *hdbc);
        return (SQL_ERROR);
    } else {                    /* Print connection information */
        printf(">Connected to 
    }
    return (SQL_SUCCESS);
}
/* ... */