C++ SQLDriverConnect API

Question

Hi,

I am using visual studio 2008 and sql server 2008 for developing application(SQL server is in my system). I need to fetch some fields from the database. I am using the SQLDriverConnect API to connect to the database.

If I use the "SQL_DRIVER_PROMPT" I will get pop window to select the data source. I don't want this window to appear. As per my understanding this window will appear if we provide insufficient information in the connection string. I think I have provided all the information. I am trying to connect with windows authentication. I tried different options but still no luck. Please help me in solving this problem.

Below is the code that I am using:

//********************************************************************************
// SQLDriverConnect_ref.cpp
// compile with: odbc32.lib user32.lib
#include <windows.h>
#include <sqlext.h>

int main() {
   SQLHENV henv;
   SQLHDBC hdbc;
   SQLHSTMT hstmt;
   SQLRETURN retcode;

   SQLWCHAR OutConnStr[255];
   SQLSMALLINT OutConnStrLen;
   SQLCHAR      ConnStrIn[255] =
       "DRIVER={SQL Server};SERVER=(local);DSN=MyDSN;DATABASE=MyDatabase;Trusted_Connection=yes;";

   //SQLWCHAR *ConntStr =(SQLWCHAR *) "DRIVER={SQL Server};DSN=MyDSN;";
   HWND desktopHandle = GetDesktopWindow();   // desktop's window handle

   // Allocate environment handle
   retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

   // Set the ODBC version environment attribute
   if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
      retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0); 

      // Allocate connection handle
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
         retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 

         // Set login timeout to 5 seconds
         if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

         retcode = SQLDriverConnect( // SQL_NULL_HDBC
           hdbc,
           desktopHandle,
       (SQLWCHAR *)ConnStrIn,
       SQL_NTS,
           OutConnStr,
           255, 
           &OutConnStrLen,
           SQL_DRIVER_NOPROMPT); 


            // Allocate statement handle
            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {               
               retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); 

               // Process data
               if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
               }

               SQLDisconnect(hdbc);
            }

            SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
         }
      }
      SQLFreeHandle(SQL_HANDLE_ENV, henv);
   }
}

//********************************************************************************

Thanks in advance, Harsha

Answer 1

Use SQLConnect to connect; it makes no provision for prompting.

Whoops!. Bad idea. According to the documentation for SQLDriverConnect, SQLConnect accepts only: DSN, UID, and PW.

You should pass the NULL pointer for the hwnd, when calling SQLDriverConnect, if you don't want to allow prompting. Specify SQL_DRIVER_NOPROMPT for the prompt option (as you do). If the call fails, examine the output response code, and print out the messages.

Here's how you can do the checks:

boolean CHECK_STATUS(SQLRETURN rc, UCHAR *location, SQLHANDLE hnd, SQLSMALLINT hType)
{
    if (rc == SQL_SUCCESS)
    {
        printf("  ** %s: ok\n", location);
    }
    else
    {
        UCHAR *status = (rc == SQL_SUCCESS_WITH_INFO) ? "OK" : "failed" ;
        SQLRETURN _rc;
        UCHAR state[5+1];
        SQLINTEGER ecode;
        SQLSMALLINT msgLength;
        int recNumber = 0;

        printf("  ** %s: %s. rc(%d)\n", location, status, rc);

        do {
            _rc = SQLGetDiagRec(hType,
                                hnd,
                                ++recNumber, // RecNumber,
                                state,       // SQLState,
                                &ecode,      //    NativeErrorPtr,
                                NULL,        //      MessageText,
                                0,           //   BufferLength,
                                &msgLength);
            if (_rc == SQL_NO_DATA) {}
            else if (_rc != SQL_SUCCESS) {
                printf("    ** Cannot retrieve error message, reason: %d\n", _rc);
            }
            else {
                if (recNumber == 1)
                    printf("  ** SQL State: %s\n", state);
                if (rc != SQL_SUCCESS_WITH_INFO)
                    printf("  ** error code: %d\n", ecode);
                //printf("  ** %smsg length: %d\n", (rc != SQL_SUCCESS_WITH_INFO)?"error " : "", msgLength);
                if (msgLength > 0)
                {
                    UCHAR * msg = malloc((msgLength+2) * sizeof(UCHAR));
                    _rc = SQLGetDiagRec(hType,
                                        hnd,
                                        recNumber,   // RecNumber,
                                        state,       // SQLState,
                                        &ecode,      //    NativeErrorPtr,
                                        msg,         //      MessageText,
                                        msgLength+2, //   BufferLength,
                                        &msgLength);

                    printf("  ** msg %d: %s\n", recNumber, msg);
                    free(msg);
                }
            }
        } while (_rc == SQL_SUCCESS);
    }

    return (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO);
}


boolean CHECK_STATUS_DBC(SQLRETURN rc, UCHAR *location, SQLHANDLE hnd)
{
    return CHECK_STATUS(rc, location, hnd, SQL_HANDLE_DBC);
}


boolean CHECK_STATUS_ENV(SQLRETURN rc, UCHAR *location, SQLHANDLE hnd)
{
    return CHECK_STATUS(rc, location, hnd, SQL_HANDLE_ENV);
}

And here's how you invoke the call:

SQLRETURN rc;
UCHAR   *szDSN = "Driver={SQL Server};Server=hostname\\SQLEXPRESS;Database=Northwind;Trusted_Connection=Yes;";
HDBC    hdbc1 = SQL_NULL_HDBC;
HSTMT   hstmt1 = SQL_NULL_HSTMT;
boolean mustFreeConnect = FALSE;
boolean mustDisconnect = FALSE;
SQLSMALLINT dwLength;

printf("\nHello from OdbcEx1. \n");

printf("\nWill connect to %s\n", szDSN);

rc = SQLAllocEnv (&henv);
CHECK_STATUS_ENV(rc, "SQLAllocEnv", henv);

// Set the flavor of ODBC to 3.0
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
CHECK_STATUS_ENV(rc, "SQLSetEnvAttr", henv);

rc = SQLAllocConnect(henv, &hdbc1);
mustFreeConnect = CHECK_STATUS_DBC(rc, "SQLAllocConnect", henv);

rc = SQLDriverConnect(hdbc1,
                      NULL,
                      szDSN,
                      SQL_NTS,
                      NULL,
                      0,
                      &dwLength,
                      SQL_DRIVER_NOPROMPT);
mustDisconnect = CHECK_STATUS_DBC(rc, "SQLConnect", hdbc1);