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;
Many thanks to all for all your help