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.
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/
dbi-link: http://dbi-link.projects.postgresql.org/
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
Glad to hear. :) Thanks.
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.
Accordingly, the report gives top to bottom cross-portion examination for the man-made brainpower advertise and groups it into different levels, in this manner giving significant bits of knowledge on full scale just as small scale level. artificial intelligence course