2

I've spent a few days trying to determine how to connect to a Sybase IQ database through Python 3.6. I've tried pyodbc and pymssql, to no avail. Below are two code snippets that I've been working on, which don't seem to work, no matter what I try.

pyodbc:

conn = pyodbc.connect(driver='{SQL Server Native Client 11.0}',
                          server=server,
                          database=database,
                          port=port,
                          uid=user,
                          pwd=pwd)

pymssql:

conn = pymssql.connect(server=server,
                        port=port,
                        user=user,
                        password=pwd,
                        database=database)

I've also read that FreeTds could be the solution for connecting to a Sybase IQ database; I thought it was installed as part of the pymssql database, but I can't seem to figure out how to leverage it. Any help would be greatly appreciated!

EDIT: I am aware that sqlanydb exists; however, this package makes me downgrade to Python 2.7. My stack is 3.6 and I'd like to not have to move off of that.

xtheking
  • 575
  • 2
  • 8
  • 31
  • 1
    MSSQL client drivers or ODBC drivers will not work for Sybase databases the TDS packet structure is completely different. See this link for more info http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01776.1600/doc/html/san1357754966211.html – Rich Campbell Dec 11 '18 at 15:11
  • I've seen this `sqlanydb` package come up a few times ... Does it actually work? I've tried installing it on Anaconda and it broke my environment. I should also note that the link within Python support for Windows is dead. – xtheking Dec 11 '18 at 15:14

3 Answers3

1

After some time, I was able to resolve this issue (On Windows). First, install SQL Anywhere 17 driver. Once that's been installed, in the Windows ODBC Data Sources window, set up a connection using the SQL Anywhere 17, and your Sybase IQ credentials. Once that has been configured and successfully tested, you can use the below code snippet to connect:

from sqlalchemy import create_engine

sybase_connection_string = "sqlalchemy_sqlany://{user}:{pwd}@{host}:{port}/{db}".\
        format(user=user, pwd=pwd, host=host, port=port, db=database)
engine = create_engine(sybase_connection_string)
return engine.connect()

I believe you will need the sqlalchemy_sqlany module installed via pip, as well as sqlalchemy.

xtheking
  • 575
  • 2
  • 8
  • 31
0

Alternative use jconn4 or jconn3 driver. Example of connection:

import jaydebeapi

jar_path = "/drive/jconn4.jar"
driver_name = "com.sybase.jdbc4.jdbc.SybDriver"

_ipad = '1.1.1.1'
_port='2638'

con_prop= { "user": 'user', "password": 'pwd'}

connection_url = f"jdbc:sybase:Tds:{_ipad}:{_port}"

conn= jaydebeapi.connect(driver_name, connection_url,con_prop, jar_path)
chrslg
  • 9,023
  • 5
  • 17
  • 31
Gerrit
  • 11
  • 1
  • 3
0

You can use jconn4.jar to connect to Sybase IQ. I was able to connect with SAP IQ/16.1.080.1841

To get jconn4.jar, use dbeaver and connect with sybase batabase. Dbeaver will download this jar, which you can use. You can download community edition from official site https://dbeaver.io/

This will require JAVA, to get this running. I used JDK 1.8.0_181

Install jaydebeapi for your python with pip install jaydebeapi.
I used python 3.11.0 and jaydebeapi==1.2.3

Once you have this, connect like below:

import jaydebeapi

jconn4_file_path = '<path/to/jconn4.jar>'
driver = 'com.sybase.jdbc4.jdbc.SybDriver'
db_server = '<server hostname>'
db_port = <port>
db_user = '<database username>'
db_password = '<database password>'
db_name = '<database name>'

connection_string = f'jdbc:sybase:Tds:{db_server}:{db_port}?ServiceName={db_name}'

connection = jaydebeapi.connect(
                  driver,
                  connection_string,
                  [db_user, db_pass],
                  jconn4_file_path
             )
phoenixnitin
  • 29
  • 3