On Wed, Oct 14, 2020, at 5:35 AM, Nicolas Lykke Iversen wrote:
> Hi Mike,
>
> I've now tested inserting strings with more than 2000 characters using Azure
> Data Studio (SQL Server GUI) and everything works.
>
> Furthermore, I've tested pyodbc (DBAPI) directly, and it only fails when
> inserting such strings using parameterised SQL queries (it succeeds without
> using parametrised queries).
that would be expected because all the datatype-related issues occur when bound
parameters are passed.
>
> You can see my POC below, if you have any interest.
>
> I guess it should be submitted as a bug to pyodbc... Do you know if I can
> disable parametrisation for certain SQL queries in SQLAlchemy?
there is not and this is definitely an issue that has to be solved at the
pyodbc level, either a bug on their end or something in your configuration that
has to be changed.
>
> Best regards (and thanks for your help and support!!!)
> Nicolas
>
> *System info*:
> python v. 3.8.5
> pyodbc v. 4.0.30
> msodbcsql17 v. 17.6.1.1
>
> *POC*:
> import sys
> import pyodbc
>
> host = 'tcp:127.0.0.1,1433'
> db = 'pyodbc_test'
> user = 'sa'
> pwd = 'P@ssw0rd'
>
> print('started')
>
> cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL
> Server};SERVER='+host+';DATABASE='+'master'+';UID='+user+';PWD='+ pwd,
> autocommit=True)
> cursor = cnxn.cursor()
>
> try:
> cursor.execute(f'CREATE DATABASE {db} COLLATE
> Latin1_General_100_CI_AS_SC')
> except pyodbc.ProgrammingError as e:
> pass # database exists
>
> cursor.execute(f'USE {db}')
>
> try:
> cursor.execute("""
> CREATE TABLE msg (
> id int identity(1,1) not null,
> content varchar(max) not null
> );""")
> except pyodbc.ProgrammingError as exc:
> pass # table exists
>
> content = 2000 * 'A'
>
> cursor.execute(f"""
> INSERT INTO msg (content)
> VALUES ('{content}')""")
> print(f'non-param: {len(content)=}: success')
>
> sql = f"""
> INSERT INTO msg (content)
> VALUES (?)"""
> cursor.execute(sql, (content))
> print(f'param: {len(content)=}: success')
>
> content = 2001 * 'A'
>
> cursor.execute(f"""
> INSERT INTO msg (content)
> VALUES ('{content}')""")
> print(f'non-param: {len(content)=}: success')
>
> # this fails!
> sql = f"""
> INSERT INTO msg (content)
> VALUES (?)"""
> cursor.execute(sql, (content))
> print(f'param: {len(content)=}: success')
>
>
> #cursor.execute('SELECT * FROM msg')
> #rows = cursor.fetchall()
> #for r in rows:
> # print(r)
>
> print('finished')
>
>
>
> On Wednesday, October 14, 2020 at 12:43:25 AM UTC+2 Mike Bayer wrote:
>>
>>
>> On Tue, Oct 13, 2020, at 4:57 PM, Nicolas Lykke Iversen wrote:
>>> Thank you, Mike - very much appreciated!
>>>
>>> Just to be clear, pyodbc is not a driver, it’s a ODBC-compliant DBAPI,
>>> right? I separately downloaded a driver for SQL Server from Microsoft,
>>> which pyodbc makes use of.
>>
>> right the pyodbc is the DBAPI in this case, which we usually refer to as a
>> "driver" but in the case of ODBC the "driver" is more specifically the
>> separate ODBC driver component.
>>
>>>
>>> Do you suggest that changing pyodbc to another SQL Server DPAPI would solve
>>> the problem?
>>
>> I suggest that if there is no issue with the query you're running outside of
>> the context of pyodbc that you submit an issue to pyodbc at
>> https://github.com/mkleehammer/pyodbc/issues . However I was able to find a
>> discussion thread about your error message that seemed to be independent of
>> ODBC.
>>
>>
>>>
>>>
>>>
>>> If so, can you recommend another DBAPI for SQL Server? Or do you think that
>>> the problem is caused by Microsoft’s driver?
>>
>> pyodbc is the only supported driver for SQL Server that exists now for
>> Python. You also definitely want to use Microsoft's ODBC drivers so
>> you're already there.
>>
>>
>>>
>>> I’m pretty sure SQL Server works fine when accessed using .NET, otherwise
>>> the Internet would be full of complaints regarding not being able to insert
>>> +2000 characters in a varchar(max).
>>
>> you'd want to see if the same ODBC driver and options are in use in that
>> scenario.
>>
>>
>>
>>>
>>>
>>> Best regards
>>> Nicolas
>>>
>>>
>>> On Tue, 13 Oct 2020 at 22.22, Mike Bayer <[email protected]> wrote:
>>>> __
>>>>
>>>>
>>>> On Tue, Oct 13, 2020, at 10:50 AM, Nicolas Lykke Iversen wrote:
>>>>> Hi SQLAlchemy,
>>>>>
>>>>> *System information:*
>>>>> * Mac OS X v. 10.15.7
>>>>> * Python v. 3.8.5
>>>>> * SQLAlchemy v. 1.3.19
>>>>> * MS SQL Server 2017 and 2019 (both Enterprise and Docker images e.g.
>>>>> mcr.microsoft.com/mssql/server:2019-latest)
>>>>> *Problem*:
>>>>> I have an issue with inserting strings with a length greater than 2000
>>>>> into columns with a datatype of Text, VARCHAR, or NVARCHAR using MS SQL
>>>>> Server 2017 and 2019.
>>>>>
>>>>> I've checked the MS SQL Server and it creates it column properly with
>>>>> e.g. a datatype of varchar(max)for Text and VARCHAR, which should be able
>>>>> to store strings with a size up to 2 GB according to Microsoft
>>>>> documentation.
>>>>>
>>>>> Furthermore, I've tried using other collations, but I need _SC
>>>>> (supplementary character) support for my applications, so I cannot drop
>>>>> it, and adding _UTF8 (UTF-8) doesn't solve the problem either.
>>>>>
>>>>> Why am I not allowed to store strings with a size greater than 2000?
>>>>
>>>> I'm not really sure, this has to do with a behavior of SQL Server and/or
>>>> your ODBC driver. a google search finds fairly scant results but there
>>>> is a long discussion regarding this error here:
>>>> https://www.sqlservercentral.com/forums/topic/collation-error-when-adding-distributer
>>>>
>>>>
>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> And why is SQLAlchemy displaying that error message, when trying to
>>>>> insert plain ASCII text ("AAAA...")?
>>>>
>>>> SQLAlchemy runs SQL statements using a method called "cursor.execute()",
>>>> which is a feature of the DBAPI (database driver) in use. As your error
>>>> message indicates you're using the pyodbc driver, this method is
>>>> documented here:
>>>> https://github.com/mkleehammer/pyodbc/wiki/Cursor#executesql-parameters
>>>>
>>>> The DBAPI execute() method, and most of the other DBAPI methods, can throw
>>>> exceptions if something goes wrong. SQLAlchemy has the policy that if it
>>>> encounters such an exception when it calls cursor.execute(), it wraps it
>>>> in a SQLAlchemy-specific version of that exception (named the same) and
>>>> then throws it.
>>>>
>>>> In other words you're using a driver called pyodbc that's creating this
>>>> error. SQLAlchemy just propagates it for you but otherwise has nothing to
>>>> do with how it's produced.
>>>>
>>>>
>>>>>
>>>>>
>>>>> I would really appreciate some guidance on how to solve this problem.
>>>>> What could be causing it?
>>>>>
>>>>> *Error*:
>>>>> sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000',
>>>>> "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot
>>>>> convert to text/ntext or collate to 'Latin1_General_100_CI_AS_SC' because
>>>>> these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use
>>>>> types varchar(max), nvarchar(max) or a collation which does not have the
>>>>> _SC or _UTF8 flags. (4189) (SQLParamData)")
>>>>> [SQL: INSERT INTO msg (content) OUTPUT inserted.id VALUES (?)]
>>>>> [parameters:
>>>>> ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
>>>>> ... (1703 characters truncated) ...
>>>>> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA',)]
>>>>> (Background on this error at: http://sqlalche.me/e/13/f405)
>>>>>
>>>>>
>>>>> *Program (POC)*:
>>>>> import logging
>>>>> import sqlalchemy
>>>>> from sqlalchemy import Column, Text, Integer, NVARCHAR, VARCHAR
>>>>> from sqlalchemy.ext.declarative import declarative_base
>>>>> from sqlalchemy.orm import sessionmaker
>>>>>
>>>>> logging.root.setLevel(logging.DEBUG)
>>>>>
>>>>> Base = declarative_base()
>>>>>
>>>>>
>>>>> class Msg(Base):
>>>>> __tablename__ = 'msg'
>>>>>
>>>>> id = Column(Integer, primary_key=True, autoincrement=True)
>>>>> content = Column(VARCHAR, nullable=False)
>>>>>
>>>>>
>>>>> user = 'sa'
>>>>> pwd = 'P@ssw0rd'
>>>>> host = 'localhost'
>>>>> port = 1433
>>>>> db = 'test'
>>>>>
>>>>> logging.info('started.')
>>>>>
>>>>> engine = sqlalchemy.create_engine(
>>>>>
>>>>> f'mssql+pyodbc://{user}:{pwd}@{host}:{port}/{db}?driver=ODBC+Driver+17+for+SQL+Server',
>>>>> pool_size=5,
>>>>> max_overflow=10,
>>>>> pool_pre_ping=True,
>>>>> isolation_level='READ_UNCOMMITTED',
>>>>> pool_recycle=900,
>>>>> echo=False,
>>>>> connect_args={'connect_timeout': 10})
>>>>>
>>>>> Base.metadata.create_all(engine)
>>>>>
>>>>> Session = sessionmaker(bind=engine, autoflush=False, autocommit=False)
>>>>> session = Session()
>>>>>
>>>>> for i in range(10000):
>>>>> try:
>>>>> msg = Msg(content='A' * i)
>>>>> session.add(msg)
>>>>> session.commit()
>>>>> except Exception as exc:
>>>>> logging.exception(f'fail: {i=}: {exc}')
>>>>> break
>>>>> else:
>>>>> logging.info(f'success: {i=}')
>>>>>
>>>>> logging.info('done.')
>>>>>
>>>>> --
>>>>> SQLAlchemy -
>>>>> The Python SQL Toolkit and Object Relational Mapper
>>>>>
>>>>> http://www.sqlalchemy.org/
>>>>>
>>>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>>>> description.
>>>>> ---
>>>>> You received this message because you are subscribed to the Google Groups
>>>>> "sqlalchemy" group.
>>>>> To unsubscribe from this group and stop receiving emails from it, send an
>>>>> email to [email protected].
>>>>> To view this discussion on the web visit
>>>>> https://groups.google.com/d/msgid/sqlalchemy/ca549391-4360-480e-8c58-06577f6d92dan%40googlegroups.com
>>>>>
>>>>> <https://groups.google.com/d/msgid/sqlalchemy/ca549391-4360-480e-8c58-06577f6d92dan%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>>>
>>>>
>>>> --
>>>> SQLAlchemy -
>>>> The Python SQL Toolkit and Object Relational Mapper
>>>>
>>>> http://www.sqlalchemy.org/
>>>>
>>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>>> description.
>>>> ---
>>>> You received this message because you are subscribed to a topic in the
>>>> Google Groups "sqlalchemy" group.
>>>> To unsubscribe from this topic, visit
>>>> https://groups.google.com/d/topic/sqlalchemy/Kk6DkPNWlR4/unsubscribe.
>>>> To unsubscribe from this group and all its topics, send an email to
>>>> [email protected].
>>>> To view this discussion on the web visit
>>>> https://groups.google.com/d/msgid/sqlalchemy/b4a135ef-1c1e-476c-a3f0-60326dca76ea%40www.fastmail.com
>>>>
>>>> <https://groups.google.com/d/msgid/sqlalchemy/b4a135ef-1c1e-476c-a3f0-60326dca76ea%40www.fastmail.com?utm_medium=email&utm_source=footer>.
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>> description.
>>> ---
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an
>>> email to [email protected].
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/CAE%2B5CJABrO_459MHtDePMXah8mb67TFvQB8rUwgVgk6%2By4v-ow%40mail.gmail.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/CAE%2B5CJABrO_459MHtDePMXah8mb67TFvQB8rUwgVgk6%2By4v-ow%40mail.gmail.com?utm_medium=email&utm_source=footer>.
>>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/60f82633-887a-43a3-ac52-c5541058e0bcn%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/60f82633-887a-43a3-ac52-c5541058e0bcn%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/1f65e2d7-73cb-46f2-bf2c-ababcaa6152b%40www.fastmail.com.
