Re: Text retrieved from database column being truncated

[mpeppler]

You don't say, but I'm guessing that you are using FreeTDS and an MS-SQL data server.

If that is correct then DBD::Sybase's technique for setting LongReadLen (which involves calling ct_options() with the CS_OPT_TEXTSIZE attribute in the ClientLibrary API) does not work.

The work-around:

$dbh-do("set textsize <some_size_in_bytes");

Note that this problem is at the very least hinted at in the DBD::Sybase man page.

Michael

[millsperl]

Hi Michael, Yes, you are correct that the perl script is connecting to a MS-SQL database server. The strange thing is that on both linux servers the perl script is the same and even the database table is the exact same table. On the original server I can see the full contents of the database column from the perl script output running on that server. However on the new linux server running the same perl script against the same table the contents of the varchar 5000 column are being truncated. I tried your work around. Although it didn't complain, the results were the same, i.e. the column was still truncated. There seems to be some setting at server level or something that is affecting the size of the column contents that can be retrieved by perl script/s.

[mpeppler]

Are you using the exact same version of FreeTDS?

Is it configured in the same way?

Keep in mind that wide varchar() support depends on the TDS protocol version that is supported. IIRC this can be set in the freetds.conf file, and should be set to 7 or 8 to get wide varchar() support.

Michael

[millsperl]

The new server now has the same version of FreeTDS and the config are supposed to be the same as well. I tried the DBI tracing option on both servers which revealed that there is a maxlen set to 4000 on the one but only 255 on the new server which results in column contents being truncated. This is an excerpt of the DBI tracing (level 5):

-> prepare for DBD::Sybase::db (DBI::db=HASH(0x8d42418)~0x8d44360 'sel
+ect details from LogEntry
                    where logEntryId = 24516') thr#8b29008
    New DBI::st (for DBD::Sybase::st, parent=DBI::db=HASH(0x8d44360), 
+id=)
    dbih_setup_handle(DBI::st=HASH(0x8d444c8)=>DBI::st=HASH(0x8d4433c)
+, DBD::Sybase::st, 8d444d4, Null!)
    dbih_make_com(DBI::db=HASH(0x8d44360), 8d44c78, DBD::Sybase::st, 4
+20, 0) thr#8b29008
    dbih_setup_attrib(DBI::st=HASH(0x8d4433c), Err, DBI::db=HASH(0x8d4
+4360)) SCALAR(0x8cbfd34) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x8d4433c), State, DBI::db=HASH(0x8
+d44360)) SCALAR(0x8cbfd94) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x8d4433c), Errstr, DBI::db=HASH(0x
+8d44360)) SCALAR(0x8cbfd64) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x8d4433c), TraceLevel, DBI::db=HAS
+H(0x8d44360)) 0 (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x8d4433c), FetchHashKeyName, DBI::
+db=HASH(0x8d44360)) 'NAME' (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x8d4433c), HandleSetErr, DBI::db=H
+ASH(0x8d44360)) undef (not defined)
    dbih_setup_attrib(DBI::st=HASH(0x8d4433c), HandleError, DBI::db=HA
+SH(0x8d44360)) undef (not defined)
    syb_st_prepare() -> inUse = 0
    syb_st_prepare() -> set inUse
    <- prepare= DBI::st=HASH(0x8d444c8) at testbigchar.pl line 32 via 
+testbigchar.pl line 18
    -> execute for DBD::Sybase::st (DBI::st=HASH(0x8d444c8)~0x8d4433c)
+ thr#8b29008
    syb_alloc_cmd() -> CS_COMMAND 8d58a70 for CS_CONNECTION 8c81840
    cmd_execute() -> ct_command() OK
    cmd_execute() -> ct_send() OK
    cmd_execute() -> set inUse flag
    st_next_result() -> ct_results(4040) == 1
    ct_res_info() returns 1 columns
    ct_describe(0): type = 1, maxlen = 255
    describe() -> col 0, type 1, realtype 1
describe() retcode = 1
    st_next_result() -> lasterr = 0, lastsev = 0
    <- execute= -1 at testbigchar.pl line 33 via testbigchar.pl line 1
+8
    -> fetchrow_array for DBD::Sybase::st (DBI::st=HASH(0x8d444c8)~0x8
+d4433c) thr#8b29008
    dbih_setup_fbav for 1 fields => 0x8d45ed0
    fix_fbav() -> num_fields = 1, numCols = 1
    syb_st_fetch() -> ct_fetch() = 1 (1 rows)
    syb_st_fetch() -> 0/255/1
    <- fetchrow_array=...

The question is: How do I set/override the maxlen? I have tried a couple of things, which haven't worked.

[initself]

$schema->storage->dbh->do("set textsize 100000");


I put that in my schema and had success. 3x larger than the default of 32,768 bytes.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sdk_12.5.1.ctref/html/ctref/ctref205.htm