Unicode SQLDriverConnectW(): [unixODBC][Driver Manager]Data source name not found, and no default driver specified

Question

The following is a complete ODBC program. All it does is attempt to open a connection to a SQLite database using a fully qualified connection string. The issue I'm having is that when Unicode is enabled (use SQLDriverConnectW() instead of SQLDriverConnect()) I get the error:

libc++abi.dylib: terminating with uncaught exception of type database_error: connect: IM002: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

My odbc.ini file is empty, and here's the content of my odbcinst.ini file:

[SQLite3]
Description=SQLite ODBC Driver
Driver=/usr/local/Cellar/sqliteodbc/0.9992/lib/libsqlite3odbc-0.9992.dylib
Setup=/usr/local/Cellar/sqliteodbc/0.9992/lib/libsqlite3odbc-0.9992.dylib
Threading=2

At the top of the code there's a #if 1 that can toggle the code between Unicode and Non-Unicode (change it to #if 0 to disable Unicode). When I enable Unicode, I get the error. When I disable Unicode, it works perfectly. Any ideas why the Unicode version of connect can't find my DSN?

/*

Build command:

clang++ -Wall -Werror \
    -std=c++14 -stdlib=libc++ \
    -I/usr/local/Cellar/unixodbc/2.3.2_1/include \
    -L/usr/local/Cellar/unixodbc/2.3.2_1/lib -lodbc \
    unittest.cpp && ./a.out

*/

#include <codecvt>
#include <sstream>
#include <stdexcept>
#include <string>
#include <vector>

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

#if 1
    // Enable Unicode
    #define STRING_LITERAL(s) L ## s
    #define CHAR SQLWCHAR
    #define ODBC_FUNCTION(f) f ## W
#else
    // Enable Non-unicode
    #define STRING_LITERAL(s) s
    #define CHAR SQLCHAR
    #define ODBC_FUNCTION(f) f
#endif

bool success(RETCODE rc);
void convert(const std::wstring& in, std::string& out);
void convert(const std::string& in, std::string& out);
template<typename T, std::size_t N> std::size_t arrlen(T(&)[N]);
std::string recent_error(SQLHANDLE handle, SQLSMALLINT handle_type, long &native, std::string &state);

class database_error : public std::runtime_error
{
public:
    database_error(void* handle, short handle_type, const std::string& info = "");
    const char* what() const noexcept { return message.c_str(); }
    const long native() const noexcept { return native_error; }
    const std::string state() const noexcept { return sql_state; }
private:
    long native_error;
    std::string sql_state;
    std::string message;
};

int main()
{
    RETCODE rc;

    HENV env;
    rc = SQLAllocHandle(
        SQL_HANDLE_ENV
        , SQL_NULL_HANDLE
        , &env);
    if(!success(rc))
        throw database_error(env, SQL_HANDLE_ENV, "env: ");

    rc = SQLSetEnvAttr(
        env
        , SQL_ATTR_ODBC_VERSION
        , (SQLPOINTER)SQL_OV_ODBC3
        , SQL_IS_UINTEGER);
    if(!success(rc))
        throw database_error(env, SQL_HANDLE_ENV, "version: ");

    HDBC conn;
    rc = SQLAllocHandle(
        SQL_HANDLE_DBC
        , env
        , &conn);
    if(!success(rc))
        throw database_error(env, SQL_HANDLE_ENV, "conn: ");

    CHAR dsn[1024];
    SQLSMALLINT dsn_size = 0;
    rc = ODBC_FUNCTION(SQLDriverConnect)(
        conn                                // ConnectionHandle
        , 0                                 // WindowHandle
        , (CHAR*)STRING_LITERAL("Driver=SQLite3;Database=nanodbc.db;") // InConnectionString
        , SQL_NTS                           // StringLength1
        , dsn                               // OutConnectionString
        , sizeof(dsn) / sizeof(CHAR)    // BufferLength
        , &dsn_size                         // StringLength2Ptr
        , SQL_DRIVER_NOPROMPT               // DriverCompletion
    );
    if(!success(rc))
        throw database_error(conn, SQL_HANDLE_DBC, "connect: ");
}

bool success(RETCODE rc)
{
    return rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO;
}

void convert(const std::wstring& in, std::string& out)
{
    out = std::wstring_convert<std::codecvt_utf8<wchar_t>>().to_bytes(in);
}

void convert(const std::string& in, std::string& out)
{
    out = in;
}

template<typename T, std::size_t N>
std::size_t arrlen(T(&)[N])
{
    return N;
}

std::string recent_error(
    SQLHANDLE handle
    , SQLSMALLINT handle_type
    , long &native
    , std::string &state)
{
    std::wstring result;
    std::string rvalue;
    std::vector<CHAR> sql_message(SQL_MAX_MESSAGE_LENGTH);
    sql_message[0] = '\0';

    SQLINTEGER i = 1;
    SQLINTEGER native_error;
    SQLSMALLINT total_bytes;
    CHAR sql_state[6];
    RETCODE rc;

    do
    {
        rc = ODBC_FUNCTION(SQLGetDiagRec)(
            handle_type
            , handle
            , (SQLSMALLINT)i
            , sql_state
            , &native_error
            , 0
            , 0
            , &total_bytes);

        if(success(rc) && total_bytes > 0)
            sql_message.resize(total_bytes + 1);

        if(rc == SQL_NO_DATA)
            break;

        rc = ODBC_FUNCTION(SQLGetDiagRec)(
            handle_type
            , handle
            , (SQLSMALLINT)i
            , sql_state
            , &native_error
            , sql_message.data()
            , (SQLSMALLINT)sql_message.size()
            , &total_bytes);

        if(!success(rc)) {
            convert(result, rvalue);
            return rvalue;
        }

        if(!result.empty())
            result += ' ';

        result += std::wstring(sql_message.begin(), sql_message.end());
        i++;

        // NOTE: unixODBC using PostgreSQL and SQLite drivers crash if you call SQLGetDiagRec()
        // more than once. So as a (terrible but the best possible) workaround just exit
        // this loop early on non-Windows systems.
        #ifndef _MSC_VER
            break;
        #endif
    } while(rc != SQL_NO_DATA);

    convert(result, rvalue);
    state = std::string(&sql_state[0], &sql_state[arrlen(sql_state) - 1]);
    native = native_error;
    std::string status = state;
    status += ": ";
    status += rvalue;

    // some drivers insert \0 into error messages for unknown reasons
    using std::replace;
    replace(status.begin(), status.end(), '\0', ' ');

    return status;
}

