Can't connect with Data Virtuality Server - even though the connection via ISQL works

[rainymatch]

Hi everybody,

I am trying to connect to Data Virtuality Server using PyODBC.
I am on Mac M3, I successfully installed the Mac x86 Version of the Data Virtuality ODBC Driver, unixODBC and am able to connect to the Server in Terminal running under Rosetta.

This command works:

isql -v DataVirtualityDSN <USERNAME> <PASSWORD>

However, no matter how hard I try (different conn_str formats, as consulting whatever I found online), I always get the same error when running the following script (x86/ARM universal python venv, Terminal under Rosetta)

arch -x86_64 python3 connection.py                         
Available drivers: ['DataVirtuality']
Available DSNs: ['DataVirtualityDSN', 'dvodbc']
Attempting to connect...
Error connecting to the database: ('0000', '[0000] c (202) (SQLDriverConnect)')

Here the script:

import pyodbc

pyodbc.pooling = False

conn_str = (
    "Driver=DataVirtuality;"
    "Server=XXX.XXX.XXX.XX;"
    "Port=35432;"
    "Database=datavirtuality;"
    "UID=<USERNAME>;"
    "PWD=<PASSWORD>;"

) 

# conn_str = "DSN=DataVirtualityDSN;UID=<USERNAME>;PWD=<PASSWORD>" # also did not work
# conn_str = "DSN=DataVirtualityDSN" # also did not work
try:
    # Driver is available, DSN as well
    print("Available drivers:", [x for x in pyodbc.drivers()])
    print("Available DSNs:", [x for x in pyodbc.dataSources()])
    
    print("Attempting to connect...")
    conn = pyodbc.connect(conn_str)
    print("Connection successful!")
    
    cursor = conn.cursor()
    cursor.execute("SELECT 1")
    result = cursor.fetchone()
    print("Test query result:", result)
    
except pyodbc.Error as e:
    print(f"Error connecting to the database: {e}")

Since the isql command works fine, I am confident that the credentials and connection settings (ip, port, database) are correct.

Anything else I could try?

[gordthompson]

What version of pyodbc are you using? Also, please provide the ODBC trace from a failed connection attempt.

[rainymatch]

thanks for the response!

pyodbc==5.2.0

ODBC trace:

[ODBC][16879][1731073315.583763][__handles.c][493]
		Exit:[SQL_SUCCESS]
			Environment = 0x7f7f93042800
[ODBC][16879][1731073315.585728][SQLSetEnvAttr.c][192]
		Entry:
			Environment = 0x7f7f93042800
			Attribute = SQL_ATTR_ODBC_VERSION
			Value = 0x3
			StrLen = 4
[ODBC][16879][1731073315.587385][SQLSetEnvAttr.c][384]
		Exit:[SQL_SUCCESS]
[ODBC][16879][1731073315.588971][SQLDrivers.c][235]
		Entry:
			Environment = 0x7f7f93042800
			Direction = 2
[ODBC][16879][1731073315.590702][SQLDrivers.c][510]
		Exit:[SQL_SUCCESS]
[ODBC][16879][1731073315.592493][SQLDrivers.c][235]
		Entry:
			Environment = 0x7f7f93042800
			Direction = 1
[ODBC][16879][1731073315.594175][SQLDrivers.c][510]
		Exit:[SQL_NO_DATA]
[ODBC][16879][1731073315.596155][SQLDataSources.c][203]
		Entry:
			Environment = 0x7f7f93042800
[ODBC][16879][1731073315.602442][SQLDataSources.c][404]
		Exit:[SQL_SUCCESS]
[ODBC][16879][1731073315.604290][SQLDataSources.c][203]
		Entry:
			Environment = 0x7f7f93042800
[ODBC][16879][1731073315.606656][SQLDataSources.c][404]
		Exit:[SQL_SUCCESS]
