Buck's Journal

Frustration with DBD::ADO and Oracle

Buck — 2004-05-18T20:49:21+00:00

I've been trying to use DBD::ADO 2.84 (the latest available via ActiveState PPM). I wrote up a simple script to perform a SELECT from Oracle 8.1.7:

use strict; use DBI qw(:sql_types); my $userid = "****"; my $password = "****"; my $sql = 'SELECT SYSDATE FROM DUAL'; my $dsn = "Provider=OraOLEDB.Oracle;Data Source=****;"; unlink 'dbitrace.log' if -e 'dbitrace.log'; DBI->trace(1, 'dbitrace.log'); my $dbh = DBI->connect("dbi:ADO:$dsn", $userid, $password, { RaiseError => 1, AutoCommit => 0 }); my $sth = $dbh->prepare($sql); $sth->execute(); $sth->dump_results(); $sth->finish(); $dbh->disconnect();

Up to the execute(), it's OK. I then get the error:

>perl -w OneWorld_Table_Description.pl Use of uninitialized value in subroutine entry at E:/Perl/site/lib/DBI.pm line 587. DBD::ADO::st execute failed: Can't execute statement 'SELECT SYSDATE FROM DUAL': Description : Multiple-step operation completed with one or more errors. Check each status value. HelpContext : HelpFile : NativeError : Number : 265946 Source : OraOLEDB SQLState : at OneWorld_Table_Description.pl line 42. DBD::ADO::st execute failed: Can't execute statement 'SELECT SYSDATE FROM DUAL': Description : Multiple-step operation completed with one or more errors. Check each status value. HelpContext : HelpFile : NativeError : Number : 265946 Source : OraOLEDB SQLState : at OneWorld_Table_Description.pl line 42. >Exit code: 255

The output from the DBI trace (level 1):

DBI 1.42-ithread default trace level set to Ox1/0 (in pid 1876) -> DBI->connect(dbi:ADO:Provider=OraOLEDB.Oracle;Data Source=****;, ****, ****, HASH(0x15d5700)) -> DBI->install_driver(ADO) for MSWin32 perl=5.008003 pid=1876 ruid=0 euid=0 install_driver: DBD::ADO version 2.84 loaded from E:/Perl/site/lib/DBD/ADO.pm <- install_driver= DBI::dr=HASH(0x1b57384) ->ADO Connection: <- STORE('Provider' 'OraOLEDB.Oracle')= 'OraOLEDB.Oracle' at ADO.pm line 146 ->> Storing Provider OraOLEDB.Oracle ->> Open ADO connection using Data Source=**** ->> Transaction support: 2 Transactions can contain DML statements. DDL statements within a transaction cause the transaction to be committed. 1 <> FETCH('Warn')= 0 ('Warn' from cache) at ADO.pm line 1290 <- STORE('Active' 1)= 1 at ADO.pm line 208 1 <> FETCH('AutoCommit')= 1 ('AutoCommit' from cache) at ADO.pm line 1301 1 <> FETCH('Warn')= 0 ('Warn' from cache) at ADO.pm line 1290 <- connect= DBI::db=HASH(0x1cc814c) 1 <> FETCH('Warn')= 0 ('Warn' from cache) at ADO.pm line 1290 <- STORE('LongReadLen' 0)= 1 at ADO.pm line 450 <- STORE('LongTruncOk' 0)= 1 at ADO.pm line 451 <- STORE('RowsInCache' 0)= 0 at ADO.pm line 456 <> FETCH('ado_conn')= Win32::OLE=HASH(0x1cc847c) ('ado_conn' from cache) at ADO.pm line 1384 <> FETCH('ado_comm')= Win32::OLE=HASH(0x15d57c0) ('ado_comm' from cache) at ADO.pm line 1385 <> FETCH('Statement')= ( 'SELECT SYSDATE FROM DUAL' ) [1 items] ('Statement' from cache) at ADO.pm line 1387 <- STORE('NUM_OF_PARAMS' 0)= 1 at ADO.pm line 1404 1 <> FETCH('Statement')= 'SELECT SYSDATE FROM DUAL' ('Statement' from cache) at ADO.pm line 1459 !! ERROR: -1 'Can't execute statement 'SELECT SYSDATE FROM DUAL': Description : Multiple-step operation completed with one or more errors. Check each status value. HelpContext : HelpFile : NativeError : Number : 265946 Source : OraOLEDB SQLState : ' (err#1) 1 <- set_err(-1 'Can't execute statement 'SELECT SYSDATE FROM DUAL': Description : Multiple-step operation completed with one or more errors. Check each status value. HelpContext : HelpFile : NativeError : Number : 265946 Source : OraOLEDB SQLState : ')= undef at ADO.pm line 1568 <- destroy statement handler -> destroy statement handler -- State: 1 -- Modified ADO Connection Attributes: 0 -- AutoCommit: 0, Provider Support: 2, Comments: Transactions can contain DML statements. DDL statements within a transaction cause the transaction to be committed.

