Postgresql dbi-link to access Sql Server Database

Postgresql database can be configured to fetch data from Sql Server tables using dbi-link.

DBI-Link is a partial implementation of the SQL/MED portion of the SQL:2008 specification written in PL/Perl. It communicates with Sql Server through DBD-Sybase perl module from CPAN.
Before that however, we must first compile the freetds library. FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases.

The DBD::Sybase module is built on top of the Sybase Open Client Client Library API.

In this test, I used Postgresql 9.1.2 and Sql Server 2005.

Following steps describe how we can configure Postgresql database to fetch data from Sql Server 2008:

STEP 1: Freetds Install

wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz

tar -zxvf freetds-stable.tgz

cd freetds-0.91
./configure --prefix=/usr/local/freetds --with-tdsver=7.0
make
make install

vi freetds.conf
...
[testserver]
        host=10.0.0.1
        port=1433
        tds version = 8.0
...
:wq

Check if the installation is successful:

# /usr/local/freetds/bin/tsql -S teseserver -U testuser
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

STEP 2: Compiling DBD::Sybase

wget http://search.cpan.org/CPAN/authors/id/M/ME/MEWP/DBD-Sybase-1.14.tar.gz

tar -zxvf DBD-Sybase-1.14.tar.gz

cd DBD-Sybase-1.14

vi CONFIG

..

SYBASE=/usr/local/freetds

...


perl Makefile.pl

make

make install

Check if the installation is successful:

Create a perl script named first.pl.

#!/usr/bin/perl

use DBI;

$dsn = 'DBI:Sybase:server=testserver';

my $dbh = DBI->connect($dsn, "testuser", 'std123');
die "unable to connect to server $DBI::errstr" unless $dbh;

$dbh->do("use testdb");

$query = "SELECT * FROM testdb.dbo.school_test";
$sth = $dbh->prepare ($query) or die "prepare failed\n";
$sth->execute( ) or die "unable to execute query $query   error $DBI::errstr";

$rows = $sth->rows ;
print "$row rows returned by query\n";

while ( @first = $sth->fetchrow_array ) {
   foreach $field (@first) {
      print "field: $field\n";
   }
}

#perl /tmp/first.pl
 rows returned by query
field: 1
field:   A school
field: 2
field:  B High
field: 3
field:  C  Academy
field: 4
field:  D Academy

STEP 3: Dwonload dbi-link

http://pgfoundry.org/projects/dbi-link/

Download dbi-link from

wget http://pgfoundry.org/frs/download.php/1235/dbi-link-2.0.0.tar.bz2


tar -jxvf dbi-link-2.0.0.tar.bz2

Step 4: Configure dbi-link

psql>
create extension plperlu;


\i /usr/local/src/dbi-link-2.0.0/dbi_link.sql


UPDATE
    pg_catalog.pg_settings
SET
    setting = '"$user",public'
        CASE WHEN 'dbi_link' = ANY(string_to_array(setting, ','))
        THEN setting
        ELSE 'dbi_link,' || setting

UPDATE
    pg_catalog.pg_settings
SET
    setting = '"$user",public'
WHERE
    name = 'search_path'
;

SELECT dbi_link.make_accessor_functions(
    'dbi:Sybase:testserver'::dbi_link.data_source,
    'dswebuser'::text,
    'std123'::text,
    '---
AutoCommit: 1
RaiseError: 1
'::dbi_link.yaml,
    NULL::dbi_link.yaml,
    NULL::text,
    NULL::text,
    'testserver_mssql'::text
);

Check if the installation is successful:

select * from dbi_link.dbi_connection;
 data_source_id |  data_source  | user_name |  auth  | dbh_attributes | remote_schema | remote_catalog | local_schema
----------------+---------------+-----------+--------+----------------+---------------+----------------+--------------
              1 | dbi:Sybase:testserver | testuser | std123 | ---           +|               |                | testserver_mssql
                |               |           |        | AutoCommit: 1 +|               |                |
                |               |           |        | RaiseError: 1 +|               |                |
                |               |           |        |                |               |                |

data_source_id = 1

SELECT * from dbi_link.remote_select(
    1,
    'select school_name from testdb.dbo.school_test'::text
    ) as c(school_name text)

school_name                
-------------------------------------------
  A School
  B High
  C Academy
  D Academy
(4 rows)

References

Freetds: http://www.freetds.org/

DBD-Sybase: http://search.cpan.org/~mewp/DBD-Sybase-1.14/Sybase.pm#NAME

dbi-link:  http://dbi-link.projects.postgresql.org/