#27 Empty string replaced with single space on insert

[Bob Kline]

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'',)]

[Bob Kline]

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?

>>> from cdrapi import db
>>> cursor = db.connect().cursor()
>>> 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'',)]

[Bob Kline]

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.

[Bob Kline]

This patch appears to fix another bug I hadn't gotten around to reporting. Without the patch:

>>> cursor.execute("CREATE TABLE #foo (i INT, t NTEXT NULL)")
>>> cursor.execute("INSERT INTO #foo VALUES(?, ?)", (42, None))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python\lib\site-packages\adodbapi\adodbapi.py", line 885, in execute
    self._execute_command()
  File "C:\Python\lib\site-packages\adodbapi\adodbapi.py", line 697, in _execute_command
    self._raiseCursorError(klass, _message)
  File "C:\Python\lib\site-packages\adodbapi\adodbapi.py", line 570, in _raiseCursorError
    eh(self.connection, self, errorclass, errorvalue)
  File "C:\Python\lib\site-packages\adodbapi\apibase.py", line 53, in standardErrorHandler
    raise errorclass(errorvalue)
adodbapi.apibase.DatabaseError: (-2147352567, 'Exception occurred.', (0, u'Microsoft OLE DB Provider for SQL Server', u'Operand type clash: int is incompatible with ntext', None, 0, -2147217913), None)
Command:
INSERT INTO #foo VALUES(?, ?)
Parameters:
[Name: p0, Dir.: Input, Type: adInteger, Size: 0, Value: "42", Precision: 0, NumericScale: 0
Name: p1, Dir.: Input, Type: adInteger, Size: 0, Value: "None", Precision: 0, NumericScale: 0]

With the patch:

>>> cursor.execute("CREATE TABLE #foo (i INT, t NTEXT NULL)")
>>> cursor.execute("INSERT INTO #foo VALUES(?, ?)", (42, None))
>>> cursor.execute("SELECT * FROM #foo")
>>> [tuple(row) for row in cursor.fetchall()]
[(42, None)]

[Bob Kline]

(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.]

[Vernon Cole]

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:

(Having a conversation with myself, it seems). :-)

And I believe the patch also addresses https://sourceforge.net/p/
adodbapi/bugs/17/

---

• [bugs:#27] https://sourceforge.net/p/adodbapi/bugs/27/ Empty string
  replaced with single space on insert*

Status: open
Group: v1.0_(example)
Created: Thu Feb 01, 2018 02:08 PM UTC by Bob Kline
Last Updated: Sat Feb 17, 2018 05:08 PM UTC
Owner: nobody

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'',)]

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/adodbapi/bugs/27/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[Bob Kline]

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.