========
Newsgroups: comp.databases.sybase
Subject: Barebone Sybperl program to Compare Similar tables. on two different servers.
From: Shrirang Chikhalikar <globeview1@bankerstrust.com>
Date: Fri, 17 Jan 1997 13:25:36 +0000

Hi,

The following program will compare the contents of two Sybase tables. 

Please note :

0. The tables must have identical structure.
1. Both the tables should have the identical clustered indexes.
2. It will ignore the trailing blanks in String Comparisons.
3. There is no support of Binary, Text, Image and some other datatypes.
4. THE PROGRAM IS SUPPLIED "AS IS"  WITHOUT WARRANTY OF ANY KIND, EITHER
EXPRESSED OR IMPLIED, 
INCLUDING, BUT NOT LIMITED TO, THE  FITNESS FOR A PARTICULAR PURPOSE.
THE ENTIRE RISK AS 
TO THE PROGRAM AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE
PRGRAM PROVE DEFECTIVE, 
YOU ASSUME THE COST OF  ALL NECESSARY SERVICING, REPAIR OR CORRECTION.

Thanks,

Shrirang

#!/usr/users/chikhali/perl/bin/perl

require 'sybperl.pl';

#
# Set usernames here. In my case username is 'hare' and password is
'krishna' on
# both the servers.
#

$username1=$username2="hare";
$password1=$password2="krishna";
$display_progress=100;

%dtypes =
(
 "binary"=>                           45 , 
 "bit"=>                              50 , 
 "char"=>                             47 , 
 "datetime"=>                         61 , 
 "datetimn"=>                        111 , 
 "decimal"=>                          55 , 
 "decimaln"=>                        106 , 
 "description"=>                      39 , 
 "float"=>                            62 , 
 "floatn"=>                          109 , 
 "image"=>                            34 , 
 "int"=>                              56 , 
 "intn"=>                             38 , 
 "money"=>                            60 , 
 "moneyn"=>                          110 , 
 "nchar"=>                            47 , 
 "numeric"=>                          63 , 
 "numericn"=>                        108 , 
 "nvarchar"=>                         39 , 
 "real"=>                             59 , 
 "smalldatetime"=>                    58 , 
 "smallint"=>                         52 , 
 "smallmoney"=>                      122 , 
 "sysname"=>                          39 , 
 "text"=>                             35 , 
 "timestamp"=>                        37 , 
 "tinyint"=>                          48 , 
 "varbinary"=>                        37 , 
 "varchar"=>                          39 
);


# $ENV{SYBASE}=$ENV{PWD};

