1

I am trying to connect to a Sybase ASE 15 database on windows using sqlalchemy (1.0.9) and pyodbc. If I use a DNS url everything works as expected:

url = r'sybase+pyodbc://usename:password@dns'
engine = create_engine(url, echo=True)
Session = sessionmaker(bind=engine)
sess = Session()
conn = sess.connection()

However, if I avoid the DNS I get an error message:

url = 'sybase+pyodbc://username:password@host:port/database?driver=Adaptive Server Enterprise'

, I get an error:

DBAPIError: (pyodbc.Error) ('01S00', '[01S00] [SAP][ASE ODBC Driver]Invalid port number (30011) (SQLDriverConnect)')

The port number is correct and it is the same port as specified in the DNS.

Any ideas?

user2579685
  • 319
  • 9
  • 19

1 Answers1

2

It may be worth trying to work with some version of the semicolon-separated DSN-less format used by pyODBC (and ODBC in general). Some examples here:

http://www.connectionstrings.com/adaptive-server-enterprise-odbc-driver/

This question tackles a similar issue with FreeTDS, but the concept is the same, as the connect string is basically passed through to the low-level ODBC connect:

SqlAlchemy equivalent of pyodbc connect string using FreeTDS

The URL is being parsed down to this type of string for ultimate connection by pyodbc through to SQLDriverConnect (in the ODBC API), so specifying the ASE ODBC DSN-less connection string directly may work better.

Update: Ran a quick test to see what connect arguments are produced for this URL: 

from sqlalchemy.engine.url import *
from sqlalchemy.connectors.pyodbc import *

connector =  PyODBCConnector()
url = make_url("sybase+pyodbc://username:password@host:5555/database?driver=Adaptive Server Enterprise")
print connector.create_connect_args(url)

This results in: 

[['DRIVER={Adaptive Server Enterprise};Server=host,5555;Database=database;UID=username;PWD=password'], {}]

Note that the hostname and port are separated by a comma, per http://www.connectionstrings.com/adaptive-server-enterprise-odbc-driver/tds-based-odbc-driver-from-sybase-ocs-125/, this format works for TDS-based ODBC for Sybase 12.5: 

Driver={Sybase ASE ODBC Driver};NetworkAddress=myServerAddress,5000;
Db=myDataBase;Uid=myUsername;Pwd=myPassword;

However, the ASE 15 format (http://www.connectionstrings.com/adaptive-server-enterprise-odbc-driver/adaptive-server-enterprise-150/) specifies server=myServerAddress;port=myPortnumber with port as a key passed in the semicolon-delimited string: 

Driver={Adaptive Server Enterprise};app=myAppName;server=myServerAddress;
port=myPortnumber;db=myDataBase;uid=myUsername;pwd=myPassword;

If you "cheat" on the port spec by using host;port=5555, you get:

[['DRIVER={Adaptive Server Enterprise};Server=host;port=5555;Database=database;UID=username;PWD=password'], {}]

But this just feels like a Bad Idea™, even if it works. I'd also note that the generated string is using Database as the key vs. Db in the Sybase connection string reference. This may prove to be an issue as well.

Using ?odbc_connect as in the linked question is probably your best option for controlling the exact connect arguments being sent to ODBC.

Community
  • 1
  • 1
Will Hogan
  • 909
  • 4
  • 9
  • Thanks @will. Going to take more detailed look/test later this afternoon. A little annoying as not so clean and code becomes less generic. With special handling for Sybase. What do you think of Creator method suggested in the question you attached? – user2579685 Nov 15 '15 at 09:19
  • Will use the first method you suggest (the Bad Idea!). The second did not appear to work. I could do also remove port from the string and do the following: connect_args = {'port': port} engine = create_engine(url, echo=False, connect_args=connect_args) – user2579685 Nov 15 '15 at 11:35
  • That seems reasonable. – Will Hogan Nov 15 '15 at 14:59