FreeTDS Development Group

[Eric Deutsch <edeutsch AT systemsbiology.org>]

On Mon, 4 Jun 2001, Brian Bruns wrote:

> Ok, I got a live one! ;-)
>
> Eric, if you issue the query "set textsize 10000" prior to selecting the
> text column does that also fix the problem?

Yes it does!  If I just do:
  SELECT * FROM dbaccesstests
I get:
  2001-06-04 13:56:51 inside ct_describe()
    datafmt->maxlength = 2147483647, datafmt->usertype = 0,
    datafmt->precision = 0, datafmt->scale = 0
  Out of memory!

But if I do as you suggest:
  set textsize 10000  SELECT * FROM dbaccesstests
I get:
  2001-06-04 13:55:14 inside ct_describe()
    datafmt->maxlength = 10000, datafmt->usertype = 0,
    datafmt->precision = 0, datafmt->scale = 0
  No Error

I'm not really sure I know what the implications of this are.  I wouldn't
be thrilled with the prospect of having to include such a statement before
every SELECT to a table with a TEXT column, or even doing it at the start
of every connection.  But maybe there's no other way.

I'm suspecting that somewhere there's a malloc() to this maxlength, so I
was hoping that maybe SQL Server also provides the actual length that
could be used for malloc() instead of the maxlength (which apparently may
be ridiculously huge).

You solution is better than my code hack, but I'm hoping there's a tidier
way...?

thanks,
Eric


> I will see if I can get this to happen under SQL Server 7.

I can send the perl script I'm using to test if you like.  It does a table
drop, create, insert, select all in one script.


> Brian
>
>
> On Mon, 4 Jun 2001, Eric Deutsch wrote:
>
> >
> > >A second related problem is that some people are having text problems
> > >under TDS 7.0, but no seems to care enough to give an example so it be
> > >fixed. And that is where the CAST(mytextcol as varchar(8000)) would come
> > >in.
> >
> > Hi, I care very much about this because my software would be useless if 
> > this
> > remained a problem.  Below is my last post on this subject.  If there's 
> > any
> > additional information I can provide, please let me know.  The problem
> > occurs for me for any SELECT to any column of type TEXT.  I assume this is
> > not the case for other versions of SQL Server, so maybe it's just for 
> > 2000.
> > The debugging code I inserted in the patch below could be used to examine
> > the behavior on other versions of SQL Server to see where the maxlength
> > started being 2GB.
> >
> > Please don't hesitate to contact me if I can provide more information.  My
> > code is working for now, but the hack I inserted to fix it is not a good
> > solution.
> >
> > thanks,
> > Eric
> >
> >
> > -----Original Message-----
> > From: bounce-freetds-127899 AT franklin.oit.unc.edu
> > [mailto:bounce-freetds-127899 AT franklin.oit.unc.edu]On Behalf Of Eric
> > Deutsch
> > Sent: Friday, March 30, 2001 10:03 AM
> > To: TDS Development Group
> > Subject: [freetds] Re: SqlServer text size limitation
> >
> >
> > >On Fri, 30 Mar 2001, Guille -bisho- wrote:
> > >
> > > I have another problem. Using text fields and protocol version 7.0 and 
> > > SQL
> > > Server 7.0 SP3 it says that the server gives a erroneous response.
> > >
> > > The protocol 4.2 connect and return queries, but the text fields are
> > returned
> > > without the last character.
> > >
> > > I'm using FreeTDS 0.51.
> > >
> > > Is a known issue??? Is there any patch???
> > >
> >
> > Hi, I have been having problems with TEXT fields in the following setup:
> > Perl 5.6.0 on Linux accessing MS SQL Server 2000
> > via perl DBI::Sybase + FreeTDS 0.51 + TDS 7.0
> >
> > If I try to retrieve data from a columntype of TEXT, program crashes with
> > error "Out of Memory!" (VARCHAR, CHAR, etc. are all fine; indeed if I
> > SELECT a TEXT field with SUBSTRING(textcolumn,1,8000) all is also well).
> > But a raw TEXT column is bad news.
> >
> > I inserted the following hack to fix the problem:
> >
> > *** ct.c:orig       Sat Nov  4 12:41:43 2000
> > --- ct.c    Fri Mar 30 09:30:59 2001
> > ***************
> > *** 743,746 ****
> > --- 745,756 ----
> >     datafmt->precision = curcol->column_prec;
> >     datafmt->scale = curcol->column_scale;
> > +   tdsdump_log("%L inside ct_describe() datafmt->maxlength = %d,
> > datafmt->usertype = %d, datafmt->precision = %d, datafmt->scale = %d\n",
> > datafmt->maxlength, datafmt->usertype, datafmt->precision, 
> > datafmt->scale);
> > +
> > +         /* FIX ME Deutsch hack */
> > +         if (datafmt->maxlength > 10000) {
> > +           datafmt->maxlength=100000;
> > +           tdsdump_log("%L inside ct_describe() datafmt->maxlength = %d
> > after artificial limiting\n", datafmt->maxlength);
> > +         }
> > +
> >     /* FIX ME -- TDS 5.0 has status information in the results
> >     ** however, this will work for 4.2 as well */
> >
> > ----------------
> >
> > The TDSDUMP output looks like this for me now:
> >
> > 2001-03-30 09:31:37 inside ct_describe()
> > 2001-03-30 09:31:37 inside _ct_get_client_type()
> > 2001-03-30 09:31:37 inside ct_describe() datafmt->datatype = 16 server 
> > type
> > 35
> > 2001-03-30 09:31:37 inside ct_describe() datafmt->maxlength = 2147483647,
> > datafmt->usertype = 0, datafmt->precision = 0, datafmt->scale = 0
> > 2001-03-30 09:31:37 inside ct_describe() datafmt->maxlength = 100000 after
> > artificial limiting
> > 2001-03-30 09:31:37 inside ct_bind()
> > 2001-03-30 09:31:37 inside ct_bind() item = 2 datafmt->datatype = 16
> > 2001-03-30 09:31:37 inside ct_fetch()
> >
> >
> > What appears to be happening is that the maxlength for a TEXT field
> > appears to be getting returned as 2GB, and I assume that some part of the
> > program is then trying a malloc() 2GB, which is bad.  So my hacked code
> > just limits this to 100 kB, which is probably fine for my current
> > purposes.  But obviously, this is not a proper fix.  If a TEXT field
> > really were larger than 100 kB, the result would be truncated.
> >
> > Perhaps the experts out there can figure out how to handle this properly
> > and incorporate into current codebase?
> >
> > thanks,
> > Eric
> >
> >
> >
> > ---
> > You are currently subscribed to freetds as: [edeutsch AT systemsbiology.org]
> > To unsubscribe, forward this message to
> > $subst('Email.Unsub')
> >
> >
> > ---
> > You are currently subscribed to freetds as: [camber AT ais.org]
> > To unsubscribe, forward this message to $subst('Email.Unsub')
> >
>
>
> ---
> You are currently subscribed to freetds as: [edeutsch AT systemsbiology.org]
> To unsubscribe, forward this message to $subst('Email.Unsub')
>

-- 
----------------------------------------------------------------------
Eric Deutsch                        email: edeutsch AT systemsbiology.org
Institute for Systems Biology                    Voice: (206) 732-1397
4225 Roosevelt Way NE, Suite 200                   FAX: (206) 732-1260
Seattle, WA  98105-6099                                  TJ's Location
http://www.systemsbiology.org/