Connect to Sybase IQ server using Python script on Mac M2

Question

I am trying to connect to the Sybase server to pull some data using a Python script running on my Mac M2 chip.

The SAP IQ (aka Sybase IQ) server version that I am trying to connect:

SAP IQ SAP IQ/16.1.040.1841/16220/P/SP04.11/Sun_x64/OS 5.11/64bit/2021-11-01

I was able to successfully connect from a DBeaver client using the JConnect JDBC driver.

https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01776.1604/doc/html/san1357754912881.html

jConnect (TM) for JDBC (TM) jConnect (TM) for JDBC(TM)/16.0 SP03 PL02 (Build 27403)/P/EBF27518/JDK 1.6.0/jdbcmain/OPT/Mon Aug 28 18:41:14 PDT 2017

I am trying to automate some workloads using Python script and want the same connection established from my local machine.

I am using the jaydebeapi library (https://pypi.org/project/JayDeBeApi/) to establish the connection. The connection string looks like:

    jconn4_file_path = ["/Users/abishek.arunachalam/Library/DBeaverData/drivers/drivers/sybase/jconnect/jconn4.jar"]
    driver = "com.sybase.jdbc4.jdbc.SybDriver"
    connection_string = f"jdbc:sybase:Tds:{hostname}:2055?ServiceName={database}"

    conn = jaydebeapi.connect(
                    driver,
                    connection_string,
                    [username, password],
                    jconn4_file_path
                )

Since mine is a M2 machine, I am using zulu-21.jdk(https://docs.azul.com/core/install/macos) version of Java JDK and 3.12 version of Python. I am using the same JConnect JDBC driver path.

For some strange reason, I get the following error that there is no suitable driver found when I try to connect:

Error connecting to Sybase Data Warehouse: java.sql.SQLException: No suitable driver found for jdbc:sybase:Tds:hostname?ServiceName=database_name

I have tried using SQL Server ODBC driver with pyodbc and pymysql but have not been successful due to the limitations of using it with M2 Apple chip. Would appreciate any help with debugging or pointing me in the right direction.

I followed this while connecting using jaydebeapi: Sybase IQ connection in Python

Answer 1

Sometime back, I encountered the same "No suitable driver found for jdbc" error when tasked with analyzing data from a legacy Sybase (SAP ASE 16.0) database. If I recall correctly, some people had suggested that threading issues may cause this error, but no one had a working solution.

I ended up using jpype.dbapi2, which is faster than jaydebeapi.

import jpype
import jpype.dbapi2

hostname = 'host'
port = '2055'
database_name = 'db_name'
database_user = 'username'
database_pass = 'password'

connection_string = f"jdbc:sybase:Tds:{hostname}:{port}/{database_name}"

jpype.addClassPath(r'/path/to/jconn4.jar')
jpype.startJVM()

try:
    conn = jpype.dbapi2.connect(connection_string,
                                driver="com.sybase.jdbc4.jdbc.SybDriver",
                                driver_args={
                                    "user": database_user,
                                    "password": database_pass
                                })

except jpype.dbapi2.Error as err:
    print("Error: " + str(err))

cursor = conn.cursor()
cursor.execute("SELECT TOP 1 * FROM table_name")
result = cursor.fetchall()
print(result)