I'm running 5.8.3.809 of ActiveState's Perl. I looked on perl.dbi.users with no luck. I'd post this to the list, but my company's spam filter would snarf it. :( Any ideas?

Update (21-May-2004): I tried to build DBD::Oracle via CPAN. It failed tests on long and ph_types which included a Memory Violation. Long story short, I downgraded to ActiveState's 5.6.1 build 638, and installed the latest available DBI amd DBD::Oracle via PPM. Now, I'm "shittin' in tall cotton". :) Thanks, to everyone who commented.

LLLWF!

Buck — 2003-12-27T21:11:26+00:00

Today's my birthday as well as KM's. Mine happens to be the Big Four-O; hard to believe I made it this far. So, what did you get me? Where's my present, dammit? :)

Perl, Ruby and Python (Part Deux)

Buck — 2003-01-15T03:48:40+00:00

Continuing with my comments made here, here's the source for the Perl and Ruby scripts I used to test with. The Python version is included for ... completeness (I'm being polite, here :).

#!/usr/bin/perl my $target = shift; for (my $iter = 0; $iter < 100; $iter++) {   my $count = 0;   open(INDEX, "/usr/ports/INDEX");   while (<INDEX>)   {     chomp;     my @fields = split/\|/;     if ($fields[0] =~ m{$target} || $fields[6] =~ m{$target} ||         $fields[3] =~ m{$target} || $fields[7] =~ m{$target} ||         $fields[8] =~ m{$target})     {        $count++;     }   }   close INDEX; }

#!/usr/local/bin/ruby # distribution-name|port-path|installation-prefix|comment| \ # description-file|maintainer|categories|build deps|run deps|www site target = ARGV[0] 100.times do |iter|   index = File.new("/usr/ports/INDEX")   count = 0   index.each do |line|     fields = line.chomp.split('|')     if fields[0] =~ /#{target}/ || fields[6] =~ /#{target}/ || \         fields[3] =~ /#{target}/ || fields[7] =~ /#{target}/ || \         fields[8] =~ /#{target}/       count += 1     end   end   index.close end

#!/usr/local/bin/python import sys import string import re target = re.compile(sys.argv[1]) for iter in range(100):     index = open('/usr/ports/INDEX', 'r')     count = 0     line = index.readline()     while line <> '' :         fields = line.strip().split('|')         if target.search(fields[0]) or target.search(fields[6]) \                  or target.search(fields[3]) or target.search(fields[7]) \                  or target.search(fields[8]):              count = count + 1         line = index.readline()     index.close()

An example of the data used (from /usr/ports/INDEX in FreeBSD)
# distribution-name|port-path|installation-prefix|comment| \ # description-file|maintainer|categories|build deps|run deps|www site 9e-1.0|/usr/ports/archivers/9e|/usr/local|Explode Plan9 archives|/usr/ports/archivers/9e/pkg-descr|ports@FreeBSD.Org|archivers|||http:// www.eecs.harvard.edu/~wkj/Software/9e/ arc-5.21e.8_1|/usr/ports/archivers/arc|/usr/local|Create & extract files from DOS .ARC files|/usr/ports/archivers/arc/pkg-descr|ache@FreeBSD.org|archivers||| arj-3.10b|/usr/ports/archivers/arj|/usr/local|Open-source ARJ|/usr/ports/archivers/arj/pkg-descr|kot@premierbank.dp.ua|archivers|autoconf2 13-2.13.000227_5 expat-1.95.5 gettext-0.11.5_1 gmake-3.80 libiconv-1.8_2 m4-1.4_1||http://arj.sourceforge.net/ bzip-0.21|/usr/ports/archivers/bzip|/usr/local|A block-sorting file compressor|/usr/ports/archivers/bzip/pkg-descr|ports@FreeBSD.org|archivers|||htt p://www.muraroa.demon.co.uk/ bzip2-1.0.2|/usr/ports/archivers/bzip2|/usr/local|A block-sorting file compressor|/usr/ports/archivers/bzip2/pkg-descr|jharris@widomaker.com|archivers| ||http://sources.redhat.com/bzip2/ cabextract-0.6|/usr/ports/archivers/cabextract|/usr/local|A program to extract Microsoft cabinet (.CAB) files|/usr/ports/archivers/cabextract/pkg-descr|sobomax@FreeBSD.org|archivers|li bgnugetopt-1.2|libgnugetopt-1.2|http://www.kyz.uklinux.net/cabextract.php3

The average runtimes, minutes and seconds, have been (for running each of the above scripts once):
Perl: 0:35
Python: 0:38
Ruby: 2:29