if ( $#ARGV != 3 )
{
        print "usage : %s  server1 server2 database table\n";
        exit;
}

$server1=$ARGV[0];
$server2=$ARGV[1];
$database=$ARGV[2];
$table=$ARGV[3];

printf "/*\nComparison Report\n";

printf("\tserver 1 : %s\n\tserver 2 : %s\n\tdatabase : %s\n\ttable :
%s\n",
        $server1, $server2, $database, $table );

printf "\n*/\n\n\n";

&clustered_cols($username1,$password1, $server1,$table,$database);
if ( $#ind_cols == -1 )
{
        printf ("No clustered index found on %s\n", $server1);
        exit;
}
(@ind_cols1)=(@ind_cols);

&clustered_cols($username2,$password2,$server2,$table,$database);
if ( $#ind_cols == -1 )
{
        printf ("No clustered index found on %s\n",$server2);
        exit;
}
(@ind_cols2)=(@ind_cols);

$max = $#ind_cols1 > $#ind_cols2 ? $#ind_cols1 : $#ind_cols2;

if ( $#ind_cols1 != $#ind_cols2 )
{
        printf "Indexes do not match.\n";
        exit;
}

for($i=0;$i<=$max;$i++)
{
        if ( $ind_cols1[$i] ne $ind_cols2[$i] )
        {
                printf "Indexes do not match.\n";
                exit;
        }
}

$x = &dblogin($username1,$password1,"$server1");
$y = &dblogin($username2,$password2,"$server2");
$z = &dblogin($username2,$password2,"$server2");

&dbcmd($x,"select * from $database..$table");
&dbcmd($y,"select * from $database..$table");


&dbsqlexec($x);
&dbresults($x);

&dbsqlexec($y);
&dbresults($y);

for($i=1;$i<=dbnumcols($y);$i++)
{
        $dat_types{ dbcolname($y,$i) } =dbcoltype($y,$i);
}

$i=0;

$rwcnt=0;

@lsttm=&sql($z,"select convert(char(30),getdate(),109)");
  while( 1 )
  {
        if ( $rwcnt++%$display_progress == 0  and $rwcnt > 99)
        {
                        $cmd=sprintf "select
datediff(ms,\"%s\",getdate())",$lst
tm[0];
                        @tm=&sql($z,$cmd); 
                        printf "/* %d ms, %d rows, avg %f rows/sec
*/\n", $tm[0]
,$rwcnt-1,
                                ( ($display_progress * 1.0) * 1000.0 ) /
( $tm[0
] * 1.0);
                        @lsttm=&sql($z,"select
convert(char(30),getdate(),109)")
;
        }
        if ( %dat1 = &dbnextrow($x,1) )
        {
                if ( %dat2 = &dbnextrow($y,1) )
                {
#                       print "\n\nRECORD : \n";

#                       foreach(keys %dat1) 
#                       { 
#                               printf "%-30s : %-20s %-20s \n", 
#                               $_, 
#                               $dat1{$_}, $dat2{$_} 
#                                       if $dat_types {$_} eq 60;
# 
#                       }

                        foreach( keys %dat2 )
                        {
                                $fld=$_;

                                if ( !( &equal( \$dat1{$fld},
                                                \$dat2{$fld},
                                                \$fld) ) )
                                {
                                        print $dat1{$fld},"|\n";
                                        print $dat2{$fld},"|\n";

                                        printf ("/* Row different
*/\n\n\n");
                                        printf  "select %s \n", $fld;
                                        printf("from  %s..%s\n",
                                                $database,$table);
                                        printf "where\n";

                                        $i=0;

                                        foreach(@ind_cols1)
                                        {
                                                $_=~s/ $//;
                                           if ( $dat_types{$_} == 
                                                $dtypes{char} or 
                                                $dat_types{$_} ==  
                                                $dtypes{varchar} or
                                                $dat_types{$_} == 
                                                $dtypes{smalldatetime}
or 
                                                $dat_types{$_} ==  
                                                $dtypes{datetime}
                                                ) 
                                          {
                                                printf  "%s=\t\"%s\"",
                                                $_, $dat1{$_}
                                          }
                                          else
                                          {
                                                printf  "%s=%s",
                                                $_, $dat1{$_},
$dat2{$_};
                                          }

                                                if ( $i++!=$#ind_cols)
                                                {
                                                        printf " and\n";
                                                }
                                        }
                                        printf "\ngo\n";

                                        &exit_prog();
                                }
                        }
                }
                else
                {
                        if ( %dat1 = &dbnextrow($x,1) )
                        {
                                printf "\n\n/* Row absent on connection
2 : %s *
/\n\n\n", $server2;
                                        printf  "select * \n";

                                        printf("from 
%s..%s\n",$database,$table
);

                                        printf "where\n";

                                        $i=0;

                                        foreach(@ind_cols1)
                                        {
                                                $_=~s/ $//;
                                           if ( $dat_types{$_} == 47 or 
                                                $dat_types{$_} ==  61 or
                                                $dat_types{$_} == 58 or 
                                                $dat_types{$_} ==  39 or
                                                $dat_types {$_} == 37 or 
                                                $dat_types{$_} == 39 )
                                          {
                                                printf  "%s=\t\"%s\"",
                                                $_, $dat1{$_}
                                          }
                                          else
                                          {
                                                printf  "%s=%s",
                                                $_, $dat1{$_};
                                          }

                                                if ( $i++!=$#ind_cols)
                                                {
                                                        printf " and\n";
                                                }
                                        }

                                        printf "\ngo\n";
                        }

                        &exit_prog();
                }
        }
        else
        {
                if ( %dat2 = &dbnextrow($y,1) )
                {
                        printf "\n\n/* Row absent on connection 1 : %s
*/\n\n\n"
, $server1;
                        printf  "select * \n";

                        printf("from  %s..%s\n",$database,$table);

                        printf "where\n";

                        $i=0;

                        foreach(@ind_cols1)
                        {
                                $_=~s/ $//;
                           if ( $dat_types{$_} == 47 or 
                                $dat_types{$_} ==  61 or
                                $dat_types{$_} == 58 or 
                                $dat_types {$_} == 37 or 
                                $dat_types{$_} == 39 )
                          {
                                printf  "%s=\t\"%s\"",
                                $_, $dat2{$_}
                          }
                          else
                          {
                                printf  "%s=%s",
                                $_, $dat2{$_};
                          }

                                if ( $i++!=$#ind_cols)
                                {
                                        printf " and\n";
                                }
                        }
                        printf("go\n");
                }

                &exit_prog();
        }
  }

sub exit_prog {
         foreach(sort keys %dat1) 
                { printf "%-30s : '%s' '%s' \n", $_, $dat1{$_},
$dat2{$_}; }
  exit;
}

sub clustered_cols {
        my($user,$pwd,$server,$table, $database)=(@_);
        my($b)="";
        my($x) = &dblogin("$user","$pwd","$server");
        &dbuse($x,"gbvsepdb01");
        my(@a)=&sql($x,"exec $database..sp_helpindex $table");
        foreach $b ( @a)
        {
                my(@c)=split(/~/,$b);
                if ( $c[1] =~ /clustered/ )
                {
                        @ind_cols=split (/,/, $c[2] );
                        last;
                }
        }
        $#ind_cols;
}


sub equal {
        my($aref,$bref,$cref)=@_;

        my($a)=$$aref;
        my($b)=$$bref;
        my($c)=$$cref;

        if ( defined $a and defined $b  )
        {
                if ($dat_types{$c}==$dtypes{money} and abs($a-$b)<0.01)
                {
                        return 1;
                }
                elsif ( $dat_types{$c} == $dtypes{tinyint} or
                        $dat_types{$c} == $dtypes{smallint} or
                        $dat_types{$c} == $dtypes{int} or
                        $dat_types{$c} == $dtypes{float} 
                        )
                {
                        if ( $a == $b )
                        {
                                return 1;
                        }
                }
                elsif ( $dat_types{$c} == $dtypes{char} or
                        $dat_types{$c} == $dtypes{varchar} or 
                        $dat_types{$c} == $dtypes{nchar}  or 
                        $dat_types{$c} == $dtypes{nvarchar} or 
                        $dat_types{$c} == $dtypes{smalldatetime} or
                        $dat_types{$c} == $dtypes{datetime} )
                {
                        $a=~s/ $//;
                        $b=~s/ $//;

                        my($ln)=        length($b) > length($a) ?
                                        length($a) : length($b);

                        $a=substr($a,0,$ln);
                        $b=substr($b,0,$ln);

                        if ( $a eq $b )
                        {
                                return 1;
                        }
                }
        }
        elsif ( (!defined $a )  and ( !defined $b ) )
        {
                return 1;
        }
        elsif ( (!defined $a) or (!defined $b ) )
        {
                if ( 
                        $dat_types{$c} == $dtypes{char} or
                        $dat_types{$c} == $dtypes{varchar} or 
                        $dat_types{$c} == $dtypes{nchar}  or 
                        $dat_types{$c} == $dtypes{nvarchar} or 
                        $dat_types{$c} == $dtypes{smalldatetime} or
                        $dat_types{$c} == $dtypes{datetime} 
                )
                {
                        if ( !defined $a )
                        {
                                $a="";
                        }
                        else
                        {
                                $a=~s/ $//;
                        }

                        if ( !defined $b )
                        {
                                $b="";
                        }
                        else
                        {
                                $b=~s/ $//;
                        }

                        if ( $a eq $b )
                        {
                                return 1;
                        }
                }
        }

        return 0;
}