DBD::ODBC/DBI connection from Windows to Oracle

[DoraC]

Hi,

I'm trying to connect to our Oracle9 database via the Perl DBI (I'm using ActiveState Perl, and installed the DBI and DBD::ODBC via the ActiveState ppm).

Here's my basic connection attempt:

use DBI;

$DSN = 'driver={Microsoft ODBC for Oracle};database=MyDatabase';

$DBH = DBI->connect( "dbi:ODBC:$DSN"
                    ,"MyUserId"
                    ,"MyPassword"
                    ,{
                       RaiseError => 1
                      ,AutoCommit => 0 } )
    || die "couldnt connect to database...\n";

When I run this, I get the following error message:

DBI connect('driver={Microsoft ODBC for Oracle};database=MyDatabase','MyUserId',...) failed: [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

Is there another package/module I should be using for my ODBC connections? Searches on the internet seem to indicate that this is an Oracle issue in general, but it seems strange to me that connections to Oracle via Windows with Perl can't happen...

Any help greatly appreciated,
thanks!
dora c

 

[mactonio]

this is what I use and it works:

use DBI;

my $dbh = DBI->connect('DBI:Oracle:mydatabase')
or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare('SELECT * FROM people WHERE lastname = ?')
or die "Couldn't prepare statement: " . $dbh->errstr;

I think your problem might be in the substitution for $DSN

 

[mactonio]

or you could try and insert 'use Win32::ODBC ' in your code and see if that's the problem

 

[DoraC]

Hello Mactonio,

Thanks for the reply.

Which Oracle driver are you using - the "DBD::Oracle" driver? I first tried to use an Oracle driver I already had on my PC (the one I reference above), but that didn't work. I went to CPAN, and DBD::Oracle looks promising. However, when I try to install it I run into problems. Specifically, it seems to imply that I must have an Oracle installation on my machine. What I'm trying to do is access Oracle on a remote server.

Is there another driver I should use instead?

Thanks again,
dora c

 

[mactonio]

use DBI;

# this line tells where oracle is - path is just my example
$ENV{ORACLE_HOME} = "/pkgs2/oracle8.0.4/app/oracle/product/8.0.4";

$DBH = DBI->connect( 'dbiracle:mydatabase'
,"MyUserId"
,"MyPassword"
,{
RaiseError => 1
,AutoCommit => 0 } )
|| die "couldnt connect to database...\n";

not 100%, never connected to a remote oracle server, did it with mySql so if this does not work I'll send the script to connect to mysql and see if changing the driver would help

 

[MikeLacey]

You do need an Oracle client if you want to use DBD::Oracle.

ODBC should work alright though, I've used it in the past - Sounds as if you need a more recent version of the driver.

Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

It's like this; even samurai have teddy bears, and even teddy bears get drunk.