database_error::database_error(void* handle, short handle_type, const std::string& info)
: std::runtime_error(info), native_error(0), sql_state("00000")
{
    message = std::string(std::runtime_error::what()) + recent_error(handle, handle_type, native_error, sql_state);
}

I am compiling on OS X. I've installed sqlite, sqliteodbc, and unixodbc via Homebrew. I'm using clang as my compiler. The command I'm using to compile and run my program is in the comment at the top of the source code.

Bicentenary

Note that SQLDriverConnectW needs SQLWCHAR as parameters for in- and out-buffers. Dont you get any compiler warnings from calling SQLDriverConnectW with a (CHAR*) as argument for the in and out buffer? – Hexachlorophene

@Hexachlorophene There is the following mapping #define CHAR SQLWCHAR, so correct type is used. I can reproduce the same problem on Linux with clang 3.7, unixODBC 2.3.4, sqliteodbc 0.9992, sqlite 3.10.1. – Schilt

CHAR might be confusing. For better style, change your #define CHAR xxx to #define SQLTCHAR xxx (TCHAR is usually the type which switches between char/wchar depending on config). – Babcock

Also the cast should be unnecessary. I suggest you remove that. It an error rsults that suggests that there is another problem – Babcock

The cast is necessary to convert a string literal of the form L"..." to the parameter type required, unsigned short *. Without the cast, clang++ produces the following error: /usr/local/Cellar/unixodbc/2.3.2_1/include/sqlucode.h:257:19: with candidate function not viable: no known conversion from 'const wchar_t [36]' to 'SQLWCHAR *' (aka 'unsigned short *') for 3rd argument of SQLDriverConnectW(). – Bicentenary

Very similar code also using a cast can be found in the MSDN for this SQLDriverConnectW(): msdn.microsoft.com/en-us/library/ms715433(v=vs.85).aspx This cast is also required if compiling C code, with clang outputting the error: error: incompatible pointer types passing 'int [36]' to parameter of type 'SQLWCHAR *' (aka 'unsigned short *') [-Werror,-Wincompatible-pointer-types] for the line of code , L"Driver=SQLite3;Database=nanodbc.db;" // InConnectionString. – Bicentenary

@ben: Additionally, I'm pretty sure TCHAR is a Windows thing and I haven't used Windows since 1998. This is just concise example code demonstrating why I have found it apparently impossible to connect to a database using SQLDriverConnectW() (perhaps due to unixODBC or sqliteodbc or some misconception in my code, I'm not sure). Please rest assured I don't have any #define CHAR in my actual library code. Being able to easily toggle between unicode enabled and unicode disabled, for this example, is instructive. Because when unicode is disabled, things work perfectly. – Bicentenary

Answer 1

Ben's mention of UTF-16-LE in his answer pointed me down the path to resolving this issue. Changing the string literal from L"..." to u"..." and keeping the cast solved it for me. Without the cast you still get the error: no known conversion from 'const char16_t [36]' to 'SQLWCHAR *' (aka 'unsigned short *').

Bicentenary

Answer 2

The cast was masking the error. The ODBC API SQLWCHAR is UTF16-LE - unsigned short. On the Mac, wchar_t is int - 32 bits. These are incompatible.

You must convert your wide character strings to UTF16-LE, or use a char16_t string type. I believe iconv is shipped with xcode and has what you need.

Babcock

Answer 3

You should make the connection string a parameter, not a hard coded value. These string conversions are nasty, but in addition with the posted solutions, you can simplify the code like this

This is a UNICODE only solution tested in MacOS

1. regarding the string literal define the macro

#define WODBC_TEXT(s) u##s

regarding the conversion to std::string to SQLWCHAR * , you can simply do

use std::u16string and do in the function call both a cast to SQLWCHAR* and the string to char* call

std::u16string conn;
(SQLWCHAR*)conn.c_str();

complete function

void wodbc_t::connect(const std::u16string &conn)
{
  SQLHSTMT hstmt;
  SQLWCHAR dsn[1024];
  SQLSMALLINT dsn_size = 0;
  RETCODE rc;
#pragma unused(rc)
  rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
  rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER);
  rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

  rc = SQLDriverConnect(
        hdbc
        , 0
        , (SQLWCHAR*)conn.c_str()
        , SQL_NTS
        , dsn
        , sizeof(dsn) / sizeof(SQLWCHAR)
        , &dsn_size
        , SQL_DRIVER_NOPROMPT
        );

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

    //process data

    rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
  }
}

and an example call

std::u16string sql;
  sql = WODBC_TEXT("SELECT * FROM [QMSAnalysisResults];");
  wodbc_t wquery;
  wquery.connect(WODBC_TEXT("DRIVER=ODBC Driver 17 for SQL Server;SERVER=192.168.1.237, 1433;UID=SA;PWD=password;"));
  wquery.fetch(sql);
  wquery.disconnect();

Fedora