Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Buck (2837)

Buck
  buck@buckpyland.com
http://www.buckpyland.com/
AOL IM: BuckPyl (Add Buddy, Send Message)

Is there anything I haven't worked on/with? 8) I like Perl because it let's me code the way I think. Scary, huh?

Journal of Buck (2837)

Tuesday May 18, 2004
03:49 PM

Frustration with DBD::ADO and Oracle

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.

Saturday December 27, 2003
04:11 PM

LLLWF!

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? :)
Tuesday January 14, 2003
10:48 PM

Perl, Ruby and Python (Part Deux)

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