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
