SQLDriverConnect error

[jsignanini]

After successfully setting up unixODBC and the Amazon Hive ODBC driver. And successfully being able to connect and run queries on Hive through the command line (isql -v "[DSN]"), I am not able to connect/run a query through your library in Go.

The error I receive is very vague, after which the program exits(1):

SQLDriverConnect: {H} [unixODBC][

Note that there is nothing missing from the error... it just ends there.

Have you experienced this before? I am running Mac OSX 10.11.5 (El Capitan) and unixODBC 2.3.4.

[alexbrainman]

The error I receive is very vague, after which the program exits(1):

Please, show your program. Thank you.

Alex

[lrewega]

I can reproduce this with Vertica's ODBC driver (https://my.vertica.com/client_drivers/7.1.x/vertica-odbc-7.1.2-0.mac.pkg) and unixODBC 2.3.4 as well as 2.3.2_1 (both from brew install unixodbc)

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/alexbrainman/odbc"
)

func main() {
    db, err := sql.Open("odbc", "Driver=Vertica;Server=localhost;Database=test;Port=5433;UID=dbadmin;PWD=notarealpassword")
    //db, err := sql.Open("odbc", "Driver=SQLite3;Database=test_sqlite.db")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    if err := db.Ping(); err != nil {
        fmt.Println(err)
    }

    var v string
    if err := db.QueryRow("select 1").Scan(&v); err != nil {
        fmt.Println(err)
    } else {
        fmt.Println(v)
    }
}

produces:

SQLDriverConnect: {H} [unixODBC][
SQLDriverConnect: {H} [unixODBC][

If I try with SQLite3, I get 1 as expected.

The contents of my /usr/local/etc/odbcinst.ini are:

[ODBC Drivers]
Vertica = Installed
SQLite3 = Installed

[SQLite3]
Description=SQLite3 ODBC Driver
Driver=/usr/local/lib/libsqlite3odbc.dylib
Setup=/usr/local/lib/libsqlite3odbc.dylib

[Vertica]
Description=HP Vertica ODBC Driver
Driver=/Library/Vertica/ODBC/lib/libverticaodbc.dylib
Setup=/Library/Vertica/ODBC/lib/libverticaodbc.dylib
ErrorMessagesPath=/Library/Vertica/ODBC/messages/
DriverManagerEncoding=UTF-16

I've tried every possible tweak imaginable to my odbcinst.ini to no avail. This configuration works fine with both isql and pyodbc.

I haven't tried the Amazon Hive ODBC driver, but I would guess that the underlying cause is the same, given the identical output.

[lrewega]

I just tried the same code (and Vertica ODBC driver release) on Ubuntu 12.04 and encountered no issues:

$ ldd main
    linux-vdso.so.1 =>  (0x00007ffc021d7000)
    libodbc.so.1 => /usr/lib/x86_64-linux-gnu/libodbc.so.1 (0x00007fe7ddbdb000)
    libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fe7dd9be000)
    libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fe7dd600000)
    libltdl.so.7 => /usr/lib/x86_64-linux-gnu/libltdl.so.7 (0x00007fe7dd3f6000)
    /lib64/ld-linux-x86-64.so.2 (0x00007fe7dde42000)
    libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fe7dd1f2000)
$ ./main
1

$ dpkg -l | grep -i odbc
ii  libodbc1                                       2.2.14p2-5ubuntu3                     ODBC library for Unix
ii  odbcinst                                       2.2.14p2-5ubuntu3                     Helper program for accessing odbc ini files
ii  odbcinst1debian2                               2.2.14p2-5ubuntu3                     Support library for accessing odbc ini files
ii  unixodbc                                       2.2.14p2-5ubuntu3                     Basic ODBC tools
ii  unixodbc-dev                                   2.2.14p2-5ubuntu3                     ODBC libraries for UNIX (development files)

/etc/odbcinst.ini:

[Vertica]
Description     = Vertica driver for Linux
Driver      = libverticaodbc.so
ErrorMessagesPath       = /usr/lib/
UsageCount      = 1

Vertica ODBC driver: https://my.vertica.com/client_drivers/7.1.x/vertica-client-7.1.2-0.x86_64.tar.gz

edit: Perhaps also worth mentioning is that errors display correctly on Linux. Here is a bad password:

SQLDriverConnect: {28000} [unixODBC]FATAL 3781:  Invalid username or password

SQLDriverConnect: {28000} [unixODBC]FATAL 3781:  Invalid username or password

[jsignanini]

@alexbrainman my code is basically the same as @lrewega

package main

import (
    "database/sql"
    "log"
    _ "github.com/alexbrainman/odbc"
)

var (
    db  *sql.DB
    err error
)

func main() {
    db, err = sql.Open("odbc", "DSN=AmazonHiveDSN;UID=[USER];PWD=[PWD]")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
}

[alexbrainman]

@jsignanini and @lrewega

I don't have darwin. And I don't use Amazon Hive or Vertica. So it is difficult for me to debug. But you should be able to do that. Couple of ideas.

