sybperl-l Archive

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
correct!):

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]);
	}
	++$resnum;
    }

    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
names
my $coltypes = $data->[0]->{info}->[1];

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

etc...

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
-- 
Michael Peppler                              Data Migrations, Inc.
mpeppler@peppler.org           *or*          mpeppler@mbay.net
http://www.mbay.net/~mpeppler
International Sybase User Group: http://www.isug.com