I was wondering if anyone can help the script below does work but returns dates from the MSSQL server as "Mar 01 2007 12:00AM " I want it to return it as 01/03/07.
I have tried changing the DATEFORMAT but nothing seems to work .
I have also been trying with a test script which is further below and it returns the date in the wrong format. Can anyone please point me in the right direction??
I have # out the statements that are the problem here I just don't know where i am going wrong with them .
Thanks
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 = 'user';
# DB user's password
my $DB_PASS = 'password';
# 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] =~ s/%CurrentReleases%/_show_releases($1)/ge; #substitution
+GREP command
}
# Connect to the DB
sub _connect {
my $this = shift;
unless( $connection ) {
$connection = DBI->connect( $DB_PATH,
$DB_USER,
$DB_PASS,
{
PrintError => 0,
RaiseError => 1,
});
}
return $connection;
}
sub _show_releases {
my $args = shift;
my @headers = map { "*$_*" } qw/ Product Target_Feature_Freeze Targ
+et_CCRB Target_DEV_Release Target_RTM/;
my $fmt = "| %s | %s | %s | %s | %s | \n";
_connect();
my $format = "DMY1_YYYY";
#my $sth->prepare( "set dateformat dmy" );
my $sth =$connection->prepare(my $sql = "SELECT Release, dtTargetF
+eatureFreeze, dtTargetCCRB, dtTargetReleaseFromDev, dtTargetRTM, Twik
+iURL, ReleaseNum, Display FROM ReleaseDates WHERE (Display = 1)");
#$sth->do( "set dateformat dmy" ) || die $sth->$errstr;
$sth->execute;
my $result = sprintf $fmt, @headers;
while (my @rows = $sth->fetchrow_array() ) {
$rows[0]="<a class=\"twikilink\" href=\"$rows[5]\">$rows[0]</A>";
$result .= sprintf $fmt, @rows;
}
$sth->finish;
return $result;
}
1;
TEST SCRIPT
#! /usr/bin/perl -w
#
# Small exmaple tool for DB connection
# Written by Jazz 2007
use DBI; #Load DBI Module
## Attributes to pass DBI-connect() to disable automatic error checkin
+g.
my %attr = (
PrintError => 1,
RaiseError => 1,
);
## Perform the connect using the Sybase Driver
my $datasource = 'DBI:Sybase:192.168.70.56';
my $user = "user";
my $password = "password";
my $dbh = DBI->connect($datasource,$user,$password)
or die "Cant connect to Database: $DBI::errstr\n";
# my $dbh = DBI->connect($datasource,$user , $password)
# or die "Cant connect to Database: $DBI::errstr\n";
# Prepare a SQL statement for execution
my $sth = $dbh->prepare_cached("SELECT * FROM ReleaseDates");
$sth->DATEFORMAT('dmy');
#execute statement in the DB
$sth->execute
or die "Can't prepare SQL statement : $sth->errstr\n";
#return rows of data
while (my @row = $sth->fetchrow_array() ) {
print "Row: @row\n";
}
warn "Problem in fetchrow_array(): ", $sth->errstr(),"\n"
if $sth->err();
## Disconnect from db
$dbh->disconnect
or warn "Failed to disconnect: ",$dbh->errstr(),"\n";
exit;
What can i do to get the right format of date out of it?
Many thanks again for all your help