Replace pymssql at the test scripts

[juliogonzalez]

pymssql is not maintained anymore, so we need to think about replacing it with an alternative.

We already saw several issues, the last one adding CI support for CentOS7 when we added support for PostgreSQL13 (see comments at https://github.com/tds-fdw/tds_fdw/pull/261).

But the more we wait, the more problems we will find, when pymssql becomes just incompatible with newer FreeTDS versions, or new Python versions.

We use pymssql to create the MSSQL structures we need to then test tds_fdw. Nothing too complicated, so we should aim for a drop-in replacement if possible.

Doing this will require:

• [ ] Replacing pymssql at the Docker image definitions at https://github.com/tds-fdw/ci-setup, build the images, then publish them
• [ ] Replacing pmyssql at https://github.com/tds-fdw/tds_fdw/blob/master/tests/mssql-tests.py (and maybe https://github.com/tds-fdw/tds_fdw/blob/master/tests/lib/tests.py)

[GeoffMontee]

Hi @juliogonzalez,

pymssql is not maintained anymore, so we need to think about replacing it with an alternative.

That's too bad. Do you have an opinion on which alternative to use?

I've used FreeTDS with ODBC many times, so pyodbc might be a good option, if it isn't too much effort to convert our current code to that.

https://pypi.org/project/pyodbc/

If the pyodbc interface turns out to be too different from pymssql to easily transition, it sounds like python-tds is another option.

https://pypi.org/project/python-tds/

And I guess ctds is another option too.

https://pypi.org/project/ctds/

[juliogonzalez]

Basically I think we need to review the alternatives from https://github.com/pymssql/pymssql/issues/668, but pyodbc looks like a good candidate.

I just need to review the docs of each alternative to see the interfaces, but even if some of them require some changes to our code... we should aim for the one with most contributors and maintenance... so I guess the first option to consider is pyodbc :-)

[GeoffMontee]

If we were to go with pyodbc, I believe that there are two ways to connect:

1.) The data source can be configured in odbc.ini using odbcinst. e.g.:

$ tee jenkins_mssql_dsn.ini <<EOF
[Jenkins-MSSQL-Server]
Description = Jenkins Microsoft SQL Server
Driver = FreeTDS ODBC Driver
Trace = Yes
TraceFile = /tmp/trace.log
ForceTrace = Yes
SERVER=ourserver.database.windows.net
PORT=1433
DATABASE=tds_fdw_jenkins
TDS_VERSION=7.4
ENCRYPTION=request
DUMPFILE = /tmp/freetds.log
DUMPFILEAPPEND = Yes
EOF
$ sudo odbcinst -i -s -l -f jenkins_mssql_dsn.ini

And then the DSN name is used to connect:

cnxn = pyodbc.connect('DSN=Jenkins-MSSQL-Server;UID=mylogin;PWD=mypassword')

https://github.com/mkleehammer/pyodbc/wiki/Drivers-and-Driver-Managers#odbcini

2.) Or we can skip odbc.ini and specify the DSN directly in the connection string:

cnxn = pyodbc.connect('driver=FreeTDS ODBC Driver', host='ourserver.database.windows.net', database='tds_fdw_jenkins', user='admin', password='mypwd', port=1433, tds_version=7.4, encryption='request')

https://github.com/mkleehammer/pyodbc/wiki/The-pyodbc-Module#connect

[juliogonzalez]

Looks like pymssql is back to life :-)

Anyway I did some research:

• pyodbc requires installing an ODBC driver, theodbc.ini config can be skipped. But because of the driver requirement, I'd avoid it.
• pytds is almost a drop-in replacement, that only requires some magic to provision a cafile (for some reason doesn't use those provided by the ssl modile, I wonder if it's because it uses pyOpenSSL), but has a problem: https://github.com/denisenkom/pytds/issues/106 (there's a workaround merged, but not released yet).

So for the time being, let's close the card, and we will see in the future (in case pymssql is discontinued again).