On Wed, Feb 21, 2018 at 7:12 PM, Bob Kline <bkl...@rksystems.com> wrote:
> The solutions (for the two bugs my patch attempted to address) which > come immediately to mind include: .... Here's what I think I've been able to determine at this point. 1. The adodbapi package uses two approaches to the ADO Parameter objects needed for operations with placeholders. The approach adopted currently for stored procedure invocation tries to use the Parameter objects created by the underlying provider, based on the definition of the column associated with the placeholder. For operations which are not invoking stored procedures (and for stored procedures where the provider fails to create Parameter objects) the package creates the Parameter objects itself, based not on the column definitions associated with the placeholders, but instead on the values being plugged in for those placeholders. 2. Creating Parameter objects based on the values being passed for an operation sometimes results in failure or an incorrect result. For example, trying to pass None in the values list for a column defined as "NTEXT NULL" fails on SQL Server with an exception complaining "Operand type clash: int is incompatible with ntext." For another example, passing an empty string ("") for a VARCHAR column results in a single blank character being stored on SQL Server. 3. Some providers do a better job than others at figuring out what the ADO data type code should be for a given column definition. For example, the SQLOLEDB provider, used to talk to Microsoft's SQL Server, selects adVarBinary for a column defined as varbinary(5), whereas the Microsoft ACE provider, connected to an Access database, thinks the correct type is adVarWChar for the same column definition, with the result that an incorrect value is stored. The ODBC 5.3(w) driver for MySQL has a similar problem (worse, actually, as it discards information). 4. Running against MS SQL Server and PostgreSQL (I've completed the setup of my test bed for the package), all tests in the unit test suite pass using either approach to ADO Parameter objects. Running the suite against an Access or MySQL database, one test fails when the package uses the Parameter objects created by the ADO layer (because of the problem described immediately above), but passes when the package creates its own Parameter objects. All of the other tests pass for Access. As I reported earlier in this thread, the test of storing a value in a 'bit' column fails when running the suite against a MySQL database no matter which approach to Parameter objects is used. 5. These observations seem to lead to the conclusion that we need one approach to Parameter object handling when connected to SQL Server and a different approach when we're talking to MySQL or an Access database (or at least when using the driver/providers with which I've been testing). PostgreSQL is the only DBMS which appears to work correctly with either approach. 6. The package already has an extension attribute (dbms_name) on the connection object identifying which DBMS we're connected to. My recommendation would be to use that information to decide which approach to take to Parameter objects, using the existing Parameter Objects if the DBMS name is "Microsoft SQL Server" or if (preserving the current behavior) the operation is the invocation of a stored procedure. If you agree, I'll come up with a new pair of pull requests to address the two bugs describe above in paragraph #2. 7. If it is unacceptable to change the current behavior silently for connections to SQL Server, we would need a way for the programmer to tell the package which approach is needed for handling Parameter objects. The simplest way would be to have a module-level variable (similar to the dateconverter variable, which is documented as something the user can override). However, this would be awkward for a program which needed to communicate with two different databases. Perhaps a connection-level extension would be more appropriate, with an optional keyword argument passed to the connect() function. Presumably the default should be to keep the current behavior to avoid introducing new breakage for existing code (though I suspect most of the use of the package is for talking to SQL Server, possibly by a large margin). When you come up for air, Vernon, I'll be grateful for any corrections you can provide for any errors I've made in this analysis. One thing I should point out is that some of what I've written here is based on the results of the regression test suite, and I can see that there are places where certain of those tests are altered or even suppressed at run time depending on which engine is in play. It's conceivable that had the package always used Parameter object created internally by ADO (when available) that tweaking would have been done differently, and there would be no failures or errors from the test suite for those objects. -- Bob Kline http://www.rksystems.com mailto:bkl...@rksystems.com _______________________________________________ python-win32 mailing list python-win32@python.org https://mail.python.org/mailman/listinfo/python-win32