Greetings brothers and sisters.

On my Centos box i have connected my perl is now talking to an MSSQL server (using freetds and DBD::Sybase drivers) now i can connect to a database and access the information on the table three of the fields in the table is a numeric integer (which is a primary key in a different table which has the string value) I need to get the string value in the lookuptable instead of the integer on the primary table.
I have written this script but its not working as it tries to look for the bugState, BugPri and bugAssign in the database.
The script does work when i take these values out as the rest of the values refer to the database specifically and i can return them perfectly ....can anyone help.

The error message i get is
SELECT bugid,sTitle,bugpri,bugassign,bugstate FROM Bug WHERE bugid IN (23) DBD::Sybase::st execute failed: Server message number=207 severity=16 state=3 line=1 server=APWADEV01 text=Invalid column name 'bugid'. Server message number=207 severity=16 state=3 line=1 server=APWADEV01 text=Invalid column name 'bugpri'. Server message number=207 severity=16 state=3 line=1 server=APWADEV01 text=Invalid column name 'bugassign'. Server message number=207 severity=16 state=3 line=1 server=APWADEV01 text=Invalid column name 'bugstate'. Server message number=207 severity=16 state=3 line=1 server=APWADEV01 text=Invalid column name 'bugid'. at /var/www/html/twiki/lib/TWiki/Plugins/FogbugzTablePlugin.pm line 125.
i know its looking at the database when it should be the indirect

SCRIPT HERE 

#
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 = '';
# DB user's password
my $DB_PASS = '';

# lookup i created
my $indirect = {
    bugstate => { table => 'Status',
                  index => 'ixStatus',
                  string => 'sStatus' },
    bugpri =>  { table => 'Priority',
                 index => 'ixPriority',
                 string => 'sPriority' },
    bugassign => { table => 'Person_1',
                   index => 'ixPerson',
                   string => 'sFullName' }
   };

# TWiki ihook
sub initPlugin {
    return 1;
}


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;

    unless( $connection ) {
#        DBI->trace(10);
        $connection = DBI->connect( $DB_PATH,
                                    $DB_USER,
                                    $DB_PASS,
                                    {
                                        PrintError => 0,
                                        RaiseError => 1,
                                    });
    }

    return $connection;
}

# Look up a string in another table, as indicated by the indirection
# table in $indirect. If the string isn't indirected, simply return it
+.
# Note that this could also be done by compiling a smarter query, but 
+in
# most cases this is quite fast enough, and demonstrates the principle
# nicely.
sub _lookup {
    my( $db, $fieldName, $row ) = @_;

    my $indy = $indirect->{$fieldName};
    if( $indy ) {
        my $query = "SELECT $indy->{string} FROM $indy->{table} ".
          "WHERE $indy->{index}=$row->{$fieldName}";
        my $sth = $db->prepare( $query );
        eval { $sth->execute(); };
        die "$query $@" if $@;
        my $row = $sth->fetchrow_hashref();
        return $row->{$indy->{string}};
    } else {
        return $row->{$fieldName};
    }
}



sub _show_bugs {
    my $args = shift;

    my $headers =
      '| *ID* | *Title* | *Priority* | *Assignment* | *State* |';

    my $format =
      '| fog$bugid | $sTitle | $bugpri | $bugassign | $bugstate |';

    my $query = 'SELECT ';
    my $fieldsel = $format;
    my @fieldset;
    while( $fieldsel =~ s/\$(\w+)// ) {
        push( @fieldset, $1 );
    }
    $query .= join(',', @fieldset );
    $query .= " FROM Bug";

    $query .= ' WHERE bugid IN ('.$args.')';

    my $db = _connect();
    my $sth = $db->prepare( $query );
    eval { $sth->execute(); };
    die "$query $@" if $@;

    my $result = $headers;
    my $table = "$headers\n";
    while( my $row = $sth->fetchrow_hashref() ) {
        my $fields = $format;
        $fields =~ s/\$(\w+)/_lookup($db, $1, $row)/ge;
        $table .= "$fields\n";
    }
    return $table;
}

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

1;

[download]

Many thanks to all for all your help

In reply to Can get values from db but cannot get related values from the relevant lookup table 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.