1

When using adodbapi 2.6 package on Windows 7, Python will crash (generate a "Python has stopped working" popup window) when more than one parameter is passed to a SQL query. I am trying to pass parameters to a ADO connection cursor to query an SQL Server Compact database (.sdf).

I have also tried normal string formatting which works fine but is a security flaw according to many stackoverflow discussions.

My code is as follows:

import adodbapi as ado

#connection provider
provider = "Microsoft.SQLSERVER.CE.OLEDB.4.0"
#database location path
filePath = "results.sdf"
#max size of database file in MB
maxSize = "4000"

#combine into connection string
connectionString = ("Provider = {}; Data Source = {}; SSCe:Max Database Size = {}"
                         .format(provider, filePath, maxSize))

#create connection
dbConnection = ado.connect(connectionString)

#create cursor
cursor = dbConnection.cursor()

cursor.execute("--THE QUERIES DESCRIBED BELOW--")  #**** location of fault****

print(cursor.fetchall().ado_results)

cursor.close()

Passing one parameter works fine, eg:

cursor.execute("SELECT MeasurementId, Decimals FROM Compound WHERE MeasurementId >= ?", (11774,))

And performing a non-parameterized query works fine, eg:

cursor.execute("SELECT MeasurementId, Decimals FROM Compound WHERE (MeasurementId >= 11774 AND MeasurementId <= 11779)")

But when the EXACT same query is attempted with two parameters, it crashes python with no error message generated in the editor, eg:

cursor.execute("SELECT MeasurementId, Decimals FROM Compound WHERE (MeasurementId >= ? AND MeasurementId <= ?)", (11774, 11779))

When running the file from the command prompt (in Windows 7), this generates a popup window claiming "Python has stopped working" with a bunch of info including:

Problem Event Name      BEX64 
Application Name:       python.exe
Fault Module Name:      MSVCR90.dll
Fault Module Version:   9.0.30729.6161
Exception Code:         c0000417
OS Version:             6.1.7601.2.1.0.256.4

And then the proccess quits (when tried on JupyterLabs/VSCode it freezes the kernel)

I have tried passing the parameters as a list, using different paramastyles (format & named although I think adodbapi converts this all back to standard qmark formatting), changing the cursor setting, and different multi-parameter queries, all with the same result.

Is this proper use of parameter passing? I know you are not supposed to pass tables or column names but this seems like a correct query that should execute fine.

johnDanger
  • 1,990
  • 16
  • 22
  • Shouldn't it be [ ] ? `cursor.execute("SELECT MeasurementId, Decimals FROM Compound WHERE (MeasurementId >= ? AND MeasurementId <= ?)", [11774, 11779])` – Serg Jul 23 '19 at 17:46
  • "I have tried passing the parameters as a list" – johnDanger Jul 23 '19 at 17:48
  • Also, the form of passing parameters works in the first code example – johnDanger Jul 23 '19 at 17:49
  • The code is in a .py script being run in the IPython console within JupyterLabs. In VSCode it was also in a script being run in IPython through the Python extension. Is there an up to date alternative to adodbapi that I missed? – johnDanger Jul 23 '19 at 20:55
  • Running it in command line (after pip installing adodbapi and pywin32) results in the same behavior. Python version is 3.7.4 – johnDanger Jul 23 '19 at 23:20
  • Looks like error points to the OLEDB .dll for the MSSQL Compact database. Can you test ado with MS Access (`Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\To\Database.accdb;`) querying a table; even MS Excel (`Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\To\Workbook.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";`) querying a sheet `[SELECT * FROM [SheetName$]`? This will isolate if the OLEDB is the issue. – Parfait Jul 24 '19 at 00:26
  • Accessing a ms access database with```"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=madb.accdb"``` can be qmark parameterized with > 1 parameters successfully. So this indicates the issue is with MSSQL Compact connections specifically? Would the bug be in the ```adodbapi``` scripts? – johnDanger Jul 24 '19 at 17:06

0 Answers0