pymssql truncating when running in AWS Lambda

[parachute py]

Hello,

                           I have a large text field over 4K that works perfectly when testing in my local (ubuntu) and AWS EC2 instance and I get the data back as expected. 

When I try to run the same code within the AWS lambda the text gets truncated at 4k. 

python 3.6.8 

FreeTDS 

Database : SQL Server 2016

Can someone point me towards some useful info or help me with this issue? 

Thanks in advance. 

[parachute py]

Freetds Version v0.91

[David Rueter]

Try explicitly setting TEXTSIZE in your stored procedure or query:  it could be the server limiting the size.

 

For example:

 

     SET TEXTSIZE 2147483647   (for maximum size)

 

See:

 

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-textsize-transact-sql?view=sql-server-2017

--
You received this message because you are subscribed to the Google Groups "pymssql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pymssql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pymssql/c72f505a-5e8a-425c-917e-e9011ed785ab%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[parachute py]

I tried doing it on the query and I see it is running fine on the ec2 and local, but again no luck inside the lambda. 

Is there any configuration that limits this size in pymssql? 

On Tuesday, May 14, 2019 at 1:34:28 PM UTC-4, David Rueter wrote:

Try explicitly setting TEXTSIZE in your stored procedure or query:  it could be the server limiting the size.

 

For example:

 

     SET TEXTSIZE 2147483647   (for maximum size)

 

See:

 

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-textsize-transact-sql?view=sql-server-2017

 

 

From: pym...@googlegroups.com <pym...@googlegroups.com> On Behalf Of parachute py
Sent: Tuesday, May 14, 2019 10:03 AM
To: pymssql <pym...@googlegroups.com>
Subject: [pymssql] Re: pymssql truncating when running in AWS Lambda

 

Freetds Version v0.91

On Tuesday, May 14, 2019 at 11:52:59 AM UTC-4, parachute py wrote:

Hello,

 

                           I have a large text field over 4K that works perfectly when testing in my local (ubuntu) and AWS EC2 instance and I get the data back as expected. 

When I try to run the same code within the AWS lambda the text gets truncated at 4k. 

 

python 3.6.8 

FreeTDS 

Database : SQL Server 2016

 

Can someone point me towards some useful info or help me with this issue? 

 

 

 

Thanks in advance. 

 

--
You received this message because you are subscribed to the Google Groups "pymssql" group.

To unsubscribe from this group and stop receiving emails from it, send an email to pym...@googlegroups.com.

[David Rueter]

You can set the default TEXTSIZE in the freetds.conf file, but I would expect that explicitly executing a SET TEXTSIZE xxx  in your stored procedure would override that.

 

See:  https://www.freetds.org/userguide/freetdsconf.htm and specifically the “text size” setting.

 

There is nothing in pymssql that limits the size of column data that is returned in a resultset.  Input parameters on a stored procedure are limited to 8000 bytes.  (see https://github.com/pymssql/pymssql/issues/275 )

 

I reliably retrieve data in the 10 to 100 megabyte range from a varbinary(MAX) regularly without any problems using _mssql.  (I personally don’t use pymssql…so I can’t personally vouch for it, but it should be fine.)

 

4K is an interesting size though---the max of a nvarchar()  In SQL there are things that could limit you, such as inadvertently storing the value in an nvarchar() variable instead of a nvarchar(MAX)…OR encountering this same kind of limit through implicit type conversion:

 

DECLARE @Data1 nvarchar(4000)

SET @Data1 = REPLICATE(N’x’, 4000)

 

PRINT LEN(@Data1 + @Data1)

--returns 4000 due to implicit type conversion

PRINT LEN(CAST(@Data1 AS nvarchar(MAX)) + @Data1)

--returns 8000

 

 

Also, the default TEXTSIZE for the server may well be 4096:

 

SET TEXTSIZE 0

PRINT @@TEXTSIZE

--returns 4096

 

SET TEXTSIZE 512000

PRINT @@TEXTSIZE

--returns 512000

 

Take a look at what @@TEXTSIZE and LEN() return to AWS Lambda.

 

I suspect the truncation is happening in SQL, for one of the reasons above (or a similar reason). 

To unsubscribe from this group and stop receiving emails from it, send an email to pymssql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pymssql/6870ebf8-0e9f-4dbe-801a-9c1c75e4c737%40googlegroups.com.

[parachute py]

Thanks David. I set up the size in query and increased the freetds text size as well and it is currently working. 

Appreciate the quick turnaround.