How do I connect to a Teradata via ODBC in code?

Question

The connection string specified is:

Provider=Teradata;DBCName=dbc_name;Database=database_name; Uid=user_name;Pwd=password;

I installed Teradata ODBC client version 15.1 and setup a connection to it via the control panel.

When use the code:

#include "stdafx.h"
#include <Windows.h>
#include <sql.h>
#include <sqlext.h>
#include <string>

int _tmain(int argc, _TCHAR* argv[])
{
    SQLHANDLE hdbc = SQL_NULL_HANDLE;
    SQLHANDLE henv = SQL_NULL_HANDLE;
    SQLRETURN retval = SQL_SUCCESS;

    retval = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    if (retval != SQL_SUCCESS) {
        printf("SQLAllocHandle SQL_HANDLE_ENV failed! Result = %d\n", retval);
    }

    retval = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
    if (retval != SQL_SUCCESS) {
        printf("SQLSetEnvAttr SQL_ATTR_ODBC_VERSION failed! Result = %d\n", retval);
    }

    SQLINTEGER output_nts, autocommit;
    retval = SQLGetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, &output_nts, 0, 0);

    retval = SQLSetEnvAttr(henv, SQL_ATTR_OUTPUT_NTS, (SQLPOINTER)SQL_TRUE, 0);
    if (retval != SQL_SUCCESS) {
        printf("SQLSetEnvAttr SQL_ATTR_OUTPUT_NTS failed! Result = %d\n", retval);
    }

    retval = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    if (retval != SQL_SUCCESS) {
        printf("SQLAllocHandle SQL_HANDLE_DBC failed! Result = %d\n", retval);
    }

    SQLCHAR szConn[1024];
    SWORD cbConn = 0;

    std::string connectionString("Provider=teradata;DBCName=myLocalTDcop;database=myDatabaseName;uid=myUID;pwd=myPwd;");

    retval = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)connectionString.c_str(), SQL_NTS, szConn, 1024, &cbConn, SQL_DRIVER_NOPROMPT);
    if (retval != SQL_SUCCESS) {
        printf("SQLDriverConnect failed! Result = %d\n", retval);
    }
}

The SQLDriverConnect command always returns -1.

Am I doing something wrong with the connection string?

Update: Using SQLGetDiagRec I have obtained the error message:

The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 03.80

However, if I change the ODBC version to SQL_OV_ODBC3_80 then I get the error message:

[Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

and then:

The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 03.80

Is this to do with the ODBC version of 15.10? I have looked at the documentation but cannot see where the ODBC version is specified. Is there a way to check it in Windows?

It seems unlikely that the user id is myUID and the password is myPwd. Are you supplying the correct values? Also, you can use SQLGetDiagRec to get an informative error message. — user2100815
I have changed the user name and password (probably not allowed to post them on a public forum). Anyone running this sample code would need to change them to be whatever is relevant for their ODBC setup. — Stefan

Stefan Stefan · Accepted Answer · 2017-02-23T16:16:41

I had not added the correct directory to the Path environmental variable!!!!

How do I connect to a Teradata via ODBC in code?

1 Answers