Question

I have installed DatabasePlugin (TWiki 4.1.2 on Windows) and trying to connect to a MS SQL server. The line

%DATABASE_SQL{description="test" sql="SELECT * from authors"}%

gives an error:

DBI connect('database=pubs;host=sqlserver','sa',...) failed: [Microsoft][ODBC Driver Manager] Data source name 
not found and no default driver specified (SQL-IM002)(DBD: db_login/SQLConnect err=-1) at 
/twiki/lib/TWiki/Plugins/DatabasePlugin/Connection.pm line 31 at 
/usr/lib/perl5/site_perl/5.8/cygwin/DBI.pm line 652 DBI::__ANON__('undef', 'undef') called at 
/usr/lib/perl5/site_perl/5.8/cygwin/DBI.pm line 707

My LocalSite.cfg contains:

$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigSource} = 'Local';

and

$TWiki::cfg{Plugins}{DatabasePlugin}{Databases} =
[
   {
       description => 'test',
       driver => 'ODBC',
       hostname => 'sqlserver',
       database => 'pubs',
       sid => '',
       username => 'sa',
       password => 'sa123',
       table_name => 'authors',
   },
];

I have installed DBI and DBD::ODBC

What could be the problem?

Environment

-- ChengappaCB - 29 Jan 2008

Answer

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.

I cannot test this because I don't have a TWiki on Windows, but in Connection.pm, the connect code says:

   y $db = DBI->connect(
     "DBI:$this->{driver}:database=$this->{database};host=$this->{hostname}$sid",
            $this->{username}, $this->{password},
            {PrintError=>1, RaiseError=>1});

Normally, for ODBC, you also need a DSN (defined under Control Panel, Administrative Tools, Data Sources), and connect is called something like this:

   my $db = DBI->connect("DBI:$this->{driver}:my_dsn_name",
            $this->{username}, $this->{password},
            {PrintError=>1, RaiseError=>1});

-- SusanCassidy - 28 Feb 2008

Thanks Susan, I'll try and let you know if there is success

-- ChengappaCB - 29 Feb 2008

Maybe also Bugs:Item4892 is relevant. It seems that the description parameter must be the database name.

-- LarsEik - 02 Mar 2008

I was able to get rid of the error messages by changing the code in connection.pm for the sub connect to the code below. I don't get any results from my query:

%DATABASE_SQL{description="UPMetadata" command="Select tgt_table from dbo.s2target" format="$tgt_table<br />"}%

just the command displayed back...

code changed in connection.pm (replace sub connect with this)

sub connect {
    my $this = shift;

    unless ($this->{db}) {
        my $sid = $this->{sid} ? ";sid=$this->{sid}" : '';
#add odbc option wws
   if ($this->{driver} eq 'ODBC') {
        my $db = DBI->connect(
            "DBI:$this->{driver}:$this->{database};
            $this->{username}, $this->{password},
            {PrintError=>1, RaiseError=>1});
        if (! $db ) {
            die "Can't open database specified by description '$description'";
        }
        
    } else {
        my $db = DBI->connect(
            "DBI:$this->{driver}:database=$this->{database};host=$this->{hostname}$sid",
            $this->{username}, $this->{password},
            {PrintError=>1, RaiseError=>1});
        if (! $db ) {
            die "Can't open database specified by description '$description'";
        }
        
    }


        $this->{db} = $db;
    }
}

-- BillStandke - 27 Mar 2008

Closing this question after more than 30 days of inactivity, seems to be answered.

-- PeterThoeny - 01 May 2008

SusanCassidy's suggestion worked! Thanks a lot !

-- ChengappaCB - 10 Dec 2008