PyODBC connection issues

[gaelforce]

This is starting to bug me, I have been looking at other ways to connect using the 64-bit ODBC via Python, since the previous method using SSIS stopped working. I am trying to connect in via ODBC, which works just fine in Excel, but I want to automate this better as well as build other things off of the Greentree JADE backend. Once I solve this, I should be able to get motoring on, just falling down at the first hurdle.

I am using the following to connect. I store the username and password in another file, which is not part of source control.

============================================
import pyodbc
import credentials as c

connString = "DRIVER={driver};DSN=gt-user;SCHEMA=LoginSchema;UID={uid};PWD={pwd};INI=C:\\GTClient\\Bin\\jade.ini;".format( driver="{Jade
thin-client ODBC Driver}",uid=c.uid,pwd=c.pwd)
conn = pyodbc.connect(connString)
=============================================

The error that I get when running is

============================================
Exception has occurred: Error
('HY000', "[HY000] [Jade Software Corporation][JADE ODBC Thin Client] Schema '' does not exist (8335) (SQLDriverConnect); [HY000] [Jade Software Corporation][JADE ODBC Thin Client] Schema '' does not exist (8335)")
File "C:\Users\Sarah.Smith\Documents\external\internal_application_misc\JadeDB\connect.py", line 6, in <module>
conn = pyodbc.connect(connString)
============================================


I am getting the following in the jommsg log

============================================
2021/05/03 16:24:22.924 205dc-2181c JomLog: ODBC driver: DSN={Jade thin-client ODBC Driver} Driver Location: C:\GTClient\Bin
2021/05/03 16:24:22.941 205dc-2181c ODBCDriver: ODBCConnect: serverHost=, serverPort=, protocolFamily=2
2021/05/03 16:24:22.943 205dc-2181c ODBCDriver: ODBCConnect: schema=, view=, keywords=
2021/05/03 16:24:22.944 205dc-2181c JomLog: ODBC Error: Schema is null string.
2021/05/03 16:24:22.945 205dc-2181c JomLog: ODBC driver: DSN={Jade thin-client ODBC Driver} Driver Location: C:\GTClient\Bin
2021/05/03 16:24:22.947 205dc-2181c ODBCDriver: ODBCConnect: serverHost=, serverPort=, protocolFamily=2
2021/05/03 16:24:22.948 205dc-2181c ODBCDriver: ODBCConnect: schema=, view=, keywords=
2021/05/03 16:24:22.949 205dc-2181c JomLog: ODBC Error: Schema is null string.
============================================

Why is it a null string? I thought I was specifying it? Where am I supposed to specify this?

As an addendum, I am getting the exact same behaviour when using C#/.NET 3.1 LTS. I have also attempted to use the full Jade ODBC Driver, but it spits out that 8327 error in Python and C#, and Excel can't use it either, so sticking with the thin-client unless there's an easier fix. Registry is pointing to the right locations for the driver, it's in C:\GTClient\Bin\JADODBC.DLL

[BeeJay]

I'm not sure about specifying everything via a connection string. When I've configured up a Jade thin client ODBC connection previously I've always created a DSN via the Windows ODBC applet, as per the following screen shot:

ODBC_Setup.png (9.18 KiB) Viewed 3571 times

This allows you to specify the name DSN/Server name, the TcpIp port number the GT thin client ODBC background application is listening on, the schema name, the name of the relational view within GT you want to use for the queries, etc. You may want to try this yourself, ensuring you use the 32bit version of the Windows ODBC applet to define the DSN.

Hope that helps,
BeeJay.
Any thoughts expressed are my own and in no way reflect the views of my employer. If this reply includes any sample code, or other changes such as registry key settings, I believe that this information is accurate and reliable and has been prepared with care but give no guarantee that the sample code or suggested changes will be free of defects or errors. No responsibility or liability, financial or otherwise, is accepted for any consequences arising out of the use of any sample code including loss of profits, indirect, special or consequential losses or damages.

[gaelforce]

This ties into the other topic I posted. I can't get the 32-bit ODBC driver for the version of JADE I am trying to connect to. Below is my current config that works in Excel. I am specifying this connection name in my connection string.

2021-05-07 08_33_00-Window.png (55.43 KiB) Viewed 3562 times

I have tried to specify this DSN, changed it to something without spaces, it's not being picked up? I'm puzzled.