The error message looks wrong.

$ git diff
diff --git a/conn.go b/conn.go
index 5569dec..732cdc2 100644
--- a/conn.go
+++ b/conn.go
@@ -26,7 +26,7 @@ func (d *Driver) Open(dsn string) (driver.Conn, error) {
    h := api.SQLHDBC(out)
    drv.Stats.updateHandleCount(api.SQL_HANDLE_DBC, 1)

-   b := api.StringToUTF16(dsn)
+   b := api.StringToUTF16("crap")
    ret = api.SQLDriverConnect(h, 0,
        (*api.SQLWCHAR)(unsafe.Pointer(&b[0])), api.SQL_NTS,
        nil, 0, nil, api.SQL_DRIVER_NOPROMPT)
$ go test -v -run=TestMSSQLSingleCharParam
=== RUN   TestMSSQLSingleCharParam
--- FAIL: TestMSSQLSingleCharParam (0.00s)
    mssql_test.go:228: db.Prepare("create table dbo.temp(name nvarchar(50), age int)") failed: SQLDriverConnect: {IM002} [unixODBC][Driver Manager]Data source name not found, and no default driver specified
FAIL
exit status 1
FAIL    github.com/alexbrainman/odbc    0.004s
$

Note how my message starts with {IM002}. The IM002 is a five-character SQLSTATE (google SQLGetDiagRec documentation for details). Your output says: {H}. Why only single character? Put some fmt.Printf in NewError to make sure nothing is a miss.

Maybe try to write small C program that calls SQLDriverConnect with the same parameters (see conn.go). Does it fail too? If fails, call SQLGetDiagRec to get error message. Is C error message looks different?

Thank you.

Alex

[jsignanini]

UPDATE

I've tried the same using nodejs's ODBC library and got the same exact error... so it appears to not be a problem with this library. I'll keep investigating but the problem most likely lies with the Amazon Hive ODBC driver + Darwin config.

[lrewega]

TL;DR: Specify DriverManagerEncoding=UTF-16 to your ODBC driver.

@alexbrainman I think you were on the right track. Just when I thought I understood Vertica's ODBC driver, I was proven wrong.

It looks like, by default, it doesn't support unicode:

$ echo 'SELECT 1;' | isql -b ...
+---------------------+
| ?column?            |
+---------------------+
| 1                   |
+---------------------+
SQLRowCount returns 1
1 rows fetched
$ echo 'SELECT 1;' | iusql -b ...
[ISQL]ERROR: Could not SQLDriverConnect

Cool. According to the Vertica documentation you need to set the VERTICAINI environment variable to point to a file with additional Vertica-specific configuration. Setting this fixes everything. Literally.

$ echo 'SELECT 1;' | VERTICAINI= isql -b ...
+---------------------+
| ?column?            |
+---------------------+
| 1                   |
+---------------------+
SQLRowCount returns 1
1 rows fetched
$ echo 'SELECT 1;' | VERTICAINI= iusql -b ...
+---------------------+
| ?column?            |
+---------------------+
| 1                   |
+---------------------+
SQLRowCount returns 1
1 rows fetched

edit: It looks like Vertica's ODBC driver ships with a vertica.ini that defaults to UTF-32, but if you use a custom ini file that does not specify an encoding, it defaults to UTF-16, which works.

I'd bet @jsignanini has a similar issue, and probably needs to set the Amazon HIVE ODBC driver to use the right encoding (e.g. UTF-16)

[jsignanini]

Spot on @alexbrainman!

Turns out the Amazon HIVE ODBC driver also needs it's amazon.hiveodbc.ini file to have the correct encoding. Changing DriverManagerEncoding to UTF-16 fixed the issue. If you're using default paths, the file lives in the same directory as the driver (i.e.: /opt/amazon/hiveodbc/lib/universal/amazon.hiveodbc.ini on Max OS X).

From the "Install Guide" that comes with the driver, here is the explanation:

Edit the DriverManagerEncoding setting. The value is usually UTF-16 or UTF-32, depending on the ODBC driver manager you use. iODBC uses UTF-32, and unixODBC uses UTF-16. To determine the correct setting to use, refer to your ODBC driver manager documentation.

Since this library uses unixODBC, we need to set it to UTF-16.

Thanks for the help @alexbrainman and @lrewega.

[ajozz13]

Hello I'm having a similar issue. In my case we are using informix datatabase odbc driver and it only supports UCS-2, UCS-4 and UTF-8 Is it possible to build unixodbc with any of these settings? I've tried manually building from source like this ./configure --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF-8 --with-iconv-ucode-enc=UTF-8

For reference here is the informix recommendation for its odbc.ini setting https://www.ibm.com/docs/en/informix-servers/14.10?topic=application-configuration

[ganjarsetia]

@ajozz13 follow that reference then change the unicode to UCS-2. So it something like this

[ODBC]
.
.
.
UNICODE=UCS-2

I tried it works!