On 18/05/11 18:48, eric.b...@barclayscapital.com wrote:
Hey, Martin.
TEXTSIZE is a server-side variable that determines the data length
returned by a SELECT statement.
It appears to be roughly the server-side equivalent of LongReadLen,
where LongReadLen (per your notes below) is a driver setting that
determines at what size buffer will be allocated to store column data
returned by the dataserver, the TEXTSIZE setting (select @@TEXTSIZE)
determines the length of the data that the dataserver returns.
That seems to be the distinction: TEXTSIZE is server side, and
LongReadLen is a driver option. For Sybase, you can pass in
LongReadLen as a connection parameter and I believe that it also sets
TEXTSIZE, but I'm not really sure, so I brought the question to the
DBI group at large.
Eric
DBD::ODBC does not touch TEXTSIZE. DBD::ODBC is for ODBC drivers (more than
Sybase, MS SQL Server) whereas DBD::Sybase is for Sybase/MS SQL Server which
both support TEXTSIZE.
If TEXTSIZE is > 80 then you'll always have to set LongReadLen to get all the
data without truncating it.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
-----Original Message----- From: Martin J. Evans
[mailto:martin.ev...@easysoft.com] Sent: Wednesday, May 18, 2011
11:52 AM To: Berg, Eric: IT (NYK) Cc: dbi-users@perl.org Subject:
Re: What's the relationship between LongReadLen and TEXTSIZE?
Sorry I'm late on this - I did not see it first time.
On 17/05/11 17:11, eric.b...@barclayscapital.com wrote:
I'm setting up to use DBD::ODBC with the FreeTDS driver to go
against a MS SQL server and am hoping to nail down the specifics
of the relationship between setting $dbh->{LongReadLen} and
TEXTSIZE.
what is TEXTSIZE?
So far, LongReadLen has had exactly no impact on retrieval of
long data from my MS sql server. Regardless of to what
LongReadLen is set, the data that I retrieve is complete beyond
the LongReadLeng setting as long as TEXTSIZE is adequately
large.
Normally DBD::ODBC uses a buffer of 80 bytes (or 160 for Unicode)
to hold column data. If your column is longer than that it will be
truncated by the ODBC driver and the ODBC driver should return
SQL_SUCCESS_WITH_INFO indicating the data is truncated. If it does
DBD::ODBC sees this and if LongTruncOk is set then it is fine,
otherwise it errors.
Setting LongReadLen allows you to change the buffer DBD::ODBC uses
to avoid truncation. It is necessary since some database columns
can report a huge size it would be ridiculous to allocate unless
the application is sure it wants it all. However, LongReadLen does
not affect all columns e.g., integer columns. It does affect
SQL_BINARY, nvarchar(MAX) in SQL Server, any column where the
driver reports its size as 0, SQL_LONGVARBINARY, SQL_WLONGVARCHAR,
SQL_LONGVARCHAR, MS_SQLS_XML_TYPE.
Setting TEXTSIZE to a value that is less than the length of the
data, however, does result in truncated data returned from the
dataserver.
I don't know what TEXTSIZE is.
Additionally, truncated values end with an extended ascii
char...probably a null...I have to admit I haven't looked yet.
So, can we just ignore LongReadLen? We're migrating from Sybase,
and have been using LongReadLen as a connection parameter in
Sybase for years to set the size of long data returned by the
dataserver.
Any light you can shed on this is appreciated.
All the light I have so far. I don't use freeTDS actively myself
although I have it. If you can provide a simple standalone example
which creates a table and demonstrates the issue I will look at
it.
Eric
Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
_______________________________________________
This e-mail may contain information that is confidential, privileged
or otherwise protected from disclosure. If you are not an intended
recipient of this e-mail, do not duplicate or redistribute it by any
means. Please delete it and any attachments and notify the sender
that you have received it in error. Unless specifically indicated,
this e-mail is not an offer to buy or sell or a solicitation to buy
or sell any securities, investment products or other financial
product or service, an official confirmation of any transaction, or
an official statement of Barclays. Any views or opinions presented
are solely those of the author and do not necessarily represent those
of Barclays. This e-mail is subject to terms available at the
following link: www.barcap.com/emaildisclaimer. By messaging with
Barclays you consent to the foregoing. Barclays Capital is the
investment banking division of Barclays Bank PLC, a company
registered in England (number 1026167) with its registered office at
1 Churchill Place, London, E14 5HP. This email may relate to or be
sent from other members of the Barclays Group.
_______________________________________________
