Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at peppler dot org>
Subject: RE: Retaining Data Format
Date: Apr 16 2002 2:21PM

On Tue, 2002-04-16 at 06:54, Sabherwal, Balvinder (MBS) wrote:
> Sabherwal, Balvinder (MBS) wrote:
> >> I am trying to select few rows from the database using ct_sql function.
> If
> >>I
> >> exec. same sql from an ISQL prompt I see the zeros after the decimal
> point
> >> where as if I try to print the results from the perl script, it doesn't
> >>show
> >> the zeros after the decimal. (i.e. 34.00 and 35.30 in isql are printed as
> >>34
> >> and 35.3 respectively in perl)
> >> Is this the normal behavior?? is there anyway I can get the values in the
> >> same format as in the ISQL Session??
> >Try printf or sprintf.
> >For example:
> >printf "%10.2f", $x;
> >or
> >$formatted_x = sprintf "%10.2f", $x;
> The only problem is I don't know if the value is a numeric or string as
> ct_sql,undef,CS_TRUE will lose the sequence in which the columns are fetched
> from the server. Any other wayouts??

You could test the values to see what type they are using a regular
expression. For example $val =~ /^[\d\.]$/ would only match a number.

However, it seems to me that you are trying to stuff a square peg into a
round hole. If you really need to print the values nicely, and if you
really need to know the types, column names, etc, I suggest that you
write your own version of ct_sql() that will give you that information.

It's actually quite simple (off the top of my head - not guaranteed

sub my_sql {
    my $dbh = shift;
    my $sql = shift;

    my $restype;
    my $data;
    my $resnum = 0;

    $dbh->ct_execute($sql) || die "Can't execute $sql!";
    while($dbh->ct_results($restype) == CS_SUCCEED) {
        next unless $dbh->ct_fetchable($restype);
	my @cols = $dbh->ct_col_names;
	my @types = $dbh->ct_col_types;
	$data->[$resnum]->{info}->[\@cols, \@types];
	while(my @d = $dbh->ct_fetch) {
	    push(@{$data->[$resnum]->{data}}, [@d]);

    return $data;

This will return a data structure that holds all the results, including
the column names and column types.

If there is only one select you can get at the data like this:

my $colnames = $data->[0]->{info]->[0];   # reference to an array of col
my $coltypes = $data->[0]->{info}->[1];

foreach my $row (@{$data->[0]->{data}}) {
    $row is a reference to an array...


As you see, writing ad-hoc functionality in perl is really quite simple,
because storing the intermediate results in a hash is so easy.

I also suggest the use of Data::Dumper to view complex data structures
if you aren't sure how they are built up.

Michael Peppler                              Data Migrations, Inc.           *or*
International Sybase User Group: