Microsoft ODBC cannot create valid handle

Question

I'm using Microsoft's ODBC driver to connect a C++/Linux application to a SQL Server database running remotely. When I try to connect to the database with SQLDriverConnect(), the call fails with a return code error of SQL_INVALID_HANDLE.

Reading through their documentation, I find this:

SQL_INVALID_HANDLE Function failed due to an invalid environment, connection, statement, or descriptor handle. This indicates a programming error. No additional information is available from SQLGetDiagRec or SQLGetDiagField. This code is returned only when the handle is a null pointer or is the wrong type, such as when a statement handle is passed for an argument that requires a connection handle.

Fair enough, but none of the preceding function calls to create the environment and handles before the SQLDriverConnect()function call return an error result. For the second argument to SQLDriverConnect(), their documentation says I can pass in a null pointer if there is no desktop window (as is the case on this linux console application).

Here is a MVCE, adapted from Microsoft's example program:

#include "sql.h"
#include "sqlext.h"
#include "msodbcsql.h"
#include <iostream>
#include <string>

int main(int, char**)
{
  using std::cerr;
  using std::endl;
  SQLHENV henv;  
  SQLHDBC hdbc;
  HWND dhandle = nullptr; // no desktop handle in linux
  SQLHSTMT hstmt;  
  SQLRETURN retcode;  
  SQLCHAR OutConnStr[255];  
  SQLSMALLINT OutConnStrLen;

  retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);  
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    cerr << "SQLAllocHandle (environment) failed " << retcode << endl;

  retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    cerr << "SQLSetEnvAttr failed " << retcode << endl;

  retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    cerr << "SQLAllocHandle (connection) failed " << retcode << endl;

  retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);  
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    cerr << "SQLSetConnectAttr failed " << retcode << endl;

  std::string dsn = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=*.*.*,1433;DATABASE=***;UID=***;PWD=***";
  retcode = SQLDriverConnect(hdbc, dhandle, (SQLCHAR*)dsn.c_str(), dsn.length(), OutConnStr, 255, &OutConnStrLen, SQL_DRIVER_PROMPT);
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    cerr << "SQLDriverConnect failed " << retcode << endl;

  // cleanup code redacted for brevity

  return 0;
}

The program outputs SQLDriverConnect failed -2, which is SQL_INVALID_HANDLE. I'm stumped. hdbc is clearly the right type, and examining it in the debugger shows me it is not null.

It may be worth noting that the exact same connection string works in a python program using pyodbc. It seems that the C++ program isn't even getting as far as looking at that string, though. It just doesn't like the handle I'm sending into the connect call.

Microsoft's documentation clearly says they provide no additional information. If anyone can provide any direction on how to diagnose/debug this, I'd appreciate it greatly.

This application uses gcc 4.9.1 on Centos 7.

Answer 1

I was having this same problem compiling a simple test program for SQLIte3 ODBC driver from http://www.ch-werner.de/sqliteodbc/ . In my case just trying to do a list of drivers was failing with the SQL_INVALID_HANDLE error.

In my case I was using Visual Studio 2019 C++ compilers under Windows 10 using a make file in a terminal window that had initialized the environment properly. The .bat file that created a command shell terminal window and initialized the environment is:

echo setting up the Visual Studio 2019 compile environment

for /f "usebackq tokens=*" %%i in (`"C:\Program Files (x86)\Microsoft Visual Studio\Installer\vswhere" -latest -products * -requires Microsoft.VisualStudio.Component.VC.Tools.x86.x64 -property installationPath`) do (
  set InstallDir=%%i
)

%comspec% /k "%InstallDir%\Common7\Tools\VsDevCmd.bat"

The test function that created and initialized the test database, createDatabase(), had the following order of ODBC library calls:

rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
rcMessage ("SQLAllocHandle(SQL_HANDLE_ENV)", rc);
rc= SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
rcMessage ("SQLSetEnvAttr()", rc);
rc = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
rcMessage ("SQLAllocHandle(SQL_HANDLE_DBC)", rc);

if (!SQL_SUCCEEDED(rc)) {
    fprintf(stderr, "AllocConnect failed\n");
    exit(1);
}
fprintf(stderr, "SQLDriverConnect() ");
rc = SQLDriverConnect(dbc, NULL, (SQLCHAR *) dsn, SQL_NTS, NULL, 0, NULL,
          SQL_DRIVER_COMPLETE /* | SQL_DRIVER_NOPROMPT */);

and the source for reporting the error data from SQLDriverConnect() is:

if (!SQL_SUCCEEDED(rc)) {
    SQLCHAR     SQLState[8] = {0};
    SQLINTEGER  NativeErrorPtr = 0;
    SQLSMALLINT RecNumber = 1;
    SQLCHAR     MessageText[256] = {0};
    SQLSMALLINT  TextLength = 0, BufferLength = 255;

    SQLGetDiagRec(SQL_HANDLE_DBC, dbc, RecNumber, SQLState, &NativeErrorPtr, MessageText, BufferLength, &TextLength);
    fprintf(stderr, "DriverConnect failed rc 0x%x SQLState = %x%x TextLength = %d Message=%s\n", rc, SQLState[0], SQLState[1], TextLength, MessageText);
    exit(1);
}

