Item8148: ODBC support for DatabasePlugin and fix to not error on NULL fields - and fix for Sybase and MSSQL

Using the Plugin Version: 0 (28 nov. 2008) on Foswiki-1.0.4, Thu, 19 Mar 2009, build 3201, Plugin API version 2.0

I found that out of the box, DatabasePlugin wouldn't work using an ODBC connection. So (apart from having to download freeTDS, DBD::ODBC and make with odbc support to give me libtsodbc.so.0) I looked around t'internet and found some other people had made changes to the original twiki/foswiki code. I've taken those, along with the sybase fix and put it in my version. I've also included a fix to spweing errors in the http logs when you reach a NULL field ( shows error message like view: Use of uninitialized value in substitution iterator) and being able to use functions in the output format (e.g. $format="$count(name)").

Note - I am doubling up the use of the sid variable as a DSN for ODBC.

myserver # diff -c Connection.pm Connection.pm.orig
*** Connection.pm     Wed Apr 29 13:33:17 2009
--- Connection.pm.orig  Fri Nov 28 14:09:06 2008
***************
*** 27,48 ****

      unless ( $this->{db} ) {
          my $sid = $this->{sid} ? ";sid=$this->{sid}" : '';
!       my $driver = $this->{driver};
!       my $hoststring="host";
!       my $connectstring="DBI:";
!
!       if ( $driver =~ /Sybase/i ) { $hoststring = "server"; }
!       if ( $driver eq 'ODBC' ) {
!               $connectstring = $connectstring."$this->{driver}:$this->{sid}";
!       } else {
!               $connectstring = $connectstring."$this->{driver}:datbase=$this->{database};$hoststring=$this->{hostname}$sid";
!       }
!
!       # not quite sure why, but don't put this next line within an if statement as that breaks it
!       my $db = DBI->connect($connectstring, $this->{username}, $this->{password},
!                       { PrintError => 1, RaiseError => 1 });
          if ( !$db ) {
!             die "Can't open database specified by description '$this->{description}'";
          }

          $this->{db} = $db;
--- 27,41 ----

      unless ( $this->{db} ) {
          my $sid = $this->{sid} ? ";sid=$this->{sid}" : '';
!
!         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;
myserver # diff -c DATABASE_SQL.pm DATABASE_SQL.pm.orig
*** DATABASE_SQL.pm   Wed Apr 29 13:24:34 2009
--- DATABASE_SQL.pm.orig        Fri Nov 28 14:09:06 2008
***************
*** 22,31 ****

              # reverse sort so we handle longer keys first
              foreach my $k ( reverse sort keys %$res ) {
!               my $thisres = defined $res->{$k} ? $res->{$k} : '';
!               ($z = $k ) =~ s/\(/[\(]/g;
!               ($z = $k ) =~ s/\(/[\(]/g;
!                 $row =~ s/\$$z/$thisres/g;
              }
              $result .= $row . $separator;
          }
--- 22,28 ----

              # reverse sort so we handle longer keys first
              foreach my $k ( reverse sort keys %$res ) {
!                 $row =~ s/\$$k/$res->{$k}/g;
              }
              $result .= $row . $separator;
          }
myserver # 

I hope that these can help someone else and maybe get included (with whatever cleanup is required) in an updated version.

As an example, the Database setting in my LocalSite.cfg looks like this (pertinent pieces santised for security);

$Foswiki::cfg{Plugins}{DatabasePlugin}{Databases} = [
          {
            # hack to use the sid as a DSN when driver=ODBC
            'sid' => 'HPQC',
            'hostname' => 'myremoteserver',
            'description' => 'QualityCenter',
            'username' => 'Readonly',
            'database' => 'my_db',
            'password' => 'cleartextpassword',
            'table_name' => 'BUG',
            'driver' => 'ODBC'
          }
        ];

-- SallyHoughton - 29 Apr 2009

I've also made the following change so that I can extract large field entries from my ODBC database without being limited to using CAST and only 8000 characters. It would be nice to have this as a tunable, but I can live without that...20000 seems to be a high enough limit for me right now.

Basically I added a single line to the Connection.pm script;

fnet-rio1 # diff -c Connection.pm Connection.pm.orig
*** Connection.pm       Wed May  6 14:11:53 2009
--- Connection.pm.orig  Wed May  6 14:07:27 2009
***************
*** 44,50 ****
          if ( !$db ) {
              die "Can't open database specified by description '$this->{description}'";
          }
-       if ( $driver eq 'ODBC' ) { $db->{LongReadLen} = 20000; }

          $this->{db} = $db;
      }
--- 44,49 ----

-- SallyHoughton - 06 May 2009

Many thanks! Just in case anyone else is searching for how to get DatabasePlugin to work with MSSQL (Microsoft SQL Server), the "sybase fix" mentioned above is needed if your configuration setting for DatabasePlugin has 'driver' => 'Sybase'. (The fix is that Connection.pm has to say "server=" instead of "host=" when it does the DBI->connect.)

-- StevenKrahn - 26 Mar 2010

I've tested this again on a fresh installation of 1.1.0, and it is still true that you need the above fix in order to use Microsoft SQL Server with DatabasePlugin

-- StevenKrahn - 20 Oct 2010

I just fixed the warnings on NULL fields as Item11363

-- FlorianSchlichting - 19 Dec 2011

I find unified diffs (diff -u) much easier to read. Here's how I understand your patch:

--- a/DatabasePlugin/lib/Foswiki/Plugins/DatabasePlugin/Connection.pm
+++ b/DatabasePlugin/lib/Foswiki/Plugins/DatabasePlugin/Connection.pm
@@ -30,15 +32,26 @@ sub connect {
 
     unless ( $this->{db} ) {
         my $sid = $this->{sid} ? ";sid=$this->{sid}" : '';
+        my $data_source = "DBI:$this->{driver}:";
+        my $hoststring = 'host';
+
+        if ($this->{driver} =~ /Sybase/i) {
+            $hoststring = 'server';
+        }
+        if ($this->{driver} eq 'ODBC') {
+            $data_source .= "$this->{sid}";
+        } else {
+            $data_source .= "database=$this->{database};$hoststring=$this->{hostname}$sid";
+        }
 
         my $db = DBI->connect(
-"DBI:$this->{driver}:database=$this->{database};host=$this->{hostname}$sid",
+            $data_source,
             $this->{username},
             $this->{password},
             { PrintError => 1, RaiseError => 1 }
         );
         if ( !$db ) {
-            die "Can't open database specified by description '$description'";
+            die "Can't open database specified by description '$this->{description}'";
         }
 
         $this->{db} = $db;

The last chunk is an independent bugfix.

I don't like how sid is abused for something unrelated; probably a new variable, odbcdsn or the like, should be used.

Regarding LongReadLen, why is that only used for ODBC databases? And is that really useful to have in general, given that the standard formatting puts limits on the amount of text that can be displayed in a sensible manner? I guess I haven't fully understood your use case...

-- FlorianSchlichting - 19 Dec 2011

Commenting is disabled while not logged in