Home: Perl Programming Help: DBI:
Problem with DBI Link



rivo
New User

Feb 2, 2009, 3:20 AM


Views: 11334
Problem with DBI Link

Hi, I'm trying to execute a DBI Link script in a postgresql database, but the follow error is returned:

dbhab_teste=# \i dbi_teste_ora.sql
SELECT dbi_link.make_accessor_functions(
'dbi:Oracle:sid=veides2;host=172.25.136.85'::dbi_link.data_source,
'abc'::text,
'def'::text,
'---
AutoCommit: 1
RaiseError: 1
'::dbi_link.yaml,
NULL::dbi_link.yaml,
NULL::text,
NULL::text,
'veides2'::text
);
psql:dbi_teste_ora.sql:13: NOTA: Setting bail in %_SHARED hash. at line 25.

CONTEXTO: comando SQL "SELECT dbi_link.dbi_link_init()"
psql:dbi_teste_ora.sql:13: NOTA: Setting quote_literal in %_SHARED hash. at line 25.

CONTEXTO: comando SQL "SELECT dbi_link.dbi_link_init()"
psql:dbi_teste_ora.sql:13: NOTA: Setting get_connection_info in %_SHARED hash. at line 25.

CONTEXTO: comando SQL "SELECT dbi_link.dbi_link_init()"
psql:dbi_teste_ora.sql:13: NOTA: Setting quote_ident in %_SHARED hash. at line 25.

CONTEXTO: comando SQL "SELECT dbi_link.dbi_link_init()"
psql:dbi_teste_ora.sql:13: NOTA: Setting get_dbh in %_SHARED hash. at line 25.

CONTEXTO: comando SQL "SELECT dbi_link.dbi_link_init()"
psql:dbi_teste_ora.sql:13: NOTA: Setting remote_exec_dbh in %_SHARED hash. at line 25.

CONTEXTO: comando SQL "SELECT dbi_link.dbi_link_init()"
psql:dbi_teste_ora.sql:13: NOTA: SELECT dbi_link.set_up_connection(
'dbi:Oracle:sid=veides2;host=172.25.136.85',
'abc',
'def',
'---
AutoCommit: 1
RaiseError: 1
',
NULL,
NULL,
NULL,
'veides2'
)

psql:dbi_teste_ora.sql:13: NOTA: SELECT count(*) AS "driver_there"
FROM dbi_link.available_drivers()
WHERE available_drivers = 'dbi:Oracle:sid=veides2;host=172.25.136.85'

CONTEXTO: comando SQL "SELECT dbi_link.set_up_connection(
'dbi:Oracle:sid=veides2;host=172.25.136.85',
'abc',
'def',
'---
AutoCommit: 1
RaiseError: 1
',
NULL,
NULL,
NULL,
'veides2'
)
"
psql:dbi_teste_ora.sql:13: ERRO: error from Perl function "make_accessor_functions": error from Perl function "set_up_connection": install_driver(Oracle) failed: Can't load '/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: File cannot be found at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/DynaLoader.pm line 230.
at line 3
Compilation failed in require at line 3.
Perhaps a required shared library or dll isn't installed where expected
at line 37 at line 35.

Someone said that can be a problem in the search paths of perl...
Can anyone help to solve this?
thanks

Cool


(This post was edited by rivo on Feb 2, 2009, 3:21 AM)


FishMonger
Veteran / Moderator

Feb 3, 2009, 5:33 AM


Views: 11316
Re: [rivo] Problem with DBI Link

You say that you're working with a postgresql database, but the error relates to an Oracle database. Which are you really working with?

Can you show us the Perl script?

Based on the error, it would seem that DBD::Oracle isn't installed or it was not installed correctly.


rivo
New User

Feb 3, 2009, 8:58 AM


Views: 11308
Re: [FishMonger] Problem with DBI Link

Hi,

I'm installed DBI Link in a Postgresql database, now I'm trying to access the oracle database using psql (trying to make this using DBI Link). So, what I want is execute a query on a oracle database from a postgres database (using psql).
The script is listed below:

SELECT dbi_link.make_accessor_functions(
'dbi:Oracle:sid=veides2;host=172.25.136.85'::dbi_link.data_source,
'abc'::text,
'efg'::text,
'---
AutoCommit: 1
RaiseError: 1
'::dbi_link.yaml,
NULL::dbi_link.yaml,
NULL::text,
NULL::text,
'veides2'::text
);

Sometimes, the psql freeze...
The DBD::Oracle is installed.


FishMonger
Veteran / Moderator

Feb 3, 2009, 9:23 AM


Views: 11305
Re: [rivo] Problem with DBI Link

So, do you have a perl script?

I haven't used psql or Oracle (I use mysql and pervasive) and I'm not sure what you mean by "installed DBI Link in a Postgresql database".


rivo
New User

Feb 3, 2009, 9:35 AM


Views: 11302
Re: [FishMonger] Problem with DBI Link

I have a perl script, used to test if DBI Link works on perl, and yes, it works, see the script below:

#!/usr/bin/env perl
use strict;
use warnings;

use DBI;

my $dbh = DBI->connect(
'dbi:Oracle:sid=veides2;host=172.25.136.85', # Connection info
'abc', # User
'def', # Password
{
AutoCommit => 1,
RaiseError => 1,

}
) or die "Could not connect to Oracle: $DBI::errstr";

my $sql = qq{ SELECT * FROM emilio.locadora WHERE ano_fab = 2005 };
my $sth = $dbh->prepare( $sql );
$sth->execute();

my( $cod_placa, $num_cnpj, $dsc_marca, $dsc_tipo, $ano_fab, $dat_alter );
$sth->bind_columns( undef, \$cod_placa, \$num_cnpj, \$dsc_marca, \$dsc_tipo, \$ano_fab, \$dat_alter );

while( $sth->fetch() ) {
print "$cod_placa, $dsc_marca, $ano_fab\n";
}

$sth->finish();

$dbh->disconnect();

-- end of script

but the sql file that I want to run in postgres not works

SELECT dbi_link.make_accessor_functions(
'dbi:Oracle:sid=veides2;host=172.25.136.85'::dbi_link.data_source,
'abc'::text,
'def'::text,
'---
AutoCommit: 1
RaiseError: 1
'::dbi_link.yaml,
NULL::dbi_link.yaml,
NULL::text,
NULL::text,
'veides2'::text
);

and excuse my english, but I dont speak english very well


(This post was edited by rivo on Feb 3, 2009, 9:36 AM)


FishMonger
Veteran / Moderator

Feb 3, 2009, 10:08 AM


Views: 11295
Re: [rivo] Problem with DBI Link

Since the perl script works correctly but the other sql file that is executed in postgres doesn't, then the problem you're experiencing is related to postgres and/or that other sql script, not perl.

You will probably need to ask this on a forum that is dedicated to postgres rather than perl.