Use pyodbc instead of pypyodbc

[tombola]

For use with filemaker (18?) on Mac would need to make sure pyodbc uses libiodbc instead of unixodbc.

I got really stuck on this last time, and just went with pypyodbc (and unixodbc uninstalled on Mac), but this is a little brittle for my development environment and also assume there may be some performance advantage using straight pyodbc.

I had another little look at this using chat and it looks worth another go:

https://chatgpt.com/share/67a4cffe-373c-8009-8261-ed85764b5eb3

[tombola]

Still finding same issue. Even with

export ODBC_INCLUDE_DIR=$(brew --prefix libiodbc)/include
export ODBC_LIB_DIR=$(brew --prefix libiodbc)/lib
export LD_LIBRARY_PATH=$ODBC_LIB_DIR:$LD_LIBRARY_PATH
export CPATH=$ODBC_INCLUDE_DIR  # Helps compiler find headers
export LIBRARY_PATH=$ODBC_LIB_DIR  # Helps linker find libraries

still get the following error when i try to build pyodbc:

File "/private/var/folders/6x/91pd09bj41g7mkkr8c9x4d2w0000gn/T/pip-build-env-5haaekaj/overlay/lib/python3.11/site-packages/setuptools/build_meta.py", line 304, in _get_build_requires
          self.run_setup()
        File "/private/var/folders/6x/91pd09bj41g7mkkr8c9x4d2w0000gn/T/pip-build-env-5haaekaj/overlay/lib/python3.11/site-packages/setuptools/build_meta.py", line 320, in run_setup
          exec(code, locals())
        File "<string>", line 179, in <module>
        File "<string>", line 35, in main
        File "<string>", line 135, in get_compiler_settings
      ValueError: not enough values to unpack (expected 2, got 0)

[tombola]

Possibly could check on import and run pyodbc if on Windows, pypyodbc on Mac (or simply if installed). This would potentially give performance advantage and maybe improve connection stability.

Would need Windows test environment, maybe VM.

This would not help adopting pyodbc dependent libraries eg sqlalchemy as development environment unless could pass in pypyodbc in dev situation, eg

connection_string = "mssql+pypyodbc://username:password@DRIVER={SQL Server};SERVER=your_server;DATABASE=your_db"
engine = create_engine(connection_string)

[tombola]

Summary: I think for my use case (production app on windows machine, development on mac) I should give up on trying to get pyodbc to work, though the following could reduce brittleness:

• ensure pypyodbc can use libiodbc on mac even if unixodbc is installed (fork pypyodbc again?)
• use an env var feature flag or operating system to switch import - pyodbc on windows, pypyodbc on mac

[tombola]

Could use a windows VM to test pyodbc access to filemaker on windows.

See vitalseeds/vs-data-api#92

[tombola]

Windows VM is ready to test, so next step would be to simply install pyodbc on the windows machine and switch the import statement.

If it doesn't work close this issue unresolved.