Saturday, February 18, 2012

Postgresql dbi-link to access Sql Server Database

3 comments

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

3 Responses so far

  1. To tell you the teuth I was passing around and come across your site. It is wonderful. I mean as a content and design. I added you to my list and decided to spent the rest of the weekend browsing. Well done! Plagiarism detection service

  2. AI Rumman says:

    Hi, unfortunately, I don't understand your language except the global technical term. However, translator helped me, are you asking -
    where I do the acion?
    Create extension plperlu;

    If I am right, then you should create this extension in the database for which you are trying to use dbi-link.

Leave a Reply

Labels