This was the output from the obench.exe test program:

C:\Users\rcham\Documents\github\richardchambers\sqliteodbc\tccex>obench -- -dsn "Driver={SQLite ODBC Driver};Database=\TEMPDISK\BENCH.DB" -init -v -tpc 200 -clients 4
ListDrivers()
  SQLAllocHandle(SQL_HANDLE_ENV)  SQL_SUCCESS
  SQLSetEnvAttr()  SQL_SUCCESS
  SQLDrivers()  SQL_INVALID_HANDLE
 Driver =
Scale factor value: 1
Number of clients: 4
Number of transactions per client: 200

Initializing dataset...
CreateDatabase dsn=Driver={SQLite ODBC Driver};Database=\TEMPDISK\BENCH.DB
  SQLAllocHandle(SQL_HANDLE_ENV)  SQL_SUCCESS
  SQLSetEnvAttr()  SQL_SUCCESS
  SQLAllocHandle(SQL_HANDLE_DBC)  SQL_SUCCESS
SQLDriverConnect()     SQL_INVALID_HANDLE
DriverConnect failed rc 0xfffffffe SQLState = 00 TextLength = 0 Message=

My source code for the list drivers function:

int ListDrivers () {

     SQLRETURN rc;
     SQLHENV         env;
     SQLUSMALLINT    Direction = SQL_FETCH_FIRST;
     SQLCHAR         DriverDescription[256] = {0};
     SQLSMALLINT     BufferLength1 = 255;
     SQLSMALLINT     DescriptionLengthPtr;
     SQLCHAR         DriverAttributes[512] = {0};
     SQLSMALLINT     BufferLength2 = 511;
     SQLSMALLINT     AttributesLengthPtr;

    fprintf (stderr, "ListDrivers()\n");

    rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    rcMessage ("SQLAllocHandle(SQL_HANDLE_ENV)", rc);
    rc= SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
    rcMessage ("SQLSetEnvAttr()", rc);

    do {
        rc = SQLDrivers(  
        env,  
        Direction,  
        DriverDescription,  
        BufferLength1,  
        &DescriptionLengthPtr,  
        DriverAttributes,  
        BufferLength2,  
        &AttributesLengthPtr);

        rcMessage ("SQLDrivers()", rc);

        fprintf (stderr, " Driver = %s\n", DriverDescription);

        Direction = SQL_FETCH_NEXT;
    } while(SQL_SUCCEEDED(rc));
    
     return 0;
}

I finally started changing the order of the libraries in the nmake file for the definition of EXELIB and the following is what worked.

It was:

EXELIBS=    sqlite3odbc.lib ucrt.lib vcruntime.lib msvcrt.lib odbc32.lib odbccp32.lib kernel32.lib user32.lib

and I changed the order of the odbc libraries to front as in:

EXELIBS=    odbccp32.lib odbc32.lib sqlite3odbc.lib ucrt.lib vcruntime.lib msvcrt.lib kernel32.lib user32.lib 

The complete make file which creates two test programs, sbench.exe to test using the SQLite libraries embedded and obench.exe to test the SQLite ODBC driver.

# VC++ 6 Makefile
# uses the SQLite3 amalgamation source which must
# be unpacked below in the same folder as this makefile

CC=     cl
LN=     link
RC=     rc

!IF "$(DEBUG)" == "1"
LDEBUG=     /DEBUG
CDEBUG=     -Zi
!ELSE
LDEBUG=     /RELEASE
!ENDIF


CFLAGS=     -I. -Gs -EHsc -D_WIN32 -D_DLL -nologo $(CDEBUG) \
        -DHAVE_SQLITE3COLUMNTABLENAME=1 \
        -DHAVE_SQLITE3PREPAREV2=1 \
        -DHAVE_SQLITE3VFS=1 \
        -DHAVE_SQLITE3LOADEXTENSION=1 \
        -DSQLITE_ENABLE_COLUMN_METADATA=1 \
        -DWITHOUT_SHELL=1
CFLAGSEXE=  -I. -I.. -Gs -EHsc -D_WIN32 -nologo $(CDEBUG)

DLLLFLAGS=  /NODEFAULTLIB $(LDEBUG) /NOLOGO /MACHINE:IX86 \
        /SUBSYSTEM:WINDOWS /DLL

DLLLIBS=    ucrt.lib vcruntime.lib msvcrt.lib odbccp32.lib kernel32.lib \
        user32.lib comdlg32.lib legacy_stdio_definitions.lib legacy_stdio_wide_specifiers.lib

EXELIBS=    odbccp32.lib odbc32.lib sqlite3odbc.lib ucrt.lib vcruntime.lib msvcrt.lib kernel32.lib user32.lib 

DRVDLL=     sqlite3odbc.dll

.c.obj:
        $(CC) $(CFLAGS) /c $<