[ODBC][16879][1731073315.608735][SQLDataSources.c][203]
		Entry:
			Environment = 0x7f7f93042800
[ODBC][16879][1731073315.610957][SQLDataSources.c][404]
		Exit:[SQL_SUCCESS]
[ODBC][16879][1731073315.612944][SQLAllocHandle.c][469]
		Entry:
			Handle Type = 2
			Input Handle = 0x7f7f93042800
[ODBC][16879][1731073315.614506][SQLAllocHandle.c][585]
		Exit:[SQL_SUCCESS]
			Output Handle = 0x7f7f93045200
[ODBC][16879][1731073315.616943][SQLDriverConnectW.c][298]
		Entry:
			Connection = 0x7f7f93045200
			Window Hdl = 0x0
			Str In = [Driver=DataVirtuality;Server=192.168.230.22;Port=35432;Database=datavirtuality;UID=m.hyca;PWD=*******;ODBCVer=3;][length = 112 (SQL_NTS)]
			Str Out = 0x0
			Str Out Max = 0
			Str Out Ptr = 0x0
			Completion = 0
		Threading Level set from Driver Entry in ODBCINST.INI 99 from '99'

		Threading Level set from [ODBC] Section in ODBCINST.INI 0 from '0'

		UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2-INTERNAL'

[ODBC][16879][1731073315.773582][SQLDriverConnectW.c][870]
		Exit:[SQL_ERROR]
[ODBC][16879][1731073315.776422][SQLGetDiagRecW.c][535]
		Entry:
			Connection = 0x7f7f93045200
			Rec Number = 1
			SQLState = 0x3058c1714
			Native = 0x3058c1708
			Message Text = 0x7f7f83008200
			Buffer Length = 1023
			Text Len Ptr = 0x3058c1712
[ODBC][16879][1731073315.778803][SQLGetDiagRecW.c][596]
		Exit:[SQL_SUCCESS]
			SQLState = [0]
			Native = 0x3058c1708 -> 202 (32 bits)
			Message Text = [c]
[ODBC][16879][1731073315.780884][SQLFreeHandle.c][324]
		Entry:
			Handle Type = 2
			Input Handle = 0x7f7f93045200
[ODBC][16879][1731073315.782449][SQLFreeHandle.c][373]
		Exit:[SQL_SUCCESS]

[gordthompson]

This looks similar to #1329 (comment) and #1313 (comment)

Can you try pip install pyodbc==4.0.39 and see if that helps?

[rainymatch]

(under python 3.9.20)
pyodbc 4.0.39, python crashes

Available drivers: ['DataVirtuality']
Available DSNs: ['DataVirtualityDSN', 'dvodbc']
Attempting to connect...
Connection successful!
Python(21861,0x200ca1240) malloc: Incorrect checksum for freed object 0x7f9e95009800: probably modified after being freed.
Corrupt value: 0x6600000066
Python(21861,0x200ca1240) malloc: *** set a breakpoint in malloc_error_break to debug
zsh: abort      arch -x86_64 python connection.py

[gordthompson]

OK, so it looks like downgrading pyodbc to 4.0.39 got you past the .connect() error and now there's something else going on.

I notice that Installing CData Virtuality ODBC (macOS) specifically mentions iODBC. I don't know if trying to use that driver with unixODBC could cause problems for pyodbc. @v-chojas may have some ideas about that.

[rainymatch]

I had some conversations with Data Virtuality support and I was mentioning unixODBC and got no response in the sense that it should come with any extra issues. Also, I can connect to the Server with unixODBC using the isql command. So unixODBC actually works.

isql -v DataVirtualityDSN <USERNAME> <PASSWORD>

I get to a similar stage with iODBC just to realize that building pyodbc with unixODBC is above my skills, that's why I went with unixODBC...

[rainymatch]

@gordthompson
Finally I made it, I had to downgrade pyodbc even further. That pretty much did the trick.
pyodbc==4.0.34