Connection Issues from Python to SAP SQLAnywhere Using ODBC Driver

Oksana_Odoo — Wed, 30 Oct 2024 12:27:16 GMT

I am experiencing connection issues when trying to connect to my SAP SQLAnywhere 17 database from Python using the sqlanydb package (version 1.0.11) and the ODBC driver (libdbodbc17_r.so). The connection works in some contexts but fails in others. Here are the details:

System Information:
- Operating System: Ubuntu 24
- Python Version: 3.10
- SAP SQLAnywhere Version: 17
- ODBC Driver Version: unixODBC 2.3.12
- sqlanydb Library Version: 1.0.11
Error Details:
- When attempting to initialize the connection, the application encounters an error at sqlany_init_ex, indicating a possible issue with the DB API context initialization.
- ValueError: NULL pointer access is displayed in the debug logs when initializing dbcapi.
Environment Configuration:
- ODBCINI=/etc/odbc.ini
- ODBCSYSINI=/etc
- SQLANY17=/opt/sqlanywhere17
- LD_LIBRARY_PATH=/opt/sqlanywhere17/lib64:/opt/sqlanywhere17/lib32:/opt/sqlanywhere17/bin64/jre180/lib/amd64/client:/opt/sqlanywhere17/bin64/jre180/lib/amd64/server:/opt/sqlanywhere17/bin64/jre180/lib/amd64:/opt/sqlanywhere17/bin64/jre180/lib/amd64/native_threads:/opt/sqlanywhere17/lib64:/opt/sqlanywhere17/lib32:/opt/sqlanywhere17/bin64/jre180/lib/amd64/client:/opt/sqlanywhere17/bin64/jre180/lib/amd64/server:/opt/sqlanywhere17/bin64/jre180/lib/amd64:/opt/sqlanywhere17/bin64/jre180/lib/amd64/native_threads:

Relevant paths in /etc/odbcinst.ini and /etc/odbc.ini:

[SQL Anywhere 17]

Description=SAP SQL Anywhere 17 ODBC Driver

Driver=/opt/sqlanywhere17/lib64/libdbodbc17_r.so

Setup=/opt/sqlanywhere17/lib64/libdbodbc17_r.so

[SQLRemote]

Driver=/opt/sqlanywhere17/lib64/libdbodbc17_r.so

ServerName=XXX

DatabaseName=xxxxxx

CommLinks=tcpip(host=xx.xxx.xxx.xxx;port=18163)

UID=xxxxx

PWD=xxxxx

Tried switching between libdbodbc17_r.so and libdbcapi_r.so, but the issue persists.

Additional Observations:

Successful Terminal Connection: I can successfully connect to the database from the terminal using isql after sourcing the sa_config.sh file:
root@computer:/# source /opt/sqlanywhere17/bin64/sa_config.sh

root@computer:/# strace -t -f -o trace_out.txt isql -v SQLRemote xxxxxx xxxxxx

This terminal connection returns a successful connection message, indicating that the database configuration and credentials are correct. The issue appears specific to the Python environment or interaction with sqlanydb.

Requested Assistance:

Could you help identify any potential configuration issues that might cause sqlanydb to fail while isql connects successfully?
Are there known compatibility issues with SQLAnywhere 17, sqlanydb 1.0.11, and unixODBC 2.3.12?
Are additional debugging steps or logs I should review to help identify the issue?

Re: Connection Issues from Python to SAP SQLAnywhere Using ODBC Driver

chris_keating — Thu, 31 Oct 2024 00:10:30 GMT

In the same context that you execute python, I would run ldd on both libdbcapi_r.so and libdbcapi.so to ensure that all libraries are resolved. I would also compare the working and non-working environments for completeness.

Re: Connection Issues from Python to SAP SQLAnywhere Usin...

Oksana_Odoo — Thu, 31 Oct 2024 08:30:29 GMT

Dear Chris_Keating,

Thank you for your prompt response and guidance regarding my issue. I took your advice into account and examined the environment variables and configurations between the working and non-working setups.

This comparison allowed me to identify discrepancies and properly adjust the non-working environment. However, despite these changes, I still faced connection issues and continued to encounter errors when using sqlanydb. To troubleshoot further, I attempted to connect using an alternative library, pyodbc, and successfully established a connection.

Thank you again for your assistance!

Question Re: Connection Issues from Python to SAP SQLAnywhere Usin... in Technology Q&A

Connection Issues from Python to SAP SQLAnywhere Using ODBC Driver

Requested Assistance:

Re: Connection Issues from Python to SAP SQLAnywhere Using ODBC Driver

Re: Connection Issues from Python to SAP SQLAnywhere Usin...