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: "Mark Sutfin" <MSutfin at affinitygroup dot com>
Subject: Unable to format sproc output
Date: Apr 27 2004 4:26PM

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. So, in _ISQL_, the
following produces exactly what I seek. (Each column preserved with
trailing spaces) I concatenated the columns in this select to avoid
having to use alias' to preserve the length...

	SELECT
	CONVERT(char(20), MemberNo),
	CONVERT(char(20), MemberType),
	CONVERT(char(20), Fname),
	CONVERT(char(20), Lname),
	CONVERT(char(10), Address1)
	FROM DrMembers

....result set....
1143862             CCR                 Richard             Stacy
120 Conway

However, this select statement produces a different result (format-wise)
when the proc is called by the perl script....

....result set.....
1143862             CCRRichardStacy120 Conway

Not understanding the reason, I thought I may need to explicitly format
the output, so I tried a number of variations like

CONVERT(char(10), RIGHT(REPLICATE(' ', 10) + CONVERT(VARCHAR(10),
MemberNo), 10))
And
ISNULL(RIGHT(CONVERT(VARCHAR(3), MemberType) + REPLICATE(' ',3 -
CHAR_LENGTH(MemberType)) , 3), SPACE(3))

This didn't seem intuitive, because I've never had to worry about
truncation of trailing spaces in CHAR(n) columns before now...

Anyway, here I am in the debugger, inspecting the value returned from
the fetch...(the '!N!' are delimiters I put into the select so that I
could verify the length of the data returned in the debugger....and I'm
aware they don't appear in the select statement above..)

main::(AGIS_member_to_digital_rez.pl:132):
132:            my $rowref = $sth->fetchrow_arrayref();
  DB<2> n
main::(AGIS_member_to_digital_rez.pl:133):
133:            last unless defined $rowref;
  DB<2> x $rowref
0  ARRAY(0x40bbdaf8)
   0  '     19513'
   1  '!1!'
   2  ' '
   3  '!2!'
   4  '9*****              '
   5  '!3!'
   6  'CCR'
   7  '!4!'
   8  'Ronald'
   11  '103 S Benson Dr'
  DB<3> 

Line 4 of the dump '9*****           ' is a literal I inserted into the
SELECT statement.. Interesting that the trailing spaces were not
truncated...

I'm unclear as to how to affect the formatting to produce a fixed length
file. Any help is much appreciated!

Mark Sutfin

Here's my code snippets from the perl script... I can supply more
details on the code, but at this point, I'm not sure that's were my
problem lies... Thanks in advance...

###########################
# Run the stored procedure.
# This code is in the script
###########################
my $sth = $sql->do_stored_proc();
if ($sql->error()) {
    $agi->notify($sql->get_error());
    exit;
}

##########################
# This is the method referenced in 
# the code above..
##########################
sub do_stored_proc {
    my $self = shift or die;
    my $dbh = $self->{dbh} or die;
    my $sp = $self->{stored_proc};
    my $datasource = $self->{datasource};

    logger("INFO: do_stored_proc\n");

    my $sth = $self->prepare($sp);
    if (not defined $sth) {
        logger("FAILURE: do_stored_proc calling prepare()\'$sp\'\n");
        return undef;
    }

    my $rv = $self->execute();
    if (not defined $rv) {
        logger("FAILURE: do_stored_proc calling execute()\'$sp\'\n");
        return undef;
    }

    logger("SUCCESS: called \'$sp\' in \'$datasource\'\n");

    return $sth;