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

In reply to Simple perl script creates a table but no SQL results ..why? by yoyomonkey

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.