When using placeholder parameters for an INSERT statement, the library replaces an empty string with a single space. The behavior is correct when the value is provided without placeholder parameters:
>>> cursor.execute("create table #foo (s ntext)") >>> cursor.execute("insert into #foo values(?)", ("",)) >>> cursor.execute("insert into #foo values(?)", (u"",)) >>> cursor.execute("insert into #foo values('')") >>> cursor.execute("SELECT * FROM #foo") >>> [tuple(row) for row in cursor.fetchall()] [(u' ',), (u' ',), (u'',)]
The expected behavior is shown here (using another DBAPI implementation):
>>> cursor.execute("create table #foo (s ntext)") >>> cursor.execute("insert into #foo values(?)", ("",)) >>> cursor.execute("insert into #foo values(?)", (u"",)) >>> cursor.execute("insert into #foo values('')") >>> cursor.execute("SELECT * FROM #foo") >>> [tuple(row) for row in cursor.fetchall()] [(u'',), (u'',), (u'',)]
Here is a patch which demonstrates that it is possible to get the right behavior from ADO/DB. Note that this is not a full-blown solution (the patch only addresses one non-sproc path and hasn't been through regression testing), but it might point the work on this ticket in the right direction. Feedback?
Here's a second patch which addresses the problem in a more general way, hopefully closer to something which could actually be applied to the repository (perhaps with some added comments). Basically, it applies the logic for trying to use ADO's existing parameter list for all calls to
_buildADOparameterList()
, not just for stored procedures. The original version had a comment "needed only if we are calling a stored procedure" but not explaining why that would be true. Given the incorrect behavior behind this bug report, I'm inclined to think it would be needed for all paths.Last edit: Bob Kline 2018-02-17
This patch appears to fix another bug I hadn't gotten around to reporting. Without the patch:
With the patch:
(Having a conversation with myself, it seems). :-)
And I believe the patch also addresses https://sourceforge.net/p/adodbapi/bugs/17/
[EDIT: no, the patch isn't that good :-) The datetime precision bug will still need some digging.]
Last edit: Bob Kline 2018-02-17
I will look at pulling your patches in very soon. My old test setup
evaporated and I'm building a replacement.
On Sat, Feb 17, 2018 at 10:25 AM, Bob Kline bkline@users.sourceforge.net
wrote:
Related
Bugs: #27
I don't have permission to modify the status of my own bug report, it seems, but if I did I would close this ticket and withdraw the accompanying patches. For more information see https://github.com/mhammond/pywin32/pull/1165 and https://mail.python.org/pipermail/python-win32/2018-February/013994.html.