other db tests

[ndmlny-qs]

create tests for as many of the below dbs as possible

• infromix
• spark
• azure sql
• sqlite
• excel

Originally posted by @ndmlny-qs in #1253 (comment)

[CrasCris]

Do you know if it possible to connect with SSAS ?

[bkline]

@ndmlny-qs Your original comment in the discussion forum said

... I can create tickets and start working on them.

I'd be happy to pitch in to help with some of these, but I don't want to step on anyone's toes or duplicate work you already have in progress. What's your status with this ticket? Were you still planning to create separate tickets for each back end, or did you decide to drive everything from this ticket?

Looks like we can check off sqlite as it appears those tests have already been ported.

[bkline]

Still no response from the OP, so I'm going to tackle this ticket myself. Don't think I have sufficient privileges to assign it to myself, though, so this message is all I can do on that front, as far as I am aware.

[bkline]

#1492 is in.

• Informix (complete rewrite, as the existing version appeared to be an unedited copy/paste from another test suite)
• Spark (ported but untested, as I don't have access to the driver)
• Azure DW (dropped as it looks like an older version of the SQL Server test suite)¹
• SQLite (had already been ported)
• Excel (added parallel testing of *.xls and *.xlsx workbooks)²
• Access (added parallel testing of *.mdb and *.accdb files)

Footnotes

1. I looked for any clues, comments, etc., indicating special handling for DW/Synapse Analytics but didn't find any. ↩

2. Still read-only. I can get the driver to create and populate tables in a new workbook, but Excel reports that the resulting file is corrupted. ↩

[bkline]

As a bonus, here's a patch that will make sqlserver_test.py run (and pass) against an Azure DB. Two changes were needed:

• a replacement for xp_msver (not available with Azure)
• a retry loop (to get around the auto pause state)

diff --git a/tests/sqlserver_test.py b/tests/sqlserver_test.py
index 6feee9e..f230ecd 100755
--- a/tests/sqlserver_test.py
+++ b/tests/sqlserver_test.py
@@ -8,6 +8,7 @@ from collections.abc import Iterator
 from decimal import Decimal
 from datetime import date, time, datetime
 from functools import lru_cache
+from time import sleep
 
 import pyodbc
 import pytest
@@ -28,7 +29,16 @@ CNXNSTR = os.environ.get('PYODBC_SQLSERVER', 'DSN=pyodbc-sqlserver')
 
 
 def connect(autocommit=False, attrs_before=None):
-    return pyodbc.connect(CNXNSTR, autocommit=autocommit, attrs_before=attrs_before)
+    attempt = 0
+    while True:
+        try:
+            return pyodbc.connect(CNXNSTR, autocommit=autocommit, attrs_before=attrs_before)
+        except pyodbc.OperationalError:
+            if attempt < 10:
+                attempt += 1
+                sleep(5)
+            else:
+                raise
 
 
 DRIVER = connect().getinfo(pyodbc.SQL_DRIVER_NAME)
@@ -48,8 +58,8 @@ def _get_sqlserver_year():
         return 0
     cnxn = connect()
     cursor = cnxn.cursor()
-    row = cursor.execute("exec master..xp_msver 'ProductVersion'").fetchone()
-    major = row.Character_Value.split('.', 1)[0]
+    ver = cursor.execute("select cast(serverproperty('ProductVersion') as varchar(255))").fetchval()
+    major = ver.split(".")[0]
     return {
         # https://sqlserverbuilds.blogspot.com/
         '8': 2000, '9': 2005, '10': 2008, '11': 2012, '12': 2014,