all:        obench.exe sbench.exe

clean:
        del *.obj
        del *.res
        del *.exp
        del *.ilk
        del *.pdb
        del *.res
        del *.exe

Obench.exe: obench.c
        $(CC) $(CFLAGSEXE) obench.c $(EXELIBS)

sbench.exe: sbench.c
        $(CC) $(CFLAGSEXE) sbench.c ../sqlite3.obj $(EXELIBS) 

The complete source code copy I'm working from is in a GitHub repository of mine, https://github.com/RichardChambers/sqliteodbc

Answer 2

I have exactly the same error using similar code (that was working in Ubuntu 18.04, but not with a update to 20.04)

cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1
UsageCount=1

using this connection string

DRIVER=ODBC Driver 17 for SQL Server;SERVER=127.0.0.1, 1433;UID=SA;PWD=password;DATABASE=my_database;

this is my library link order

if(UNIX)
  find_program(LSB_RELEASE_EXEC lsb_release)
  execute_process(COMMAND ${LSB_RELEASE_EXEC} -is OUTPUT_VARIABLE LSB_RELEASE_ID_SHORT OUTPUT_STRIP_TRAILING_WHITESPACE)
  message(STATUS "Building in " ${LSB_RELEASE_ID_SHORT})
  if("${LSB_RELEASE_ID_SHORT}" STREQUAL "Ubuntu")
    message(STATUS "Linking with SQL-Server library")
    set(lib_dep ${lib_dep} msodbcsql-17)
  endif()
  set(lib_dep ${lib_dep} pthread odbc dl)
endif()

As noted in the solution above, changing the link order fixed the problem

set(lib_dep ${lib_dep} pthread odbc dl msodbcsql-17)

Answer 3

For any handle in SQL Server, it has to be allocated before used!

So the order is Environment, Connection and Statement.

Example:

SQLHENV hEnv = nullptr;
SQLHDBC hDbc = nullptr;
SQLHSTMT hStmt = NULL;

Allocations

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);

Below is sample code that may help you.

Basics fast,

Create table in your sql server database and insert some data

create table test (id int, name nvarchar(128));

Insert some data

insert into test (id,name) values (1, 'Awesome Name');

C++ Code to query items in the table

#include <iostream>
#include <string>
#include <sql.h>
#include <sqlext.h>


int main(int argc, char **argv) {
    SQLHENV hEnv = nullptr;
    SQLHDBC hDbc = nullptr;
    SQLHSTMT hStmt = NULL;

    /**
     * Allocate environment handle
     */
    SQLRETURN allocReturn = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);

    //Set environment
    SQLRETURN setEnvReturn = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);

    //Allocate connection handle

    SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
    SQLCHAR *connection_string = (SQLCHAR *)
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost,1433;DATABASE=database;UID=sa;PWD=password";

    //Connect to database
    SQLRETURN connReturn = SQLDriverConnect(hDbc, NULL, connection_string, SQL_NTS, NULL, 0, NULL,
                                            SQL_DRIVER_COMPLETE);

    //Allocate Statement Handle
    SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);

    //Create statement
    SQLCHAR *query = (SQLCHAR *) "SELECT * FROM TEST;";
    SQLRETURN sqlPrepareResponse = SQLPrepare(hStmt, query, SQL_NTS); //strlen(reinterpret_cast<const char *>(query))

    //Bind columns
    SQLCHAR personName[20];
    SQLLEN personNameIndex;
    SQLRETURN bindNameResponse = SQLBindCol(hStmt, 2, SQL_C_CHAR, personName, sizeof(personName),
                                            &personNameIndex);


    SQLINTEGER personId;
    SQLLEN personIdIndex;
    SQLRETURN personIdBindResponse = SQLBindCol(hStmt, 1, SQL_INTEGER, &personId, 0, &personIdIndex);

    SQLRETURN execResponse = SQLExecute(hStmt);

    SQLRETURN fetchResponse;

    while ((fetchResponse = SQLFetch(hStmt)) != SQL_NO_DATA) {
        std::cout << "ID: [" << personId << "] :" << personName << std::endl;
    }


    /* Free the statement handle. */
    SQLFreeHandle(SQL_HANDLE_STMT, hStmt);

    /* Disconnect from the database. */
    SQLDisconnect(hDbc);

    /* Free the connection handle. */
    SQLFreeHandle(SQL_HANDLE_DBC, hDbc);

    /* Free the environment handle. */
    SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

    return EXIT_SUCCESS;
}

Answer 4

After two weeks of digging, this turned out to be some kind of versioning problem.

Eventually, this program will be doing some BCP uploads via Microsoft's extensions in libmsodbcsql.so. It turns out that library also has implementations of many of the SQL* functions, which are the ones that are failing in this test program. When I change the order of the link so that libodbc.so is before the MSFT extensions library so that the loader finds those implementations first, the program works fine.

I'm curious why this is, and it probably points to something else I'm doing wrong that may bite me down the road. But for now, at least, I am able to get connected to the database and do basic queries and updates.

Thanks to those who helped.