First up, here's a little bit about my environment:
I have a text field in the database with no max length. There is text in it that is 890591 characters long.
When I retrieve this field using SQLAlchemy
it is truncated to 64512 characters. I've tried it with several other large rows too, and it's always truncated to 64512.
SELECT @@TEXTSIZE
returns some absurd value like 1.6 million, so that's not the problem. If I do SELECT DATALENGTH(field)
it returns the correct 890591. So it doesn't seem to be the database, it seems to be SQLAlchemy
. Or perhaps it could be some Python limit.
Any ideas? I seem to be at my wits end.
EDIT: Here's some more info that was requested:
OS: Debian 5.0.9
SQLAlchemy: 0.7.3
SQL: MS Sql Server 2008
DB Connection: mssql+pymssql://name:password@server/dbname
pymssql version: 1.0.2
Model in question:
class RACReport(Base):
__tablename__ = 'RACReport'
id = Column(properUUID(), primary_key=True, nullable=False, default=genuuid4, server_default=text('NEWID()'))
client_id = Column(properUUID(), ForeignKey(Client.id), nullable=False)
rawdata = Column(Text(), nullable=True)
rawtime = Column(DateTime(), nullable=True, default=datetime.datetime.now())
processeddata = Column(Text(), nullable=True)
processedtime = Column(DateTime(), nullable=True)
reportstartdate = Column(DateTime(), nullable=False)
reportenddata = Column(DateTime(), nullable=False)
numberofdocs = Column(Integer(), nullable=True)
RACReport.__table__.schema='rac'
class properUUID(st.TypeDecorator):
impl = mssql.MSUniqueIdentifier
def process_result_value(self, value, dialect):
if value:
return str(uuid.UUID(bytes_le=value))
def genuuid4():
return str(uuid.uuid4())
rawdata and processdata are the two fields he is having the problem with.
Here's a test query and echo:
rac.session.query(rac.RACReport).filter(rac.RACReport.id=='8fb76cb7-d752-45af-a20a-3b85d5e7b8a6').all()
2011-11-17 09:39:46,890 INFO sqlalchemy.engine.base.Engine SELECT [RACReport_1].id AS [rac_RACReport_id], [RACReport_1].client_id AS [rac_RACReport_client_id], [RACReport_1].rawdata AS [rac_RACReport_rawdata], [RACReport_1].rawtime AS [rac_RACReport_rawtime], [RACReport_1].processeddata AS [rac_RACReport_processeddata], [RACReport_1].processedtime AS [rac_RACReport_processedtime], [RACReport_1].reportstartdate AS [rac_RACReport_reportstartdate], [RACReport_1].reportenddate AS [rac_RACReport_reportenddate]
FROM rac.[RACReport] AS [RACReport_1]
WHERE [RACReport_1].id = %(id_1)s
2011-11-17 09:39:46,890 INFO sqlalchemy.engine.base.Engine {'id_1': '8fb76cb7-d752-45af-a20a-3b85d5e7b8a6'}
I know little of *nix connectivity to SQL Server, but simple googling suggests that the issue is related to FreeTDS configuration:
My text data are being truncated or are causing my client to break.
The text data type is different from char and varchar types. The maximum data length of a text column is governed by the textsize connection option. Microsoft claims in their documentation to use a default textsize of 4000 characters, but in fact their implementation is inconsistent. Sometimes text columns are returned with a size of 4 GB!
The best solution is to make sure you set the textsize option to a reasonable value when establishing a connection. For example:
1> set textsize 10000
2> go
See also the text size option in freetds.conf.
And just a side note: you seem to be using rather outdated version of pymssql
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With