Question

I am successfully using the DatabasePlugin with an Oracle 10g Express Edition database server.

I now have data within a Microsoft SQL Server 2000 SP4 database called HEAT on an entirely different server that I need to query with the DatabasePlugin.

I have configured the LocalSite.cfg and created a test topic but I get a Perl error.

The TWiki markup of the test topic is as follows:

%DATABASE_SQL{description="heat_database" sql="SELECT Application, Manufacturer, AppOwner FROM Heat.Applications ORDER BY Application" 
header = "| *Application* | *Manufacturer* | *AppOwner* | *LeadUser1* | *LeadUser2* |" 
format="| $APPLICATION | $MANUFACTURER | $APPOWNER | $LEADUSER1 | $LEADUSER2 |"}%

The Perl error that the topic displays is as follows:

DBI connect('database=heat;host=abcnt01sql','sqlserver',...) failed: [Microsoft][ODBC Driver Manager] Data source name not 
found and no default driver specified (SQL-IM002) at D:/inetpub/twiki/lib/TWiki/Plugins/DatabasePlugin/Connection.pm line 33 
at D:/Perl/lib/DBI.pm line 646 DBI::__ANON__('undef', 'undef') called at D:/Perl/lib/DBI.pm line 701 
DBI::connect('DBI', 'DBI:ODBC:database=heat;host=abcnt01sql', 'sqlserver', 'dwgg3rwe', 'HASH(0x24a5b00)') called at 
D:/inetpub/twiki/lib/TWiki/Plugins/DatabasePlugin/Connection.pm line 33 
TWiki::Plugins::DatabasePlugin::Connection::connect('TWiki::Plugins::DatabasePlugin::Connection=HASH(0x162992c)') called 
at D:/inetpub/twiki/lib/TWiki/Plugins/DatabasePlugin/DATABASE_SQL.pm line 8 
TWiki::Plugins::DatabasePlugin::DATABASE_SQL::handle('TWiki::Plugins::DatabasePlugin::Connection=HASH(0x162992c)', 'undef') called at 
D:/inetpub/twiki/lib/TWiki/Plugins/DatabasePlugin.pm line 82 eval {...} called at 
D:/inetpub/twiki/lib/TWiki/Plugins/DatabasePlugin.pm line 76 TWiki::Plugins::DatabasePlugin::_dispatch('ODBC', 'undef') called at 
D:/inetpub/twiki/lib/TWiki/Plugins/DatabasePlugin.pm line 99 TWiki::Plugins::DatabasePlugin::commonTagsHandler('---++ Heat Database Query\x{a}\x{a}---+++ SQL Query

The database connection parameters in LocalSite.cfg are (the bottom array element is the one I'm getting the error with):

$TWiki::cfg{Plugins}{DatabasePlugin}{Databases} = [
                                                    {
                                                      'sid' => 'xe',
                                                      'hostname' => 'abc2k3vk85',
                                                      'description' => 'interfaces_database',
                                                      'username' => 'interface',
                                                      'database' => 'xe',
                                                      'password' => 'a7f6fd55we',
                                                      'table_name' => '',
                                                      'driver' => 'Oracle'
                                                    },
                                                    {
                                                      'database' => 'heat',
                                                      'password' => 'dwgg3rwe',
                                                      'hostname' => 'abcnt01sql',
                                                      'table_name' => '',
                                                      'description' => 'heat_database',
                                                      'username' => 'sqlserver',
                                                      'driver' => 'ODBC'
                                                    }
                                                  ];

I think its something to do with the wrong parameters being given to the DBI::connect method but as I said the plugin is fine with the Oracle database.

The following line that throws the error is:

DBI::connect('DBI', 'DBI:ODBC:database=heat;host=abcnt01sql', 'sqlserver', 'dwgg3rwe', 'HASH(0x24a5b00)') 

I think that from my googling, the DSN parameter should be DBI:ODBC:heat and there is no need for a host. heat is the name of the ODBC datasource I have created on my TWiki server, abc2k3vk85. The MSSQL database is also called heat and it lives on the Windows 2000 Server SP4 server called abcnt01sql. I've tried replacing abcnt01sql in the LocalSite.cfg parameters with abc2k3vk85 (where the ODBC DSN was created) but the same error comes up.

Can some genius please help?

Environment

-- JamesGMoore - 14 Aug 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.

You may need to look at switching to using the DBIQueryPlugin as you can explicitly define a DSN for your databases in it's configuration.

Have you checked that the Perl on your TWiki server can actually connect to this DSN?

-- DuncanKinnear - 01 Sep 2008

Closing after more than 30 days. Please reopen with more details if needed...

-- PeterThoeny - 06 Nov 2008