Connect to HANA with unixODBC fails

[Private_Member_145664]

Dear community

I like to connect with PHP to the HANA server, using all information from the treads (http://scn.sap.com/message/13382450#13382450 http://scn.sap.com/thread/2134802 http://scn.sap.com/thread/3204662 )

I tried the following on the HANA box (SLES 10 SP2) itself and from a linux client (openSuse 12.2)

Using unixODBC with isql i get always the following error messages

isql DSN SYSTEM secret -v

openSuse 32Bit: (downloaded from here)

[08S01][unixODBC][SAP AG][LIBODBCHDB32 SO][HDB] Communication link failure;-10709 Connect failed (no reachable host left)

SLES 10SP2 64Bit (download from here)

[08S01][unixODBC][SAP AG][LIBODBCHDB SO][HDB] Communication link failure;-10709 Connect failed (no reachable host left)

The error comes imediatly which gives me the impression, that the ServerNode parameter is not interpreted at all

openSuse 32Bit:

/etc/unixODBC/odbc.ini:

[HANADSN]

Driver=/usr/sap/hanaclient32/libodbcHDB32.so

ServerNode=hanab1:30015

SLES 10SP2 64Bit

/etc/unixODBC/odbc.ini:

[HANADSN]

Driver=/usr/sap/hanaclient/libodbcHDB.so

ServerNode=hanab1:30015

I tried with unixODBC 2.12 and also 2.3. On the HANA host it self I tried with localhost, 127.0.0.1 and the IP address

On the openSUSE Client I tried servername and ip-address.

To use /usr/sap/hdbclient32/odbcreg32 (or /usr/sap/hdbclient/odbcreg) I got first an error message, that the libodbcHDB... was not found.

LD_LIBRARY_PATH=/usr/sap/hdbclient32/ and export LD_LIBRARY_PATH solved that problem

using odbcreg SERVER:PORT NDB SYSTEM sercret works. Connects to HANA and shows now()

Using odbcreg DSN SYSTEM secret does not work, shows the same error as above immediately (not timeout waiting for the connection).

I assume, that the parameter ServerNode= is not interpreted. I have tried Host= / Port= and Server=, but always the same result.

It would be helpfull, when SAP publishes an odbctemplate, that tools like ODBCconfig (X-Windows tool to configure the odbc.ini file) can work.

Thanks

Ingo

[Former Member]

Hi All ,

I have AIX 6.1 server . I have installed HANA client and unixODBC.

hdbsql works

./hdbsql -n 19.151.89.122:30015  -u USERNAME -p PASSWORD

Welcome to the SAP HANA Database interactive terminal.

Type:  \h for help with commands
       \q to quit

hdbsql HA1=>

But i cannot make isql to work

odbcinst -j

unixODBC 2.3.2

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /etc/odbc.ini

SQLULEN Size.......: 4

SQLLEN Size........: 4

SQLSETPOSIROW Size.: 2

cat /etc/odbc.ini

[HANA_TEST]

Driver=/usr/sap/hdbclient/libodbcHDB.so

ServerNode=19.151.89.122:30015

odbcinst HANA -q -s

[HANA_TEST]

odbcinst HANA -q -d

odbcinst: SQLGetPrivateProfileString failed with .

isql -v HANA_TEST USERNAME PASSWORD

[01000][unixODBC][Driver Manager]Can't open lib '/usr/sap/hdbclient/libodbcHDB.so' : file not found

[ISQL]ERROR: Could not SQLConnect

ls -ltr /usr/sap/hdbclient/libodbcHDB.so

-r-xr-xr-x    1 root     system     62742158 Dec 15 16:22 /usr/sap/hdbclient/libodbcHDB.so

Can any one help me how to fix this . I have followed the you tutorial but still it dnt work .

Please let me know if you need any more info.

Thanks in advance .

[Private_Member_145664]

Dear Maruf,

sounds stupid, but try to name the ODBC without "_".

Have you installed 32 bit and 64 bit?

Regards

Ingo

[Private_Member_145664 · Accepted solution]

Success: There is bug in the ODBC Driver. The parameter ServerNode is read from /etc/odbc.ini instead of /etc/unixODBC/odbc.ini. When I copy the file in both locations isql can connect. [I found that using strace -f -eopen isql TESTDSN SYSTEM secret]

Method1 works for isql HANALocDSN SYSTEM secret

UserDNS = ~/.odbc.ini for example /root/.odbc.ini

[HANALocDSN]

Driver=/loc/sap/libodbcHDB.so

ServerNode=hanab1:10015

Method2 works for isql HANADSN SYSTEM secret

SystemDSN

/etc/unixODBC/odbc.ini

[HANADSN]

Driver=/loc/sap/libodbcHDB.so

ServerNode=hanab1:10015

and a copy in /etc/odbc.ini

[HANADSN]

Driver=/loc/sap/libodbcHDB.so

ServerNode=hanab1:10015

[Private_Member_145664 · Accepted solution]

I tested on openSUSE and it works fine. Instead of copying the file I have created a symbolic link in /etc/

[Former Member]

HI ,

i tried  the below command it dnt work coz it AIX may be .

strace -f -eopen isql HANA  USENAME PASS

I tried truss

truss  isql HANA  USENAME PASS.

Please find the attached file for details .

truss_Output.txt.zip

[Private_Member_145664]

In line 534 you see, that /etc/ODBC.ini will be used. Please try my workaround copy ODBC ini there or make a link.

[Former Member]

Hi Ingo ,

Thanks for the reply .

I tried without _  with the ODBC still have the same error .

I guess its 64 bit but not sure .Any more suggestions ?

Thanks

Maruf

[Private_Member_145664]

Hi,

try

strace -f -eopen isql HANATEST SYSTEM secret

to see, why the file is not found.

[lbreddemann]

Hi Ingo,

I take it that you finally managed to get the ODBC connection going. Is that right?

Reading your thread post I agree that currently the developer documentation for JDBC/ODBC is pretty thin and needs to be extended.

However, the thread is marked as a question and I fail to find the question in your post.

So what exactly is it you want to know?

- Lars

[Private_Member_145664]

Hello Lars,

No it does not work. I always get the error message "Communication link failure;-10709 Connect failed (no reachable host left)" trying to use a DSN via ODBC. It has never worked.

Only odbcreg using directly the server name and port works, not via ODBC. This means I cannot use it with php.

Thanks

Ingo

[lbreddemann]

Hmm... don't have a linux box handy right now, but would it work if you put in

servername = hanab1

port = 30015

instead of

serverdb = hanab1:30015

into the odbc.ini file?

At least that would be consistent with the ODBC settings required for MS Windows.

- Lars

[Private_Member_145664]

servername,port serverdb, serverdb/port all does not work.

Windows uses ServerNode server:port

[lbreddemann]

Indeed, you're right about ServerNode.

Must have mixed that up.

[dvankempen]

Hi Lars,

I agree on your observation about the documentation of the ODBC topic.

I will discuss with our colleagues at documentation but for the mean time, I have recorded a video for the SAP HANA Academy on configuring ODBC for Linux (RHEL and SLES). Hope this will provide some clarification.

SAP HANA Installations - SPS 08 - YouTube

Denys van Kempen, SAP HANA Academy