PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at peppler dot org>
Subject: RE: Unable to format sproc output
Date: Apr 28 2004 3:15PM

On Wed, 2004-04-28 at 17:04, Mark Sutfin wrote:
>  On Wednesday, April 28, 2004 7:53 AM, Michael Pepplar wrote:
> 
> > On Tue, 2004-04-27 at 18:26, Mark Sutfin wrote:
> > > If there's a better list for this question, please point me in the 
> > > right direction..!
> > > 
> > > $ perl -MDBI -le 'print $DBI::VERSION' = 1.42 $ perl 
> > -MDBD::Sybase -le 
> > > 'print $DBD::Sybase::VERSION' = 1.02 $ perl -v This is perl, v5.8.2 
> > > built for PA-RISC2.0 Sybase 11.9.2 HPUX 11i
> > > 
> > > I have a stored procedure called from a .pl script. The final 
> > > statement in the sproc is a SELECT from a temporary table, 
> > the result 
> > > set from which is stored in a flat file.
> > > 
> > > In the origin table, all columns except col1 are NULLABLE, 
> > there are 
> > > no indexes and all columns are CHAR(nn) datatypes.
> > 
> > A NULLABLE char() column will be treated the same way as a 
> > varchar() column, i.e. trailing whitespace will be truncated.
> > 
> > isql will display the formating correctly because it 
> > post-processes the data for display, fetching the max column 
> > width and doing something like a printf("%${width}s", $coldata). 
> > 
> > If you need to output the data in fixed width format then you 
> > need to get this data as well, and do the post-processing in 
> > the same way.
> > 
> > IIRC you can use $sth->{PRECISION} to get the length of columns.
> 
> This is becoming clearer... I'm going to give the $sth->{PRECISION} a
> go.. But I'm wondering why the explicit conversion (of data in a
> NULLable column) to CHAR and right padding with spaces doesn't work..
> Can you shed any light on this?

The NULLable characteristic is carried over. See this example:

Given a table null_test(string char(20) null, string2 char(20)) I ran
the following in sqsh:

[22] test.testdb.1> select convert(char(10), string), string2 from
null_test
[22] test.testdb.2> go -m meta
CS_ROW_RESULT
   CS_BROWSE_INFO        = CS_FALSE
   CS_CMD_NUMBER         = 1
   CS_NUMDATA            = 2
   CS_NUMORDERCOLS       = 0
   COLUMN #1
      name      =
      namelen   =  0
      datatype  =  CS_CHAR_TYPE
      format    =  CS_FMT_UNUSED
      maxlength =  10
      scale     =  0
      precision =  0
      status    =  CS_UPDATABLE,CS_CANBENULL
      count     =  0
      usertype  =  1
      locale    =  0x0x8b40b98
   COLUMN #2
      name      =  string2
      namelen   =  7
      datatype  =  CS_CHAR_TYPE
      format    =  CS_FMT_UNUSED
      maxlength =  20
      scale     =  0
      precision =  0
      status    =  CS_UPDATABLE
      count     =  0
      usertype  =  1
      locale    =  0x0x8b40b98
   CS_TRANS_STATE        = CS_TRAN_COMPLETED
CS_CMD_DONE
   CS_CMD_NUMBER         = 1
   CS_ROW_COUNT          = 1
   CS_TRANS_STATE        = CS_TRAN_COMPLETED

Note that the meta data for the first column is still NULLABLE, even
after the convert(char(10)).

So as far as DBD::Sybase is concerned the actual length of the string is
still only the length of the non-whitespace part, because that's what
Sybase gives me.

You can confirm this by running with DBI->trace(3) which should give you
the column widths that DBD::Sybase gets from OpenClient.

Michael
-- 
Michael Peppler                              Data Migrations, Inc.
mpeppler@peppler.org                       http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions - http://www.peppler.org/resume.html