Connecting to SAP ASE fails

sap-ase-16unixodbc

ALL,

igor@IgorReinCloud ~ $ cat /etc/unixODBC/odbc.ini 
[Sybase16]
Driver=Sybase
Description=Sybase 16 Native Driver
Trace=Yes
Server=REINCLOUD
Database=draft
TraceLog=/tmp/sql.log
Port=5000
UserID=sa

igor@IgorReinCloud ~ $ cat /etc/unixODBC/odbcinst.ini 
[ODBC]
Trace=yes
TraceFile=/tmp/sql.log

[Sybase]
Description=Native Sybase Driver
Driver=/opt/sap/DataAccess/ODBC/lib/libsybdrvodb.so
FileUsage=1

igor@IgorReinCloud ~ $ ls -la /opt/sap/DataAccess/ODBC/lib/
total 8388
drwxrwxr-x 3 root root    4096 Nov 15 17:46 .
drwxrwxr-x 6 root root    4096 Nov 15 17:46 ..
-rwxr-xr-x 1 root root 4691399 Aug 28  2017 libsapcrypto.so
-rwxr-xr-x 1 root root  416329 Aug 28  2017 libslcryptokernel.so
-rwxr-xr-x 1 root root     166 Aug 28  2017 libslcryptokernel.so.sha256
-rwxr-xr-x 1 root root 3459940 Aug 28  2017 libsybdrvodb.so
drwxrwxr-x 5 root root    4096 Nov 15 17:46 locales

igor@IgorReinCloud ~ $ isql Sybase16 sa <pwd>
[ISQL]ERROR: Could not SQLConnect

