Runtime: Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [H] [unixODBC][

[qqbuby]

# odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

# dotnet --info
.NET Command Line Tools (2.0.0)

Product Information:
 Version:            2.0.0
 Commit SHA-1 hash:  cdcd1928c9

Runtime Environment:
 OS Name:     debian
 OS Version:  9
 OS Platform: Linux
 RID:         linux-x64
 Base Path:   /usr/share/dotnet/sdk/2.0.0/

Microsoft .NET Core Shared Framework Host

  Version  : 2.0.0
  Build    : e8b8861ac7faf042c87a5c2f9f2d04c98b69f28d

# cat /root/.odbcinst.ini
[ODBC]
TraceFile=/tmp/sql.log
Trace=Yes

[ODBC Drivers]
Cloudera ODBC Driver for Impala=Installed

[Cloudera ODBC Driver for Impala]
Description=Cloudera ODBC Driver for Impala
Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so

# odbcinst -q -d
[ODBC Drivers]
[Cloudera ODBC Driver for Impala]

# dpkg -l | grep cloudera
ii  clouderaimpalaodbc          2.5.40.1025-2                  all          Cloudera ODBC Driver for Impala

# dotnet run

Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [H] [unixODBC][
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()
   at dotnet_cross_odbc.Program.Main(String[] args) in /root/dotnet-cross-odbc/src/dotnet-cross-odbc/Program.cs:line 12

P.S: the host and database has been replaced with '...'

# cat /tmp/sql.log
[ODBC][4192][1510748384.915616][__handles.c][460]
                Exit:[SQL_SUCCESS]
                        Environment = 0x171d2e0
[ODBC][4192][1510748384.917195][SQLSetEnvAttr.c][189]
                Entry:
                        Environment = 0x171d2e0
                        Attribute = SQL_ATTR_ODBC_VERSION
                        Value = 0x3
                        StrLen = -6
[ODBC][4192][1510748384.917219][SQLSetEnvAttr.c][364]
                Exit:[SQL_SUCCESS]
[ODBC][4192][1510748384.917230][SQLSetEnvAttr.c][189]
                Entry:
                        Environment = 0x171d2e0
                        Attribute = SQL_ATTR_CONNECTION_POOLING
                        Value = 0x2
                        StrLen = -6
[ODBC][4192][1510748384.917237][SQLSetEnvAttr.c][364]
                Exit:[SQL_SUCCESS]
[ODBC][4192][1510748384.918088][SQLAllocHandle.c][375]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x171d2e0
[ODBC][4192][1510748384.918112][SQLAllocHandle.c][493]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x171df10
[ODBC][4192][1510748384.918506][SQLSetConnectAttrW.c][332]
                Entry:
                        Connection = 0x171df10
                        Attribute = SQL_ATTR_LOGIN_TIMEOUT
                        Value = 0xf
                        StrLen = -5
[ODBC][4192][1510748384.918646][SQLSetConnectAttrW.c][606]
                Exit:[SQL_SUCCESS]
[ODBC][4192][1510748384.919578][SQLDriverConnectW.c][290]
                Entry:
                        Connection = 0x171df10
                        Window Hdl = (nil)
                        Str In = [Driver=Cloudera ODBC Driver for Impala;Host=*.*.*.*;Port=21050;Database=*;][length = 82 (SQL_NTS)]
                        Str Out = (nil)
                        Str Out Max = 0
                        Str Out Ptr = 0x7ffe4c382190
                        Completion = 0
                UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

[ODBC][4192][1510748384.928525][SQLDriverConnectW.c][699]
                Exit:[SQL_ERROR]
[ODBC][4192][1510748384.931695][SQLGetDiagRecW.c][514]
                Entry:
                        Connection = 0x171df10
                        Rec Number = 1
                        SQLState = 0x7ffe4c381f40
                        Native = 0x7ffe4c382130
                        Message Text = 0x17ba0f0
                        Buffer Length = 1024
                        Text Len Ptr = 0x7ffe4c382128
[ODBC][4192][1510748384.931733][SQLGetDiagRecW.c][561]
                Exit:[SQL_SUCCESS]
                        SQLState = [H]
                        Native = 0x7ffe4c382130 -> 10360
                        Message Text = [[unixODBC][]
[ODBC][4192][1510748384.931772][SQLGetDiagRecW.c][514]
                Entry:
                        Connection = 0x171df10
                        Rec Number = 2
                        SQLState = 0x7ffe4c381f40
                        Native = 0x7ffe4c382130
                        Message Text = 0x17ba0f0
                        Buffer Length = 1024
                        Text Len Ptr = 0x7ffe4c382128
[ODBC][4192][1510748384.931783][SQLGetDiagRecW.c][561]
                Exit:[SQL_NO_DATA]

Source Repo on GitHub: https://github.com/qqbuby/dotnet-cross-odbc

[danmosemsft]

Are you comfortable to debug a little?

[bigmacd]

@qqbuby , have you tried isql and iusql? I would be curious to know if one or the other can connect.

I am having a similar issue with Netezza via unixODBC. For me, isql will connect but iusql (the unicode version) will not. It seems System.Data.Odbc only calls SQLDriverConnectW and this may be the issue. I think we need a way to say "no unicode for me, please", and call SQLDriverConnect (without the W).

[qqbuby]

@bigmacd

From the ODBC trace message UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE', it seems that the problem was caused with DriverManagerEncoding.

@danmosemsft

The problem has been solved by set the DriverManagerEncoding=UTF-16 in the Cloudera Impala Driver cloudera.impalaodbc.ini or just comment it as below:

[Driver]

## - Note that this default DriverManagerEncoding of UTF-32 is for iODBC.
## - unixODBC uses UTF-16 by default.
## - If unixODBC was compiled with -DSQL_WCHART_CONVERT, then UTF-32 is the correct value.
##   Execute 'odbc_config --cflags' to determine if you need UTF-32 or UTF-16 on unixODBC
## - SimbaDM can be used with UTF-8 or UTF-16.
##   The DriverUnicodeEncoding setting will cause SimbaDM to run in UTF-8 when set to 2 or UTF-16 when set to 1.

DriverManagerEncoding=UTF-16
## UTF-32
ErrorMessagesPath=/opt/cloudera/impalaodbc/ErrorMessages/
LogLevel=0
LogPath=
SwapFilePath=/tmp


## - Uncomment the ODBCInstLib corresponding to the Driver Manager being used.
## - Note that the path to your ODBC Driver Manager must be specified in LD_LIBRARY_PATH (LIBPATH for AIX).
## - Note that AIX has a different format for specifying its shared libraries.

# Generic ODBCInstLib
#   iODBC
#ODBCInstLib=libiodbcinst.so

#   SimbaDM / unixODBC
#ODBCInstLib=libodbcinst.so

# AIX specific ODBCInstLib
#   iODBC
#ODBCInstLib=libiodbcinst.a(libiodbcinst.so.2)

#   SimbaDM
#ODBCInstLib=libodbcinst.a(odbcinst.so)

#   unixODBC
ODBCInstLib=libodbcinst.a(libodbcinst.so.1)

However, the System.Data.Odbc should give us an explicit error message, instead of ERROR [H] [unixODBC][.

[bigmacd]

Anyone want to take a guess at this:

Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [奈〰0쌺翾] [unixODBC]敓癲牥愠摮漯⁲潰瑲愠瑴楲畢整⁳牡⁥浥瑰y絈膠缮
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, 
    OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, 
    OdbcConnectionString connectionOptions)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()

I took those characters (in Python):
In [1]: s = '敓癲牥愠摮漯⁲潰瑲愠瑴楲畢整⁳牡⁥浥瑰y䵈琀缰'
In [2]: s.encode('utf-16')
Out[3]: b'\xff\xfeServer and/or port attributes are empty\x00HM\x00t0\x7f'

It is on Linux using unixODBC->Netezza

I have followed the instructions in the Netezza ODBC_README:

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/nz/lib
ODBCINI=/root/odbc.ini
NZ_ODBC_INI_PATH=/root

odbc.ini: (located in /root/.odbc.ini

;
;  odbc.ini
;
[ODBC Data Sources]
NZSQL = NetezzaSQL


[Driver]
DriverManagerEncoding=UTF16

[NetezzaSQL]
;Path of a driver used for database connection
DriverManagerEncoding=UTF16
Driver                = /usr/local/nz/lib/libnzsqlodbc3.so

;A decription used to identify the database connection properties.
Description           = NetezzaSQL ODBC

;The name of a machine or IP address where the database is located.
Servername            = serverIP

;The port number to connect.
Port                  = 5480

;The name of a database.
Database              = database

;The name of a valid user.
Username              = username

;The password for  the user.
Password              = password

;Only Select SQL statements will be allowed if this field is checked.
ReadOnly              = false

;When this option is enabled and the application bindtype is same as backend
;datatype the query performance will be faster.Query performance gain will be achieved
;only for single select statements (not for batch queries).It also works in case when
;application bind type is different than backend datatype but there are some restrictions on it.
FastSelect            = false

;When set to true, system tables will be included in the available table list.
ShowSystemTables      = false

;When set to true, use old (2.5) functionality for returning schemas in SQLTables.
LegacySQLTables       = false

;Specifies login timeout in seconds.
LoginTimeout          = 0

;Specifies query timeout in seconds.
QueryTimeout          = 0

;Specifies date format as follows -
; 1: YMD
; 2: MDY
; 3: DMY
DateFormat            = 1

:When selected, driver treats SQL_C_NUMERIC buffers as SQL_C_CHAR buffers.
NumericAsChar         = false

;Return SQL_BIT as "1"/"0" when char.
SQLBitOneZero         = false

;When enabled, replaces all occurences of CRLF, CR and LF with a single space.
StripCRLF             = false

;Specifies level of security on the system
securityLevel         = preferredUnSecured

;Name of the Root CA certificate.
caCertFile            =

;Specifies maximum errors allowed. 0 value means no limit on loadMaxErrors value.
;loadMaxErrors         = 1

;If value is true, it rounds the data of numeric columns whose precision exceeds the precision defined.
;loadRoundFraction     = 0


[ODBC]
IANAAppCodePage=4
InstallDir=/opt/odbc32v51
Trace=1
TraceDll=/opt/odbc32v51/lib/odbctrac.so
TraceFile=odbctrace.out
UseCursorLib=0

odbcinst.ini: (located in /root/.odbcinst.ini)

[ODBC Drivers]
NetezzaSQL = Installed


[Driver]
DriverManagerEncoding=UTF16


[NetezzaSQL]
Driver           = /usr/local/nz/lib/libnzsqlodbc3.so
Setup            = /usr/local/nz/lib/libnzsqlodbc3.so
DriverManagerEncoding=UTF16
APILevel         = 1
ConnectFunctions = YYN
Description      = Netezza ODBC driver
DriverODBCVer    = 03.51
DebugLogging     = false
LogPath          = /tmp
UnicodeTranslationOption = utf8
CharacterTranslationOption = all
PreFetch         = 256
Socket           = 16384

unixODBC config:

./odbc_config --cflags

-DHAVE_UNISTD_H -DHAVE_PWD_H -DHAVE_SYS_TYPES_H -DHAVE_LONG_LONG -DSIZEOF_LONG_INT=8 -I/usr/local/include

I have lost the ability to generate an trace log. You can see Trace=1, and it was previously generating the log (this is running in a docker container). But I was seeing: " UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'"

Any help will be much appreciated.

[qqbuby]

@bigmacd For your problem, I think this issue should be reopened.

[danmosemsft]

@bigmacd could you please open a new issue as this does not seem related to the previous one? I will close this.

@qqbuby with respect to unhelpful errors of the form ERROR [H] [unixODBC][....] .. it looks like this information comes essentially from the ODBC driver, in this case unixODBC, via the SQLGetDiagRec call. (Doc for Windows version: https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetdiagrec-function). I am guessing System.Data.Odbc can't do better. In particular one thing that is odd in ERROR [H] [unixODBC][.... is the H which is the SQLState which should be 5-characters. This looks to be passed back verbatim from unixODBC. Perhaps if you ask on the unixODBC mailing list, they may have a suggestion about that. Without more information though, it does not seem an issue in System.Data.Odbc.

[SayHelloBriggs]

I got the same error. " UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'"
I am connect to an Informix DB
try to do this
vi /etc/odbc.ini
add this

[ODBC]
UNICODE=UCS-2