Brothers and Sisters,

With the much appreciated help on my last script where the sql was not functioning correctly I decided to re-write the script. The sql command works as i have tested this out in a simple perl query script but when using this script it does create the top columns of a table but not the results can anyone tell me why please?

package TWiki::Plugins::FogbugzTablePlugin;

use strict;
use DBI;



use vars qw( $connection ); # cached connection

# DBI specification of the connection to the database
my $DB_PATH = "DBI:Sybase:server=192.168.70.56";
# DB user
my $DB_USER = 'twiki';
# DB user's password
my $DB_PASS = 'twiki';



# TWiki ihook

sub initPlugin {

    return 1;
}


# TWiki hook; this is run on the text being translated to HTML, and is
# your opportunity to expand any tags in that text.
sub commonTagsHandler {
    my($text, $topic, $web ) = @_;
    #$_[0] refers to the first parameter to this function
    $_[0] =~ s/\bFog(\d+)/_link_to_bug($1)/ge;
    $_[0] =~ s/%FOG{(.*?)}%/_show_bugs($1)/ge;
}

# Connect to the DB
sub _connect {
    my $this = shift;
    #DBI->trace(10);
    unless( $connection ) {
        $connection = DBI->connect( $DB_PATH,
                                    $DB_USER,
                                    $DB_PASS,
                                    {
                                        PrintError => 0,
                                        RaiseError => 1,
                                    });
    }
    return $connection;
}




sub _show_bugs {
   my $args = shift;

   my @headers = map { "*$_*" } qw/ ID Title Priority Name FixFor Stat
+e /;

   my $fmt = "| %s | %s | %s | %s | %s | %s |\n";

  #my $sql ="SELECT Bug.ixBug, Bug.sTitle, Priority.sPriority, Person.
+sFullName, FixFor.sFixFor, Status.sStatus FROM Bug, Priority, Person,
+ FixFor, Status WHERE (Bug.ixPriority=Priority.ixPriority) AND (Bug.i
+xPersonAssignedTo=Person.ixPerson) AND (Bug.ixStatus = Status.ixStatu
+s) AND (Bug.ixFixFor = FixFor.ixFixFor) AND (Bug.ixBug = 3500)";
  # my $sql ="SELECT Bug.ixBug, Bug.sTitle, FROM Bug";
   my $sql ="SELECT Bug.ixBug, Bug.sTitle, Bug.ixBug, Bug.sTitle, Bug.
+ixBug, Bug.sTitle FROM Bug WHERE Bug.ixBug = $args";


_connect();
my $sth =$connection->prepare($sql);
my @rows = $sth->fetchrow_array;
my @array = $sth->fetchrow_array;

return join '', map {sprintf $fmt, @$_ } \@headers, \@rows;


 $sth->finish;

}


sub _link_to_bug {
    my $bugid = shift;
    return '[[http://apwadev01/fogbugz/default.asp?'.$bugid.'][Fog'.$b
+ugid.']]';
}

1;
[download]

i also wrote this script here which check whether the db connection can be made and if it can get results back as well.
I know the twiki plugin works as the original script (where the initial problem was that the sql statement did not return the values in the look up tables but rather the foreign key values in the primary table instead so the results were meaningless. With the advice of the perlmonk i rewrote the perl statement and test it out here then incorporated this into a new script above) Anyway here is the basic script .

#!/usr/bin/perl

use strict;

use DBI;

use vars qw( $sth ); # cached connection

my $DB_PATH = "DBI:Sybase:server=192.168.70.56" ;
my $DB_USER = "twiki";
my $DB_PASS = "twiki";

my $dbh = DBI->connect($DB_PATH, $DB_USER, $DB_PASS)|| die "Couldn't c
+onnect to database";

print "Database connection established. \n";

my $args=<STDIN>;

my $sth = $dbh->prepare
(
my $sql ="SELECT Bug.ixBug, Bug.sTitle, Priority.sPriority, Person.sFu
+llName, FixFor.sFixFor, Status.sStatus FROM Bug, Priority, Person, Fi
+xFor, Status WHERE (Bug.ixPriority=Priority.ixPriority) AND (Bug.ixPe
+rsonAssignedTo=Person.ixPerson) AND (Bug.ixStatus = Status.ixStatus) 
+AND (Bug.ixFixFor = FixFor.ixFixFor) AND (Bug.ixBug = $args)");

$sth->execute;

#print out data

#my $headers =
#'| *ID* | *TITLE* | *PRIORITY* | *Assigned To* | *Fix For* | *Status*
+ |';
my $headers = map { "*$_*" } qw/ ID Title Priority Assigned To 'Fix Fo
+r' State /;
my $fmt = "| %s | %s | %s | %s | %s | %s | %s |\n ";

print "$headers \n";
my @row;
while ( @row = $sth->fetchrow_array() ) {
print "@row \n";
}



$dbh->disconnect;
[download]

Got it working thanks to cdarke, rodion, corion for all your help and tips. in sorting this out thanks again:)