igor@IgorReinCloud ~ $ cat /tmp/sql.log
[ODBC][15915][1609181009.277918][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x55eac5c98a00
[ODBC][15915][1609181009.278029][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x55eac5c98a00
[ODBC][15915][1609181009.278075][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x55eac5c99b90
[ODBC][15915][1609181009.278132][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLConnect.c][3751]
        Entry:
            Connection = 0x55eac5c99b90
            Server Name = [Sybase16][length = 8 (SQL_NTS)]
            User Name = [sa][length = 2 (SQL_NTS)]
            Authentication = [******][length = 6 (SQL_NTS)]
        UNICODE Using encoding ASCII 'UTF8' and UNICODE 'UTF16LE'

[ODBC][15915][1609181009.281022][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLConnect.c][1138]Can't open lib '/opt/sap/DataAccess/ODBC/lib/libsybdrvodb.so' : file not found
[ODBC][15915][1609181009.281164][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x55eac5c99b90
[ODBC][15915][1609181009.281215][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]
[ODBC][15915][1609181009.281259][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x55eac5c98a00

I don't understand this error.

File does exist, it has read/execute permissions and AFAIU, I don't need to set sybase environment to get an ODBC connection from unixODBC isql.

Connecting with SAP DBISQL works fine.

Could someone please help?

Thank you.

EDIT:

After fixing couple of bugs in my code, here is the odbc log file after running my code.

I'm trying to connect to SAP ASE 16 instance.

[ODBC][30289][1612492650.771991][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x564ee933ab90
[ODBC][30289][1612492650.772100][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x564ee933ab90
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = 0
[ODBC][30289][1612492650.772141][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLSetEnvAttr.c][381]
        Exit:[SQL_SUCCESS]
[ODBC][30289][1612492650.772187][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLDataSourcesW.c][122]
        Entry:
            Environment = 0x564ee933ab90
[ODBC][30289][1612492650.782200][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLDataSourcesW.c][323]
        Exit:[SQL_SUCCESS]
[ODBC][30289][1612492650.782328][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLDataSourcesW.c][122]
        Entry:
            Environment = 0x564ee933ab90
[ODBC][30289][1612492650.782809][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLDataSourcesW.c][323]
        Exit:[SQL_SUCCESS]
[ODBC][30289][1612492650.782879][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLDataSourcesW.c][122]
        Entry:
            Environment = 0x564ee933ab90
[ODBC][30289][1612492650.783125][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLDataSourcesW.c][323]
        Exit:[SQL_SUCCESS]
[ODBC][30289][1612492650.783171][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x564ee933ab90
[ODBC][30289][1612492666.505105][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x564ee9426000
[ODBC][30289][1612492666.505220][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x564ee9426000
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = -6
[ODBC][30289][1612492666.505261][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLSetEnvAttr.c][381]
        Exit:[SQL_SUCCESS]
[ODBC][30289][1612492666.505302][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x564ee9426000
[ODBC][30289][1612492666.505348][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x564ee9236e00
[ODBC][30289][1612492666.505390][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLDataSourcesW.c][122]
        Entry:
            Environment = 0x564ee9426000
[ODBC][30289][1612492666.505718][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLDataSourcesW.c][323]
        Exit:[SQL_SUCCESS]
[ODBC][30289][1612492666.505785][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLSetConnectAttrW.c][332]
        Entry:
            Connection = 0x564ee9236e00
            Attribute = SQL_ATTR_LOGIN_TIMEOUT
            Value = 0x5
            StrLen = 0
[ODBC][30289][1612492666.505828][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLSetConnectAttrW.c][623]
        Exit:[SQL_SUCCESS]
[ODBC][30289][1612492666.505875][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x564ee9236e00
            Window Hdl = 0x564ee9268260
            Str In = [DSN=Sybase16;Driver=Sybase;UID=sa;PWD=wasqra][length = 44 (SQL_NTS)]
            Str Out = 0x564ee947d5a0
            Str Out Max = 1024
            Str Out Ptr = 0x7fffced2eb98
            Completion = 0
        UNICODE Using encoding ASCII 'UTF8' and UNICODE 'UTF16LE'

[ODBC][30289][1612492666.522916][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLDriverConnectW.c][699]
        Exit:[SQL_ERROR]
[ODBC][30289][1612492666.523100][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLGetDiagRecW.c][516]
        Entry:
            Connection = 0x564ee9236e00
            Rec Number = 1
            SQLState = 0x7fffced2e710
            Native = 0x7fffced2e6c4
            Message Text = 0x7fffced2e740
            Buffer Length = 1024
            Text Len Ptr = 0x7fffced2e6bc
[ODBC][30289][1612492666.523238][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLGetDiagRecW.c][563]
        Exit:[SQL_SUCCESS]
            SQLState = [01S00]
            Native = 0x7fffced2e6c4 -> 30011
            Message Text = [[SAP][ASE ODBC Driver]Invalid port number]
[ODBC][30289][1612492666.523381][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLGetDiagRecW.c][516]
        Entry:
            Connection = 0x564ee9236e00
            Rec Number = 2
            SQLState = 0x7fffced2e710
            Native = 0x7fffced2e6c4
            Message Text = 0x7fffced2e740
            Buffer Length = 1024
            Text Len Ptr = 0x7fffced2e6bc
[ODBC][30289][1612492666.523429][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLGetDiagRecW.c][563]
        Exit:[SQL_NO_DATA]
[ODBC][30289][1612492670.055754][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLGetConnectAttrW.c][236]
        Entry:
            Connection = 0x564ee9236e00
            Attribute = 1209
            Value = 0x7fffced310f8
            Buffer Length = 0
            StrLen = 0x7fffced310f0
[ODBC][30289][1612492670.055861][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLGetConnectAttrW.c][325]Error: 08003
[ODBC][30289][1612492670.055930][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLGetDiagRecW.c][516]
        Entry:
            Connection = 0x564ee9236e00
            Rec Number = 1
            SQLState = 0x7fffced30c70
            Native = 0x7fffced30c24
            Message Text = 0x7fffced30ca0
            Buffer Length = 1024
            Text Len Ptr = 0x7fffced30c1c
[ODBC][30289][1612492670.055998][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLGetDiagRecW.c][563]
        Exit:[SQL_SUCCESS]
            SQLState = [08003]
            Native = 0x7fffced30c24 -> 0
            Message Text = [[unixODBC][Driver Manager]Connection not open]
[ODBC][30289][1612492670.056064][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLGetDiagRecW.c][516]
        Entry:
            Connection = 0x564ee9236e00
            Rec Number = 2
            SQLState = 0x7fffced30c70
            Native = 0x7fffced30c24
            Message Text = 0x7fffced30ca0
            Buffer Length = 1024
            Text Len Ptr = 0x7fffced30c1c
[ODBC][30289][1612492670.056107][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLGetDiagRecW.c][563]
        Exit:[SQL_NO_DATA]
[ODBC][30289][1612492670.056158][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x564ee9236e00
[ODBC][30289][1612492670.056209][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]
[ODBC][30289][1612492670.056253][/var/tmp/portage/dev-db/unixODBC-2.3.5-r1/work/unixODBC-2.3.5/DriverManager/SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x564ee9426000

Asking around I've been told that I need to modify the interfaces file so that the clients will not be using it but instead will be using ODBC interface.

This is my interfaces file:

REINCLOUD
    master tcp ether localhost 5000
        query tcp ether localhost 5000


REINCLOUD_BS
    master tcp ether localhost 5001
        query tcp ether localhost 5001


REINCLOUD_XP
    master tcp ether localhost 5002
        query tcp ether localhost 5002


REINCLOUD_JSAGENT
        master tcp ether localhost 4900
        query tcp ether localhost 4900

Sorry for such a huge post.

Could anyone explain what is going on and why I can't connect neither with isql, nor with my program?

Best Answer

ODBC logging is a tad verbose. Digging into it, we find this:

Str In = [DSN=Sybase16;Driver=Sybase;UID=...
Exit:[SQL_ERROR]

OK, so your connection is failing. Luckily, ODBC itself kindly goes off and tries to find out why ...

Message Text = [[SAP][ASE ODBC Driver]Invalid port number]

So you failed to specify the port number needed to make the connection.
If [ever] in doubt, refer to ConnectionStrings.com.

DSN=Sybase16;Driver=Sybase;UID=sa;PWD=********

Thank you for sharing your sa password with us. Go and change it - immediately!

Then:

  • Create a dedicated account for your application to use,
  • Grant that account suitable permissions for the application to work, and
  • Stop using the sa account for anything other than cleaning up the mess made by other people/processes.

Always keep the biggest and best tools in the toolbox for yourself!