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: "Martin Bonner" <martin dot bonner at argentas dot co dot uk>
Subject: fetchrow_hashref issue
Date: Nov 9 2003 4:07PM

Hi,

I'm using DBD::Sybase 1.01 with FreeTDS 0.62.dev.20031109 to connect to an
MSSQL 7.0 server using TDS protocol 4.2.

I am experiencing issues with hashref 'key' names which appear to be
corrupted and/or holding stale characters from previous data sets. The issue
I am seeing is probably best described with the aid of a few examples...
I've included a copy of the stored procedure and Perl script that I am using
at the end of this email..

I'm not sure whether this is a DBD::Sybase issue or a FreeTDS issue, but
perhaps you guys will have some idea. Also, am i correct in assuming that
the CS_STATUS_RESULT dataset should contain the return code of the stored
procedure? it appears to always return 0, so maybe I have misunderstood what
this dataset is used for.

Thanks in advance for any help..

Kind Regards,
Martin.

--example 1-- 
// This one works as expected //

declare @two int, @retval int EXEC @retval = sp_test 1, @timestwo = @two
OUTPUT SELECT @two as x2, @retval AS retval
resulttype: 4043 (CS_STATUS_RESULT)
$VAR1 = {
          'COL(1)' => 0
        };
resulttype: 4042 (CS_PARAM_RESULT)
$VAR1 = {
          '@timestwo' => 2
        };
resulttype: 4040 (CS_ROW_RESULT)
$VAR1 = {
          'retvaless' => 4,
          'x2
' => 2
        };
--end of example 1--

--example 2-- 
// swapping the arguments of the select query around causes corruption of
key names for the 4040 result set. In this instance it's not immediately
clear where the 'ess' on the end of 'retval' comes from. //

declare @two int, @retval int EXEC @retval = sp_test 1, @timestwo = @two
OUTPUT SELECT @two as x2, @retval AS retval
resulttype: 4043 (CS_STATUS_RESULT)
$VAR1 = {
          'COL(1)' => 0
        };
resulttype: 4042 (CS_PARAM_RESULT)
$VAR1 = {
          '@timestwo' => 2
        };
resulttype: 4040 (CS_ROW_RESULT)
$VAR1 = {
          'retvaless' => 4,
          'x2
' => 2
        };
--end of example 2--


--example 3--
// the key name for the 4040 result set, appears to contain stale characters
from the previous key name //
declare @two int, @retval int EXEC @retval = sp_test 1, @timestwo = @two
OUTPUT SELECT @retval AS rv
resulttype: 4043 (CS_STATUS_RESULT)
$VAR1 = {
          'COL(1)' => 0
        };
resulttype: 4042 (CS_PARAM_RESULT)
$VAR1 = {
          '@timestwo' => 2
        };
resulttype: 4040 (CS_ROW_RESULT)
$VAR1 = {
          'rvimestwo' => 4
        };
--end of example 3--

---sp_test---
create procedure sp_test (
@param int,
@timestwo int OUTPUT )
as
begin
set @timestwo = @param * 2
return @param * 4
end
---

---test.pl---
#!/usr/bin/perl

use DBI;
use strict;
use Data::Dumper;

my $query = ;

my %rowtypes;
$rowtypes{4040}='CS_ROW_RESULT';
$rowtypes{4041}='CS_CURSOR_RESULT';
$rowtypes{4042}='CS_PARAM_RESULT';
$rowtypes{4043}='CS_STATUS_RESULT';
$rowtypes{4044}='CS_MSG_RESULT';
$rowtypes{4045}='CS_COMPUTE_RESULT';

# connect to db
my $dbh = DBI->connect("dbi:Sybase:server=MyServer;database=mydb",
'username', 'password', {PrintError => 1});
die "Unable to connect to server $DBI::errstr" unless $dbh;

my $sth=$dbh->prepare($query);
if($sth->execute()) {
  do {
    while( my $results=$sth->fetchrow_hashref()) {
      printf ("resulttype: %d
(%s)\n",$sth->{syb_result_type},$rowtypes{$sth->{syb_result_type}});
      print Dumper $results;
    }
  } while($sth->{syb_more_results});
}
---